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)

Answer 1

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