SqlAlchemy的mysql毫秒或微秒精度 [英] SqlAlchemy mysql millisecond or microsecond precision

问题描述

我一直在冒险尝试在数据库中正确使用分数时间分辨率。为了创建日期对象，我使用python方法 datetime.now（）然后我将这些对象存储在映射到SqlAlchemy库中的 COLUMN（DATETIME（9））的字段中。最初，我收到一个错误，我的数据被截断。这是因为我使用的是mysql 5.5。我已经更新到5.6.19，不再得到数据截断错误。

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 objects. I then store these objects in a field which is mapped to a COLUMN(DATETIME(9)) which is from SqlAlchemy's library. Originally, I was getting an error that my data was being truncated. This is because I was using mysql 5.5. I have since updated to 5.6.19 and no longer get the data truncated error.

但是，数据库仍然不包含小数时间条目。例如，这里是datetime.now（）对象被实例化时的值：

However, the database still does not actually contain fractional time entries. For example, here is the value from when the datetime.now() object is instantiated:

2015-04-17 16:31:12.804444

以上是我期望的。存储器中的对象具有微秒分辨率。现在，将它保存到mysql数据库后，如果我打开mysql命令行客户端并使用select语句返回该行，我将看到以下值：

The above is exactly what I would expect. The object in memory has microsecond resolution. 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 row using a select statement:

2015-04-17 16:31:13

显然，正在四舍五入到最接近的秒。这是坏的，我不知道是什么导致它！

Obviously, the value is being rounded to the nearest second. This is bad, and I have no idea what is causing it!

如果它是相关的，我使用mysql-connector-python == 2.0.3

In case it is relevant, I'm using mysql-connector-python==2.0.3

更新：
我也尝试使用 COLUMN（DATETIME（6）），但获得相同的行为

UPDATE: I also tried using COLUMN(DATETIME(6)), but got the same behavior.

如果信息相关，我在下面包含模型：

I am below including the model, in case that 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()

更新：
佩德罗的建议不是问题虽然这绝对有助于我取得进展，但是非常感谢。我尝试在sql连接器中遍历代码，直到我找到了mysql的insert语句。该声明确实包含分数时间值。但是，执行时，值将舍入。当我在桌面上做了​​一个描述时，我注意到datetime类型只是这样， datetime 当它应该是 datetime（6）。

我使用SA模型生成数据库本身，它显式声明了 Column（DATETIME（6）） ，和 Base.metadata.create_all（self.db，checkfirst = True），所以我不明白为什么（6）不是在实际的表中结束结构体。我想我很快就会想出来，当我这样做的时候我会发布一个更新。

I'm generating the database itself 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 the (6) isn't ending up in the actual table structure. I think I'll figure it out shortly though, and I'll post an update when I do.

更新：
DATETIME的构造函数不接受场长度规格。它只需要时区的争论。我不清楚如何指定datetime字段的长度，因为对于像varchar这样的类型，只能将其传递给构造函数。潜水继续。

UPDATE: The constructor to DATETIME does not accept field length specification. It only takes an argument for timezones. It isn't clear to me how to specify the length of a datetime field, since for types like varchar one would just pass it in to the constructor. The dive continues.

推荐答案

我遇到的问题是库存SqlAlchemy DATETIME类不符合mysql要求的传递一个（6）的值进入构造函数，以便表示分数时间值。相反，需要使用 sqlalchemy.dialects.mysql.DATETIME 类。该类允许使用 fsp 参数（小数秒参数）。因此，列声明实际上应如下所示：

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.

这篇关于SqlAlchemy的mysql毫秒或微秒精度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！