[Tutor] Re: Q (original) (raw)
John Fabiani jfabiani at yolo.com
Sat Jul 31 22:46:48 CEST 2004
- Previous message: [Tutor] Understanding DBAPI cursor.execute
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
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 "", 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 autoincrement 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
- Previous message: [Tutor] Understanding DBAPI cursor.execute
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]