How to round trip python and sqlite dates

Mark Lawrence breamoreboy at yahoo.co.uk
Sun Nov 17 20:39:59 CET 2013


On 17/11/2013 02:16, Mark Lawrence wrote:
> All the references regarding the subject that I can find, e.g.
> http://stackoverflow.com/questions/1829872/read-datetime-back-from-sqlite-as-a-datetime-in-python,
> talk about creating a table in memory using the timestamp type from the
> Python layer.  I can't see how to use that for a file on disk, so after
> a bit of RTFM I came up with this.
>
> import sqlite3
> from datetime import datetime, date
>
> def datetime2date(datetimestr):
>      return datetime.strptime(datetimestr, '%Y-%m-%d')
>
> sqlite3.register_converter('DATETIME', datetime2date)
>
> db = sqlite3.connect(r'C:\Users\Mark\Cash\Data\test.sqlite',
> detect_types=sqlite3.PARSE_DECLTYPES)
> c = db.cursor()
> c.execute('delete from temp')
> row = 'DWP ESA', date(2013,11,18), 'Every two weeks', 143.4, date.max
> c.execute('insert into temp values (?,?,?,?,?)', row)
> c.execute('select * from temp')
> row = c.fetchone()
> nextdate = row[1]
> print(nextdate, type(nextdate))
>
> Run it and
>
> Traceback (most recent call last):
>    File "C:\Users\Mark\MyPython\mytest.py", line 13, in <module>
>      c.execute('select * from temp')
>    File "C:\Users\Mark\MyPython\mytest.py", line 7, in datetime2date
>      return datetime.strptime(datetimestr, '%Y-%m-%d')
> TypeError: must be str, not bytes
>
> However if I comment out the register_converter line this output is printed
>
> 2013-11-18 <class 'str'>
>
> Further digging in the sqlite3 file dbapi2.py I found references to
> convert_date and convert_timestamp, but putting print statements in them
> and they didn't appear to be called.
>
> So how do I achieve the round trip that I'd like, or do I simply cut my
> loses and use strptime on the string that I can see returned?
>
> Note that I won't be checking replies, if any, for several hours as it's
> now 02:15 GMT and I'm heading back to bed.
>

Problem solved by RTFMing to section 12.6.5.4. of the standard library 
reference for Python 3.3.2, which even gives an example.

-- 
Python is the second best programming language in the world.
But the best has yet to be invented.  Christian Tismer

Mark Lawrence




More information about the Python-list mailing list