? and %s placeholders, help?

[John Hunter]

>>>>> "Duncan" == Duncan Smith <buzzard at urubu.freeserve.co.uk> writes:

    Duncan> Can anyone please explain the following.  Basically I need
    Duncan> to know the difference between the ? placeholder and the
    Duncan> %s placeholder.  I have only been able to find examples of
    Duncan> the use of '?' in situations where it clearly works
    Duncan> (integers?).  But I have no good idea why I cannot use it
    Duncan> as below.  Or am I stuck with having the statement parsed
    Duncan> each time I execute it?

As far as I know, the '?' syntax is used in the perl DBI but nowhere
in the python MySQLdb.  So forget about that one.

The %s in python is string interpolation, but there is a special way
to do it in MySQLdb.  You can do

>>> query = "INSERT INTO %s (%s) VALUES %s"
>>> tbl = 'AA_temp'
>>> vars = 'var1, var2, var3, var4, var5, var6, var7'
>>> vals = '("level1", "level1", "level1", "level1", "level1", "level1",
"level1")'
>>> curs.execute(query % (tbl, vars, vals))

but it's not the right way.  When converting the value arguments, you
want to use MySQLdb's built in conversion functions, otherwise strings
and other things won't be properly quoted.

To start with a simple example from the docs:

c.execute("""SELECT spam, eggs, sausage FROM breakfast
            WHERE price < %s""", (max_price,))

Note that this *is not* the same as

c.execute("""SELECT spam, eggs, sausage FROM breakfast
            WHERE price < %s""" % max_price)

The former uses the MySQLdb built in conversions (which are
extensible), the latter uses python's string format conversions.

Your example is more involved, but something like this should work:

import string
(val1, val2, val3) = (1, 'John', 6.0)
tbl = 'mytable'
vars = ('var1', 'var2', 'var3')
vals = (val1, val2, val3)
valsfmt = len(vals) * '%s,'
valsfmt = valsfmt[:-1]  # valsfmt == '%s,%s,%s'

# use python string format to build the mysqldb query string
# query == 'INSERT INTO mytable (var1,var2,var3) VALUES %s,%s,%s'
query =  "INSERT INTO %s (%s) VALUES %s" % \
	(tbl, string.join(vars, ','), valsfmt) 


# use mysqldb to format the values
curs.execute(query, vals)

Don't know if this will help you with your efficiency problem, but at
least you'll be doing the conversions properly.

John Hunter