[Tutor] Understanding DBAPI cursor.execute

[Lloyd Kvam]

On Sat, 2004-07-31 at 13:07, John Fabiani wrote:
> I'm using MySQLdb (but I'd like to use others DB too) and I'm trying to 
> understand the DBAPI cursor.execute.
> when I use
> cursor.execute("SELECT * FROM mytest where address = %s",string1)

This is the most reliable form.  The module will escape special
characters and create a valid SQL statement placing your parameters into
the slots marked by %s.

> above works but  
> cursor.execute("SELECT * FROM mytest where address = %s" % string1)

cursor.execute("SELECT * FROM mytest where address = '%s'" % string1)
                                                     ^  ^
would have worked.  If you do the string interpolation, you need to
follow all of the MySQLdb requirements in formating the parameters.

> above does not work.
> So why did 
>  cursor.execute("SELECT * FROM mytest where clientID = %d" % numb)

A numeric value does not need quotes to set it off in the SQL command
string.

> work?
> clientID,address was created using
> clientID int not null auto_increment primary key
> address varchar(40)
> 
> Can someone explain why the difference and does the same thing work with other 
> DBAPI drivers? 

There are multiple parameter styles among the DB modules.  The primary
documentation is the DB API spec (version 2).   You might want to
subscribe to the DB-SIG mail list.  It is low volume, but monitored by
the people who write the DB modules and a terrific source of help if you
need it.

> 
> Thanks for the help - someday soon I hope I'll be able to help.
> John
> 
> _______________________________________________
> Tutor maillist  -  Tutor at python.org
> http://mail.python.org/mailman/listinfo/tutor
-- 

Lloyd Kvam
Venix Corp.
1 Court Street, Suite 378
Lebanon, NH 03766-1358

voice:	603-653-8139
fax:	801-459-9582