By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,125 Members | 1,570 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,125 IT Pros & Developers. It's quick & easy.

MySQLdb select

P: n/a
Hi,
I'm a newbie and I'm attempting to learn howto create a select statement.
When I use
string1='18 Tadlock Place'
cursor.execute("SELECT * FROM mytest where address = %s",string1) All works as expected. But numb=10
cursor.execute("SELECT * FROM mytest where clientID = %d",numb)

Traceback (innermost last):
File "<stdin>", line 1, in ?
File "/usr/lib64/python2.3/site-packages/MySQLdb/cursors.py", line 95, in
execute
return self._execute(query, args)
File "/usr/lib64/python2.3/site-packages/MySQLdb/cursors.py", line 110, in
_execute
self.errorhandler(self, TypeError, m)
File "/usr/lib64/python2.3/site-packages/MySQLdb/connections.py", line 33,
in defaulterrorhandler
raise errorclass, errorvalue
TypeError: int argument required

ClientID was created using "clientID int not null auto_increment primary
key"
What is the correct way passing the numb var to the string?
Thanks John
Jul 18 '05 #1
Share this Question
Share on Google+
21 Replies


P: n/a

"John Fabiani" <jf******@yolo.com> schrieb im Newsbeitrag
news:lG*****************@newssvr21.news.prodigy.co m...
Hi,
I'm a newbie and I'm attempting to learn howto create a select statement.
When I use
string1='18 Tadlock Place'
cursor.execute("SELECT * FROM mytest where address = %s",string1) All works as expected. But numb=10
cursor.execute("SELECT * FROM mytest where clientID = %d",numb)

I'm used to do that this way:

cursor.execute("SELECT * FROM mytest where clientID = %d" % numb)

HTH
Franz GEIGER
Traceback (innermost last):
File "<stdin>", line 1, in ?
File "/usr/lib64/python2.3/site-packages/MySQLdb/cursors.py", line 95, in execute
return self._execute(query, args)
File "/usr/lib64/python2.3/site-packages/MySQLdb/cursors.py", line 110, in _execute
self.errorhandler(self, TypeError, m)
File "/usr/lib64/python2.3/site-packages/MySQLdb/connections.py", line 33, in defaulterrorhandler
raise errorclass, errorvalue
TypeError: int argument required

ClientID was created using "clientID int not null auto_increment primary
key"
What is the correct way passing the numb var to the string?
Thanks John

Jul 18 '05 #2

P: n/a
F. GEIGER wrote:
"John Fabiani" <jf******@yolo.com> schrieb:
Hi,
I'm a newbie and I'm attempting to learn howto create a select statement.
When I use
>string1='18 Tadlock Place'
>cursor.execute("SELECT * FROM mytest where address = %s",string1)
All works as expected. But
>numb=10
>cursor.execute("SELECT * FROM mytest where clientID = %d",numb)

[...] raise errorclass, errorvalue
TypeError: int argument required


Then use %i for integers ;-)
I'm used to do that this way:

cursor.execute("SELECT * FROM mytest where clientID = %d" % numb)


That's particularly BAD STYLE. It's best to keep to letting the DB-API
do the proper quoting for all parameters.

-- Gerhard
Jul 18 '05 #3

P: n/a
"Gerhard Häring" <gh@ghaering.de> wrote in message
news:ma**************************************@pyth on.org...
F. GEIGER wrote:
"John Fabiani" <jf******@yolo.com> schrieb:

<SNIP>
Then use %i for integers ;-)
I'm used to do that this way:

cursor.execute("SELECT * FROM mytest where clientID = %d" % numb)


That's particularly BAD STYLE. It's best to keep to letting the DB-API
do the proper quoting for all parameters.

-- Gerhard


Gerhard, I really have no intention of starting a pissing match with you or
anyone else on this ng, but when you tell someone else that a particular
piece of code is BAD STYLE without showing an example of GOOD STYLE, it
pisses me off. The newbies here (myself included) don't learn anything, and
IMO, that what the newsgroups are for, to pass along hints, tips, and
knowledge from people who DO know what's right and what's wrong. How about
an example?

Thanks
Ruben
Jul 18 '05 #4

P: n/a
thanks that worked but I don't understand why.
cursor.execute("SELECT * FROM mytest where address = %s",string1)
above works but - following your suggestion:
cursor.execute("SELECT * FROM mytest where address = %s" % string1)
above does not work. So why did
cursor.execute("SELECT * FROM mytest where clientID = %d" % numb)
work???????????????
john
F. GEIGER wrote:

