[DB-SIG] db module wrapper

[M.-A. Lemburg]

Vernon Cole wrote:
> Ah, the games begin!

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.

> Here is my suggestion for how to encode parameters into an cursor.execute
> call: DON'T!
> 
> Use an arbitrary argument list, formatted like a PRINT statement:
> 
>  cursor.execute("Select foo from table where goo >",foo,"and bar =",bar)
> 
> Now THAT is easy to read and reliable.  
 >
> "BUT," you say: "the other way is easy to parse and convert into other
> formats."
> 
> I quote from Guido on this list last January 2nd:
> vvvvv (begin clip) vvvvvv
> 
> For Python itself, making the life of the developers harder is
> usually considered okay if it makes the life of the users easier.
> 
> I'm curious if the life of the users isn't made *considerably* harder
> by some gratuitous differences between DB-API implementations, when
> a user needs to switch database vendors or (a slightly different
> case!) needs to support multiple vendors simultaneously.
> 
> Yes, you can write all your code using the facilities to inquire
> DB-API features, but in practice nobody does that until they are
> forced to consider multiple versions and realize just how different
> they are.  In a few months on this list I've seen many folks post
> about this.
> 
> (Of course, you can argue that the database vendors are a major source
> of the differences -- the differences in SQL syntax are indeed
> staggering!  But I'm not sure if that gives DB-API developers an
> excuse -- otherwise we might as well have no standard at all.)
> ^^^^^ (end clip) ^^^^^^
> 
> Let's go for the gold. We do not need to copy ANY given dialect of SQL. We
> need to find OR CREATE a dialect which will give our application programmers
> the best possible productivity.

Hmm, ODBC tried to go for gold, but didn't get very far.
I have a feeling that your quest is not going to have a
different outcome :-/

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.).

I think that it would be more helpful to application
writers to have a database of common database (and
interface module) specific gotchas, so that it becomes
easier to write the application specific abstraction
layer.

Even if you only use one Python database
interface module, e.g. mxODBC, to access all your
supported database backends, the situation does not
get much better. You still have to deal with different
supported data types, ways of writing schemas, indexes,
triggers, etc. and the different database drivers usually
use differing error handling too (some raise errors, others
don't, error codes usually don't match between database
beckends).

So if you really have the need to support multiple backends,
the situation is not made a lot easier by having a single
interface to write applications against.

PS: I'm off to EuroFooCamp now... anybody from the list
attending that event ?

Cheers,
-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Aug 20 2004)
 >>> Python/Zope Consulting and Support ...        http://www.egenix.com/
 >>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::