[Tutor] MySQLdb: cant get '... where field in %s' to work

Alan Gauld alan.gauld at freenet.co.uk
Mon Jun 26 18:36:37 CEST 2006

>>> ARTICLES = ('XXX99999', 'ABZ00002')
>>> TESTARTICLENAME = """SELECT * FROM tblForTransfer2Prodsite
>>> WHERE articleName IN %r""" % (ARTICLES,)

This uses normal string substitution so puts the tuple ARTICLES
in the query using the repr format - includes parens) which is what 
you want.
(BTW using uppercase for variable names is considered bad
practice - it makes your code much harer to read, and in Python
uppercase usually means a constant.)

>>> SQLARTICLENAME = """SELECT * FROM tblForTransfer2Prodsite
>>> WHERE articleName IN %s"""
>>>         print cur.execute(SQLARTICLENAME, (ARTICLES,))

This puts a tuple in the tuple but the substitution uses the 
character of your database adaptor . Are you sure your adaprtor uses 
to substitute a tuple? You mat need to use str() on it first:

        print cur.execute(SQLARTICLENAME, (str(ARTICLES))

> Sorry, I neglected to state that I do not get any error message.
> I expected both 'execute' statements to print 2 but the second 
> prints 0.
> For integers, I get the results I expected.

But I'm guessing...

Alan Gauld
Author of the Learn to Program web site

More information about the Tutor mailing list