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

Groups > comp.lang.python > #59678 > unrolled thread

How to round trip python and sqlite dates

Back to article view | Back to comp.lang.python

Contents

  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

#59678 — How to round trip python and sqlite dates

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

[toc] | [next] | [standalone]

#59693

"Mark Lawrence" <breamoreboy@yahoo.co.uk> wrote in message 
news:mailman.2752.1384654581.18130.python-list@python.org...
> 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
>
Just a quicky, but I believe you don't have to register the datetime or 
timestamp converter as it is already implicit in the python to sql 
adaptation.    This should handle the round trip conversion for you.  I use 
some similar code but it's late here now.

Paul 

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.python

csiph-web