MySQLdb select

[Sibylle Koczian]

Re: MySQLdb select

Dennis Lee Bieber schrieb:[color=blue]
>
> 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(que ry)
> else:
> r = self._query(que ry % 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.
>[/color]
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(ar gs) for a pair of DateTime objects and
for a single one:
[color=blue][color=green][color=darkred]
>>> arg[/color][/color][/color]
<DateTime object for '2004-06-18 00:00:00.00' at 1199da0>[color=blue][color=green][color=darkred]
>>> arglist[/color][/color][/color]
[<DateTime object for '2004-07-29 00:00:00.00' at 117af20>, <DateTime
object for '2004-07-07 00:00:00.00' at 1199de0>][color=blue][color=green][color=darkred]
>>> conn.literal(ar glist)[/color][/color][/color]
("'2004-07-29 00:00:00'", "'2004-07-07 00:00:00'")[color=blue][color=green][color=darkred]
>>> conn.literal(ar g)[/color][/color][/color]
"'2004-06-18 00:00:00'"[color=blue][color=green][color=darkred]
>>> s1 = 'SELECT * FROM fehllief WHERE fehltag IN (%s, %s)'
>>> s1 % conn.literal(ar glist)[/color][/color][/color]
"SELECT * FROM fehllief WHERE fehltag IN ('2004-07-29 00:00:00',
'2004-07-07 00:00:00')"[color=blue][color=green][color=darkred]
>>> s2 = 'SELECT * FROM fehllief WHERE fehltag = %s'
>>> s2 % conn.literal(ar g)[/color][/color][/color]
"SELECT * FROM fehllief WHERE fehltag = '2004-06-18 00:00:00'"[color=blue][color=green][color=darkred]
>>> curs.execute(s1 , arglist)[/color][/color][/color]
0L[color=blue][color=green][color=darkred]
>>> curs.execute(s2 , arg)[/color][/color][/color]
1L

Why doesn't the query using IN find anything while the query using =
does? The records are there, of course.

Koczian

[Dennis Lee Bieber]

Re: MySQLdb select

On Thu, 05 Aug 2004 14:09:23 +0200, Sibylle Koczian
<Sibylle.Koczia n@Bibliothek.Un i-Augsburg.de> declaimed the following in
comp.lang.pytho n:
[color=blue]
> Why doesn't the query using IN find anything while the query using =
> does? The records are there, of course.
>[/color]
I have to pass on this -- you've gone beyond my skill level;
I've not used any of the "internal" date types.

--[color=blue]
> =============== =============== =============== =============== == <
> wlfraed@ix.netc om.com | Wulfraed Dennis Lee Bieber KD6MOG <
> wulfraed@dm.net | Bestiaria Support Staff <
> =============== =============== =============== =============== == <
> Home Page: <http://www.dm.net/~wulfraed/> <
> Overflow Page: <http://wlfraed.home.ne tcom.com/> <[/color]

[Andy Todd]

Re: MySQLdb select

Sibylle Koczian wrote:[color=blue]
> Dennis Lee Bieber schrieb:
>[color=green]
>>
>> 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(que ry)
>> else:
>> r = self._query(que ry % 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.
>>[/color]
>
> 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(ar gs) for a pair of DateTime objects and
> for a single one:
>[color=green][color=darkred]
> >>> arg[/color][/color]
> <DateTime object for '2004-06-18 00:00:00.00' at 1199da0>[color=green][color=darkred]
> >>> arglist[/color][/color]
> [<DateTime object for '2004-07-29 00:00:00.00' at 117af20>, <DateTime
> object for '2004-07-07 00:00:00.00' at 1199de0>][color=green][color=darkred]
> >>> conn.literal(ar glist)[/color][/color]
> ("'2004-07-29 00:00:00'", "'2004-07-07 00:00:00'")[color=green][color=darkred]
> >>> conn.literal(ar g)[/color][/color]
> "'2004-06-18 00:00:00'"[color=green][color=darkred]
> >>> s1 = 'SELECT * FROM fehllief WHERE fehltag IN (%s, %s)'
> >>> s1 % conn.literal(ar glist)[/color][/color]
> "SELECT * FROM fehllief WHERE fehltag IN ('2004-07-29 00:00:00',
> '2004-07-07 00:00:00')"[color=green][color=darkred]
> >>> s2 = 'SELECT * FROM fehllief WHERE fehltag = %s'
> >>> s2 % conn.literal(ar g)[/color][/color]
> "SELECT * FROM fehllief WHERE fehltag = '2004-06-18 00:00:00'"[color=green][color=darkred]
> >>> curs.execute(s1 , arglist)[/color][/color]
> 0L[color=green][color=darkred]
> >>> curs.execute(s2 , arg)[/color][/color]
> 1L
>
> Why doesn't the query using IN find anything while the query using =
> does? The records are there, of course.
>
> Koczian[/color]

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@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/

