Hi,
not sure if I should post this issue here or if its a mysql-python one but
perhaps someone with better knowledge can help me figure that out?
A more pretty print version of this issue is available
here:
http://stackoverflow.com/questions/17315422/unable-to-store-datetime-datetime-max-using-sqlalchemy-0-8-1-with-the-mysql-pyt
I've noticed a change in behavior for storing datetime.datetime.max via
`SQLAlchemy==SQLAlchemy==0.8.1` and going from `mysql-python==1.2.3` to
`mysql-python==1.2.4`. By only changing the driver from 1.2.3 to 1.2.4 I go
from being able to store to being unable to store it.
Where do I turn to for help in this matter? SQLAlchemy or mysql-python? Is
this expected behaviour or a bug or do I have a bad setup? I fear that a
change like this will break a lot of systems out there.
This is my SQLAlchemy setup:
from sqlalchemy import create_engine, Integer, DateTime, Column
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime
engine = create_engine('mysql://root@localhost/test_database',
echo=True)
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
age = Column(DateTime, default=datetime.max)
Base.metadata.create_all(engine)
session = sessionmaker(bind=engine)()
u = User()
session.add(u)
session.commit()
I also have a virtualenv called test. This is what happens when I run the
code above.
(test)➜ ~ pip install MySQL-python==1.2.3
(test)➜ ~ python test.py
2013-06-26 10:29:18,885 INFO sqlalchemy.engine.base.Engine SELECT
DATABASE()
2013-06-26 10:29:18,885 INFO sqlalchemy.engine.base.Engine ()
2013-06-26 10:29:18,887 INFO sqlalchemy.engine.base.Engine SHOW
VARIABLES LIKE 'character_set%%'
2013-06-26 10:29:18,887 INFO sqlalchemy.engine.base.Engine ()
2013-06-26 10:29:18,891 INFO sqlalchemy.engine.base.Engine SHOW
VARIABLES LIKE 'sql_mode'
2013-06-26 10:29:18,891 INFO sqlalchemy.engine.base.Engine ()
2013-06-26 10:29:18,896 INFO sqlalchemy.engine.base.Engine DESCRIBE
`users`
2013-06-26 10:29:18,896 INFO sqlalchemy.engine.base.Engine ()
2013-06-26 10:29:18,904 INFO sqlalchemy.engine.base.Engine BEGIN
(implicit)
2013-06-26 10:29:18,905 INFO sqlalchemy.engine.base.Engine INSERT INTO
users (age) VALUES (%s)
2013-06-26 10:29:18,905 INFO sqlalchemy.engine.base.Engine
(datetime.datetime(9999, 12, 31, 23, 59, 59, 999999),)
2013-06-26 10:29:18,908 INFO sqlalchemy.engine.base.Engine COMMIT
And the database (test_database) looks like this:
mysql> select * from users;
+----+---------------------+
| id | age |
+----+---------------------+
| 1 | 9999-12-31 23:59:59 |
+----+---------------------+
1 row in set (0.00 sec)
This is my expected result so nothing strange here.
However, by simply switching the driver to mysql-python==1.2.4 I get this
result.
(test)➜ ~ pip install MySQL-python==1.2.4
(test)➜ ~ python test.py
2013-06-26 10:33:39,544 INFO sqlalchemy.engine.base.Engine SELECT
DATABASE()
2013-06-26 10:33:39,544 INFO sqlalchemy.engine.base.Engine ()
2013-06-26 10:33:39,546 INFO sqlalchemy.engine.base.Engine SHOW
VARIABLES LIKE 'character_set%%'
2013-06-26 10:33:39,546 INFO sqlalchemy.engine.base.Engine ()
2013-06-26 10:33:39,546 INFO sqlalchemy.engine.base.Engine SHOW
VARIABLES LIKE 'sql_mode'
2013-06-26 10:33:39,546 INFO sqlalchemy.engine.base.Engine ()
2013-06-26 10:33:39,547 INFO sqlalchemy.engine.base.Engine DESCRIBE
`users`
2013-06-26 10:33:39,547 INFO sqlalchemy.engine.base.Engine ()
2013-06-26 10:33:39,551 INFO sqlalchemy.engine.base.Engine BEGIN
(implicit)
2013-06-26 10:33:39,552 INFO sqlalchemy.engine.base.Engine INSERT INTO
users (age) VALUES (%s)
2013-06-26 10:33:39,552 INFO sqlalchemy.engine.base.Engine
(datetime.datetime(9999, 12, 31, 23, 59, 59, 999999),)
/Users/pelle/.virtualenvs/test/lib/python2.7/site-packages/sqlalchemy/engine/default.py:324:
Warning: Datetime function: datetime field overflow
cursor.execute(statement, parameters)
/Users/pelle/.virtualenvs/test/lib/python2.7/site-packages/sqlalchemy/engine/default.py:324:
Warning: Out of range value for column 'age' at row 1
cursor.execute(statement, parameters)
2013-06-26 10:33:39,553 INFO sqlalchemy.engine.base.Engine COMMIT
And the database looks like this.
mysql> select * from users;
+----+---------------------+
| id | age |
+----+---------------------+
| 1 | 0000-00-00 00:00:00 |
+----+---------------------+
1 row in set (0.00 sec)
So now all of the sudden I receive a warning `Warning: Datetime function:
datetime field overflow` and I end up with a nullable value in my database.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
