Re: [Tutor] Dates and databases, and blobs and Python.
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
>
>
> 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()
> [(,)]
>
> 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.
___
Tutor maillist - Tutor@python.org
http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] Dates and databases, and blobs and Python.
Liam Clarke wrote:
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?
Liam, SQL is another language. You need to learn it like you are
learning Python. It has pretty decent support for dates and times as
part of the ANSI SQL spec.
There are defined types, SQL functions, etc.
week(date) = week(mydate) for instance.
I tend to prefer to write programming language agnostic databases
because that way I can interact with them from any random language I
need to. Sometimes things go bad and a quick little script which does:
rows = exec("select * from table")
exec("begin transaction")
for row in rows:
row[3] = row[2] + row[4] - 5 # we decided on a new value
exec("update fields in table")
can be handy.
___
Tutor maillist - Tutor@python.org
http://mail.python.org/mailman/listinfo/tutor
[Tutor] Dates and databases, and blobs and Python.
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 - Tutor@python.org
http://mail.python.org/mailman/listinfo/tutor
