[DB-SIG] SQL PEP for discussion

M.-A. Lemburg mal@lemburg.com
Mon, 18 Jun 2001 11:47:50 +0200

Stuart Bishop wrote:
> > >       It was difficult to write code that connected to multiple
> > >       database vendor's databases. Each seperate driver used defined
> > >       its own heirarchy of exceptions that needed to be handled, and
> > >       similar problems occurred with identifying column datatypes etc.
> >
> > DBAPI specifies how column datatypes should be treated and identified.
> What I was getting at here was they are defined in each driver.
> The following code attempts to show some of the issues I have been
> considering
> try:
>     con_a = drivera.connect(....)
>     con_b = driverb.connect(....)
>     con_c = driverc.connect(....)
>     cur_a = con_a.cursor()
>     cur_b = con_b.cursor()
>     cur_c = con_c.cursor()
>     cur_a.execute('select c1,c2,c3 from sometable')
>     cur_b.execute('select c1,c2,c3 from sometable')
>     # How can I check that the table being accessed by cur_a and cur_b
>     # have the same datatypes? I can't say
>     # cur_a.description[x] == cur_b.description[x].

True. You'd first have to map the description value to one of
the standard DB API type objects and then compare these.
>     for cur in (cur_a,cur_b):
>         row = cur.fetchrow()
>         while row is not None:
>             # Note that the bind parameter placeholders may change if we use
>             # a different DB vendor for driver C, and row may need to become
>             # a mapping
>             #
>             cur_c.execute('insert into sometable values (:p1,:p2,:p3)',row)

Also true -- this case is normally handled with in an abstraction
class around connection and cursor objects.
> # Note that the exceptions thrown from the drivers do not have a common base
> # class we can test for.
> #
> except (drivera.Error,driverb.Error,driverc.Error),x:
>     print >>log,'DB error - %s' % str(x)
>     raise

Dito here: the abstraction class can make this possible by
catching the exceptions and reraising them using a common
base class.

IMHO, the cursors or connection objects should provide access
to the exceptions objects too: that way you would be able to catch 
DB errors in a database interface independent way (at the cost
of polluting the cursor/connection attribute namespace).
> > but this pep does not define an higher level or meny new features. 90%
> > of the api is a re-definition of the stuff in the DBAPI documenti.
> I'm expecting suggestions and possibly implementations from the SIG. I
> just put in what I consider the minimum at this stage.

I think that rather than duplicating a the DB API layer on top
of the existing DB interfaces, we should provide the DB interface
writers with more standard tools.

E.g. a helper which converts between the different parameter binding
schemes would be nice (I think someone already wrote such a tool...).

We could add these to a standard Python modules dbtools.py.

> > so lets make the DB-SIG and the dbapi document more visible. you won't
> > sole the problem by adding yet another layer.
> Yup. If this or a similar PEP doesn't go ahead, I'd personally like to
> see the DB API spec moved into the library reference.

The DB API is already available as PEP. I am not sure what Fred
thinks about adding these informational PEPs to the std lib reference.
> > if you're awriting a multithreaded application, your code is only part
> > of the whole. you need to think carefully about the db, the driver, etc.
> > if you really *need* multithreading independant code is the last of your
> > problems.
> Yes - I can now see situations where this could lead to deadlocks so
> this will need to be scrapped :-(
> > anyway, a wrapper with some utility functions is, imho, a very good
> > idea. things *really* db independent like a function that transform any
> > bound parameters to a particular format and such. i'll call it
> > sqlutils.py, though.
> One of the things that I forgot to put in the PEP was that
> this wrapper would effectivly replace DB API 3.0. As it stands,
> it would be able to add iterator support and a common bind
> parameter style to all DB API 2.0 and probably all DB API 1.0
> compliant drivers, without the driver authors having to update
> their code.
> The v2.0 spec was finalized April 1999, and a number of drivers are still
> being developed. Assuming iterator support rolls out with Python 2.2,
> this spec will become obsolete IMHO (if anything can obviously make use
> of iterators, it is a relational database API). I've thrown this PEP into the
> ring as one approach to tackling these issues, by simultaneously updating the
> API and updating most if not all of the existing drivers.
> Another approach is as Federico suggests, and having a library of
> utility functions to work with the DB drivers.
> A third approach would be to define DB API 3.0 so drivers make use
> of a shared library of Exceptions, constants and Mixins.

That would be similar to the XML parser approach: you pass in
drivers and the XML lib uses it only as backend, providing a consistent 
interface to the user. Not a bad idea, but in certain situations
you really want to get full access to the underlying driver,
e.g. to call driver specific APIs which only apply to the specific
driver context.

Marc-Andre Lemburg
CEO eGenix.com Software GmbH
Company & Consulting:                           http://www.egenix.com/
Python Software:                        http://www.lemburg.com/python/