classification
Title: sqlite3 convert_date error with DATE type
Type: behavior Stage:
Components: Library (Lib) Versions: Python 2.7
process
Status: closed Resolution: not a bug
Dependencies: Superseder:
Assigned To: Nosy List: gruszczy, sherpya
Priority: normal Keywords:

Created on 2011-12-09 17:57 by sherpya, last changed 2011-12-11 03:41 by sherpya. This issue is now closed.

Messages (4)
msg149113 - (view) Author: Gianluigi Tiesi (sherpya) Date: 2011-12-09 17:57
When using the 'DATE' datatype in a sqlite3 db and type converters are enabled the function in sqlite3/dbapi2.py fails

I'm not sure why sqlite3 returns something like 10-JAN-11, but the function expects a ts

example:

import sqlite3
d = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
c = d.cursor()
c.execute("create table testdate (t1 date)")
c.execute("insert into testdate values ('now')")
c.execute("select * from testdate")
msg149194 - (view) Author: Filip GruszczyƄski (gruszczy) Date: 2011-12-10 22:22
c.execute("insert into testdate values ('now')")

This works, but you actually are putting string "now" into a field with DATE type. When conversion occurs after retrieving data, there is an error. Also if you use datetime() function


c.execute("insert into testdate values (datetime())")

you'll get an error later during conversion, because python expects date string and will get datetime string. This should work for you:

>>> c.execute("insert into testdate values (date())")
>>> x = c.execute("select * from testdate")
>>> for a in x:
...  print(a)
... 
(datetime.date(2011, 12, 10),)
msg149206 - (view) Author: Gianluigi Tiesi (sherpya) Date: 2011-12-11 03:22
I've made a simplified testcase, my problem is importing from a sql dump with dates in the format '10-OCT-11', so if I understand 'DATE' in sqlite is fake and really a string? I have no way to control this behavior if my dump is text?
msg149207 - (view) Author: Gianluigi Tiesi (sherpya) Date: 2011-12-11 03:41
So I suppose I have to blame sqlite3 and not fill a bug here
History
Date User Action Args
2011-12-11 03:41:04sherpyasetstatus: open -> closed
resolution: not a bug
messages: + msg149207
2011-12-11 03:22:11sherpyasetmessages: + msg149206
2011-12-10 22:22:41gruszczysetnosy: + gruszczy
messages: + msg149194
2011-12-09 17:57:22sherpyacreate