Parameterized queries with psycopg2 / Python DB-API and PostgreSQL

57
votes

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.

3
pythonpostgresqlpsycopg2
What sort of parameterization do you want ? Pseudocode sample will be useful. - whatnick
Sidenote, you may want to look into SQLAlchemy, the cost of entry may be a bit higher in some ways, but it really is a very nice ORM. - Bryan McLemore
For future reference, the answer is in the first page of the documentation: initd.org/psycopg/docs/usage.html - piro
On the documentation the examples are very easy. There is none that shows how a more complex query like an update would be done for dynamic values. Something like: set height=5, weight=70 - Mutai Mwiti
Stack Overflow becomes a lot more useful if you pose a specific problem you are trying to solve. For example querying vs updating a table, etc. The answers here are quite generic as a result, and do not help resolve the problem I'm hitting despite the title which brought me in. - Bill Gale

3 Answers

108
votes

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 student WHERE last_name = %(lname)s", 
               {"lname": "Robert'); DROP TABLE students;--"})
30
votes

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

3
votes

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)

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