[DB-SIG] Towards a single parameter style

[Anthony Tuininga]

On Mon, 2003-02-24 at 11:40, Federico Di Gregorio wrote:
> Il lun, 2003-02-24 alle 15:42, Anthony Tuininga ha scritto:
> [snip]
> > 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)
> 
> let me say i would never write code like that. i am not saying it is
> particulary bad or good. simply it doesnot suit my programming habits. 

That was meant to be an example -- but __NOT__ of code that was an
example of good (or bad) programming habits. Obviously it didn't make my
point very effective. Sigh. :-)

Perhaps this example would be better?

class CursorCache:

    def __init__(self, connection):
        self.connection = connection
        self.cursors = {}

    def Cursor(self, tag):
        cursor = self.cursors.get(tag)
        if cursor is None:
            cursor = self.cursors[tag] = self.connection.cursor()
        return cursor

def GetTableList(self, cache, **parameters):
    cursor = cache.Cursor("TableList")
    if cursor.statement is None:
        cursor.prepare(the_query)
    cursor.execute(None, parameters)
    for name, in cursor.fetchall():
        columns = GetColumnList(cache, name = name)

def GetColumnList(self, cache, **parameters)
    cursor = cache.Cursor("TableList")
    if cursor.statement is None:
        cursor.prepare(the_query)
    cursor.execute(None, parameters)

cache = CursorCache()
fred_tables = GetTableList(owner = "FRED")
joe_tables = GetTableList(owner = "JOE")

In the above example, if "the_query" is a generated string, it will not
match (by address) the statement last executed which means that
internally the prepare will occur multiple times. If an actual string
comparison is done, a performance penalty takes place. If the DB API is
followed to the letter, the cursor cache must also contain the
statements that were "prepared" since the DB API does not give access to
the cached statement, right? That means that the function Cursor() must
return a cursor __AND__ a statement to execute on it, right? Obviously,
this can be done but at the expense of writing a lot more code which is
more difficult to read -- in my opinion anyway. And it would seem that
others tend to agree since the prepare() method is exposed in DCOracle2
as well.... :-)

> now, adding complexity to an API to follow someone's programming habits
> is bad, imho. especially if you can obtain the same effect in a
> different, already estabilished way.

That was not intended. And I am not saying that you can't do it with
existing code. All I am saying is that you are requiring __MORE__ code
with the existing DB API to get the same job done, and that bugs me. :-)

> > 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. :-)
> 
> this is a very good reason to *not* have prepare(). :)

Huh? Could you explain a bit here as I don't get the joke. :-)

Looks to me like I will simply continue to violate the DB API with
cx_Oracle by exposing prepare(). Significant performance gains are
possible by so doing and I don't have to continue to keep track of the
SQL in every place that execute() is called which is what is necessary
to be strictly compliant with the DB API. Such is life. :-(

> -- 
> Federico Di Gregorio
> Debian GNU/Linux Developer                                fog@debian.org
> INIT.D Developer                                           fog@initd.org
>   99.99999999999999999999% still isn't 100% but sometimes suffice. -- Me
-- 
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