[DB-SIG] DB-API 1.1
Thu, 04 Jun 1998 11:23:40 +0200
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
Things to be considered obsolete:
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.
| Python Pages: http://starship.skyport.net/~lemburg/ |