[DB-SIG] Preparing statement API

[Eric Brunson]

* M.-A. Lemburg (mal@lemburg.com) [000125 18:25]:
> 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.

How does what Hrvoje is asking for differ from the Handle object in
the DCOracle module?

From the DCOracle docs:

        prepare(sql) --
             Returns a new Handle Object.  A handle is a pre-prepared
             notion of a cursor, where some handling of the SQL
             parsing has already been done. 

You use it like this:

(Assume a DB connection called "dbc")

inshndl = dbc.prepare( "insert into mytable ( attribute, value ) "
		       "values ( :p1, :p2 )" )
for attr, val in list:
    inshndl.execute( (attr, val) )


:p1 and :p2 can also be named parameters, but as I use them in that
example, they are simply positional.

This is a pretty common usage from within an API like Pro*C.  I'm
sorry that I don't have experience with anything but Oracle and the
DCOracle python module, so perhaps I'm missing something in the
generalization of the problem to other DB vendors.

For what it's worth,
e.

-- 
Eric Brunson                * _ o  *       Faster and faster,             
brunson@brunson.com        * / //\           until the thrill of speed    
brunson@level3.net            \>>|   *         overcomes the fear of death
page-eric@level3.net           \\,