'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.
Solution 1:[1]
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
Solution 2:[2]
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.
Solution 3:[3]
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Community |
| Solution 2 | |
| Solution 3 | Szabolcs Szepesi |