[DB-SIG] WHat's the status of DB modules and datetime.py supp ort?

Kevin Jacobs jacobs at penguin.theopalgroup.com
Fri Jan 2 16:44:11 EST 2004

On Fri, 2 Jan 2004, Federico Di Gregorio wrote:
> Il ven, 2004-01-02 alle 17:55, Kevin Jacobs ha scritto:
> [snip]
> > My above concerns are directed toward adopting a Python-type based method of
> > escaping SQL literals based on adding a new magic method (like __sqlrepr__
> > or __sqlstr__).  I assumed (incorrectly) that this was what Frederico was
> > suggesting with his proposal; a topic that has come up many times before and
> > one the sources of the "DB-API snob" perception.
> i did not. i think __sqlstr__ (name it as you like) is very usefull but
> it is not the solution for cross-driver compatibility. (that was my
> *wrong* idea about 6 month ago.)

We agree -- I said that I _incorrectly_ assumed this was what you were
bringing up again.

> [snip]
> > compute the best represent ion of various Python data as SQL literals may
> > require an SQL dialect parser and schema introspection.  This is why
> > mandating a one-size-fits all type mapping solution that naively guarantees
> > round-trip data representations is not possible via a simple extension to
> > DB-API, or at least I've yet to see a proposal to do it properly.
> sorry, but i don't agree here. please, show me a postgresql type that
> does not support round trip. i think i can write a wrapper object that
> does the round trip correctly for every postgresql type. :)

I don't disagree that you can write code to round-trip any datum from fetch
to bound argument.  However, the real trick is doing that in such a way that
the datum is useful in between -- i.e., if all we cared about was naive
round-trip-ability, then we'd pass around pre-escaped literal strings
everywhere.  However, I can SELECT an integer, add 1.5 to it, and be very
annoyed when the literal representation is or is not truncated/rounded back
to an integer without regard for the destination field type.  This is where
the result context is so critically important.  To this, add the previously
mentioned problem of data that doesn't come from any database backend, and
you're right back where you started before.

Problems like this also become insidious when one tries to maintain normal
Python semantics on objects being mapped to and from SQL types.  i.e., it is
perfectly reasonable to expect that 0/1 can be used interchangeably with
False/True as bound arguments, but the converse in SQL is decidedly not
true.  Thus, to know how to properly encode the resulting literal requires
that one know the SQL expression type.

So this entire debate boils down to one thing:

  Do you want to map SQL types to and from native classes, or do you need
  something more specialized?

I am in favor of letting the DB-API user decide which perspective benefits
their application best, rather than mandating anything simplistic or
invasive that any sophisticated user will want to bypass anyway.


Kevin Jacobs
The OPAL Group - Enterprise Systems Architect
Voice: (440) 871-6725 x 19         E-mail: jacobs at theopalgroup.com
Fax:   (440) 871-6722              WWW:    http://www.theopalgroup.com/

More information about the DB-SIG mailing list