[DB-SIG] Preparing statement API

Hrvoje Niksic hniksic@iskon.hr
25 Jan 2000 15:33:01 +0100

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

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.