Misnamed datetime attributes in mysql/base.py

Issue #3096 invalid
Tara Andrews
created an issue

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,

Comments (12)

  1. Michael Bayer repo owner

    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):
    +    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
      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?

  2. tackler


    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

  3. Michael Bayer repo owner

    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.

  4. Log in to comment