python - 带有时间戳的 pandas 数据框插入到 mysql 的问题

Question

将 pandas 数据帧插入 MySQL 数据库时出现问题。列出了示例数据和使用的代码以及随后出现的错误。如何使用 pandas dataframe 向 mysql 插入数据？

使用的包

Pandas - 0.22.0
sqlalchemy - 1.2.1

使用的数据框:

Out[135]: 
  P_ID  S_ID            Action                        Timestamp  \
0     Harold           1  Extensions 2017-11-07 03:17:27.342295+00:00   
1     Harold           1  Extensions 2017-11-07 03:17:27.447234+00:00   
2     Harold           1  Extensions 2017-11-07 03:17:27.552406+00:00   
3     Harold           1  Extensions 2017-11-07 03:17:27.657676+00:00   
4     Harold           1  Extensions 2017-11-07 03:17:27.762737+00:00   

       Value  
0 -0.096083  
1 -0.003894  
2 -0.004779  
3  0.131210  
4  0.161990  

数据类型:

P_ID                 object
S_ID                 int64
Action               object
Timestamp            datetime64[ns, UTC]
Value                float64
dtype: object

使用了下面的代码片段，后面有错误

此外 pd.to_datetime() 似乎没有任何效果。

使用的代码(一)无索引

engine = create_engine('mysql+pymysql://xxxx:3306/xxxx')
test.to_sql(name='table1', con=engine, if_exists = 'append',index=False)
conn.close()

错误:

ValueError: Cannot cast DatetimeIndex to dtype datetime64[us]

使用的代码 (2) 带有索引的时间戳列

engine = create_engine('mysql+pymysql://xxxx:3306/xxxx')
test.to_sql(name='table1', con=engine, if_exists = 'append',index=True,
                   index_label='Timestamp',
                   dtype={'Timestamp':typeTIMESTAMP(timezone=True)})
conn.close()

错误:

ValueError: duplicate name in index/columns: cannot insert Timestamp, already exists

Answer 1

如果在写入数据库之前将 Timestamp 的数据类型转换为 datetime64[us]，它应该可以工作:

test['Timestamp'] = test['Timestamp'].values.astype('datetime64[us]')