[DB-SIG] Towards a single parameter style

[Anthony Tuininga]

On Fri, 2003-02-21 at 07:09, Federico Di Gregorio wrote:
> Il ven, 2003-02-21 alle 04:33, Stuart Bishop ha scritto:
> > On Wednesday, February 19, 2003, at 10:59  PM, Kevin Jacobs wrote:
> > 
> > > This requires storing a cursor for each prepared command, and still 
> > > does not
> > > address the main point of prepared statements -- they only make
> sense 
> > > if you
> > > call them many times!  DB-API 2.0 provides a simplistic version of
> this
> > > concept -- executemany -- though it requires that all of the
> arguments 
> > > are
> > > available at once, and does not allow any other statements
> interleaved
> > > between them.
> > 
> > Passing the same string to cursor.execute should re-execute the
> already
> > prepared statement if it already exists.
> 
> prepared statements are usefull only ona bunch of db backends that
> support such stuff (and through odbc). i am absolutely against prepared
> statements, goven the fact that a lot of db will be penalized by them.
> executemany is much better, because can be implemented the right way for
> both typpes of db.

This isn't the only use case. If you already have in hand all of the
data that needs to be manipulated by a cursor, then executemany() is the
way to go. The fact of the matter is that there are many cases where you
don't have all of the data, but it is made available at different times
in the application. Obviously, you could change the way the application
is written to acquire all of the data up front but that isn't always
possible or desirable. The existing DB API works fairly well with one
extension that I have written for cx_Oracle and I know others have
written as well.

def prepare(self, statement):
    self.statement = statement
    do the work of preparing

def execute(self, statement, args):
    if statement is not None and statement is not self.statement:
        statement.prepare(statement)
     do the work of executing

Whether or not the prepare method is visible (it is very convenient
though if it is), the prepare step takes place internally and can be
skipped if the same statement (or None) is passed through to the
execute() method, thus improving performance. And the performance gain
here is considerable, even for Oracle where a second prepare is not as
costly as in some other environments. I have noted about 30-40%
performance improvements for heavy use of prepared cursors.

> note: the python dbapi is great because it frees the user from a lot of
> low level details. implicit transaction begin is good. executemany is
> good. why introduce a lot of db-specific concepts?

One word: performance. If performance isn't a big deal, then neither is
prepared cursors. If you need other reasons: convenience is a big one.
And we all know how lazy we programmers are, eh? :-)

> -- 
> Federico Di Gregorio
> Debian GNU/Linux Developer                                fog@debian.org
> INIT.D Developer                                           fog@initd.org
>                            Don't dream it. Be it. -- Dr. Frank'n'further
-- 
Anthony Tuininga
anthony@computronix.com
 
Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com