Why does PostgreSQL cursor.execute() command add single quotes?

Question

Simple SQL statement:

sql = "DROP TABLE IF EXISTS %s"
cursor.execute(sql, ("user_table",))

It fails with:

psycopg2.ProgrammingError: syntax error at or near "'user_table'"
LINE 1: DROP TABLE IF EXISTS 'user_table'

The single quotes in the SQL statement are the problem. If I run this it works just fine:

cursor.execute("DROP TABLE IF EXISTS user_table")

Clodoaldo Neto · Accepted Answer · 2015-08-24 00:31:53Z

5

The AsIs adapter is useful for objects whose string representation is already valid as SQL representation

from psycopg2.extensions import AsIs

sql = "DROP TABLE IF EXISTS %s"
cursor.execute(sql, (AsIs("user_table"),))

answered Aug 24, 2015 at 0:31

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

Comments

Daniel · Accepted Answer · 2015-08-23 21:50:34Z

3

You can use the argument-syntax only for arguments, not for table or column names.

answered Aug 23, 2015 at 21:50

Daniel

42.9k4 gold badges57 silver badges82 bronze badges

And that makes total sense. Thanks!

More strictly, query parameters are suitable for passing literals only. You cannot use them to substitute identifiers or keywords.