Parameterized queries with psycopg2 / Python DB-API and PostgreSQL

Question

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.

Thanks. : )

Answer 1

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:

fields = ', '.join(my_dict.keys())
values = ', '.join(['%%(%s)s' % x for x in my_dict])
query = 'INSERT INTO some_table (%s) VALUES (%s)' % (fields, values)
cursor.execute(query, my_dict)

Answer 2

This does not answer your question, but it does address a side comment in your question.

If you have difficulty reading pages at initd.org, try doing a view-source on the difficult page.

They apparently have a misconfiguration at the server, and it is deliverying plain text as html, and gets the white-space-compression treatment from the browser. View-source doesn't do white space compression.

Answer 3

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

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

Answer 4

See the examples directory included with psycopg2. The code samples are very helpful.