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

[paul@boddie.net]

Dustin Sallings <dustin+pydbsig@spy.net> wrote:
>
>	That connection strings were not defined in the spec made it a bit
>difficult for me to get going at first, and I was just playing with a
>couple of drivers.  Trying to create an application that is database
>independent cannot be done using the DB API spec, it's got to be wrapped
>with driver-specific code.

Indeed. Even modules/drivers for the same database system (eg. pyPgSQL and 
psycopg) use different enough connection strings/parameters to make 
things "interesting" for developers. If one were porting stuff from PostgreSQL 
to MySQL then one might expect discomfort, but should it really be necessary to 
experience that "porting" to the same system?

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

The standard response to the proliferation of paramstyles is that different 
databases expose different notations and that people who are used to those 
systems like to use their favourite notation. A somewhat more likely 
explanation is that it might be slightly easier for the module/driver developer 
to receive parameters in the native notation. In any case, JDBC ruled on a 
particular paramstyle and surely only the most die-hard of Oracle developers 
(for example) must still be complaining about it now; and I suspect that the 
most die-hard developers only use the vendor's tools, anyway, so should we 
really pay that much attention to them?

"Matthew T. Kromer" <matt@zope.com> wrote:
>
>I also have to chuckle every time someone wants a "purely generic" 
>interface.  Most RDBMS have divergent SQL dialects, with divergent 
>types.  Any "power user" usually is tied to a specific RDBMS and wants 
>to maximize it, and thus is going to get very frustrated when there is 
>no "generic" way to access a very specific feature.

As far as I can see, just standardising the simple things like connection 
strings/parameters and paramstyles would help people out a lot. I doubt that 
anyone really expects their SQL statements to be translated, even though 
there's been some talk of that of late. Even so, for reasonably straightforward 
queries, most database systems have surely converged in terms of SQL standards 
support and have enough in common to support generic querying and updating.

In other words, I don't think the full (but reasonable) potential of the DB-API 
has been realised yet.

Paul