MySQLdb select

[Andy Todd]

Sibylle Koczian wrote:
> Dennis Lee Bieber schrieb:
> 
>>
>>     Except that the conversion function, .literal(),  /does/  accept
>> a single item, a sequence (tuple OR list), or a mapping (dictionary).
>> The arguments are just passed through .execute(), which doesn't really
>> care one way or the other.
>>
>>     Tediously, .execute() calls ._execute(), which has:
>> ...
>>         try:
>>             if args is None:
>>                 r = self._query(query)
>>             else:
>>                 r = self._query(query % self.connection.literal(args))
>>         except TypeError, m:
>> ...
>>
>>     Since .literal() accepts single objects (well, in truth, I'd
>> guess one would have to say that it is .escape() that processes the
>> arguments -- I can't say what that does, as it is likely in the binary
>> module. However, the actual forming of the final query string /is/ the
>> standard Python "string" % arg operator, which, in my experience, has
>> never complained about not being given a 1-tuple.
>>  
> 
> True. I had hopes that this might explain another riddle I couldn't 
> solve: datefield is a database column of type date, and I execute two 
> queries:
> 
> a) SELECT * FROM mytable WHERE datefield = %s
> 
> b) SELECT * FROM mytable WHERE datefield IN (%s, %s)
> 
> case a): the parameter for %s can be a mx.DateTime object, a 
> datetime.date object or the sort of DateTime object MySQLdb returns from 
> another query; or it can be a string with format 'YYYY-MM-DD'. The 
> results of the query are correct in every case.
> 
> case b): the parameters must be strings of the form 'YYYY-MM-DD'. With 
> all other sorts of parameters (DateTime objects, strings in other date 
> formats) the query doesn't find anything.
> 
> I tried this with different versions of MySQL, MySQLdb and Python, under 
> Windows and Linux, always with the same results.
> 
> Now I've looked at conn.literal(args) for a pair of DateTime objects and 
> for a single one:
> 
>  >>> arg
> <DateTime object for '2004-06-18 00:00:00.00' at 1199da0>
>  >>> arglist
> [<DateTime object for '2004-07-29 00:00:00.00' at 117af20>, <DateTime 
> object for '2004-07-07 00:00:00.00' at 1199de0>]
>  >>> conn.literal(arglist)
> ("'2004-07-29 00:00:00'", "'2004-07-07 00:00:00'")
>  >>> conn.literal(arg)
> "'2004-06-18 00:00:00'"
>  >>> s1 = 'SELECT * FROM fehllief WHERE fehltag IN (%s, %s)'
>  >>> s1 % conn.literal(arglist)
> "SELECT * FROM fehllief WHERE fehltag IN ('2004-07-29 00:00:00', 
> '2004-07-07 00:00:00')"
>  >>> s2 = 'SELECT * FROM fehllief WHERE fehltag = %s'
>  >>> s2 % conn.literal(arg)
> "SELECT * FROM fehllief WHERE fehltag = '2004-06-18 00:00:00'"
>  >>> curs.execute(s1, arglist)
> 0L
>  >>> curs.execute(s2, arg)
> 1L
> 
> Why doesn't the query using IN find anything while the query using = 
> does? The records are there, of course.
> 
> Koczian

It's a bug. I think it is a bug in MySQL. I'm using 4.0.18 on Debian and 
an interactive session shows the problem;

"""
andy47 at vetinari:~$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25 to server version: 4.0.18-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use portfolio
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select count(*) from stock_prices where price_date = '2004-07-30';
+----------+
| count(*) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from stock_prices where price_date = '2004-07-30 
00:00:00';
+----------+
| count(*) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)
"""

When using '=' the two forms of date are identical, but if we switch to 
using 'in';

"""
mysql> select count(*) from stock_prices where price_date in ('2004-07-30');
+----------+
| count(*) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from stock_prices where price_date in 
('2004-07-30 00:00:00');
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql>
"""

Ta-da. Of course, this may have already been notified to MySQL AB, I'd 
check their web site (http://www.mysql.com) or try one of their mailing 
lists.

Regards,
Andy
-- 
--------------------------------------------------------------------------------
 From the desk of Andrew J Todd esq - http://www.halfcooked.com/