#134 single item tuple mishandled for IN clause

[Kyle VanderBeek]

Example code:

q = """SELECT * FROM foo WHERE a IN %s"""
tup = (11,2)
c.execute(q, (tup,))

This, of course, works great. The tuple "tup" is
escaped correctly for the IN clause. However, if you
change tup to a single item list:

tup = (11,)

The query will fail with invalid syntax:

Traceback (most recent call last):
File "/home/kylev/tmp/foo.py", line 12, in ?
c.execute(q, (tup,))
File
"/usr/lib/python2.3/site-packages/MySQLdb/cursors.py",
line 137, in execute
self.errorhandler(self, exc, value)
File
"/usr/lib/python2.3/site-packages/MySQLdb/connections.py",
line 33, in defaulterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (1064, "You have an
error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right
syntax to use near ')' at line 1")

It looks like a single-item array/tuple is converted to
a single value, instead of a parenthesized list with
one member. I.e., the above is converted to "'11'"
instead of "('11')". I think the latter is more
correct and wouldn't cause this error.

[Kyle VanderBeek]

Logged In: YES
user_id=51762

Oops, wrong analysis. The problem is that a single-item
list ends up with a trailing comma. Example code to show
what actually happens:

con = MySQLdb.connect(...)
t = [1]
print con.escape(t)

The output is:

('1',)

Which, if course, will cause a MySQL syntax error.

[Andy Dustman]

Logged In: YES
user_id=71372

Yeah, that's a interesting problem, but probably not a bug,
since the parameter placeholders are only supposed to hold a
single value. I think you would have this same problem in
most, if not all, other DB API databases.

Since tup may be of variable length, the solution is:

def make_placeholders(n):
return ','.join(["%s"] * n)

q = """SELECT * FROM foo WHERE a IN (%s)""" % \ make_placeholders(len(tup))
c.execute(q, tup)