CODEWITHSUNDEEP
pythonpostgresqlpsycopg2
jeffcook2150
jeffcook2150

Reputation: 4226

Parameterized queries with psycopg2 / Python DB-API and PostgreSQL

What's the best way to make psycopg2 pass parameterized queries to PostgreSQL? I don't want to write my own escpaing mechanisms or adapters and the psycopg2 source code and examples are difficult to read in a web browser.

If I need to switch to something like PyGreSQL or another python pg adapter, that's fine with me. I just want simple parameterization.

Upvotes: 76

Views: 105806

Answers (4)

Hank Gay
Hank Gay

Reputation: 72039

psycopg2 follows the rules for DB-API 2.0 (set down in PEP-249). That means you can call execute method from your cursor object and use the pyformat binding style, and it will do the escaping for you. For example, the following should be safe (and work):

cursor.execute("SELECT * FROM students WHERE last_name = %(lname)s", 
               {"lname": "Robert'); DROP TABLE students;--"})

Edit: tekHedd's comment rightly points out that the SELECT and the DROP TABLE used different table names, so I fixed it.

Upvotes: 131

adam
adam

Reputation: 6738

Here are a few examples you might find helpful

cursor.execute('SELECT * from table where id = %(some_id)d', {'some_id': 1234})

Or you can dynamically build your query based on a dict of field name, value:

query = 'INSERT INTO some_table (%s) VALUES (%s)'
cursor.execute(query, (my_dict.keys(), my_dict.values()))

Note: the fields must be defined in your code, not user input, otherwise you will be susceptible to SQL injection.

Upvotes: 3

Szabolcs Szepesi
Szabolcs Szepesi

Reputation: 136

I love the official docs about this:

https://www.psycopg.org/psycopg3/docs/basic/params.html

enter image description here

Upvotes: 5

Fábio Dias
Fábio Dias

Reputation: 686

From the psycopg documentation

(http://initd.org/psycopg/docs/usage.html)

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

The correct way to pass variables in a SQL command is using the second argument of the execute() method:

SQL = "INSERT INTO authors (name) VALUES (%s);" # Note: no quotes

data = ("O'Reilly", )

cur.execute(SQL, data) # Note: no % operator

Upvotes: 44

Related Questions