[DB-SIG] Towards a single parameter style

[Anthony Tuininga]

I guess this depends on your philosophy since it is clear that the
prepare() method is not strictly necessary to give a performance boost.
I guess the question is whether or not we want a strictly minimal API
that will get the job done, or a rich API that will make life easier for
programmers. I personally prefer the latter ("batteries included") but I
do understand a reluctance to increase the API, since API tends to
increase and never decrease and we'd better be sure we want to live with
it forever.... :-)

Exposing a prepare method would be convenient only in the sense of code
abstraction and code simplicity and such nebulous things. :-)
Specifically, you can do the following (or something similar)

def use_cursor(cursor, **parameters):
    cursor.execute(None, parameters)
    # some drivers use cursor.execute(cursor.statement, parameters)
    for row in cursor.fetchall():
        do_stuff()

cursor = connection.cursor()
cursor.prepare(sql_1)
use_cursor(cursor)
cursor.prepare(sql_2)
use_cursor(cursor)

If the cursor cannot be prepared in advance, you must pass the SQL
through in addition to the cursor. That makes it unclear whether or not
the cursor has truly been prepared or not and whether the statement
being executed is really the one you intend to execute. If that doesn't
make sense, ask me a different question. :-)

Now for your other question:

Oracle does permit a mode whereby you can "execute" but not really
execute in order to determine the list of parameters. That said, Oracle
itself does not recommend extensive use of that method.

On Sat, 2003-02-22 at 19:25, Stuart Bishop wrote:
> On Saturday, February 22, 2003, at 02:31  PM, Anthony Tuininga wrote:
> 
> > True. But if there were a common module, there wouldn't be a problem
> > with "implementing" prepare as a common __optional__ part of the API.
> > There is nothing inherently wrong with
> >
> > def prepare(self, statement):
> >     if rawCursor.hasattr("prepare"):
> >         rawCursor.prepare(statement)
> >     self.statement = statement
> >
> > def execute(self, statement, args):
> >     if self.statement is not None and self.statement is not statement:
> >         self.prepare(statement)
> >     rawCursor.execute(self.statement, args)
> 
> Assuming these are methods on the Cursor object, are there any cases 
> where
> you would ever get a boost by explicitly calling prepare() before the 
> execute?
> 	cur.prepare(my_sql)
> 	cur.execute(my_sql,args1)
> 	cur.execute(my_sql,args2)
> would perform exactly the same as just
> 	cur.execute(my_sql,args1)
> 	cur.execute(my_sql,args2)
> 
> This prepared statement caching behaviour is already specified like this
> in the DB-API, except that the prepare method is not exposed as there 
> has been
> no  need for it. The only rationale I have seen so far in this thread 
> would be
> to prefill the 'description' attribute (if the backend can actually do 
> this
> without executing a possibly very expensive query).
> 
> """ A reference to the operation will be retained by the
>      cursor.  If the same operation object is passed in again,
>      then the cursor can optimize its behavior.  This is most
>      effective for algorithms where the same operation is used,
>      but different parameters are bound to it (many times). """
> 
> -- 
> Stuart Bishop <zen@shangri-la.dropbear.id.au>
> http://shangri-la.dropbear.id.au/
> 
> 
> _______________________________________________
> DB-SIG maillist  -  DB-SIG@python.org
> http://mail.python.org/mailman/listinfo/db-sig
-- 
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