[DB-SIG] db module wrapper

Randall Smith randall at tnr.cc
Fri Aug 20 20:32:49 CEST 2004


What about executemany?  Then you'll have to pass in a list of lists as 
paramaters.

l1 = ['harry', 'sam', 'charley']
l2 = [1, 2, 3]

query = "select * from tname where col1 = ", l1, " and col2 = ", l2
cursor.execute(query)

Is this what you're talking about?  It's actually pretty cool, but a big 
break from API 2.  Maybe it could be an added feature b/c I want the 
wrapper to be DBAPI2 compliant.  It wouldn't be that hard to code.  The 
more I think about it, the more I like it.  This is probably what would 
happen in practice.

query = []
query.append('select * from tname where col1 = ')
query.append(l1)
query.append('and col2 = ')
query.append(l2)
cursor.execute(query)

That's actually quite readable.  Am I getting the right idea?  Is this 
what you are intending?

Is this in anyway disadvantageous to the API2 way?

You're certainly on track about the API 3.0 issue.  This IS a new API. 
The wrapper is implementing a more strict API than 2.0 does.

I'm definitely giving this some thought.  It seems very nice.

Randall

Vernon Cole wrote:
> I confess, and beg pardon. I let myself wander off from Randall's topic --
> which is creation of a wrapper for various DB API 2.0 packages -- onto the
> topic of creation of DB API 3.0.  Nevertheless, I think that Randall's work
> may help to define 3.0 in some ways.
>   I feel very strongly that the eventual 3.0 must be able to run simple to
> medium querys, and simple adds or updates, using a completly standardized
> syntax.  When one gets to the more complex stuff -- indexing and triggers
> for example -- standardization is out the window. Way out. I think the goal
> of the wrapper is roughly that, too. 
>    Having said all that, is it not true that the role of cursor.execute() in
> the wrapper is not to PARSE an SQL statement, but to BUILD one?
>    The four ways now used to encode parameters are all based loosly on the
> principle of an fprintf() function in C. A string with some type of escape
> patterns is passed, followed by a list of parameters. I am trying to suggest
> that we can simplify things by using the power of python. We are using a
> language with strongly typed parameters.  A module implementor can tell at
> run time the data type of the contents of an arbitrary parameter.
> If the cursor.execute() method accepts an arbitrary parameter list, then we
> have no need of escapes.  The application programmer simply breaks his sql
> statement at that point, puts in the python variable he has in mind, and
> then continues the SQL as another string literal. The implementor will
> inject the appropriate escape into the string she is building and prepare
> the paramater for passing as needed by the target SQL engine.
>     Am I missing something?
>     Why would this not work?
> -----------
> Vernon
> 
> -----Original Message-----
> From: M.-A. Lemburg [mailto:mal at egenix.com]
> Sent: Friday, August 20, 2004 5:06 AM
> [clip]
> Before yuo head off, may I suggest that you create a layer on
> top of the DB API compatible drivers (much like you have in Perl)
> instead of forcing incompatible changes onto the various DB interface
> modules ?!
> 
> The user can then use the abstraction layer and the module
> authors can continue to craft modules that provide interfaces
> which adhere to the DB API interface standard.
> The typical approach to the problem (if at all) that
> Guido mentions is to have an application specific
> abstraction layer which then gets adapted for the
> intended list of database backends.
> 
> This layer has all the knowledge about the application
> needs as well as the ways in which these needs can be
> mapped to the databases (both in terms of Python database
> module interface and database SQL dialect).
> 
> In my experience trying to generalize these abstraction
> layers always fails at some point: either the database
> SQL dialects and offered features are too different,
> which let's you end up only supporting a very low-tech
> common subset, or the abstraction has to go through
> enormous lengths at trying to create a common behaviour
> among the database backends (eg. on how multiple result
> sets are handled, array processing, result set scrolling,
> etc.etc.).
> [clip]
> _______________________________________________
> DB-SIG maillist  -  DB-SIG at python.org
> http://mail.python.org/mailman/listinfo/db-sig


More information about the DB-SIG mailing list