Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #59678

How to round trip python and sqlite dates

Show key headers only | View raw

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

Back to comp.lang.python | Previous | Next — Next in thread | Find similar | Unroll thread

Thread

How to round trip python and sqlite dates Mark Lawrence <breamoreboy@yahoo.co.uk> - 2013-11-17 02:16 +0000
  Re: How to round trip python and sqlite dates "Paul Simon" <psimon@sonic.net> - 2013-11-16 20:44 -0800

csiph-web