#156 Tuples of length 1 are not correctly quoted

MySQLdb-1.2
closed
MySQLdb (285)
5
2012-09-19
2005-10-11
Anonymous
No

Example:

c.execute("""SELECT * FROM test
WHERE attribute IN %s""", ((1,), ))

generates an error

but

c.execute("""SELECT * FROM localekit_attributes
WHERE attribute IN %s""", ((1, 2), ))

does not.

Charlie Clark
charlie@begeistert.org

Discussion

  • Andy Dustman

    Andy Dustman - 2005-10-11

    Logged In: YES
    user_id=71372

    You can't pass a tuple as a parameter to execute(). There's
    no database adapter that this would work on. This is one of
    the rare cases where you have to build up your query with
    the % operator, i.e.

    c.execute("""SELECT * FROM localekit_attributes
    WHERE attribute IN %s""" % ((1, 2), ))

    A tuple is effectively a multi-valued argument, and only
    single-valued arguments can be passed to execute().

     
  • Nobody/Anonymous

    Logged In: NO

    So I have to do the quoting myself?
    As I said tuples with more than one element are quoting
    correctly which makes this a very useful function.

     
  • Andy Dustman

    Andy Dustman - 2005-10-12

    Logged In: YES
    user_id=71372

    Well I guess it depends on what your error is, but since you
    never said what that is, I'm not going to guess.

     
  • Nobody/Anonymous

    Logged In: NO

    This is from the log:
    051012 14:21:04 3 Query SELECT * FROM
    localekit_attributes WHERE attribute IN ('About', 'Cancel')
    051012 14:22:05 3 Query SELECT * FROM
    localekit_attributes WHERE attribute IN ('About',)

    In both cases I'm passing a tuple in as an argument - so it
    works all but for the special case of the one element tuple
    which raises a 1064 syntax error because of the extra comma.
    Unfortunately your suggestion of using %s formatting
    generates the same syntax error!

     
  • Andy Dustman

    Andy Dustman - 2005-10-12

    Logged In: YES
    user_id=71372

    I've written some code on-the-fly to try to demonstrate how
    to fix/work around this:

    Python 2.4.2 (#1, Oct 3 2005, 09:43:02)
    [GCC 3.4.4 (Gentoo 3.4.4-r1, ssp-3.4.4-1.0, pie-8.7.8)] on
    linux2
    Type "help", "copyright", "credits" or "license" for more
    information.

    from MySQLdb.converters import conversions
    conversions
    {<type 'object'="">: <function Instance2Str="" at="" 0xb7b77c6c="">, 1:
    <type 'int'="">, 2: <type 'int'="">, 3: <type 'long'="">, 4: <type 'float'="">, <type 'str'="">: <function Thing2Literal="" at="" 0xb7b77c34="">, 0: <class 'decimal.Decimal'="">, 7: <function mysql_timestamp_converter="" at="" 0xb7b77a04="">, 8: <type 'long'="">,
    9: <type 'int'="">, 10: <function Date_or_None="" at="" 0xb7b7795c="">,
    11: <function TimeDelta_or_None="" at="" 0xb7b778ec="">, 12:
    <function DateTime_or_None="" at="" 0xb7b778b4="">, 13: <type 'int'="">,
    5: <type 'float'="">, <type 'int'="">: <function Thing2Str="" at="" 0xb7b77b54="">, <type 'datetime.timedelta'="">: <function DateTimeDelta2literal="" at="" 0xb7b779cc="">, <type 'tuple'="">:
    <built-in function="" escape_sequence="">, <type 'dict'="">:
    <built-in function="" escape_dict="">, <type 'list'="">: <built-in function="" escape_sequence="">, <type 'float'="">: <function Float2Str="" at="" 0xb7b77bc4="">, <type 'NoneType'="">: <function None2NULL="" at="" 0xb7b77bfc="">, <type 'datetime.datetime'="">:
    <function DateTime2literal="" at="" 0xb7b77994="">, <type 'instance'="">: <function Instance2Str="" at="" 0xb7b77c6c="">, <type 'array.array'="">: <function array2Str="" at="" 0xb7b77cdc="">, <type 'long'="">: <function Thing2Str="" at="" 0xb7b77b54="">, 248: <function Str2Set="" at="" 0xb7b77b1c="">, <type 'unicode'="">: <function Unicode2Str="" at="" 0xb7b77b8c="">, 252: [(128, <function char_array="" at="" 0xb7b77ca4="">), (None, None)]}
    def escape_tuple(t, d):
    ... q = escape_sequence(t,d)
    ... if len(q) == 1:
    ... return "(%s)" % q[0]
    ... else:
    ... return q
    ...
    t=('foo',)
    from MySQLdb import escape_sequence
    escape_sequence(t,conversions)
    ("'foo'",)
    escape_tuple(t,conversions)
    "('foo')"

    To actually use this, you'll need to do:

    converters[tuple] = escape_tuple

    (For older versions of Python, use types.TupleType instead
    of tuple)

    If you look at conversions.py, you should see where this is
    being done with escape_sequence already.

     
  • Andy Dustman

    Andy Dustman - 2005-10-12

    Logged In: YES
    user_id=71372

    Here's another variation:

    def quote_tuple(t, d):
    return "(%s)" % ','.join([item for item in
    escape_sequence(t,d)]
    )

    This is not quite perfect (it fails for nested tuples), but
    it may be better than the original solution.

     
  • Andy Dustman

    Andy Dustman - 2005-10-12

    Logged In: YES
    user_id=71372

    Actually, this seems to work perfectly well on nested tuples
    as well:

    def quote_tuple(t, d):
    ... return "(%s)" % ','.join(escape_sequence(t,d))
    ...
    conversions[tuple]=quote_tuple
    print t
    ("It's great!", 'foo', ('bar', 'baz'))
    print quote_tuple(t,conversions)
    ('It\'s great!','foo',('bar','baz'))

    Same function should work for lists as well.

    I'll probably incorporate this.

     
  • Nobody/Anonymous

    Logged In: NO

    Thanks for this code and the explanation. Certainly neater
    than the workaround I've come up with!

    Charlie

     
  • Alexey

    Alexey - 2006-09-11

    Logged In: YES
    user_id=1079993

    Do you (and when) plan to fix this issue?

     
  • Mario Frasca

    Mario Frasca - 2006-11-10

    Logged In: YES
    user_id=512199

    Hi Andy, I've encountered this bug too... nice solution
    here, I adapted it just a bit so that it works on the
    current version of your library and can provide a patch if
    you want. can we expect to find it in the distributed module?

    thanks, Mario.

    diff -U3
    /home/mariof/Local/mysql-python.sourceforge.net/MySQLdb/MySQLdb/converters.py
    /home/mariof/lib/python2.4/site-packages/MySQLdb/converters.py


    /home/mariof/Local/mysql-python.sourceforge.net/MySQLdb/MySQLdb/converters.py
    2006-11-10 15:11:37.000000000 +0100
    +++
    /home/mariof/lib/python2.4/site-packages/MySQLdb/converters.py
    2006-11-10 16:04:07.000000000 +0100
    @@ -107,13 +107,16 @@
    def array2Str(o, d):
    return Thing2Literal(o.tostring(), d)

    +def quote_tuple(t, d):
    + return "(%s)" % (','.join(escape_sequence(t,d)))
    +
    conversions = {
    types.IntType: Thing2Str,
    types.LongType: Long2Int,
    types.FloatType: Float2Str,
    types.NoneType: None2NULL,
    - types.TupleType: escape_sequence,
    - types.ListType: escape_sequence,
    + types.TupleType: quote_tuple,
    + types.ListType: quote_tuple,
    types.DictType: escape_dict,
    types.InstanceType: Instance2Str,
    array.ArrayType: array2Str,
    diff -U3
    /home/mariof/Local/mysql-python.sourceforge.net/MySQLdb/MySQLdb/cursors.py
    /home/mariof/lib/python2.4/site-packages/MySQLdb/cursors.py


    /home/mariof/Local/mysql-python.sourceforge.net/MySQLdb/MySQLdb/cursors.py
    2006-11-10 15:03:39.000000000 +0100
    +++
    /home/mariof/lib/python2.4/site-packages/MySQLdb/cursors.py
    2006-11-10 16:02:07.000000000 +0100
    @@ -121,7 +121,8 @@
    if args is None:
    r = self._query(query)
    else:
    - r = self._query(query %
    self.connection.literal(args))
    + r = self._query(query %
    tuple([self.connection.literal(item)
    + for item in
    args]
    ))
    except TypeError, m:
    if m.args[0] in ("not enough arguments for
    format string",
    "not all arguments converted"):

     

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks