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 21 5160
"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
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
"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
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
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
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/
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/> <
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
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 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/
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
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/> <
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
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/> <
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
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/> <
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/
"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
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/
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Chris Stromberger |
last post by:
When issuing updates in mysql (in the console window), mysql will tell
you if any rows matched and how many rows were updated (see below). I
know how to get number of rows udpated using MySQLdb,...
|
by: Wesley Kincaid |
last post by:
I'm attempting to run a simple query through MySQLdb's
cursor.execute(). However, when the request includes a timestamp
field, I'm getting "ValueError: invalid literal for int(): 9-."
Could...
|
by: ws Wang |
last post by:
MySQLdb is working fine at command line, however when I tried to use
it with mod_python, it give me a "server not initialized" error.
This is working fine:
----------------------- testmy.py...
|
by: Fred |
last post by:
I hope someone can help me with the below problem...
Thanks,
Fred
My enviroment:
--------------------------
Slackware Linux 10.2
Python 2.4.2
MySql version 4.1.14
|
by: John Salerno |
last post by:
Since the connect method of mysqldb requires a database name, it seems
like you can't use it without having a database already created. So is
there a way to connect to your mysql server (without a...
|
by: Sells, Fred |
last post by:
I had some code originally that printed the sql and params when I called
the .execute method. I removed it but it still prints. I rebooted and
renamed files and still it prints. I am totally...
|
by: Nikhil |
last post by:
I am using the MySQLdb python module. I have a table named 'testing'
with few columns, under the 'test' database, what is hosted on a remote
mysql server.
I want to run the following query to...
|
by: Evan |
last post by:
a simple problem but I do not know why...:(, could anyone help me?
MySQLdb nominally uses just the %s placeholder style, in my script, i
got error if you want to use placeholder(%s) for table...
|
by: Edwin.Madari |
last post by:
replace the name of table before calling *.execute.
s.dbptr.execute(str % (e))
good luck.
Edwin
-----Original Message-----
From: python-list-bounces+edwin.madari=verizonwireless.com@python.org...
|
by: lllomh |
last post by:
Define the method first
this.state = {
buttonBackgroundColor: 'green',
isBlinking: false, // A new status is added to identify whether the button is blinking or not
}
autoStart=()=>{
|
by: Aliciasmith |
last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
|
by: tracyyun |
last post by:
Hello everyone,
I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
|
by: giovanniandrean |
last post by:
The energy model is structured as follows and uses excel sheets to give input data:
1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
|
by: NeoPa |
last post by:
Introduction
For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
|
by: Teri B |
last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course.
0ne-to-many. One course many roles.
Then I created a report based on the Course form and...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM)
Please note that the UK and Europe revert to winter time on...
|
by: NeoPa |
last post by:
Introduction
For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
|
by: GKJR |
last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...
| |