sqlite DateTimeMixin data formatting

[Former user]

The DateTimeMixin._cvt method requires that data retrieved from the database is in "%Y-%m-%d %H:%M:%S", "%Y-%m-%d", or "%H:%M:%S" format depending on the column type.

However, DateTimeMixin.convert_bind_param just stores the passed in value as a string. Since sqlite doesn't really pay any attention to column types, it just stores the string. When that row is fetched from the database, various exceptions are raised depending on what the data is.

Maybe SLDate,SLTime,SLDateTime and/or DateTimeMixin could handle some common value types like timestamps and time tuples before storing the data in the database.

from sqlalchemy import *
import time, datetime, traceback

testTable = Table('test',
        Column('d', DateTime, nullable=False),
        )

global_connect("sqlite://")
testTable.create()

testTable.insert().execute(d = "just a string")
testTable.insert().execute(d = time.time())
testTable.insert().execute(d = datetime.datetime.now())

for row in testTable.select().execute().fetchall():
    try:
        print row
    except:
        traceback.print_exc()
        continue

gives this output:

Traceback (most recent call last):
  File "test.py", line 17, in <module>
    print row
  File "/home/catlee/src/sqlalchemy/lib/sqlalchemy/engine/base.py", line 805, in __repr__
    return repr(tuple([key) for key in range(0, len(self.__row))](self.__parent._get_col(self.__row,)))
  File "/home/catlee/src/sqlalchemy/lib/sqlalchemy/engine/base.py", line 666, in _get_col
    return rec[0](0).dialect_impl(self.dialect).convert_result_value(row[rec[1](rec[1)], self.dialect)
  File "/home/catlee/src/sqlalchemy/lib/sqlalchemy/databases/sqlite.py", line 60, in convert_result_value
    tup = self._cvt(value, dialect, "%Y-%m-%d %H:%M:%S")
  File "/home/catlee/src/sqlalchemy/lib/sqlalchemy/databases/sqlite.py", line 54, in _cvt
    return time.strptime(value, fmt)[0:6](0:6) + (microsecond,)
  File "_strptime.py", line 310, in strptime
    (data_string, format))
ValueError: time data did not match format:  data=just a string  fmt=%Y-%m-%d %H:%M:%S
Traceback (most recent call last):
  File "test.py", line 17, in <module>
    print row
  File "/home/catlee/src/sqlalchemy/lib/sqlalchemy/engine/base.py", line 805, in __repr__
    return repr(tuple([key) for key in range(0, len(self.__row))](self.__parent._get_col(self.__row,)))
  File "/home/catlee/src/sqlalchemy/lib/sqlalchemy/engine/base.py", line 666, in _get_col
    return rec[0](0).dialect_impl(self.dialect).convert_result_value(row[rec[1](rec[1)], self.dialect)
  File "/home/catlee/src/sqlalchemy/lib/sqlalchemy/databases/sqlite.py", line 60, in convert_result_value
    tup = self._cvt(value, dialect, "%Y-%m-%d %H:%M:%S")
  File "/home/catlee/src/sqlalchemy/lib/sqlalchemy/databases/sqlite.py", line 50, in _cvt
    (value, microsecond) = value.split('.')
AttributeError: 'float' object has no attribute 'split'
(datetime.datetime(2007, 2, 7, 21, 0, 10, 584770),)

[Mike Bayer]

well for convert_bind_param we are calling str() on the param, so thats where those formats are coming from (but I guess you knew that).

so, are we looking here for "if not isinstance(x, datetime): raise error" ? or being able to plug in formatters for other things that are not datetime/time/date? I might prefer new types for these, since the behavior of DateTime/Time/Date across the board is meant to only support datetime/time/date objects.

[Former user]

if not isinstance(x, datetime): raise error is one option...It makes sense since you're getting datetime objects back from the database.

convert_bind_param() could also try and parse the string in the same way that _cvt() does and raise an exception if the string can't be parsed?

[Mike Bayer]

we can put string parsing in convert_bind_param. can you provide a patch ?

[Former user]

Replying to zzzeek: new patch adds check of input values if it's possible to restore actiual values from str(value).

[Former user]

Second version of patch, should be more faster than 1st one.

[Former user]

I tried it out, with Turbogears and version 0.3.8, and also with the new version, but doesn't seem to work: every now and then, I get an error, when retrieving a record from the database. The records all belong to the same class, and are inserted with the default value 'now', as below:

Column('created', DateTime, default=datetime.now)

I get the error below with the old version: File "c:\Python25\lib_strptime.py", line 331, in strptime (data_string, format)) ValueError: time data did not match format: data=2007/06/19 22:40 fmt=%Y-%m-%d %H:%M:%S

and with the new version (only sqlite.py replaced):

File "c:\python25\lib\site-packages\sqlalchemy-0.3.8-py2.5.egg\sqlalchemy\databases\sqlite.py", line 38, in convert_bind_param return value.strftime(self.format) AttributeError: 'unicode' object has no attribute 'strftime'

[Mike Bayer]

the patches on this ticket are also out of date since ive made some enhancements to sqlite date types recently.

[Former user]

Still broken in 0.5.0 beta1.

I don't understand why SA is enforcing a format on retrieval of data if it doesn't check on insert. Isn't the task of data validation of stored data one that belongs to the app?

For example, I have stored a date '10-10-2008' in a DATE field in a model in a Pylons app and when I retrieve, I get the message "time data did not match the format: data=10-10-2008 fmt=%Y-%m-%d'.

I am using formencode to validate my dates and it only allows dates with a trailing year (in DateConverter) and SA only allows a leading year. Maybe I should roll my own validation..

[Mike Bayer]

the format on insert is meant to be a Python datetime object. My preferred solution here would be for SLDateTime etc. to raise an error if a datetime (the approrpriate subtype, that is) is not received. So I'd like to mark this as "wontfix" since now that I've actually used FormEncode quite a bit, there's no valid use case here for DateTime etc. accepting strings.

[Former user]

Replying to zzzeek:

the format on insert is meant to be a Python datetime object. My preferred solution here would be for SLDateTime etc. to raise an error if a datetime (the approrpriate subtype, that is) is not received. So I'd like to mark this as "wontfix" since now that I've actually used FormEncode quite a bit, there's no valid use case here for DateTime etc. accepting strings.

In my case, SA did not complain about a date value on insertion but only at exit. The model declared the field as datetime and the data was accepted.. so it looks like I need to format the data (or use subtype of datetime) before insert.

[Mike Bayer]

Replying to guest:

In my case, SA did not complain about a date value on insertion but only at exit. The model declared the field as datetime and the data was accepted.. so it looks like I need to format the data (or use subtype of datetime) before insert.

do not "format" the data. Create a datetime object. 0.5 is definitely going to be changed to not allow strings.

[Mike Bayer]

the assertion for datetime-only bind params is in e7e60c05c0dc5f6ee7f51a829c2e0635a26d95af.