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.
_______________________________________________
Tutor maillist - [email protected]
http://mail.python.org/mailman/listinfo/tutor
