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
– thedevd, Commented Mar 2, 2022 at 5:14

Eugene Yarmash · Accepted Answer · 2021-11-22 15:00:16Z

21

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

edited Nov 22, 2021 at 15:00

answered Oct 16, 2017 at 12:37

Eugene Yarmash

152k44 gold badges346 silver badges391 bronze badges

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

Collectives™ on Stack Overflow

Compose dynamic SQL string with psycopg2

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related