[DB-SIG] db module wrapper

[Vernon Cole]

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]