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

[Kevin Jacobs]

On Fri, 2 Jan 2004, Guido van Rossum wrote:
> > This proposal does not address the needs of those that:
> > 
> >   1) need to take data from one DB-API driver to another driver or backend
> >      database.
> >   2) have inherently ambiguous DB->Python type mappings
> >   3) like the fact that DB-API adapters return builtin Python types in most 
> >      cases (if only for efficiency reasons since they have their own type
> >      or OR mapping systems built upon DB-API).
> 
> Right, but some of these requirements seem to follow from the fact
> that there *are* so many differences between DB-API modules.  Doesn't
> the addition of a standard, builtin datetime type take away much of
> (2) and hence (1), while improving (3)?

Actually, I'm in favor of the inclusion of the new datetime objects into
DB-API.  It is ubiquitous, light-weight, has timezone support, and is
capable of representing most of the date and time values I care about.  With
the inclusion of precision information, it would be a equivalent to most
advanced SQL date/time representations.

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.

> > Thus, this proposal should be a use-case for a type mapping extension for
> > input and output variables, but is not general enough to be a stand-alone,
> > mandated feature.
> 
> I imagine it also depends on the smarts of the database.  In MySQL, I
> seem to recall that string values of a certain format are acceptable
> as date input, so if a DB-API MySQL wrapper returned strings (with the
> right format) for date values, that would work just fine.  But this
> may not work for all db vendors.

True -- for example, the problem of proving support for bound variables in
drivers for backends that support a sophisticated SQL dialect, but not
explicit support for binding (e.g., PostgreSQL before version 7.4).  To
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.

-Kevin

-- 
--
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/