Pandas: Milliseconds Dropped When Writing Data To 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
Solution 1:
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.
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
Note: you need pandas 0.15.2+ for this dtype
argument.
Post a Comment for "Pandas: Milliseconds Dropped When Writing Data To MySQL"