[DB-SIG] DB-API 1.1

[M.-A. Lemburg]

Ok, I'll try to summarize what's been proposed so far [and my
comments on them ]:

Ted Horst would like to see dictionaries used for connection
parameters and column descriptions.

	This is a good idea, but not backward compatible. Then
	again, some DB interfaces use tuples, others string, some
	both... so we might as well make the change for the connection
	arguments. It's different for the column description:
	unpacking dictionaries doesn't work. I suggested defining
	description to return a sequence of sequences. This lets
	you implement hybrid models as well, e.g. sequence of 
	sequence/mappings. Would be very useful indeed to have
	a type that behaves as both: sequence and mapping. Maybe
	a project for some rainy sunday...

Jim wanted to know how what kind of exception to raise for interface
related errors. I suggested InterfaceError with Error as base class.

	Seems to be excepted unless I hear anything different.

Stored Procedures. As expected this causes troubles.

	AFAIK, it is possible to call stored procedures via the
	standard execute method. Input parameters get passed in
	via the parameter tuple, output goes into a result set.

	Since databases tend to use different calling syntaxes
	the callproc() could take care of mapping the procedure
	to the database specific syntax and then pass the parameters
	on to the execute method to have the procedure call itself
	executed. Results would then be available via the
	result set. IN OUT parameters are not possible using this
	scheme, but then: which Python type would you use for them
	anyway...

Things to be considered obsolete:
	arraysize
	setinputsize()
	setoutputsize()
	
	Completely agree on punting these... fetchmany() would then
	have a mandatory argument instead of an optional one.

begin() method. Jim mentioned this without too many details attached
to it. Michael responded with a transaction model.

	Michaels model is easily implemented in Python on top
	of database cursors. As are some other techniques of getting
	the OO look&feel into database handling. This would
	well define a new project for the DB-SIG.

Return values for DDL, DML and DQL statements.

	The only really useful return value defined is the
	number of rows affected for DMLs. mxODBC provides two
	new attributes for cursors:
	rowcount - number of rows affected or in the result set
	colcount - number of columns in the result set
	We drop all the special return values and add these two
	(or maybe just rowcount) attributes instead.

Multiple results sets.

	Same problem as with stored procedures. There doesn't
	seem to be much of a standard and most DBs probably don't
	even support these.

Mapping of database types to Python types.

	Since different databases support different types (including
	some really exotic ones like PostgreSQL), I think the
	general idea should be: preserve as much accuracy as you
	can e.g. if a money databse type doesn't fit Python's integers,
	use longs. The only type debatable, IMHO, is what to use
	for long varchar columns (LONGS or BLOBS). The most common
	usage for these is probably storing raw data, just like you
	would in a disk file. Two Python types come into play here:
	arrays (mutable) and strings (immutable). Strings provide
	a much better access interface and are widely supported, so
	I'd vote for strings.

Having coltype compare '==' instead of 'is'. Bill doesn't like it.

	I don't think it's much of a change and it allows me
	to return the raw database data in coltype. The types
	defined in dbi are only a very small subset of the possible
	database types (again, see PostgreSQL for an example of
	many wild types ;). This small change makes it possible
	to add magic in Python to have multiple values compare
	equal to one, which would otherwise not be possible. I don't
	like the idea of having to access the coltype through a
	seperate (non-portable) API.
	
dbiDate. I suggested using DateTime types, Bill agrees, Jim probably
has his own set of types ;-) which he'd like to use.

	I'm biased, of course, but the DateTime type are readily
	available and easy to use on the user as on the interface
	programmer side. They provide a rich set of API functions
	which they export via a CObject (meaning: no linking problems).
	mxODBC uses them already. Though it also allows you to
	choose two other way of passing date/time values: as strings
	and as tuples.



In general having more of the API return dedicated types with a
nice OO interface would be a really nice thing, but I fear that
most interface programmers are not willing to hack up two or
three other types in addition to connections and cursors.

-- 
Marc-Andre Lemburg
----------------------------------------------------------------------
             | Python Pages:  http://starship.skyport.net/~lemburg/  |
              -------------------------------------------------------