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

[M.-A. Lemburg]

Magnus Lycka wrote:
> M.-A. Lemburg wrote:
>=20
>> 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.
>=20
>=20
> 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.
>=20
> 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.
>=20
> 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.

I can understand that, but I run a business and have
to pay for my pizza too :-)

>> 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.
>=20
>=20
> 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.)
>=20
> I've also used DB2, Access, Sybase, MySQL etc, and almost all the
> time I manage to restrict myself to using standard SQL constructs.

That's possible indeed, but only if you restrict yourself
to the absolute minimum in terms of data types and
functionality.

Some examples which almost always need some way of tweaking:

* auto-increment or sequencing
* blobs and other more esoteric data types like images and Unicode
* for MySQL and non-SQL databases: sub-selects, joins, views, transaction=
s
* count()
* select distinct
* locking
* transaction isolation
* database, table and user creation
* various limits on data types
* syntax for defining data types
* names of data types

and probably a dozen more.

>> 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.
>=20
>=20
> 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?

Yet another plug ;-)... mxODBC can access Excel, CSV (and a
few other similar formats) files on Windows.

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

mxBeeBase is an example of a flat-file database which does
have cursors, but where SQL doesn't make too much sense.
It should be easy to come up with some similar query
dialect, though. Another idea would be to use it as backend
for Gadfly.

>> 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.
>=20
> Maybe this is an effort that could be made once, and shared
> between drivers?

I think someone already write a helper which does this
(converts various formats into one); we certainly can't
force database module authors to use it, though.

>> 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.
>=20
>=20
> Majority of what? Majority of actual users, or majority of
> people who could have been users?=20

Majority of users who have used Python's DB API modules
in the past. We would have had many more outcries if people
had felt that the DB API is not up to the task.

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

Certainly not. Improvements are always possible. You do
have to know what you want though and I have a feeling
that Java people are missing their JDBC in Python. If
that's the case, then we have a need for a JDBC style
interface in Python -- perhaps on top of the existing
DB API interface (which is much more low-level).

> 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. :-(
>=20
> 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.
>=20
> 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.
>=20
> 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? ;-)

ODBC is a *very* complicated beast, not only because
the standard was written by Microsoft, but also because
drivers and databases support various levels of ODBC which
are not necessarily compatible. You wouldn't want to
maintain it ;-)

I can assure you that it's much more fun programming
DB API than ODBC.

--=20
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/