[DB-SIG] Last round for DB API 1.1

M.-A. Lemburg mal@lemburg.com
Thu, 18 Mar 1999 13:26:37 +0100

Greg Stein wrote:
> M.-A. Lemburg wrote:
> > ...
> > I've added a note that gives the module implementor the two
> > exception possibilities mentioned above: AttributeError (.rollback()
> > not implemented) and OperationalError (.rollback() implemented,
> > but the test is done at execution time).
> The *very* strong preference should go towards not implementing
> .rollback() (by omission or dynamically making it (un)available).
> This will allow client software to do a hasattr(), rather than attempt a
> rollback only to find they couldn't... thus leaving their database
> screwed.


> > .commit() should always be implemented -- even for databases
> > that do not provide transactions.
> yes.
> Marc: could you include a list of open issues on the web page? It's
> becoming a pain to keep tracking down the right email :-)


> >  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 ?
> I think it behooves us to have a way that a module can state *which* it
> uses, but we don't *require* a specific format.
> I will suggest that we add a module global named "paramstyle" with the
> following values:
> 'qmark' -- question mark style
> 'numeric' -- numeric, positional style
> 'named' -- :name style
> 'percent' -- ANSI C printf format codes
> 'xpercent' -- Python extended format codes (e.g. %(name)s)
> However, we should also note that the API does not have a mechanism for
> named parameters. That obviates the 'named' and 'xpercent' styles at the
> moment.
> Anyways: having this global will allow a higher-level module to do
> something intelligent.

Right. I'll add that global with your proposed values.

> >  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 ?
> Per later emails, the Raw was renamed to Binary.
> I disagree with the need for seperate Date, Time, and Timestamp. I am
> unaware of an occurrance where the difference was needed. For the past
> three years, dbiDate() was sufficient, so I question the need for three
> varieties here.

Well, I'd say it doesn't hurt to have the variety and on the other
hand it may even be necessary in case the database screws up 
on specific time formats.

> Note: aliases for dbiRaw, dbiDate are not needed since those were names
> in the "dbi" module, not the database module.

Hmm. It might make porting a little easier.

> >  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 ?
> Ah. I like executemany() (better than my insert() suggestion). I'll vote
> for this, with the associated deprecation of passing a seq-of-seq to
> execute().

Anybody else in favor of this change ? If there are no objections,
I'll add it to the spec.

> > 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...]
> MySQL does handle long binary data.

The direct interface seems to do this. The output of the
test script Andy sent me indicates that his interface does in
fact also handle binary data correctly. The MyODBC driver screws
this up though as the test script complains with my setup (mxODBC->

I guess another bug report is due...

> [...]
> Dates are very tricky in MySQL, however. dbiDate arose out of the need
> to differentiate between an integer and a Unix ticks value. The database
> would then prepare the appropriate input binding. A similar need would
> still exist, so a MySQL database module would probably still require a
> date type.
> Note: this comes back to my point about Unix ticks. Marc: you said we
> should try to get rid of them. Sorry, but that is going to be quite
> difficult. Python's concept of a date is a Unix ticks value or a
> 9-tuple. We must have a constructor that accepts one of those things
> (since we can't pass a bare integer as a date, and I don't think we want
> to allow passing a 9-tuple to mean a date). Since most Python
> installations deal with dates in these formats, we must cooperate.

Hmm, I really don't like to get those ticks into the spec
again (after all they were the reason why I started with mxDateTime
in the first place). SQL defines date ranges to 0001 - 9999: that's
a range not reliably handleable using Unix ticks.

But there's a simple solution: provide constructors that do
the ticks (or seconds since midnight integers) conversion to
Date,Time,Timestamp constructor input, e.g.

def DateFromTicks(ticks):
   import time
   return apply(Date,time.localtime(ticks)[:3])

These helpers should be left to the module implementor though
(or we could provide a standard dbapi.py module providing these).

Just please leave those date/time issues out of the spec. Next,
someone will want COM dates ;-) All of these things are 
handled by mxDateTime for those that want the full monty of
date/time formats.

I'll update the spec page later today.

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