Misnamed datetime attributes in mysql/base.py

[Tara L Andrews]

Using SQLAlchemy with a DateTime column on MySQL gives me the following stack trace:

Traceback (most recent call last):
  File "/Users/tla/Projects/PBW/main.py", line 9, in <module>
    our_people = session.query(data.Person).filter_by(name="Alexios").all()
  File "/Users/tla/anaconda/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2279, in all
    return list(self)
  File "/Users/tla/anaconda/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 72, in instances
    rows = [process[0](row, None) for row in fetch]
  File "/Users/tla/anaconda/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 451, in _instance
    populate_state(state, dict_, row, isnew, only_load_props)
  File "/Users/tla/anaconda/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 305, in populate_state
    populator(state, dict_, row)
  File "/Users/tla/anaconda/lib/python2.7/site-packages/sqlalchemy/orm/strategies.py", line 154, in fetch_col
    dict_[key] = row[col]
  File "/Users/tla/anaconda/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py", line 769, in process
    microseconds = value.microseconds
AttributeError: 'datetime.datetime' object has no attribute 'microseconds'

...which makes sense, as indeed the attribute is called 'microsecond' and, on the following line, it should be 'second' rather than 'seconds'. Here is the very simple patch:

--- base.py 2014-06-25 11:03:02.000000000 +0200
+++ /Users/tla/anaconda/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py   2014-06-25 11:03:08.000000000 +0200
@@ -766,8 +766,8 @@
         def process(value):
             # convert from a timedelta value
             if value is not None:
-                microseconds = value.microseconds
-                seconds = value.seconds
+                microseconds = value.microsecond
+                seconds = value.second
                 minutes = seconds // 60
                 return time(minutes // 60,
                             minutes % 60,

[Mike Bayer]

this is highly unusual because that code is handling a datetime.timedelta() object, not a datetime.time(). I've just added this test case:

--- a/test/dialect/mysql/test_types.py
+++ b/test/dialect/mysql/test_types.py
@@ -516,6 +516,16 @@ class TypesTest(fixtures.TestBase, AssertsExecutionResults, AssertsCompiledSQL):

     @testing.only_if('mysql')
     @testing.provide_metadata
+    def test_time_roundtrip(self):
+        t = Table('mysql_time', self.metadata,
+                Column('t1', mysql.TIME())
+            )
+        t.create()
+        t.insert().values(t1=datetime.time(8, 37, 35)).execute()
+        eq_(select([t.c.t1]).scalar(), datetime.time(8, 37, 35))
+
+    @testing.only_if('mysql')
+    @testing.provide_metadata
     def test_year(self):
         """Exercise YEAR."""

and ran it against: mysql-python, oursql, mysqlconnector, pymysql. All return a timedelta(), and if we use the patch above, it breaks:

#!

python -m pytest test/dialect/mysql/test_types.py  -k test_time --dburi mysql+pymysql://scott:tiger@localhost/test
================================================ test session starts ================================================
platform darwin -- Python 2.7.5 -- py-1.4.20 -- pytest-2.5.2 -- /Library/Frameworks/Python.framework/Versions/2.7/Resources/Python.app/Contents/MacOS/Python
plugins: cov
collected 25 items 

test/dialect/mysql/test_types.py:502: TypesTest.test_time_fsp PASSED
test/dialect/mysql/test_types.py:494: TypesTest.test_time_generic PASSED
test/dialect/mysql/test_types.py:508: TypesTest.test_time_result_processor FAILED
test/dialect/mysql/test_types.py:517: TypesTest.test_time_roundtrip FAILED
test/dialect/mysql/test_types.py:416: TypesTest.test_timestamp_defaults PASSED
test/dialect/mysql/test_types.py:410: TypesTest.test_timestamp_fsp PASSED
test/dialect/mysql/test_types.py:450: TypesTest.test_timestamp_nullable PASSED

===================================================== FAILURES ======================================================
_______________________________________ TypesTest.test_time_result_processor ________________________________________
Traceback (most recent call last):
  File "/Users/classic/dev/sqlalchemy/test/dialect/mysql/test_types.py", line 512, in test_time_result_processor
    microseconds=450
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/dialects/mysql/base.py", line 797, in process
    microseconds = value.microsecond
AttributeError: 'datetime.timedelta' object has no attribute 'microsecond'
___________________________________________ TypesTest.test_time_roundtrip ___________________________________________
Traceback (most recent call last):
  File "<string>", line 2, in test_time_roundtrip
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/testing/exclusions.py", line 70, in decorate
    return fn(*args, **kw)
  File "<string>", line 2, in test_time_roundtrip
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/testing/util.py", line 190, in provide_metadata
    return fn(*args, **kw)
  File "/Users/classic/dev/sqlalchemy/test/dialect/mysql/test_types.py", line 525, in test_time_roundtrip
    eq_(select([t.c.t1]).scalar(), datetime.time(8, 37, 35))
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/sql/base.py", line 383, in scalar
    return self.execute(*multiparams, **params).scalar()
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/engine/result.py", line 860, in scalar
    return row[0]
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/dialects/mysql/base.py", line 797, in process
    microseconds = value.microsecond
AttributeError: 'datetime.timedelta' object has no attribute 'microsecond'
============================================== short test summary info ==============================================
FAIL test/dialect/mysql/test_types.py::TypesTest::()::test_time_result_processor
FAIL test/dialect/mysql/test_types.py::TypesTest::()::test_time_roundtrip
======================================= 18 tests deselected by '-ktest_time' ========================================
================================= 2 failed, 5 passed, 18 deselected in 0.11 seconds =================================
classics-MacBook-Pro-2:sqlalchemy classic$ 

so, this is not making much sense how you're getting a datetime in there. What database, Python version, DBAPI in use? Special datatypes in play here?

[Mike Bayer]

oh, unless you've applied the TIME type to a MySQL column that is actually a DATETIME or TIMESTAMP. that would be on your end.

[Tara L Andrews]

Aha, yes, to a TIMESTAMP. How absent-minded of me! Apologies for the trouble.

[Tara L Andrews]

Mistaken bug report

[tackler]

Hi,

I am getting the same error when trying to use aldjemy or django-sabridge packages and querying using Session. (I am trying to integrate django with sqla)

How about a patch that will check if the instance is of datetime.time and if so, will just return the datetime.time object ?

I am using MySQL-python==1.2.5

[Mike Bayer]

@tackler why are you using the wrong datatype for the column as described above?

[tackler]

No, actually django returns datetime.time and not datetime.timedelta for TimeField (which MySQL stores as time). It overrides MySQLdb's conversions (https://github.com/django/django/blob/master/django/db/backends/mysql/base.py#L44). These packages give this value to SQLA.

So, I am not sure if it's okay to ask for a change in SQLA itself or if there's some other way I can make this work.

Sorry for the confusion.

[Mike Bayer]

so....why not propose a patch to django-sabridge? since that's the program that's supposed to do the correct conversions between the two packages.

[Mike Bayer]

on this end, the best I could do would be to catch AttributeError, and then do the check and just raise an error that's descriptive. because first off we try very hard not to put isinstance() into a data conversion function and secondly because the datatype should be used correctly in the first place.

[tackler]

Got it. Thanks for the help! I will go bug them.