[DB-SIG] Optional DB API Extensions

Matthew T. Kromer matt@zope.com
Thu, 25 Oct 2001 08:37:31 -0400

On Thursday, October 25, 2001, at 06:52 AM, Stuart Bishop wrote:

> I also would like the connection method:
>         quote(object)
>             Returns a SQL quoted version of the given variable as a
>             string, suitable for execution by the database backend.
>             For example:

Since the primary reason you want quoting is generally to handle cases 
where you can't directly BIND values to your database, I'm of the 
opinion that this should be heavily leaned against.  Instead, use of 
parameter binding should be highly encouraged.

I would be open to having extension methods which helped construct 
statements using the API's preferred bind specification method, or a 
mechanism whereby the application can negotiate with the adapter to 
choose a quoting mechanism.

Having said all of that, this makes me lean towards a bilevel (or 
trilevel) approach where there is a standard module which all adapters 
subclass, and an optional module to attempt to provide further adapter 
abstractions (inter-adapter abstractions).  However, I'd leave the 
current API spec largely untouched except for DRIVER level features; 
APPLICATION level features (including application convenience functions) 
belong elsewhere.

> I'd also add cursor methods for Python 2.2 iterators:
>         next()
> [...]
>         __iter__()

Yep, this is simply  a good fit for result sets.

> Another optional method may be a call to have the fetchXXX methods 
> return
> dictionaries rather than sequences, as this seems to be a common 
> extension
> and feature request.

This is an application convenience issue to me; and comes at a cost of 
making things run slower.  I don't like preempting the application's 
speed for the programmer's convenience.  To give an example, in 
DCOracle2, the underlying C module might fetch 40,000 records in .7 
seconds -- but wrapping these results as python objects bumps that up to 
3.5 seconds, and going through the entire fetchmany() layer (which for 
DCOracle2 involves converting the column-based results to row-based 
results) bumps the whole thing up to almost 9 seconds.  While this 
example is an instance which I'm sure can be improved upon, I'm strongly 
opposed to making the situation worse rather than better.

> Is it worth adding the transaction isolation level stuff I outlined in
> the DB API 3 strawman I put out a month or two ago? There didn't appear
> to be much interest in these, but I may be wrong. (I will be getting
> around to replying to the last round of comments on  the 3.0 strawman
> soon, as I have spare time again as of today).

I would endorse anything which attempts to expose more features of the 
underlying RDBMS; e.g. Oracle can do the transaction levels you 
mentioned, and can also set transaction IDs for doing transaction 
coordination and distributed two-phase commit.
I would again suggest that there be an underlying base class for all 
adapters which has some discovery methods in it such that an application 
can query for capabilities rather than catch NotImplementedErrors.  

           dbapi.getSupportedFeatures() = { dbapi.NumericBinds, 
dbapi.StoredProcedures, dbapi.TransactionControl }

I also find it important to emphasize that a test suite should go 
hand-in-hand with any API; this seems to be a source of frustration for 
many adapter writers to say "Is this compliant?"