[DB-SIG] Experiences with DB-API2.0

Matthew T. Kromer matt@zope.com
Thu, 20 Jun 2002 20:45:08 -0400

Dustin Sallings wrote:

>Around 15:53 on Jun 20, 2002, Ch'marr said:
># At the time, I added that it would be nice to, say, be able to write a
># driver that supported one param style (say, the one that the C interface
># layer supported best), and have a DBI base layer do all the necessary
># conversions for the other param styles, so that the application
># programmer need only pick a paramstyle most suited to the application.
>	This sounds reasonable.  I like the 'pyformat' style, but 'qmark'
>can have special meaning that could optimize queries quite a bit.  It'd be
>great to get both.
>	Just to be clear, I don't mind that there are five styles, just
>that the driver developer gets to pick one instead of the user.

Keep in mind for any driver that wants do do binding  (bind by name, 
bind by position) formats like 'pyformat' require some degree of monkey 
work.  The reason being that the DRIVER needs to be in control of the 
TYPE of insert into the bind string... most of the time, it needs to 
turn the query expression into querystring + bind_list ... but the bind 
"names" in the query string may be '?' or ':1' or ':name' or many other 
variants.  Most of the time doing any kind of  parameter insert into the 
query string to generate a bind name is a string insert -- which means 
you have to process the query string to convert the types first! 
 Aaaargh.  I hate pyformat for that reason.  N.B. if your RDBMS supports 
real binds, string quoting is usually much less of an issue; the quote 
either comes directly from a human being, or the data originates via 
program and is bound rather than converted into a string and executed as 
part of the SQL command text.

I'd be more in favor of some type of driver-provided "query builder" 
interface, which could build up a query object in an agnostic fashion 
that met its own needs.

I also have to chuckle every time someone wants a "purely generic" 
interface.  Most RDBMS have divergent SQL dialects, with divergent 
types.  Any "power user" usually is tied to a specific RDBMS and wants 
to maximize it, and thus is going to get very frustrated when there is 
no "generic" way to access a very specific feature.

As far as people's DateTime problems go -- I certainly understand the 
frustration.  I know I had a conversation with PythonLabs some time ago 
(Christmas?)  where the notion of creating some kind of official "time" 
object was bounced around again.  I don't remember what the outcome was 
though; other than there is a lot of quicksand when dealing with 
people's expectations of what "Time" is.   As a driver author, I'm 
*extremely* reluctant to introduce a dependance on yet another piece of 
code, which is one reason I would appreciate an official Python DateTime 
object (for most RDBMS purposes, said object probably needs to have a 
date range of +/-10,000 years, and have precision down to the 
millisecond -- but that is not precise enough for some scientific purposes!)