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

[Magnus Lycka]

First of all, I have a confession to make: I haven't really used
the Python DB API extensively. I've built SQL applications for
more than 10 years, but not seriously with Python. Ok, now that's
cleared out. ;-)

I think we might agree on some things:

* It would be convenient for application programmers if they could
   write their programs in a more uniform and database independant
   way. Being able to swap databases with the smallest possible
   effort is a clear advantage.

* Python database drivers are typically developed in economical
   conditions that are very different from those that are used to
   develop Java database drivers. (And possibly some people with
   Java roots don't realize that a slightly different tone is more
   productive in an open source environment.)

* Making life easier for the database driver developer is probably
   a good way to improve the quality and quantity of Python database
   drivers.

* It's a good thing if uniformity and convenience for the application
   programmer doesn't prevent using database specific features.

* It would be good if uniformity and convenience could be achieved without
   placing additional burdens on the driver developers. Actually, if there
   was a standard abstraction layer on top of the DB API, I imagine some
   database drivers could be a bit thinner than they are today.

An alternative or complement to another layer of abstraction on top of the
DB API could be some kind of generic DB API library with code that would
be useful to share between database drivers. For instance code for handling
connection string conversion, date/time conversions, standard exceptions,
param style conversions etc.

I'm sure we can all agree that there is room for improvement, here
as everywhere else. So how do we go about to achieve that?

I think that it would be great if there was an SQL database driver
using the DB API in the python standard library. The DB API is really
a good thing to prevent too much chaos and variation, but with the
database usage in the standard docs, and in the future in typical
Python books and tutorials, as well as an example implementation,
I think we would both boost new interest in python database usage,
contribute to standardization and make it easier to create standard
support code--whether it's helpful libraries for drivers to use, or
abstraction layers on top. Maybe SQLite could be useful here?

I have a dozen Python books in my book shelf. The only ones that
cover the DB API is Hammond/Robinsons Win32 book and Holden's new
Python Web Programming. I think we can agree that python database
programming isn't as visible as it could be. I hope this will change
though.

M.-A. Lemburg wrote:
>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.

I'm working as a consultant, and apart from the contract I've
worked on for the last nine months (where I decided to use ZODB
instead of a relational database), I've never been approached to
do Python programming. Python has popped up in my projects on my
initiative, like a grass roots effort. In this situation I think
it would be much more difficult if commercial licences had been
required. Free software is great in this respect.

Last time I looked, I came to the conclusion that I'm not allowed to
use mxODBC as a consultant unless my client gets a commercial licence.
For me it would make a big difference if mxODBC had a dual licence like
MySQL or SleepyCat's db. I think GPL would be ok in these situations. I
surely appreciate that programmers want to earn money on their work.
I know I do. But in the situation I am now, I'm often forced to
select an open source (or at least free as in free beer) solution.

As soon as a purchase has to be made, more decision makers are
involved, and management will start to discuss policies etc. Maybe
things are different in different countries and different corporate
cultures, but I've had a lot of freedom to implement things the way
I like as long as I don't require purchases to be made. Not for all
kinds of software, but there have been a lot of opportunities to
introduce Python for testing tools, analysis tools, troubleshooting,
data conversion etc.

>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've written applications (not using Python) that ran unchanged
with Oracle 7, Gupta SQLBase and different versions of Informix.
This requires some disciplin, and special handling of connect
strings and access to system tables. But it worked pretty well.
I'm sure 99% of the SQL statements were identical. In this case
I used a product called JAM. (it's now called Panther, and is free
on Linux, see www.possl.org. Unfortunately, programming in its
language JPL feels like having your right hand tied behind your
back if you are used to Python.)

I've also used DB2, Access, Sybase, MySQL etc, and almost all the
time I manage to restrict myself to using standard SQL constructs.

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

Has anyone ever shown any interest in such a thing? There
are a number of Python drivers for accessing flat files, but
none that use the DB API that I heard of. Wouldn't a higher
level of abstraction be more appropriate for a uniform access
to flat files and SQL databases?

I can't see a reason to discuss other types of databases than
SQL databases (well, more or less SQL ;) when it concerns the
DB API. There are a lot of constructs such as cursors etc that
don't mean anything in most other types of databases.

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

Maybe this is an effort that could be made once, and shared
between drivers?

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

Majority of what? Majority of actual users, or majority of
people who could have been users? I'm greatful for the work
that so many people have put into this, but I don't think we
should deny that it could be much more accessible.

I've programmed extensively in SQL since 1990 and in Python
since 1996, but I've still never used any DB API driver beyond
the ODBC driver in the Mark Hammond's Windows extensions. :-(

This is partly because I've never desperately needed it, but it
has happened a few times that I choose to popen shell scripts
with SQL calls, or that I made SQL queries to text files which
I processed in Python. If the threshold to use DB API comliant
drivers had been lower, I'm pretty sure that's the way I would
have gone.

How ever we go about things, we can't expect the Python standard
library to include binary versions of most popular databases on
all the platforms Python supports.

So there will always be a higher threshold to using SQL from
Python than to use Python in general. It would be really great
if an ODBC driver could be included in the standard library, but
I suppose this is something I'll just have to dream about. As I
said, I understand Marc-Andr=E9's position, but I can always dream,
can't I? ;-)


--=20
Magnus Lyck=E5, Thinkware AB
=C4lvans v=E4g 99, SE-907 50 UME=C5
tel: 070-582 80 65, fax: 070-612 80 65
http://www.thinkware.se/  mailto:magnus@thinkware.se