CODEWITHSUNDEEP
pythonmysqlpython-3.xpandassqlalchemy
Sundar N
Sundar N

Reputation: 326

issues with pandas dataframe insert to mysql with timestamp

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

Upvotes: 2

Views: 4820

Answers (4)

Abdulrahman Bres
Abdulrahman Bres

Reputation: 2923

You can use pandas.Series.dt.tz_convert to remove timezone

df['Timestamp'] = df['Timestamp'].dt.tz_convert(None)

If pandas failed to detect datetimelike values, add pandas.to_datetime

You can skip passing format & dayfirst if pandas is able to infer dates format in your data

df['Timestamp'] = pd.to_datetime(df['Timestamp'], format='mixed', dayfirst=False).dt.tz_convert(None)

Upvotes: 0

Denis
Denis

Reputation: 21

My solution was to convert Pandas timestamp to float and then to datetime but outside of DataFrame and right before inserting to database via:

datetime.fromtimestamp(value.timestamp())

It is very strange that MySQL cannot accept pandas timestamp by default...

Upvotes: 0

Sundar N
Sundar N

Reputation: 326

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

Upvotes: 0

Ido S
Ido S

Reputation: 1352

If you convert the data type of Timestamp into datetime64[us] before writing to the DB it should work:

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

Upvotes: 1

Related Questions