[DB-SIG] Preparing statement API

Greg Stein gstein@lyra.org
Thu, 27 Jan 2000 15:25:16 -0800 (PST)

On Thu, 27 Jan 2000, M.-A. Lemburg wrote:
> All true. Still, Hrvoje has got a point there: currently
> it is not possible to prepare a statement for execution
> (without actually executing it). This is needed in case
> you plan to have one cursor per (often used) statement and
> store these in a cache of your database abstraction
> layer.

class PreparedCursor:
  "This class allows a user to prepare a statement without executing it."
  def __init__(self, conn, statement):
    self.cursor = conn.cursor()
    self.statement = statement
  def __getattr__(self, name):
    return getattr(self.cursor, name)
  def run(self, params):
    return self.cursor.execute(statement, params)

Write the code in Python. Don't force DBAPI implementors to write yet more

If you continue on this track, then the DBAPI is going to have a hundred
functions. Some of them optional, some required. Implementors will be
forced to consider each one, deciding whether it can be done with their
database, whether it should be done, and whether they want to write the
necessary code to get it done.

Users will have a hundred functions to review and figure out which they
need, try to determine through feature testing whether the database
supports it, and write alternate code paths for those that don't. Why
present the user with a lot of functions to consider, when some may not
even apply?

The current design provides for silent, behind-the-scenes, worry-free
optimization for the cases where a statement is used over and over.

> Interface modules not having access to the prepare step of the
> DB can then implement cursor.perpare(cmd) by simply setting 
> cursor.command to cmd and leaving the actual parsing and
> prepare step to the execute method as is currently done.

Or the user can implement a teeny little class like the one that I
provided above. The DBAPI implementor doesn't have to worry about more
methods, cursor state, or execution paths through his code.

The concept of a prepared statements exists solely as a performance
benefit. We don't have to expand the interface just to achieve each and
every possible benefit. The current interface does that just fine. And
users determined to *achieve* those benefits *can*.


Greg Stein, http://www.lyra.org/