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

Liam Clarke cyresse at gmail.com
Sat Mar 26 12:46:17 CET 2005


Hi, 

Just a quick query. I want to store dates and work with them in my
SQLite database.
There's no specific need for any calculations to done on one side or
another (i.e. it's a single user database).

I googled how to work with dates in SQL, and I got one like this - 

SELECT * FROM totp
WHERE wk BETWEEN '1980/05/20'
              AND '1980/05/26'

as an example of finding a range for a date. I'm not too sure about
how SQL works with dates (and times for that matter), so I'm tempted
to stick to what I know and just store dates/times as strings in the
db, and then use them to create datetime objects when needed.

i.e. 

>>>cx.execute('select date, time from foo where c_id = 10')
>>>dat = cx.next()
>>>tim = cx.next()
>>>print dat, time
2005-12-31, 7:00
>>>splitD = dat.split('-')
>>>splitT = time.split(':')
>>>intD = [int(i) for item in splitD]
>>>intT = [int(i) for item in splitT]
>>> theDateTime = datetime.datetime(intD[0], intD[1], intD[2], intT[0], intT[1])

Although working with dates like that doesn't seem that flash either.

Alternatively, I was thinking of storing the actual datetime object in
the database (this is a blob I believe?), and that's a whole new
kettle of fish.

So far I've tried this -

>>> import datetime
>>> theDT = datetime.datetime(2004, 12, 31, 7, 30)
>>> print theDT
2004-12-31 07:30:00
>>> import sqlite
>>> c = sqlite.connect('foo.db')
>>> cx = c.cursor()
>>> import pickle
>>> j = pickle.dumps(theDT)
>>> cx.execute('insert into bob values(%s)', j)
>>> cx.execute('select A from bob')
>>> q = cx.next()
>>> print q
("cdatetime\ndatetime\np0\n(S'\\x07\\xd4\\x0c\\x1f\\x07\\x1e\\x00\\x00\\x00\\x00'\np1\ntp2\nRp3\n.",)
>>> w = pickle.loads(q[0])
>>> print w
2004-12-31 07:30:00

So, it works, but I'm not too sure. I tend to have a random approach
to using the standard library, as I don't fully understand what all of
the modules do.

This provokes the following questions - 

1. Should I be using StringIO for this instead?
2. Would my retrieved unpickled datetime object still work if datetime
hadn't been imported?
3. Is there a better way to work with blobs and Python?

And then there's the overall question -

What would be the least fiddly & least error prone way of working with
dates and times? Python or SQL?


Thank you for your time.


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.


More information about the Tutor mailing list