[F. GEIGER]

Re: MySQLdb select

"Gerhard Häring" <gh@ghaering.de > schrieb im Newsbeitrag
news:mailman.10 08.1091284523.5 135.python-list@python.org ...

[color=blue]
> That's particularly BAD STYLE. It's best to keep to letting the DB-API
> do the proper quoting for all parameters.[/color]


Well, yes.

So I tried this:
[color=blue][color=green][color=darkred]
>>> import MySQLdb as ms
>>> con = ms.connect(db=" isa",user="root ")
>>> cur = con.cursor()
>>> cur.execute("se lect id from %s limit 10;", ("tagevents" ,))[/color][/color][/color]
Traceback (most recent call last):
File "<stdin>", line 1, in ?
File "C:\PROGRA~1\Py thon23\lib\site-packages\MySQLd b\cursors.py", line 95,
in execute
return self._execute(q uery, args)
File "C:\PROGRA~1\Py thon23\lib\site-packages\MySQLd b\cursors.py", line
114, in _execute
self.errorhandl er(self, exc, value)
File "C:\PROGRA~1\Py thon23\lib\site-packages\MySQLd b\connections.p y", line
33, in defaulterrorhan dler
raise errorclass, errorvalue
_mysql_exceptio ns.ProgrammingE rror: (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 ''tagevents'
limit 10' at line 1")[color=blue][color=green][color=darkred]
>>>[/color][/color][/color]

Hmm, despite the fact, that it is bad style, I tried:
[color=blue][color=green][color=darkred]
>>> cur.execute("se lect id from %s limit 10;" % "tagevents" )[/color][/color][/color]
10L[color=blue][color=green][color=darkred]
>>>[/color][/color][/color]

and succeeded.

Looks like MySQL doesn't like the quoting, MySQLdb seems to perform.

Okay, as you shouted to me "BAD STYLE" I presume, it had to work, if I only
did it right. So, what am I doing wrong? Or did I misconfig MySQL? Is MySQL
4.0 not yet supported?

My environment:

Win XP

Python 2.3.4 (#53, May 25 2004, 21:17:02) [MSC v.1200 32 bit (Intel)] on
win32
Type "help", "copyright" , "credits" or "license" for more information.

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 4.0.20a-nt

MySQLdb 1.0.0


Kind regards
Franz GEIGER


"Gerhard Häring" <gh@ghaering.de > schrieb im Newsbeitrag
news:mailman.10 08.1091284523.5 135.python-list@python.org ...[color=blue]
> F. GEIGER wrote:[color=green]
> > "John Fabiani" <jfabiani@yolo. com> schrieb:
> >[color=darkred]
> >>Hi,
> >> I'm a newbie and I'm attempting to learn howto create a select[/color][/color][/color]
statement.[color=blue][color=green][color=darkred]
> >>When I use
> >>
> >>>>>string1='1 8 Tadlock Place'
> >>>>>cursor.exe cute("SELECT * FROM mytest where address = %s",string1)
> >>
> >>All works as expected. But
> >>
> >>>>>numb=10
> >>>>>cursor.exe cute("SELECT * FROM mytest where clientID = %d",numb)
> >> [...] raise errorclass, errorvalue
> >>TypeError: int argument required
> >>[/color][/color]
>
> Then use %i for integers ;-)
>[color=green]
> > I'm used to do that this way:
> >
> > cursor.execute( "SELECT * FROM mytest where clientID = %d" % numb)[/color]
>
> That's particularly BAD STYLE. It's best to keep to letting the DB-API
> do the proper quoting for all parameters.
>
> -- Gerhard[/color]

[Andy Todd]

Re: MySQLdb select

F. GEIGER wrote:[color=blue]
> "Gerhard Häring" <gh@ghaering.de > schrieb im Newsbeitrag
> news:mailman.10 08.1091284523.5 135.python-list@python.org ...
>
>
>[color=green]
>>That's particularly BAD STYLE. It's best to keep to letting the DB-API
>>do the proper quoting for all parameters.[/color]
>
>
>
> Well, yes.
>
> So I tried this:
>
>[color=green][color=darkred]
>>>>import MySQLdb as ms
>>>>con = ms.connect(db=" isa",user="root ")
>>>>cur = con.cursor()
>>>>cur.execute ("select id from %s limit 10;", ("tagevents" ,))[/color][/color]
>
> Traceback (most recent call last):
> File "<stdin>", line 1, in ?
> File "C:\PROGRA~1\Py thon23\lib\site-packages\MySQLd b\cursors.py", line 95,
> in execute
> return self._execute(q uery, args)
> File "C:\PROGRA~1\Py thon23\lib\site-packages\MySQLd b\cursors.py", line
> 114, in _execute
> self.errorhandl er(self, exc, value)
> File "C:\PROGRA~1\Py thon23\lib\site-packages\MySQLd b\connections.p y", line
> 33, in defaulterrorhan dler
> raise errorclass, errorvalue
> _mysql_exceptio ns.ProgrammingE rror: (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 ''tagevents'
> limit 10' at line 1")
>
>
> Hmm, despite the fact, that it is bad style, I tried:
>
>[color=green][color=darkred]
>>>>cur.execute ("select id from %s limit 10;" % "tagevents" )[/color][/color]
>
> 10L
>
>
> and succeeded.
>
> Looks like MySQL doesn't like the quoting, MySQLdb seems to perform.
>
> Okay, as you shouted to me "BAD STYLE" I presume, it had to work, if I only
> did it right. So, what am I doing wrong? Or did I misconfig MySQL? Is MySQL
> 4.0 not yet supported?
>
> My environment:
>
> Win XP
>
> Python 2.3.4 (#53, May 25 2004, 21:17:02) [MSC v.1200 32 bit (Intel)] on
> win32
> Type "help", "copyright" , "credits" or "license" for more information.
>
> Welcome to the MySQL monitor. Commands end with ; or \g.
> Your MySQL connection id is 7 to server version: 4.0.20a-nt
>
> MySQLdb 1.0.0
>
>
> Kind regards
> Franz GEIGER
>[/color]
[snip]

That's because MySQLdb will take care of converting the *parameters* in
a SQL statement. These are (almost) always in the WHERE clause.

What you are trying to do is generate the SQL statement dynamically -
which you have to do yourself using string formatting as you have found
out.

It's a subtle but very important distinction.

Processing a SQL statement is usually done in two parts; parsing and
binding. Parsing is where the database engine figures out *where* to get
the information requested in the statement (e.g. which files the rows
from the tables are physically stored in), binding is when the parameter
values you supply are used to figure out *what* to return.

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

[Sibylle Koczian]

Re: MySQLdb select

Andy Todd schrieb:[color=blue]
> 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@vetinari :~$ mysql[/color]
[snip][color=blue]
> 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.
>[/color]
Will do. Thank you. I never thought of trying this directly in MySQL,
always took it for a problem between MySQL and Python. I'll check the
MySQL newsgroup first, as it's in German.

Koczian

[Sibylle Koczian]

Re: MySQLdb select

Sibylle Koczian schrieb:[color=blue]
> Andy Todd schrieb:
>[color=green]
>> 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;
>>[/color][/color]
[snip]
[color=blue][color=green]
>> 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.
>>[/color]
> Will do. Thank you. I never thought of trying this directly in MySQL,
> always took it for a problem between MySQL and Python. I'll check the
> MySQL newsgroup first, as it's in German.
>[/color]
Not really a bug, possibly no very good design decision (not new with
MySQL). From the manual:

"13.1.3 Comparison Functions and Operators
[snip]
MySQL compares values using the following rules:
[snip]
* If one of the arguments is a TIMESTAMP or DATETIME column and the
other argument is a constant, the constant is converted to a timestamp
before the comparison is performed. This is done to be more
ODBC-friendly. Note that the is not done for arguments in IN()! To be
safe, always use complete datetime/date/time string when doing comparisons."

If a date column is treated the same way, it's clear: "=" compares the
date column with a timestamp with time part 0 and gets true; IN compares
the date with a string containing '00:00:00' and gets false.

Regards,
Koczian