[DB-SIG] Preparing statement API

[M.-A. Lemburg]

Hrvoje Niksic wrote:
> 
> My idle mind has played with the option of Python's DB API supporting
> the concept of preparing an SQL statement.  I know you can pass the
> same string object to the db module and it can reuse it, but I
> consider that feature very non-obvious and (gasp) un-Pythonical --
> look at how the regexp compilation is handled in Python's regexp
> module.
> 
> I would feel much safer with an explicit prepare interface that looked
> like this:
> 
>     statement = cur.prepare("INSERT INTO foo (user, name) VALUES (?, ?)")
> 
>     for user, name in lst:
>       cur.execute(statement, (user, name))
> 
> The interface change is minimal: only one more function needs to be
> implemented, and all the fetching functions remain unchanged.  The
> advantage of this form over the reuse-old-sql-statement-string method
> is that here the programmer can control what statements to cache, and
> how long to keep the statement handles around.
> 
> Finally, the databases that don't support preparing SQL statements can
> simply define cursor.prepare(str) to return str unchanged, and
> everything will work as before.

The problem with this approach is that you'd have to store
the perpared information somewhere. This is usually done
by the cursor which is not under Python's control.

FYI, the next version of mxODBC will provide this interface:

cursor.prepare(operation) 
         Prepare a database operation (query or command) statement for
         later execution and set cursor.command. To execute a prepared
	 statement, pass cursor.statement to one of the .executeXXX() 	             methods. 

cursor.command
         Provides access to the current prepared SQL command available
         through the cursor. This is set by .prepare(), all catalog
	 methods, .execute() and .executemany().

It uses the hidden reuse optimization as basis. The main
difference between .prepare and the .execute method is that
no statement execution takes place; the command is only parsed
and verified.

This allows keeping a cache of cursors with prepared statements
around which speeds up processing of often recurring queries.

In addition to the above, mxODBC will also have a .freeset()
method which clears the result set currently available on
the cursor -- this is needed in cases where you don't fetch the
whole set as memory optimization.

-- 
Marc-Andre Lemburg
______________________________________________________________________
Business:                                      http://www.lemburg.com/
Python Pages:                           http://www.lemburg.com/python/