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()?

Cromlech

Answer 1

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.

Malnutrition