"John Fabiani" <jf******@yolo.com> schrieb im Newsbeitrag
news:lG*****************@newssvr21.news.prodigy.co m...
Hi,
I'm a newbie and I'm attempting to learn howto create a select
statement.
When I use
>>> string1='18 Tadlock Place'
>>> cursor.execute("SELECT * FROM member")

All works as expected. But
>>> numb=10
>>> cursor.execute("SELECT * FROM mytest where clientID = %d",numb)


I'm used to do that this way:

cursor.execute("SELECT * FROM mytest where clientID = %d" % numb)

HTH
Franz GEIGER
Traceback (innermost last):
File "<stdin>", line 1, in ?
File "/usr/lib64/python2.3/site-packages/MySQLdb/cursors.py", line 95,

in
execute
return self._execute(query, args)
File "/usr/lib64/python2.3/site-packages/MySQLdb/cursors.py", line 110,

in
_execute
self.errorhandler(self, TypeError, m)
File "/usr/lib64/python2.3/site-packages/MySQLdb/connections.py", line

33,
in defaulterrorhandler
raise errorclass, errorvalue
TypeError: int argument required

ClientID was created using "clientID int not null auto_increment primary
key"
What is the correct way passing the numb var to the string?
Thanks John


Jul 18 '05 #5

P: n/a
John Fabiani wrote:
thanks that worked but I don't understand why.
cursor.execute("SELECT * FROM mytest where address = %s",string1)
above works but - following your suggestion:
cursor.execute("SELECT * FROM mytest where address = %s" % string1)
above does not work. So why did
cursor.execute("SELECT * FROM mytest where clientID = %d" % numb)
work???????????????
john
F. GEIGER wrote:


John,

cursor.execute("SELECT * FROM mytest where address = %s" % string1)

would work if it was:

cursor.execute("SELECT * FROM mytest where address = '" + string1 + "'")

as strings in sql require single quotes; ints do not - but will work
with quotes.
wes

Jul 18 '05 #6

P: n/a
John Fabiani wrote:
thanks that worked but I don't understand why.
cursor.execute("SELECT * FROM mytest where address = %s",string1)
above works but - following your suggestion:
cursor.execute("SELECT * FROM mytest where address = %s" % string1)
above does not work. So why did
cursor.execute("SELECT * FROM mytest where clientID = %d" % numb)
work???????????????
john
F. GEIGER wrote:

"John Fabiani" <jf******@yolo.com> schrieb im Newsbeitrag
news:lG*****************@newssvr21.news.prodigy. com...
Hi,
I'm a newbie and I'm attempting to learn howto create a select
statement.
When I use

>>string1='18 Tadlock Place'
>>cursor.execute("SELECT * FROM member")

All works as expected. But

>>numb=10
>>cursor.execute("SELECT * FROM mytest where clientID = %d",numb)


I'm used to do that this way:

cursor.execute("SELECT * FROM mytest where clientID = %d" % numb)

HTH
Franz GEIGER

Traceback (innermost last):
File "<stdin>", line 1, in ?
File "/usr/lib64/python2.3/site-packages/MySQLdb/cursors.py", line 95,


in
execute
return self._execute(query, args)
File "/usr/lib64/python2.3/site-packages/MySQLdb/cursors.py", line 110,


in
_execute
self.errorhandler(self, TypeError, m)
File "/usr/lib64/python2.3/site-packages/MySQLdb/connections.py", line


33,
in defaulterrorhandler
raise errorclass, errorvalue
TypeError: int argument required

ClientID was created using "clientID int not null auto_increment primary
key"
What is the correct way passing the numb var to the string?
Thanks John


It's not too intuitive, but when using the 'format' parameter style in
DB-API modules[1] (as used here) you *always* use '%s' to indicate that
a substitution should take place.

The solution that F GEIGER proposed uses string formatting and will not
take advantage of parameter binding properly. So instead of
cursor.execute("SELECT * FROM mytest where clientID = %d" % numb)
You should use
cursor.execute("SELECT * FROM mytest where clientID = %s", numb)
The first version 'works' because the string is parsed before it is
passed to the MySQLdb module, so if numb is 10 it's the exact same as;
cursor.execute("SELECT * FROM mytest where clientID = 10")


