How to use dates with pysqlite

[Gerhard Häring]

* Otto Tronarp <otttr440 at student.liu.se> [2003-03-31 23:52 +0200]:
> Hi,
> 
> I can't get dates to work with pysqlite. I was under the impression that
> it should autmatically cast columns of type date to a mx.DateTime if I
> hade mx.DateTime installed. However when I do a select from the database
> I always gets a string back. Any ideas of what I do wrong?
> 
> Python 2.2.2 (#1, Mar 12 2003, 09:06:07) 
> [GCC 3.2.2] on linux2
> Type "help", "copyright", "credits" or "license" for more information.
> >>> import sqlite
> >>> from mx import DateTime
> >>> 
> >>> con = sqlite.connect('testdb', autocommit=1)
> >>> cur = con.cursor()
> >>> cur.execute('create table test (d date)')
> >>> d = DateTime.Date(2003, 03, 31)
> >>> print d
> 2003-03-31 00:00:00.00
> >>> cur.execute('insert into test values(%s)', d)
> >>> cur.execute('select * from test')
> >>> r = cur.fetchone()
> >>> print r
> ('2003-03-31 00:00:00.00',)
> >>> print type(r[0])
> <type 'str'>
> >>> print type(d)
> <type 'DateTime'>
> >>> 
> 
> I would expect type(r[0]) to return the same as type(d) here.

Generally, you'll get away without using
"-- types type1, type2, ..., typen" in many use cases, but this is
currently not one of them.

Here's a workaround for now:

#v+
...
cur.execute("-- types DateTime")
cur.execute("select * from test")
...
#v-

Similarly, DateTimeDelta will work for INVERAL types.

I'll look into getting more date/time support into CVS soon, including
transparent handling of date, time and interval types. I thought that
mxDateTime lacks the necessary from-string conversion routines for this,
but after a fresh look, it doesn't.

Note that you can get support for *any* type by adding your own type
constructor using the 'converters' parameter of sqlite.connect:

#v+
from mx.DateTime import *
...
cx = sqlite.connect("db", converters={"date": DateFrom})
...
cu.execute("-- types date")
cu.execute("select d from test")
...
#v-

Gerhard
-- 
mail:   gh at ghaering.de
web:    http://ghaering.de/