Re: How to round trip python and sqlite dates

2013-11-17 Thread Mark Lawrence 

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

--
https://mail.python.org/mailman/listinfo/python-list

How to round trip python and sqlite dates

2013-11-16 Thread Mark Lawrence 
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

--
https://mail.python.org/mailman/listinfo/python-list

Re: How to round trip python and sqlite dates

2013-11-16 Thread Paul Simon 
Mark Lawrence breamore...@yahoo.co.uk wrote in message 
news:mailman.2752.1384654581.18130.python-l...@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 


-- 
https://mail.python.org/mailman/listinfo/python-list