MySQLdb select

[John Fabiani]

Hi,
I'm a newbie and I'm attempting to learn howto create a select statement.
When I use 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]

Hi,
I'm a newbie and I'm attempting to learn howto create a select statement.
When I use

I'm used to do that this way:

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

HTH
Franz GEIGER

 

[=?ISO-8859-1?Q?Gerhard_H=E4ring?=]

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

 

[R Baumann]

Then use %i for integers ;-)


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

 

[John Fabiani]

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

 

[wes weston]

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

 

[Andy Todd]

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:

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

You should use

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;

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

 

[Dennis Lee Bieber]

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:\>

--

 

[Harald Massa]

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:

"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

 

[Peter Abel]

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:

('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 = 10'

Even if your first example works from the point of view of SQL
it wouldn't give you the results, you want.
('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:

Regards Peter

"John Fabiani" ... [snip]

 

[Andy Todd]

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:

('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 = 10'

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


('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 = '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

is a different statement to

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. aram1 or :1).

Not that it's vitally important, but the first statement I showed above
is more correctly written as;

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

 

[Sibylle Koczian]

Not that it's vitally important, but the first statement I showed above
is more correctly written as;


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

 

[Dennis Lee Bieber]

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

"""
-=-=-=-=-=-=-


--

 

[Sibylle Koczian]

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

 

[Dennis Lee Bieber]

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.


--

 

[Sibylle Koczian]

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:
"SELECT * FROM fehllief WHERE fehltag IN ('2004-07-29 00:00:00',
'2004-07-07 00:00:00')"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]

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.

--

 

[Andy Todd]

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:

"SELECT * FROM fehllief WHERE fehltag IN ('2004-07-29 00:00:00',
'2004-07-07 00:00:00')"
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

 

[F. GEIGER]

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:
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:

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

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

 

[Andy Todd]

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:


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:


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