#156 Tuples of length 1 are not correctly quoted

[Anonymous]

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

[Andy Dustman]

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]

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]

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]

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]

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]

Logged In: YES
user_id=1079993

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

[Mario Frasca]

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"):