Learn how easy it is to sync an existing GitHub or Google Code repo to a SourceForge project! See Demo

Close

#134 single item tuple mishandled for IN clause

MySQLdb-1.2
closed
Andy Dustman
MySQLdb (285)
5
2012-09-19
2005-03-17
Kyle VanderBeek
No

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.

Discussion

  • 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
    Andy Dustman
    2005-03-18

    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)