SqlAlchemy Mysql Millisecond Or Microsecond Precision

I've been venturing down the odyssey of trying to get fractional time resolution working properly in my database. I use the python method datetime.now() in order to create date obj

Solution 1:

The problem I was having is that the stock SqlAlchemy DATETIME class does not work with the mysql requirement of passing a (6) value into the constructor in order to represent fractional time values. Instead, one needs to use the sqlalchemy.dialects.mysql.DATETIME class. This class allows the use of the fsp parameter (fractional seconds parameter.) So, a column declaration should actually look like:

dateCreated = Column(DATETIME(fsp=6)) 

Thanks to those others who replied. It helped guide my investigation to ultimately stumbling over this esoteric and very confusing distinction.

Solution 2:

This seems to be an open issue with MySQLdb, not MySQL or SQLAlchemy.

http://sourceforge.net/p/mysql-python/feature-requests/24/

You can try using another MySQL driver library -- check SQLAlchemy documentation for supported options --, or you can try the monkeypatch suggested in the open issue.

Solution 3:

According to the MySQL documentation, it only supports precision up to 6 places (microseconds):

"MySQL 5.6.4 and up expands fractional seconds support for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) precision"

I don't know for sure, but specifying 9 digits might be reverting the column to a smaller default precision.

Solution 4:

In order to force using fractional seconds precision in MySQL while keeping the defaults on other engines you could modify how SQLAlchemy compiles the DateTime type for MySQL.

from sqlalchemy.ext.compiler import compiles
from sqlalchemy.types import DateTime
from sqlalchemy.dialects.mysql import DATETIME

@compiles(DateTime, "mysql")
def compile_datetime_mysql(type_, compiler, **kw):
     return "DATETIME(6)"

It is basically telling sql alchemy to compile the DateTime with a custom type only for mysql ,in our case returning DATETIME(6) where the parameter with value 6 is the fsp (fractional seconds precision [from MySQL 5.6.4])