pandas :将数据写入MySQL时减少了毫秒 [英] Pandas: milliseconds dropped when writing data to MySQL

问题描述

我正在尝试将具有毫秒时间戳的DataFrame放入MySQL数据库中.但是，这样做时，毫秒部分似乎被删除了.我创建了一个工作示例来说明发生了什么:

I'm trying to get a DataFrame with millisecond timestamps into a MySQL database. However, when doing this, the millisecond part seems to be dropped. I've created a working example to show what's going on:

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加载的日期时间进行比较:

Compare the date-times of the DataFrame created in pandas with the ones loaded from 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

您可以清楚地看到毫秒数被丢弃.有什么办法可以更改代码以保留毫秒部分?

As you can clearly see the milliseconds are dropped. Is there any way I can alter the code to keep the millisecond part?

我正在使用MySQL Workbench 6.2和pandas 0.14.1

I'm using MySQL Workbench 6.2 and pandas 0.14.1

推荐答案

如注释中所述，您需要MySQL v5.6.4 +才能获得分数支持( docs 所述，您需要指定明确指定为DATETIME(fsp)，其中fsp是小数秒精度，以便在datetime列中启用该精度.

As noted in the comments, you need MySQL v5.6.4+ for fractional seconds support (docs).
But, as the docs explain, you need to specify this explicitly as DATETIME(fsp), where fsp is the fractional seconds precision, to enable this in the datetime column.

to_sql中的默认设置是仅使用 DateTime (默认的sqlalchemy datetime类型).但是，您可以使用dtype参数覆盖此默认值，并使用指定精度的特定于MySQL的DATETIME 类型:

The default in to_sql is to just use DateTime (the default sqlalchemy datetime type). You can however override this default with the dtype argument and use the MySQL specific DATETIME type specifying the precision:

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

注意:该dtype参数需要熊猫0.15.2 +.

Note: you need pandas 0.15.2+ for this dtype argument.

这篇关于 pandas :将数据写入MySQL时减少了毫秒的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！