Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!goblin1!goblin.stu.neva.ru!uio.no!news.tele.dk!news.tele.dk!small.news.tele.dk!newsgate.cistron.nl!newsgate.news.xs4all.nl!post.news.xs4all.nl!not-for-mail
To: python-list@python.org
From: Mark Lawrence <breamoreboy@yahoo.co.uk>
Subject: How to round trip python and sqlite dates
Date: Sun, 17 Nov 2013 02:16:49 +0000
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Windows NT 6.1; rv:24.0) Gecko/20100101 Thunderbird/24.1.0
Precedence: list
Newsgroups: comp.lang.python
Message-ID: <mailman.2752.1384654581.18130.python-list@python.org>
Lines: 54
NNTP-Posting-Host: 2001:888:2000:d::a6
Xref: csiph.com comp.lang.python:59678

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.

-- 
Python is the second best programming language in the world.
But the best has yet to be invented.  Christian Tismer

Mark Lawrence