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

[Alan Gauld]

>>> 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 
substitution
character of your database adaptor . Are you sure your adaprtor uses 
%s
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
http://www.freenetpages.co.uk/hp/alan.gauld