python - Pandas:将数据写入 MySQL 时减少了毫秒数

Question

我正在尝试将带有毫秒时间戳的 DataFrame 放入 MySQL 数据库中。但是，这样做时，毫秒部分似乎被丢弃了。我创建了一个工作示例来展示正在发生的事情:

import pandas as pd
from sqlalchemy import create_engine # database connection

#Generate date_time with millisecond resolution and price column
df=pd.DataFrame({'date_time' : pd.date_range('1/1/2000 09:00:00', freq="5ms",periods=100),'price' : np.random.random_sample(100)})

#Connect with an empty MySQL database (which I simply created using CREATE DATABASE trading_db;)
disk_engine = create_engine("mysql+mysqldb://root:"+'MYPASSWORD'+"@localhost/trading_db")

#Dataframe to SQL in a Table called trading_data
df.to_sql('trading_data', disk_engine, if_exists='replace',index=False)

#When I read this back from MySQL, the milliseconds seem to dissapear
df_sql = pd.read_sql_query('SELECT *'
                   'FROM trading_data '
                   'LIMIT 20', disk_engine)

比较在 pandas 中创建的 DataFrame 与从 MySQL 加载的日期时间:

df.head()

    date_time                   price
0   2000-01-01 09:00:00         0.371986
1   2000-01-01 09:00:00.005000  0.625551
2   2000-01-01 09:00:00.010000  0.631182
3   2000-01-01 09:00:00.015000  0.625316
4   2000-01-01 09:00:00.020000  0.522437

df_sql.head()

    date_time           price
0   2000-01-01 09:00:00 0.371986
1   2000-01-01 09:00:00 0.625551
2   2000-01-01 09:00:00 0.631182
3   2000-01-01 09:00:00 0.625316
4   2000-01-01 09:00:00 0.522437

正如您可以清楚地看到毫秒数下降了。有什么办法可以更改代码以保留毫秒部分吗？

编辑:我使用的是 MySQL Workbench 6.2 和 pandas 0.14.1

Answer 1

如评论中所述，您需要 MySQL v5.6.4+ 才能支持小数秒 (docs)。
但是，作为 docs解释一下，您需要将其明确指定为 DATETIME(fsp)，其中 fsp 是小数秒精度，以便在日期时间列中启用它。

to_sql 中的默认设置是只使用 DateTime (默认的 sqlalchemy 日期时间类型)。但是，您可以使用 dtype 参数覆盖此默认值并使用 MySQL specific DATETIME指定精度的类型:

In [11]: from sqlalchemy.dialects.mysql import DATETIME

In [12]: df.to_sql('trading_data', engine, dtype={'date_time': DATETIME(fsp=6)}, if_exists='replace', index=False)

In [13]: df_sql = pd.read_sql_query('SELECT * FROM trading_data', engine)

In [14]: df_sql.head()
Out[14]:
                   date_time     price
0        2000-01-01 09:00:00  0.152087
1 2000-01-01 09:00:00.005000  0.927375
2 2000-01-01 09:00:00.010000  0.540021
3 2000-01-01 09:00:00.015000  0.499529
4 2000-01-01 09:00:00.020000  0.797420

注意:您需要 pandas 0.15.2+ 才能使用此 dtype 参数。