mssql time type and datetime.time microseconds precision

[Former user]

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

[Mike Bayer]

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.

[Former user]

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

[Mike Bayer]

thanks, works great! even though I can't get pyodbc to write the microseconds out for a full round trip.

21870c750302704890f5fc7a4e15c7e27c3e512f

[Mike Bayer]

Removing milestone: 0.7.5 (automated comment)