Problem with format string / MySQL cursor

[Gabriel Genellina]

En Sat, 20 Oct 2007 18:40:38 -0300, Dennis Lee Bieber  
<wlfraed at ix.netcom.com> escribi�:

> On Fri, 19 Oct 2007 18:50:20 -0300, "Gabriel Genellina"
> <gagsl-py2 at yahoo.com.ar> declaimed the following in comp.lang.python:

>> If the MySQLdb adapter actually converts and inserts the arguments  
>> itself
>> into the supplied SQL sentence, generating a new string for each call,
>> this advantage -and many others- are cancelled, defeating the purpose of
>> bound variables.
>
> 	Okay, I'm a bit behind the time... The book I keep on the floor next
> to my computer is the old brown/black version covering version 3.x and
> introducing ver 4.0 (my newer books are across the room in shelves
> behind the couch that take time to reach)
>
> 	It can't be fully blamed on the MySQLdb adapter... After crawling
> through too many reference books, it looks like MySQL itself didn't
> support "prepared statements" until sometime in the ver 4.1.x period --
> I believe the MySQLdb adapter is still ver 3.x compatible, using the
> mysql_query(connection, SQL_string) call, rather than the half-dozen
> newer functions for separately binding parameters to prepared
> statements.

Yes, if the database doesn't allow for prepared statements the poor  
adapter can't do much...

> 	Since, to my experience, db-api 2 doesn't expose the concepts of
> prepared statements to the user, about the only place using them would
> offer a true speed-up would be in the .executemany() call (and even then
> it would seem to depend upon which is more costly: formatting a properly  
> [...]

Some databases automatically cache SQL statements. I know DB2 does that,  
and I think Oracle does too (or is it the JDBC layer?). So it's not  
required that user code explicitely prepares SQL statements.
Prepared statements have other advantages, apart from the speed gain when  
using repetitive queries: they help to keep the statement length below  
some size limit, they help to lower the cpu load on the server (by not  
having to parse and optimize the query again), and they avoid SQL  
injection (else, you have to rely on the quoting and escaping being well  
done, even on the DB-API adapter).

> 	If the api exposed them to the user, I could see a potential use for
> them: an application with, say, multiple forms (each of one or more DB
> tables)... By preparing and saving a set of statements for each form
> (parameterized select, update, insert, delete, say) during application
> startup (or conditionally on first entry to a form) one would only need
> to reference the prepared statements for the forms as the end-user
> navigates the forms.

But DB-API 2.0 already allows that. PEP 249 says, when describing  
cursor.execute:

             "A reference to the operation will be retained by the
             cursor.  If the same operation object is passed in again,
             then the cursor can optimize its behavior.  This is most
             effective for algorithms where the same operation is used,
             but different parameters are bound to it (many times)."

So nothing special is required to enable prepared statements; the adapter  
is free to prepare and re-use queries if desired.
BTW, the situation is similar to the re module: you can pre-compile your  
regular expressions, but it's not required, as the re module caches  
compiled expressions, reusing them later when the same r.e. is seen.

> {At least we're not discussing Firebird... 1000+ page book on it and the
> closest it comes to discussing a C-language API is to mention that the C
> header file defining the functions is included with the binary
> install... And for other APIs it just gives URLs}

Nice book! :)

-- 
Gabriel Genellina