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

Dustin Sallings dustin+pydbsig@spy.net
Fri, 21 Jun 2002 13:36:57 -0700

Around 20:24 on Jun 21, 2002, M.-A. Lemburg said:

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

	You're saying that DB API can't be any more standardized because
it'd prevent multiple backends from being used, and if I want something
more standardized, I'd have to go with another database API that is
already standardized and provides access to multiple backends.  The
existence of even one invalidates the first statement, but java, perl,
ruby, etc... each has a database API providing common access across
multiple databases.

	Again, I'm not saying it's worthless, but it does need to be
fixed, and it's *certaily* possible to have an abstracted database API
that will work the same way across multiple databases without having to
modify application code.

	Fixing it will likely require some tough decisions to be made and
will mostly likely place a greater burden on driver developers, but, IMO,
that's OK.  I believe a few people have mentioned that creating a base
framework from which all database implementations should extend, and I
think that's an excellent idea.  I have no problem doing the abstract
implementation of such a framework (at least a good chunk of one) and
possibly even a driver implementing it.  I had the intention of doing this
and proposing it at some point, but I haven't taken the time to start yet.

# > 	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 ?

	Because the lack of consistency in the API would mean that I would
first have to know about all DB API implementations, have the databases to
which they connect, and write special code for each individual API
implementation, even if the same query were being sent through.

	In the meantime, I've been using jython and JDBC for my
application development, as I work on more than one database and don't
want to put forth the effort to learn how to use more than one way to
connect to my databases right now.

# > 	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,

	Java thinking?  In that case, why do we need a DB API, why not
just make a generic API for everything we could possibly conceive
(network, database, print, UI, etc...) and have everything extend from

	But I don't understand how you're trying to sway me with that
statement.  JDBC is a set of java interfaces all database drivers must
implement.  This allows end users to know exactly what to expect, and
driver developers to know when they're done.

	Anyway, Java's not the topic here, I've just been using it as
evidence that it's possible to have an abstracted driver API that is
consistent across multiple databases.

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

	Doing the same work in multiple places is certainly not the best
way to get anything done.  I think this speaks for the need for a common
API from which all driver developers should extend.

	I also wouldn't want to go around changing the way everything
works in a way that I believe makes things better if everyone else
disagrees.  It doesn't make sense to start making changes until a new API
is finalized.

# 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, I'm not saying it's bad.

	Here's an example.  If you go to http://dbi.x.perl.org/ you see
this written at the top of the page:

	``The DBI is a database interface module for Perl. It defines a
	set of methods, variables and conventions that provide a
	consistent database interface independent of the actual database
	being used.''

                                     -- Tim Bunce

	What's wrong with having a similar design goal in python?  If your
stated design goal is to achieve database independence, then you will
start making decisions that will get you there.  A db package with
extensions, types, etc...and maybe some helper classes for the driver
developers would get us there quickly.

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

	I don't understand why there should be a difference.

SPY                      My girlfriend asked me which one I like better.
pub  1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net>
|    Key fingerprint =  87 02 57 08 02 D0 DA D6  C8 0F 3E 65 51 98 D8 BE
L_______________________ I hope the answer won't upset her. ____________