- changed milestone to 0.7.5
mssql time type and datetime.time microseconds precision
Issue #2340
resolved
MS SQL time type has 7 digit microseconds (time value after decimal point), python datetime.time() allows only 6 digit value of microseconds. That raises ValueError when querying for a column of time type.
Probably regex in sqlalchemy/dialects/mssql/base.py line 300
{{{
Traceback (most recent call last):
File "/home/mikado/mydevenv/env/vte/sandbox/mikado/mstypes_sand.py", line 13, in <module>
t = session.query(getattr(Test, prop.key)).first()
File "/home/mikado/mydevenv/env/lib/python2.6/site-packages/SQLAlchemy-0.7.3-py2.6-linux-i686.egg/sqlalchemy/orm/query.py", line 1963, in first
ret = list(self[0:1](0:1))
File "/home/mikado/mydevenv/env/lib/python2.6/site-packages/SQLAlchemy-0.7.3-py2.6-linux-i686.egg/sqlalchemy/orm/query.py", line 1857, in __getitem__
return list(res)
File "/home/mikado/mydevenv/env/lib/python2.6/site-packages/SQLAlchemy-0.7.3-py2.6-linux-i686.egg/sqlalchemy/orm/query.py", line 2156, in instances
labels) for row in fetch]
File "/home/mikado/mydevenv/env/lib/python2.6/site-packages/SQLAlchemy-0.7.3-py2.6-linux-i686.egg/sqlalchemy/orm/query.py", line 3139, in proc
return row[column](column)
File "/home/mikado/mydevenv/env/lib/python2.6/site-packages/SQLAlchemy-0.7.3-py2.6-linux-i686.egg/sqlalchemy/dialects/mssql/base.py", line 308, in process
for x in self._reg.match(value).groups()])
ValueError: microsecond must be in 0..999999
Comments (5)
-
repo owner -
repo owner - marked as critical
-
Account Deleted Here's a little test class illustrating the failure. I don't have mssql, so I wrote a stand-alone test case.
class TimeTypeTest(fixtures.TestBase): def test_result_processor_no_microseconds(self): expected = datetime.time(12, 34, 56) self._assert_result_processor(expected, '12:34:56') def test_result_processor_too_many_microseconds(self): # microsecond must be in 0..999999, should truncate (6 vs 7 digits) expected = datetime.time(12, 34, 56, 123456) self._assert_result_processor(expected, '12:34:56.1234567') def _assert_result_processor(self, expected, value): mssql_time_type = TIME() result_processor = mssql_time_type.result_processor(None, None) eq_(expected, result_processor(value))
And a possible code change (/sqlalchemy/lib/sqlalchemy/dialects/mssql/base.py):
- _reg = re.compile(r"(\d+):(\d+):(\d+)(?:\.(\d+))?") + _reg = re.compile(r"(\d+):(\d+):(\d+)(?:\.(\d{0,6}))?")
--diana
-
repo owner - changed watchers to mike.dobrzanski@gmail.com, diana
- changed status to resolved
thanks, works great! even though I can't get pyodbc to write the microseconds out for a full round trip.
-
repo owner - removed milestone
Removing milestone: 0.7.5 (automated comment)
- Log in to comment
OK, will need to make a test in test.dialect.test_mssql:TypesTest first illustrating the failure, then we can plug in the fix and it's good.
I'm way behind on 0.7.4 which should go out the door soon so putting this on 0.7.5 for the moment. If you want to provide a test then I can I just commit the whole thing into 0.7.4, otherwise I'll get to it down the line.