SqlAlchemy mysql millisecond or microsecond precision
I was venturing into an odyssey trying to get a fractional temporal resolution working as expected in my database. I am using python method datetime.now()
to create date objects. Then I save these objects in a field that maps to COLUMN(DATETIME(9))
, which is from the SqlAlchemy library. I was initially getting a message about truncating my data. This is because I was using mysql 5.5. I have since updated to 5.6.19 and no longer get truncated information.
However, the database still does not contain fractional time records. For example, here's the value from which the datetime.now () object is created:
2015-04-17 16:31:12.804444
The above was exactly what I would have expected. The object in memory has a resolution in microseconds. Now, after it saves this to the mysql database, I see the following value if I open the mysql command line client and return the string using a select statement:
2015-04-17 16:31:13
Obviously, the value is rounded to the nearest second. This is bad and I have no idea what is causing it.
In case it matters, I am using mysql-connector-python == 2.0.3
UPDATE: I also tried using COLUMN(DATETIME(6))
but got the same behavior.
Below is the model, in case the information is relevant:
class User(Base):
__tablename__ = 'Users'
uid = Column(INT, primary_key=True, autoincrement=True)
dateCreated = Column(DATETIME(6))
def __init__(self, *args, **kwargs):
super(user, self).__init__(*args, **kwargs)
self.dateCreated = datetime.now()
UPDATE: Pedro's suggestion was not a problem, although it definitely helped me make progress, so thanks a lot. I tried stepping through the code in the sql connector until I got to the mysql insert statement. The statement does contain a fractional time value. However, the value is rounded on execution. When I described the table, I noticed that the datetime type is only datetime
when it really should be datetime(6)
.
I am creating a database using the SA model which explicitly declares COLUMN(DATETIME(6))
and Base.metadata.create_all(self.db, checkfirst=True)
, so I don't understand why (6) doesn't end in the actual table structure. I think I'll figure it out soon and I'll post an update when I do.
UPDATE: The DATETIME constructor does not accept a field length specification. It only takes an argument for timezones. It is not clear to me how to specify the length of the datetime field, as for types like varchar, you just pass it to the constructor. The dive continues.
source to share
The problem I am facing is that the SqlAlchemy DATETIME class does not work with mysql's requirement to pass the value (6) to the constructor to represent fractional time values. Use the class instead sqlalchemy.dialects.mysql.DATETIME
. This class allows you to use a parameter fsp
(fractional second parameter). So, the column declaration should look like this:
dateCreated = Column(DATETIME(fsp=6))
Thanks to those who answered. This helped my research eventually stumble over this esoteric and very confusing distinction.
source to share
This seems to be an open issue with MySQLdb and not MySQL or SQLAlchemy.
http://sourceforge.net/p/mysql-python/feature-requests/24/
You can try using a different MySQL driver library - check the SQLAlchemy documentation for supported options - or you can try the monkey bundle suggested in the open issue.
source to share
According to MySQL documentation , it only supports up to 6 places (microseconds):
"MySQL 5.6.4 and up extends support for fractional seconds for TIME, DATETIME, and TIMESTAMP values with microsecond (6 digits) precision"
I don't know for sure, but specifying 9 digits may return the column to a lower precision by default.
source to share