select ... in %s, tuple

[Simon Hart]

Running
cursor.execute("select * from tbl where val in %s", (['a','b','c'],))
exapnds to
select * from tbl where val in ("'a'","'b'","'c'");
i.e. compares to value surrrounded by single quotes.

Is this expected behaviour and I should write
..."in (%s,%s,%s)", ('a','b','c')
or a bug?

Thanks
Simon

[Sibylle Koczian]

I just tried this (Python 2.4.2, MySQL 5.0.18, MySQLdb 1.2.0, Windows XP Prof.) in two different ways:

1. Just as in your posting:
   cursor.execute("select * from tbl where val in %s", (('a','b','c'),))

with correct values so the query should have found some records - no error message, but no records found.

1. %s in parentheses:
   cursor.execute("select * from tbl where val in (%s)", (('a','b','c'),))

This gives the error message

Traceback (most recent call last):
File "<interactive input>", line 1, in ?
File "C:\Python24\lib\site-packages\MySQLdb\cursors.py", line 137, in execute
self.errorhandler(self, exc, value)
File "C:\Python24\lib\site-packages\MySQLdb\connections.py", line 33, in defaulterrorhandler
raise errorclass, errorvalue
OperationalError: (1241, 'Operand should contain 1 column(s)')

Items are strings without single or double quotes in them.

[Andy Dustman]

Not a bug.

[Joakim Sernbrant]

If its not a bug, what is the correct way to do what he wants? I cant work it out. Ended up with:

        conv[types.ListType] = sql_escape_list # dont understand how this works with the default converters?!

def sql_escape_list(s, d):
    return ",".join(map(lambda x: Thing2Literal(x, d), s))

[Andy Dustman]

There's no automatic way for MySQLdb to guess what should happen when it gets a list as a parameter. However, this will probably work most of the time (note that it uses a tuple as the parameter):

cursor.execute("select * from tbl where val in %s", (('a','b','c'),))

This will fail if any of the items have a single-quote in them.

Something a bit more automatic could (and probably should) be done by using the Set type which is in current versions of Python.