? and %s placeholders, help?

Paul Boddie paul at boddie.net
Tue Jun 25 06:57:01 EDT 2002


"Duncan Smith" <buzzard at urubu.freeserve.co.uk> wrote in message news:<af87cj$n5b$1 at newsg3.svr.pol.co.uk>...
> Thanks both,
>                    But I'm still having problems with this.  I want to use
> prepared statements to avoid unnecessary parsing, and I get the impression
> (from what information I can find, eg Python Programming on Win32) that I
> should (need to?) use the '?' placeholder to achieve this.  But I cannot
> come up with anything that works.

First, check the paramstyle of the MySQLdb module.

  # This generally works with database modules.

  print MySQLdb.paramstyle

The parameter notation to be used - informally known as the paramstyle
- should be printed. Only if "?" is printed can you actually use that
particular notation. (There's been some discussion about the merits of
having loads of paramstyles on the DB-SIG mailing list, but it would
arguably be a lot easier if "?" were in use throughout, just like with
JDBC.)

> >>> 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?

This isn't what you ought to be doing, since you're effectively doing
Python string substitution to supply the values. Moreover, bad data
could be used to "attack" your application or to "exploit" it, should
you employ these methods.

> >>> query = 'INSERT INTO %s (%s) VALUES (%s)' % (tblname, string.join(vars,
>  ', '), string.join(['?']*len(vars), ', '))
> >>> query
>  'INSERT INTO tbl (var1, var2, var3) VALUES (?, ?, ?)'

You're doing the right thing when building the query string, since the
table and column names cannot be passed in as parameters - therefore
you are right to substitute them in first. The only potential problem
is whether the parameter notation is correct - check out the
paramstyle to be sure, and modify the '?' to '%s' in the statement
above if the paramstyle turns out to be '%s'.

> >>> curs.execute(query, ("'a'", "'b'", "'c'"))
> Traceback (most recent call last):
>   File "<interactive input>", line 1, in ?
>   File "C:\Python22\Lib\site-packages\MySQLdb\cursors.py", line 70, in
> execute
>     raise ProgrammingError, m.args[0]
> ProgrammingError: not all arguments converted
> >>>

Be aware that by supplying "'a'", the actual inserted value will be
"'a'" and not "a" - you don't need to quote parameter values. Apart
from this, the above looks correct, and I suspect that the cause of
the error is the paramstyle that you've chosen being incorrect.

To summarise:

  1. Check the paramstyle.
  2. Build the query (or statement, in this case). Put the table and
     column names in here, along with the parameter "placeholders"
     according to the paramstyle.
  3. Execute the query (or statement) supplying the parameter values,
     remembering that you don't need to quote the parameter values.

Paul



More information about the Python-list mailing list