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

[Kevin Jacobs]

On Mon, 5 Jan 2004, Federico Di Gregorio wrote:
> Il ven, 2004-01-02 alle 22:44, Kevin Jacobs ha scritto:
> [snip]
> 1/ let's extend the dbapi by adding some usefull things (as the
> round-trip, imho, but there are other extensions that can be usefull for
> higher-level module implementors, like some more type singletons) that
> does not depend on knowledge of column types.

Can you outline exactly how you intend on doing this?  It is likely that my
concerns can be addressed, though it is difficult to know until it is 100%
clear what you are proposing.

> 2/ let's extend the dbapi with a standard way to extract
> type-information (and other kind of meta-information from the database).
> i *know* people think the dbapi is not the place for this; we can start
> a new PEP or anything else but i think we need a standard way to access
> meta-information right now.

Actually, there is a simpler things that can be done -- to start, let's
expand the existing DB-API type API.  e.g.:

  a) Add TIME, DATE, TIMESTAMP, DATETIMETZ type classes since DATETIME is
     far too generic.

  b) Similarly, add MONEY, INTEGER, FLOAT, DECIMAL/NUMERIC type classes to
     begin to disambiguate numeric types.

  c) Add VARCHAR, CHAR, and TEXT type classes to begin to disambiguate
     non-LOB string types.

  d) Add a BOOLEAN type class since NUMBER is wholly inadequate for
     describing boolean literals.

  e) Add an interface for mapping from type_id's to type classes (i.e., the
     reverse of the mapping provided by type objects).

> 3/ with the type information would be quite easy to write a
> (protocols-based?) utility module that automatically convert the types
> so that they can be safetly used cross-db. quoting is to be left to the
> DBAPI proper, we only need to convert to a type that the specific DBAPI
> implementation can understand. something like:
> 
> # pcurs is a psycopg cursor
> # mcurs is a mysql cursor
> 
> pcurs.execute("SELECT datefield FROM test")
> pdate = psycopg_curs.fetchone()[0]
> mdate = adapt(pdate, mysqlinterfaces.IDATE)
> mcurs.execute("INSERT INTO test (datefield) VALUES (%s)", (mdate,))
> 
> don't look at the formalism (adapt), look at the underlying idea. if we
> know enough about types, we can provide conversion functions from one
> module to the others quite easily. maybe we don't even need point (2) to
> write (3). 

Instead of adapt(), why not call it toSQLliteral()?  Isn't that the real
operation that execute needs a protocol to perform?

> [snip]
> >   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.
> 
> agreed. that's why i built into psycopg a user-customizable type-system
> from the start :) [and sincerely, i would like to see the
> type-registration system standarized in DBAPI-3.0. i know other modules
> provide the same functionality...]

As a happy user of your type extensions, I am grateful.  However, it isn't
very difficult to add such behavior to drivers that do not have builtin
support, as I have done with about a dozen others.  The runtime performance
hit tends to be fairly insignificant as compared to query overhead.

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