MySQLdb select

[Sibylle Koczian]

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