psycopg2 difference between AsIs and sql module

Question

To choose dynamically a table name in a query I used to use AsIs() from psycopg2.extensions ( http://initd.org/psycopg/docs/extensions.html#psycopg2.extensions.AsIs ), with the following syntax:

cur.execute("SELECT * FROM %s WHERE id = %s;", (AsIs('table_name'), id))

However, the documentation now recommends to use the new psycopg2.sql module available in version 2.7 ( http://initd.org/psycopg/docs/sql.html#module-psycopg2.sql ) with the following syntax:

from psycopg2 import sql

cur.execute(
    sql.SQL("SELECT * FROM {} WHERE id = %s;")
        .format(sql.Identifier('table_name')), (id, )

What's the difference between those two options besides the fact that objects exposed by the sql module can be passed directly to execute()?

piro · Accepted Answer · 2017-03-23 15:13:43Z

9

AsIs is... as it is. It won't perform any escape of the table name, if it contains characters need quoting. The objects in the sql module instead know what is an identifier.

More subtly, AsIs is for parameter values only: if currently works is mostly an implementation accident and in the future the behaviour may change. Query values should not be used to represent variable parts of the query, such as table or field names.

answered Mar 23, 2017 at 15:13

piro

14k6 gold badges39 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Prashantkumar K B Jan 16 at 9:03

if i want to pass '>' or '<' symbol through parameter, then AsIs will be useful. But in psycopg3 AsIs function got removed. Any alternative for this?

Collectives™ on Stack Overflow

psycopg2 difference between AsIs and sql module

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related