Passing a python dictionary to a psycopg2 cursor

Question

I need to call a PostgreSQL 8.4 function which requires 17 input paramters from Python. The values are stored in a dictionary. So I can write:

cur.execute("SELECT add_user(%s, %s, %s, %s, %s, %s, %s, .......)", user["nr"], user['email']...)

Is it possible to automatically map the values in the dictionary to the function arguments (which have the same name as the keys in the dictionary)?

Something like:

cur.execute("SELECT add_user(*magic-here*)", user)

score 10 · Accepted Answer · edited May 23 '17 at 10:27

10

The following syntax should do it:

cur.execute("SELECT add_user(%(nr)s, %(email)s, ...) ...", user)

Thanks to Thiefmaster for providing a correction to what I had here originally: The %(keyname)s format for parameters is just one of those defined in the Python Database API 2.0 - see the documentation for paramstyle. Unfortunately, other DB API 2.0 database adapters may not support this syntax.

For an answer to a somewhat similar question, but with a bonus xkcd reference, see: Parameterized queries with psycopg2 / Python DB-API and PostgreSQL

edited May 23 '17 at 10:27

Community

1
1

answered Feb 28 '11 at 08:41

Mark Longair

446,582
72
411
327

Regarding the database adapter paragraph: Search for `paramstyle` in the DBAPI docs. It's not really portable :/ – ThiefMaster Feb 28 '11 at 09:08
@ThiefMaster: ouch - thanks for the correction, I've incorporated that into my answer – Mark Longair Feb 28 '11 at 09:23

Passing a python dictionary to a psycopg2 cursor

1 Answers1