[Tutor] Dates and databases, and blobs and Python.

[Liam Clarke]

Hi Kent, 



> Most databases have a DATE type and you can directly store and retrieve date objects. Looking at the
> pysqlite code I see it uses mxDateTime for date support - it doesn't use the Python datetime module.
> 
> So the first step is to install mxDateTime from
> http://www.egenix.com/files/python/mxDateTime.html
> 
> OK, now let's create a table with a DATE column:
>   >>> import sqlite
>   >>> con = sqlite.connect('mydatabase.db')
>   >>> cur = con.cursor()
>   >>> cur.execute('CREATE TABLE foo (aDate DATE)')
> 
> The sqlite Date class is called sqlite.main.Date. (According to the DB-API this should be exposed as
> sqlite.Date but whatever...). Let's create a Date:
>   >>> d=sqlite.main.Date(2005, 3, 26)
>   >>> d
> <DateTime object for '2005-03-26 00:00:00.00' at a31f20>
> 
> insert it into the table:
>   >>> cur.execute('insert into foo (aDate) values (%s)', d)
> 
> and read it back:
>   >>> cur.execute('select * from foo')
>   >>> cur.fetchall()
> [(<DateTime object for '2005-03-26 00:00:00.00' at 9bf660>,)]
> 
> Note how the data returned from fetchall() contains a Date object.
> 
> BTW there doesn't seem to be much available in the way of pysqlite docs. You should probably become
> familiar with the DB-API spec (http://www.python.org/peps/pep-0249.html) and the pysqlite source...


Thanks for that, I've installed mxDateTime, and just playing around at
the >>> with it, I can see that it's very useful, and being able to
pass it as a parameter is just what I needed.

And yeah, I'm familiar with the (lack of) documentation for pysqlite.
Apparently a whole new version is in the works, based around the
latest SQLite release, and it's a one man show as far as I can tell,
so perhaps I should get my skills up to speed and write the docs. : )

Regards, 

Liam Clarke


-- 
'There is only one basic human right, and that is to do as you damn well please.
And with it comes the only basic human duty, to take the consequences.