[Tutor] Re: Q

[John Fabiani]

Andy Todd wrote:

> 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
Thanks very much - I think a little light was just turned on.  It makes sense 
that the number (numb in this case) be converted to a string - since I'm 
passing a string to the DB module (I want to call it a driver/interface).  I 
was writing up this long note to myself trying to explain the way to pass 
number and strings.  But now I realize how simple the issue really was.

Well I'm learning daily.  
john