? and %s placeholders, help?

[Hamish Lawson]

Duncan Smith wrote:

> >>> query = 'INSERT INTO %s (%s) VALUES (%s)' % (tblname, string.join(vars,
>  ', '), string.join(['%s']*len(vars), ', '))
> >>> query
>  'INSERT INTO tbl (var1, var2, var3) VALUES (%s, %s, %s)'
> >>> curs.execute(query % ("'a'", "'b'", "'c'"))
> 1L
> 
> #Hurray, but does this avoid parsing the statement on each INSERT?

No, use the two-argument form of the execute method instead:

    curs.execute(query, ("a", "b", "c"))

The execute method will do the interpolation of the values with
appropriate quoting, so it's not necessary to quote them yourself. The
execute method remembers which queries it has seen and only parses a
query the first time it sees it.

Note that Python DB-API drivers differ in which placeholders they
recognise ('%s', '%(fieldname)s', or '?'), usually (I understand)
based on what the native database API itself employs.

See <http://www.python.org/topics/database/DatabaseAPI-2.0.html>.


Hamish Lawson