issues with pandas dataframe insert to mysql with timestamp

Question

Having issues with inserting pandas dataframe to MySQL db.Have listed the sample data and code used along with errors that follow. How to insert data with pandas dataframe to mysql?

Packages used

Pandas - 0.22.0
sqlalchemy - 1.2.1

Dataframe used:

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  

dtypes :

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

The following code snippets were used with the errors followed

Also pd.to_datetime() did not seem to have any effect.

Code used (1) without index

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

Error :

ValueError: Cannot cast DatetimeIndex to dtype datetime64[us]

Code used (2) with timestamp column indexed

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()

Error :

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

Answer 1

Somehow this API with MySQL does not seems to accept UTC timestamps.

The solutions that worked for me :

test['Timestamp']=test['Timestamp'].apply(lambda x:datetime.replace(x,tzinfo=None))