The second (and correct) suggestion uses parameter binding, so if you
execute the statement a number of times (with different values of
'numb') the database has to do less work - and therefore your code will
run faster. This is important but probably outside the scope of this
discussion.

[1] http://www.python.org/peps/pep-0249.html

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

Jul 18 '05 #7

P: n/a
On Sat, 31 Jul 2004 08:23:45 GMT, John Fabiani <jf******@yolo.com>
declaimed the following in comp.lang.python:

cursor.execute("SELECT * FROM mytest where clientID = %d",numb)

I think I've always used %s regardless of argument type, and let
the MySQLdb module figure out how to convert the argument based on what
the type supplied actually is (after all, everything in the select is
text when sent to the engine, and the module is smart enough to
quote/escape string arguments).

-=-=-=-=-=-=-=-=-
import MySQLdb

myDB = MySQLdb.connect(host="localhost",
user=<suppressed>,
passwd=<suppressed>,
db="bestiaria")
myC = myDB.cursor()

for id in (1, 5, 7, 3):
myC.execute("select name, URL, description from comics where ID
= %s", id) #warning -- line wrapped in newsreader
for aComic in myC.fetchall():
(name, URL, description) = aComic
print '"',name,'"', URL, description

myC.close()
myDB.close()

-=-=-=-=-=-=-=-=-=-
Output:
E:\>python test.py
" Aford " http://www.afordturtle.com/ A turtle, a snake, and a robin;
and their life in the forest.
" Camera Angles " http://www.martha.net/camera_angles/ Five characters
in avoidance of an author.
" Cigarro & Cerveja by Tony Esteves " http://www.cigarro.ca/ A Canadian
goose with spirit and a rabbit who's smokin'.
" Birdbun Theater " http://www.birdbun.com/ A high society Cage aux
Folles.

E:\>

Change the %s to a %d and...

E:\>python test.py
Traceback (most recent call last):
File "test.py", line 11, in ?
myC.execute("select name, URL, description from comics where ID =
%d", id)
File "D:\Python23\Lib\site-packages\MySQLdb\cursors.py", line 95, in
execute
return self._execute(query, args)
File "D:\Python23\Lib\site-packages\MySQLdb\cursors.py", line 110, in
_execute

self.errorhandler(self, TypeError, m)
File "D:\Python23\Lib\site-packages\MySQLdb\connections.py", line 33,
in defaulterrorhandler
raise errorclass, errorvalue
TypeError: int argument required

E:\>

-- ================================================== ============ <
wl*****@ix.netcom.com | Wulfraed Dennis Lee Bieber KD6MOG <
wu******@dm.net | Bestiaria Support Staff <
================================================== ============ <
Home Page: <http://www.dm.net/~wulfraed/> <
Overflow Page: <http://wlfraed.home.netcom.com/> <

Jul 18 '05 #8

P: n/a
Ruben,

it is considered bad style wihtin c.l.p. to have aggressive emotions
apart from mails about a "ternary operator"

The correct way would have been:
cursor.execute("SELECT * FROM mytest where clientID = %d" % numb)

That's particularly BAD STYLE. It's best to keep to letting the
DB-API do the proper quoting for all parameters.


"Gerhard, why is this BAD STYLE? And how can I make the DB-API do the
proper quoting for all parameters?"
And probably the answer would have been:

with doing

"SELECT * FROM mytest where clientID = %d" % numb

you are using the standard Python String-Formatting Functions. That may
or may not lead to success.

Especially when using a mixture of strings and numbers, they have to be
quoted differently.

Also special characters within strings have to be quoted - for example,
the single ' has to be quoted within SQL because it is usually used as
string delimiter

cursor.execute("SELECT * FROM mytest where clientID = %d" , numb)

The "," is the magic -

with "Bla " % sth you are passing ONE argument --- the %s gets
substituted, and one argument gets passed to the function.

With "," two arguments get passed to the execute-"function" so that the
DB-API has the challenge to integrate the parameters into the SQL-String.

And it is more bad style to write

"SELECT * FROM mytest where clientID = %d" % numb

because % is defined with using a Tuple as second parameter, that would
be
"clientID = %d" % (numb,)

Using % with sth. else than a tuple may succeed or give you very funny
errors if numb happens to be a List in a later run.

Best wishes,

Harald
Jul 18 '05 #9

P: n/a
John Fabiani <jf******@yolo.com> wrote in message news:<9R*****************@newssvr27.news.prodigy.c om>...
thanks that worked but I don't understand why.
cursor.execute("SELECT * FROM mytest where address = %s",string1)
above works but - following your suggestion:
cursor.execute("SELECT * FROM mytest where address = %s" % string1)
above does not work. So why did
cursor.execute("SELECT * FROM mytest where clientID = %d" % numb)
work???????????????
john
F. GEIGER wrote:

numb=10
"SELECT * FROM mytest where clientID = %d",numb ('SELECT * FROM mytest where clientID = %d', 10)

The result of the above is a tuple, where the first value is a string
and the second one is an int.
So you call cursor.execute (..) with a two values. The first value is
'SELECT * FROM mytest where clientID = %d' which raises the
exception because %d is never an int.
What you wanted to do is to call cursor.execute (..) with one parameter,
which is a string. So as ohers pointed out you have to use Python's
formatting features to get the desired string:
"SELECT * FROM mytest where clientID = %d" % numb 'SELECT * FROM mytest where clientID = 10'

Even if your first example works from the point of view of SQL
it wouldn't give you the results, you want.
string1='18 Tadlock Place'
"SELECT * FROM mytest where address = %s",string1 ('SELECT * FROM mytest where address = %s', '18 Tadlock Place')

That means again, your'e calling cursor.execute (..) with two parameters
where the first one - again - is 'SELECT * FROM mytest where address = %s'
So youre SQL-result should be empty, cause you surely don't have an adress
with the value %s.

So formatting it the right way and adding single quotmarks araound string1
will give you the result you want:
"SELECT * FROM mytest where address = '%s'" % string1 "SELECT * FROM mytest where address = '18 Tadlock Place'"

Regards Peter

"John Fabiani" ... [snip]

Jul 18 '05 #10

P: n/a
Peter Abel wrote:
John Fabiani <jf******@yolo.com> wrote in message news:<9R*****************@newssvr27.news.prodigy.c om>...
thanks that worked but I don't understand why.
cursor.execute("SELECT * FROM mytest where address = %s",string1)
above works but - following your suggestion:
cursor.execute("SELECT * FROM mytest where address = %s" % string1)
above does not work. So why did
cursor.execute("SELECT * FROM mytest where clientID = %d" % numb)
work???????????????
john
F. GEIGER wrote:


numb=10
"SELECT * FROM mytest where clientID = %d",numb
('SELECT * FROM mytest where clientID = %d', 10)

The result of the above is a tuple, where the first value is a string
and the second one is an int.
So you call cursor.execute (..) with a two values. The first value is
'SELECT * FROM mytest where clientID = %d' which raises the
exception because %d is never an int.
What you wanted to do is to call cursor.execute (..) with one parameter,
which is a string. So as ohers pointed out you have to use Python's
formatting features to get the desired string:

"SELECT * FROM mytest where clientID = %d" % numb
'SELECT * FROM mytest where clientID = 10'

Even if your first example works from the point of view of SQL
it wouldn't give you the results, you want.

string1='18 Tadlock Place'
"SELECT * FROM mytest where address = %s",string1
('SELECT * FROM mytest where address = %s', '18 Tadlock Place')

That means again, your'e calling cursor.execute (..) with two parameters
where the first one - again - is 'SELECT * FROM mytest where address = %s'
So youre SQL-result should be empty, cause you surely don't have an adress
with the value %s.

So formatting it the right way and adding single quotmarks araound string1
will give you the result you want:

"SELECT * FROM mytest where address = '%s'" % string1
"SELECT * FROM mytest where address = '18 Tadlock Place'"
Regards Peter

"John Fabiani" ... [snip]
Peter,

Your first statement, explaining why the exception is raised, is correct.

Your second statement is false. Worse, misleading and possibly
dangerous. In the MySQLdb driver (as I stated in my response to the
original poster) the %s simply acts as a marker in the SQL string to
instruct the database module to place the next parameter in that part of
the statement, thus
cursor.execute("SELECT * FROM mytest WHERE address=%s", string1)
is a different statement to
cursor.execute("SELECT * FROM mytest WHERE address='%s'" % string1)
The first one uses database parameter passing (and will cope with *any*
type of object - although it will probably only work with strings,
numbers and dates) whereas the second mucks about with the string before
it is passed to the database module.

You should always do the first. This allows the database module to
handle type conversion, character encoding and a plethora of other nasty
issues which you as the application programmer really, *really* don't
want to know about.

In databases where parameter binding is properly supported (for instance
Oracle) using the first form will also make your code more efficient
(removing the parsing overhead if you must know).

The point to note here is that the MySQLdb database module uses %s not
for string interpolation but for parameter passing. Other database
modules use ? (for instance mxODBC) and others use named or numbered
parameters (e.g. :param1 or :1).

Not that it's vitally important, but the first statement I showed above
is more correctly written as;
cursor.execute("SELECT * FROM mytest WHERE address=%s", (string1,))


Because the second argument to the 'execute' method is a tuple of
parameter values to be substituted in the first argument - the SQL
statement.

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

Jul 18 '05 #11

P: n/a
Andy Todd schrieb:
Not that it's vitally important, but the first statement I showed above
is more correctly written as;
>>> cursor.execute("SELECT * FROM mytest WHERE address=%s", (string1,))


Because the second argument to the 'execute' method is a tuple of
parameter values to be substituted in the first argument - the SQL
statement.

I'm quite surprised that it works the other way - but it does. Could it
be that previous versions of MySQLdb raised an exception with the other form

cursor.execute("SELECT * FROM mytest WHERE address=%s", string1)?

Regards,
Koczian
Jul 18 '05 #12

P: n/a
On Tue, 03 Aug 2004 15:02:21 +0200, Sibylle Koczian
<Si*************@Bibliothek.Uni-Augsburg.de> declaimed the following in
comp.lang.python:

I'm quite surprised that it works the other way - but it does. Could it
be that previous versions of MySQLdb raised an exception with the other form

Well, from the MySQLdb code itself, we have these little
comments...

-=-=-=-=-=-=-
def literal(self, o):
"""

If o is a single object, returns an SQL literal as a string.
If o is a non-string sequence, the items of the sequence are
converted and returned as a sequence.

Non-standard.

"""
-=-=-=-=-=-=-
def execute(self, query, args=None):

"""Execute a query.

query -- string, query to execute on server
args -- optional sequence or mapping, parameters to use with
query.

Note: If args is a sequence, then %s must be used as the
parameter placeholder in the query. If a mapping is used,
%(key)s must be used as the placeholder.

Returns long integer rows affected, if any

"""
-=-=-=-=-=-=-
-- ================================================== ============ <
wl*****@ix.netcom.com | Wulfraed Dennis Lee Bieber KD6MOG <
wu******@dm.net | Bestiaria Support Staff <
================================================== ============ <
Home Page: <http://www.dm.net/~wulfraed/> <
Overflow Page: <http://wlfraed.home.netcom.com/> <

Jul 18 '05 #13

P: n/a
Dennis Lee Bieber schrieb:
On Tue, 03 Aug 2004 15:02:21 +0200, Sibylle Koczian
<Si*************@Bibliothek.Uni-Augsburg.de> declaimed the following in
comp.lang.python:
I'm quite surprised that it works the other way - but it does. Could it
be that previous versions of MySQLdb raised an exception with the other form
Well, from the MySQLdb code itself, we have these little
comments...

-=-=-=-=-=-=-
def literal(self, o):
"""

If o is a single object, returns an SQL literal as a string.
If o is a non-string sequence, the items of the sequence are
converted and returned as a sequence.

Non-standard.

"""


I don't see where "literal" comes into this?
-=-=-=-=-=-=-
def execute(self, query, args=None):

"""Execute a query.

query -- string, query to execute on server
args -- optional sequence or mapping, parameters to use with
query.

Note: If args is a sequence, then %s must be used as the
parameter placeholder in the query. If a mapping is used,
%(key)s must be used as the placeholder.

Returns long integer rows affected, if any

"""


That would confirm that a single string or number is not a correct args
parameter, wouldn't it? There is nothing here like "if args is no
sequence, it is treated as a tuple of length one" - but this seems to be
what happens.

Koczian
Jul 18 '05 #14

P: n/a
On Wed, 04 Aug 2004 11:03:49 +0200, Sibylle Koczian
<Si*************@Bibliothek.Uni-Augsburg.de> declaimed the following in
comp.lang.python:


I don't see where "literal" comes into this?
Ah, but that /is/ the function that converts the arguments
passed to .execute() into a form where they can replace the %s tokens..
That would confirm that a single string or number is not a correct args
parameter, wouldn't it? There is nothing here like "if args is no
sequence, it is treated as a tuple of length one" - but this seems to be
what happens.
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.
-- ================================================== ============ <
wl*****@ix.netcom.com | Wulfraed Dennis Lee Bieber KD6MOG <
wu******@dm.net | Bestiaria Support Staff <
================================================== ============ <
Home Page: <http://www.dm.net/~wulfraed/> <
Overflow Page: <http://wlfraed.home.netcom.com/> <

Jul 18 '05 #15

P: n/a
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
Jul 18 '05 #16

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

-- ================================================== ============ <
wl*****@ix.netcom.com | Wulfraed Dennis Lee Bieber KD6MOG <
wu******@dm.net | Bestiaria Support Staff <
================================================== ============ <
Home Page: <http://www.dm.net/~wulfraed/> <
Overflow Page: <http://wlfraed.home.netcom.com/> <

Jul 18 '05 #17

P: n/a
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@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/

Jul 18 '05 #18

P: n/a
"Gerhard Häring" <gh@ghaering.de> schrieb im Newsbeitrag
news:ma**************************************@pyth on.org...

That's particularly BAD STYLE. It's best to keep to letting the DB-API
do the proper quoting for all parameters.

Well, yes.

So I tried this:
import MySQLdb as ms
con = ms.connect(db="isa",user="root")
cur = con.cursor()
cur.execute("select id from %s limit 10;", ("tagevents",)) Traceback (most recent call last):
File "<stdin>", line 1, in ?
File "C:\PROGRA~1\Python23\lib\site-packages\MySQLdb\cursors.py", line 95,
in execute
return self._execute(query, args)
File "C:\PROGRA~1\Python23\lib\site-packages\MySQLdb\cursors.py", line
114, in _execute
self.errorhandler(self, exc, value)
File "C:\PROGRA~1\Python23\lib\site-packages\MySQLdb\connections.py", line
33, in defaulterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (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:
cur.execute("select id from %s limit 10;" % "tagevents") 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
"Gerhard Häring" <gh@ghaering.de> schrieb im Newsbeitrag
news:ma**************************************@pyth on.org... F. GEIGER wrote:
"John Fabiani" <jf******@yolo.com> schrieb:
Hi,
I'm a newbie and I'm attempting to learn howto create a select

statement.When I use

>>string1='18 Tadlock Place'
>>cursor.execute("SELECT * FROM mytest where address = %s",string1)

All works as expected. But

>>numb=10
>>cursor.execute("SELECT * FROM mytest where clientID = %d",numb)
>> [...] raise errorclass, errorvalue
>>TypeError: int argument required
>>


Then use %i for integers ;-)
I'm used to do that this way:

cursor.execute("SELECT * FROM mytest where clientID = %d" % numb)


That's particularly BAD STYLE. It's best to keep to letting the DB-API
do the proper quoting for all parameters.

-- Gerhard

Jul 18 '05 #19

P: n/a
F. GEIGER wrote:
"Gerhard Häring" <gh@ghaering.de> schrieb im Newsbeitrag
news:ma**************************************@pyth on.org...
That's particularly BAD STYLE. It's best to keep to letting the DB-API
do the proper quoting for all parameters.


Well, yes.

So I tried this:

import MySQLdb as ms
con = ms.connect(db="isa",user="root")
cur = con.cursor()
cur.execute("select id from %s limit 10;", ("tagevents",))
Traceback (most recent call last):
File "<stdin>", line 1, in ?
File "C:\PROGRA~1\Python23\lib\site-packages\MySQLdb\cursors.py", line 95,
in execute
return self._execute(query, args)
File "C:\PROGRA~1\Python23\lib\site-packages\MySQLdb\cursors.py", line
114, in _execute
self.errorhandler(self, exc, value)
File "C:\PROGRA~1\Python23\lib\site-packages\MySQLdb\connections.py", line
33, in defaulterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (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:

cur.execute("select id from %s limit 10;" % "tagevents")


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

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

Jul 18 '05 #20

P: n/a
Andy Todd schrieb:
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 [snip] 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.

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
Jul 18 '05 #21

P: n/a
Sibylle Koczian schrieb:
Andy Todd schrieb:
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;
[snip]
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.

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.

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

Jul 18 '05 #22

This discussion thread is closed

Replies have been disabled for this discussion.