[DB-SIG] Last round for DB API 1.1

[Andy Dustman]

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.

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.

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

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

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

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.

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.

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.

-- 
Andy Dustman  (ICQ#32922760)    You should always say "spam" and "eggs"
ComStar Communications Corp.                 instead of "foo" and "bar"
(706) 549-7689 | PGP KeyID=0xC72F3F1D   in Python examples. (Mark Lutz)