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

M.-A. Lemburg mal@lemburg.com
Mon, 24 Jun 2002 10:23:03 +0200


Andy Dustman wrote:
> On Fri, 2002-06-21 at 15:31, M.-A. Lemburg wrote:
> 
>>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, transactions
>>* 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.
> 
> 
> I think this highlights that there are two separate portability issues.
> 
> 1) Ability to execute queries on the database and return results
> 
> 2) The queries themselves
> 
> DB-API does a good job, IMHO, on point 1, once you get a connection
> object. If we were doing another version, I'd standardize connect by
> ditching connection *strings* for connection *parameters* (host, user,
> passwd are pretty universal) and keyword arguments for anything else.
> 
> DB-API doesn't do much for point 2, nor was it ever intended to. It
> doesn't even require that SQL be passed to .execute. That, perhaps,
> should be a job for the SQL-API, which doesn't exist, but could be an
> additional layer on top of DB-API. Indeed, this could be a fairly
> standard wrapper/proxy class for a connection object which is extended
> as needed with mixins for various implementations (DB-API modules) which
> actually constructs SQL statements.

Hmm, it's not only about generating SQL for queries, it's
also about semantics and different data type implementations.

I have such an abstract interface for the eGenix Application
Server which basically wraps up connections and cursors
and adds higher level methods to them like e.g. .count(condition),
.get(id), .add(id, record), .remove(id), .update(id, record).

Tables add another layer on top of these, so that the programming
interface becomes an application (and table) specific one. Tables
are then subsummed in Database objects which provide cross-table
interfacing.

The programmer doesn't see a single line of SQL at the highest
level. Database integrity is maintained by the different
layers and differences in syntax and semantics are handled there
as well.

Works great :-)

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