[DB-SIG] parameter and column binding (date/time handling)

[M.-A. Lemburg]

Vernon Cole wrote:
> BAH!  This is the 21st century!  Who cares about the column POSITION?
> Bind on the column NAME. 
> You guys have gotta quit thinking in SQL. It
> is a crappy language.  Think in Python.
> A comparison like:
>   if DataBaseRow.admitTime.date() == datetime.datetime.today().date() 
> ought to work (assuming the table has a field named "admitTime").

Note that column position is the only safe way to address
both a input parameter and an output column across databases:

Input parameters usually don't correspond to a column and if they
do the information is often enough not available from the database.

Output columns can correspond to a table column, but don't have
to (think "select count(*) from mytable where x=?"). Even when
they do, the database may apply case conversion to the column
name, so it's not clear whether the column names gets returned
as-is, in uppercase or in lowercase or even whether the database
makes this information available at all.

As a result, you can't make any assumptions on column names
in the DB API spec. You can in higher level wrappers crafted to
support a few selected databases, but the DB API is focusing
on providing a standard for a very broad range of backends.

We should really have a FAQ for these things.

> --
> Vernon Cole
> 
> 
> 
> -----Original Message-----
> From: db-sig-bounces at python.org [mailto:db-sig-bounces at python.org] On
> Behalf Of M.-A. Lemburg
> Sent: Monday, August 07, 2006 4:18 AM
> To: Christoph Zwerschke
> Cc: db-sig at python.org
> Subject: Re: [DB-SIG] parameter and column binding (date/time handling)
> 
> Christoph Zwerschke wrote:
>> Andy Todd wrote:
>>  > I don't know about the mechanism, but my preference for any DB-API 
>> module would be stdlib first and then mx.DateTime.
>>
>> Ok, I think I will do that. Otherwise, as soon as an administrator 
>> installs mx.DateTime, some non-related DB-API 2 applications may 
>> suddenly break.
> 
> Database modules should never choose a type mapping based
> on import testing.
> 
> If a module has a default of using mxDateTime and the package
> can't be found, it should raise an import error instead of
> silently reverting to Python's datetime or another type.
> 
>> Of course you will then get a problem if the 
>> administrator upgrades from Python 2.2 to 2.3, but you expect some 
>> things to break anyway in such a case (and he should have done that a 
>> long time ago anyway ;-)
>>
>> Still, I'm looking for a way to explicitely tell the module to use mx
> or 
>> stdlib dates, or other date formats.
> 
> I think we'd need something that does the following:
> 
> Parameter binding:
> 
> * allows mapping based on:
>   - parameter position in the statement
>   - parameter type (the database type code, if the database can
>     provide this information)
> 
> * allows assigning a binding function that converts the Python
>   object to whatever the database requires (these will have to
>   be database specific)
> 
>   These binding functions will then have to implement e.g.
>   "bind the Python object as VARCHAR".
> 
> Result set types:
> 
> * allows mapping based on:
>   - column position
>   - column type (the database type code)
> 
> * allows assigning a binding function that converts the database
>   type to a Python object (these will have to be database specific)
> 
>   These binding functions will have to implement e.g.
>   "convert the database type to a Python float".
> 
> Given that mapping on both position and type is hard to
> implement using dictionaries and that setting the binding
> functions is usually only done once per query, I think that
> using a callback to implement these mappings would be the
> most flexible way.
> 

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Aug 11 2006)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::