[DB-SIG] SQL PEP for discussion

Stuart Bishop zen@shangri-la.dropbear.id.au
Mon, 18 Jun 2001 18:55:00 +1000 (EST)


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

    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)

# 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


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

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

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

-- 
Stuart Bishop <zen@shangri-la.dropbear.id.au>