[DB-SIG] Experiences with DB-API2.0
Matthew T. Kromer
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!)