[DB-SIG] Last round for DB API 1.1

[M.-A. Lemburg]

Andy Dustman wrote:
> 
> On Wed, 17 Mar 1999, M.-A. Lemburg wrote:
> 
> > · What to do about Andy Dustmans proposal to have .rollback()
> >   raise an exception in case transactions are not supported by
> >   the database ?
> >
> > IMHO, we should leave the situation as it is: some DBs do not
> > support transactions (e.g. MySQL), some do but you can turn
> > transactions off (e.g. this is the ODBC default)... all in all
> > its a feature that is very dynamic, specific to connections.
> >
> > If the API were to raise an exception in case tranactions are
> > not supported, the underlying interface would have to query the
> > current state prior to every .rollback() and even prior to garbage
> > collecting the connection object -- at a time where there could
> > be no connection available any more.
> 
> Well, look at it this way. If you were using a database that supported
> transactions, and it was operating in a transactional mode, you'd expect
> an exception if, for some reason, the rollback couldn't be completed. (Are
> rollbacks guaranteed to succeed from an SQL standpoint?) It need not be
> dynamic in the sense that it is sensitive to the transaction isolation;
> presumably the programmer has set this and will only use rollbacks in
> non-auto-commit mode. All I am suggesting is that if rollback is called on
> a database which cannot possibly roll back a transaction, that some
> exception be raised, which is probably preferable to having some program
> which depends on rollback continuing on as if the rollback was successful.
> This simplest way to do this is to simply not implement the rollback
> method if no support is available, which will raise an AttributeError.

Hmm, but what if I connect to say MySQL via ODBC... the database
does not support transactions but still allows .commit() and .rollback()
without raising an exception. If the spec would say that the interface
*has* to raise an error, I'd have to check prior to every .rollback()
to see if the connection actually does do something with it.

Of course, we could add a suggestion to the spec along the lines
of "In case the database does not support transactions the interface
may throw an exception in case .rollback() is used. This can either
be an AttributeError because the interface does not provide the
.rollback() method or an OperationalError to indicate the non-ability
to perform the roll back."

> It seems harmless to allow commit to always succeed and do nothing on a
> transactionless database, though, since all statements are essentially
> committed once they are executed.

True.

> > · Andy Dustman also asked for a standard way to find out the
> >   parameter marker syntax used by the database.
> >
> > There are at least two possibilities:
> >
> >       ?,?,? in the order of the given parameters
> >       :1,:3,:2 in any order only referring to the parameter position
> >
> > Not sure how to get this information into a constant or a method...
> > Any ideas ?
> 
> Well, there seem to be three styles, AFAIK:
> 
> PARAMETER_QM = 1 # parameters represented by question marks
> PARAMETER_CP = 2 # parameters represented by colon+position number
> PARAMETER_CF = 3 # parameters represented by C format string (i.e. %s)
> 
> The main problem, of course, is how to write code to make use of this in a
> portable way. This may be beyond the scope of what the DB API can handle,
> since it is a function of the query language in use, and the API is not
> tied to any particular query language. I suspect there are a lot of
> human-written queries out there that will just have to be re-written, and
> I'm not sure this will really be useful.

I think it is beyond the scope of the API spec. Ideal would be
one syntax that could be reformatted to whatever the database uses,
e.g. something with keywords much like '... %(id)s ...' % {'id':'123'}
but I think that putting it into the 1.1 version goes a little too
far. Maybe for 2.0 ;-)

> > · Should we add additional constructor signatures to the API spec ?
> >
> > So far we have Raw,Date,Time,Timestamp. Maybe we should rename
> > Raw to BLOB or Binary to be in sync with SQL ?! What about a
> > monetary constructor... does anyone have experience with such
> > types ?
> 
> Binary would be good, but should be the same as Raw, which would be
> deprecated.

Ok, so I'll rename Raw to Binary (since we've dropped dbi this
is no big deal anyway) and add a note to provide aliases to
dbiDate and dbiRaw for backward compatibility.

> > · We still need to settle the sequence of sequence problem
> >   with .execute().
> >
> > Since e.g. ('abc',) fits this definition but surely isn't intended,
> > we'll need some other way to handle sequences of parameter/data
> > sequences. How about depreciating the use of those lists in
> > .execute() and defining a .executemany() method for this purpose
> > instead ? Other ideas ?
> 
> Right now it says "tuple or list of tuples". I'm happy with that.

Greg's not and he's got a point there. I am basically in favor of
"sequences of sequences" too; it's just that the ambiguity has to
be solved somehow. ".executemany()" would sound much like
".fetchmany"...

> Another problem: The spec currently says that when calling the
> connection.cursor() method, "An exception may be thrown if the database
> does not support a cursor concept." However, if the database does not
> support cursors, then you really can't do jack squat with the API. I think
> the old version said you could do cursor methods on the connection, but a
> better solution is probably to require the database module to emulate
> cursors.

Ah, that's an oversight on my part. I'll add a note about the
emulation.

> Take for an example, MySQL. MySQL does not have cursors. It has the
> MYSQL_RES structure, which contains a result, but queries are executed on
> the connection. However, it is not too hard to emulate a cursor in a
> Python wrapper.
> 
> And yes, I have just about finished yet another MySQL interface. This one
> is thread-friendly (wraps blocking calls with Py_BEGIN_ALLOW_THREADS ...
> Py_END_ALLOW_THREADS), based on the latest MySQL 3.22.19a API, and is in
> two parts: A _mysqlmodule.so which implements most of the MySQL API (has
> connection objects and result objects; result.describe() returns a DB API
> compatible description), and MySQLdb.py which implements the current DB
> API. There is a type_conv dictionary which maps MySQL FIELD_TYPEs to
> Python functions, so the user can specify how SQL types should be
> converted; unspecified types are returned as strings. By default the C
> module does not convert the various time types; the Python module sets the
> dictionary so they are converted to DateTime objects. On writing to the
> database, the interface converts all input parameters to strings and does
> the necessary quoting. Note that due to the design, you can subclass both
> the connection and cursor objects, as they are implemented in Python.

Sounds great. The subclassing part is swell ;-) and the callback
concept a really useful approach (with new types popping up for every
MySQL release this really helps).

BTW: I've noticed that MySQL support BLOBs but does not handle
binary data in those BinaryLongOBjects... is that a bug in their ODBC
driver or by design ? [would render the Binary() constructor meaningless
I guess...]

> Compared to MySQLmodule-1.4, the C source is slightly smaller, the .so
> library is 40% smaller (i386, YMMV), and the Python interface is 50%
> smaller. Probably this is mostly due to the fact that MySQLmodule-1.4 was
> originally written to emulate an icky perl module, and then the Python API
> is written around that.
> 
> I hope to release this soon under a Python-style license. It is not yet
> well-tested, but appears to work pretty well.

There's a preliminary test script in mxODBC you may want to adapt
for your package. I'm using it to test for memory leaks and to
check database capabilities (esp. supported data types and SQL
syntax for them).

-- 
Marc-Andre Lemburg                               Y2000: 289 days left
---------------------------------------------------------------------
          : Python Pages >>> http://starship.skyport.net/~lemburg/  :
           ---------------------------------------------------------