How to use pgdb.executemany?

Question

I'm trying to execute insert statements from Python to PostgreSQL using the pgdb module.

I see the documentation says:

    cursor.executemany(query, list of params)
    # Execute a query many times, binding each param dictionary
    # from the list.

So I'm trying things like:

>>> insert = "insert into foo (name, number) values (?,?);"
>>> params = [{ 'name': 'John', 'number': 123 }, { 'name': 'Jack', 'number': 234 }]
>>> cursor.executemany(insert, params)

This is giving me a error, pointing at the ?. What is the correct syntax for this sort of parameterized query? Also, if this is in the documentation, where do I find it?

Answer 1

you want

insert = "insert into foo (name, number) value (%(name)s, %(number)s);"

Which style for parameter inclusion is supported by your DBAPI driver can be found with the paramstyle module-level constant. According to the PEP 249 (aka python database API 2 specification):

String constant stating the type of parameter marker formatting expected by the interface. Possible values are [2]:

'qmark'         Question mark style, 
                e.g. '...WHERE name=?'
'numeric'       Numeric, positional style, 
                e.g. '...WHERE name=:1'
'named'         Named style, 
                e.g. '...WHERE name=:name'
'format'        ANSI C printf format codes, 
                e.g. '...WHERE name=%s'
'pyformat'      Python extended format codes, 
                e.g. '...WHERE name=%(name)s'

and if you check pgdb, you'll see that

>>> import pgdb
>>> pgdb.paramstyle
'pyformat'