[DB-SIG] Experiences with DB-API2.0

M.-A. Lemburg mal@lemburg.com
Fri, 21 Jun 2002 20:24:45 +0200


Dustin Sallings wrote:
> Around 10:35 on Jun 21, 2002, M.-A. Lemburg said:
> 
> # This is on purpose: the DB API spec is intended to map as many different
> # backend systems as possible. The only way to achieve this is by allowing
> # a certain amount of freedom on the implementors side.
> #
> # BTW, if you want to support multiple DB backends, why not just use
> # mxODBC ? It pretty much interfaces to all major databases out there and
> # provides a consistent interface to all of them.
> 
> 	How can you simultaneously argue that the DB API was intentionally
> designed ambiguously to allow as many backend systems as possible, and
> state that there's another driver API out there that supports ``pretty
> much all major databases'' that has a consistent interface?

I don't see your point.

mxODBC implements DB API 2.0 and extends it
with a lot of other interesting APIs which ODBC provides. All the
standardization work is done (more or less) in the ODBC drivers.
This allows mxODBC to provide a consistent interface to multiple
databases.

I don't see how this is in conflict with saying that an interface
like the Python DB API needs to be flexible to allow for more than
one backend.

> # If you want to write database independent applications you have much
> # more to do than just fiddle with the DB API interface. The SQL dialects
> # and data types differ *very* much between databases. I'm even starting
> # to talk about differences in semantics. The only way to work aroung this
> # is by adding an abstraction layer which has to be application specific.
> 
> 	I believe that arguing that it's unimportant to keep B consistent
> because you'll probably have to change A anyway is invalid.  Personally,
> I've rarely had to change my SQL when going between PostgreSQL and Sybase.
> When I do have to change a query for a specific database, I deal with
> that.  My abstraction layer deals with that in such a way that I just have
> to drop the two queries in and it's good.  However, this is all java code
> and I have never had to change the way I talk to the DB when switching
> between postgres and sybase.  My abstraction layer doesn't have to do
> anything but select a query when they are different.

That's nice. Why don't you write a wrapper for Python that implements
JDBC on top of all existing DB API interface ?

> # > 	I'm not saying the DB API is the wrong direction, it's certainly
> # > helping, but it needs to get rid of ambiguities and fill in some blanks.
> # > Any place where there's room for the developer to make decisions that
> # > affect the way the API is used makes it difficult to write code against
> # > the API.  Options are only good if they're all required (i.e. the five
> # > quoting techniques).  That is to say, options for the user of the driver,
> # > not for the developer of the driver.
> #
> # I disagree.
> #
> # The freedom is needed so that you can support more than just one
> # backend, e.g. a flat file database is likely to behave differently than
> # a full blown SQL Server.
> 
> 	To me, that says you can't group fundamentally different things
> under a single API.

That's Java-thinking. Python works in terms of interfaces and that's
also how the DB API is designed,

> # > 	Now, there may very well be cases where behavior must be optional,
> # > but there needs to be a good reason.  rollback() on a connection is
> # > reasonable because some people still use mySQL or similar DBs that don't
> # > inherently support transactions, but the paramstyle is just obscene.
> #
> # Certainly not. If you would want to enforce a standard paramstyle then
> # you'd have to add a parser to the modules that don't support the
> # "standard" way of writing parameters defined by some DB API spec.
> 
> 	So?  A driver developer should be expected to write code.  I've
> written this piece of code around a partial JDBC driver, it took me about
> fifteen minutes one day.

Great. If you can provide patches for all the Python drivers, I'm
sure the authors would love to integrate them ;-)

> # Maybe for you, but not for the majority. The DB API has a very long
> # success story. This is evidence enough for me that the approach was the
> # right one.
> 
> 	Perl, VB, etc... all have success stories longer than the DB API,
> but that doesn't make me believe they were done correctly.  I might
> believe the DB API were perfect if I hadn't used it and didn't see anyone
> else with the same complaints I've got about it.

Nothing is ever perfect, only good enough :-) Of course, there
are things in the DB API that could be better, but it's pretty
useful in its current state already, so there's really no need
to try to talk it down.

> # Again, if you don't like dealing with multiple different interface use
> # mxODBC and talk to the database via ODBC.
> 
> 	This statement reads as if you don't believe there is a DB API,
> and/or that the python community isn't capable of coming up with one (as
> opposed to what you said, ``multiple different interface[s]'').

The strategy is different to what you have in mind: the DB API
defines an API for database interfaces, not an interface to databases,
like e.g. the Perl DBI concept. We don't have a driver - manager
setup. It's only drivers.

-- 
Marc-Andre Lemburg
CEO eGenix.com Software GmbH
______________________________________________________________________
Company & Consulting:                           http://www.egenix.com/
Python Software:                   http://www.egenix.com/files/python/
Meet us at EuroPython 2002:                 http://www.europython.org/