Q

[Andy Todd]

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" <jfabiani at yolo.com> schrieb im Newsbeitrag
>>news:lGIOc.4961$AY5.4762 at 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/