Compose dynamic SQL string with psycopg2

Question

I use psycopg2 in python (2.7.10) to connect to a postgresql DB. The docs are pretty clear about composition of dynamic SQL statements:

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

In psycopg2 version 2.7 there's the new sql module to do this string composition in a way that's safe against SQL injection. I nevertheless don't understand how to properly construct a statement like:

import psycopg2 as ps

C = psycopg.connect(host='my_host', port=Port, database='My_DB')
cur = C.cursor()
schema = 'some_schema'
table = 'some_table'
SQL = cur.execute("SELECT * FROM "+schema+"."+table+";") # This is horribly wrong
SQL = cur.execute("SELECT * FROM some_schema.some_table;") # That's what the result should be

I liked your statement which is so so hilarious - "Never, never, NEVER use Python string concatenation (+) or string parameters interpolation (%) to pass variables to a SQL query string. Not even at gunpoint." — thedevd, Mar 02 '22 at 05:14

Eugene Yarmash · Accepted Answer · 2021-11-22T15:00:16.933

You can use psycopg2.sql.Identifier to interpolate an identifier to a query, e.g.:

from psycopg2.sql import Identifier, SQL

query = SQL('SELECT * FROM {}.{}').format(*map(Identifier, (schema, table)))
print(query.as_string(conn))
cur.execute(query)

As per the linked documentation page, in psycopg2 v2.8+ you can also pass multiple strings to Identifier to represent a qualified name, i.e. a dot-separated sequence of identifiers:

query = SQL('SELECT * FROM {}').format(Identifier(schema, table))

Compose dynamic SQL string with psycopg2

1 Answers1

Linked