psycopg2 cursor.execute() with SQL query parameter causes syntax error

Question

When specifying a parameter to execute() in psycopg2 in Python, like this:

cursor.execute('SELECT * FROM %s', ("my_table", ))

I'm getting this error:

psycopg2.ProgrammingError: syntax error at or near "'my_table'"
LINE 1: SELECT * FROM 'my_table'

What am I doing wrong? It looks like psycopg2 is adding single quotes to the query, and those single quotes are causing the syntax error.

If I don't use a parameter, it works correctly:

cursor.execute('SELECT * FROM my_table')

Answer 1

There is functionality within Psycopg2 that supports this use case via SQL String Composition. This approach provides a way to "generate SQL dynamically, in a convenient and safe way."

In the use case in the original answer:

cursor.execute(
    sql.SQL("SELECT * FROM {}").format(
        sql.Identifier("my_table")))

Using SQL String Composition is a much safer approach than string parameter interpolation via % discussed in Irfy's answer. As noted in the psycopg2 documentation:

Warning Never, never, NEVER use Python string concatenation (+) or string parameters interpolation (%) to pass variables to a SQL query string. Not even at gunpoint.

Answer 2

cur.execute(
    """
    SELECT * FROM %s;
    """,
    {"my_table",}
)
conn.commit()