I have some data that looks like this
Open High Low Close Volume Instrument
Date
2018-09-02 07:00:00-04:00 7269.0 7274.0 7213.0 7240.5 2321665 XBTZ18
2018-09-02 08:00:00-04:00 7240.5 7270.0 7240.5 7259.0 781280 XBTZ18
2018-09-02 09:00:00-04:00 7259.0 7259.5 7161.0 7194.5 2959099 XBTZ18
2018-09-02 10:00:00-04:00 7194.5 7238.0 7189.5 7232.0 1799117 XBTZ18
2018-09-02 11:00:00-04:00 7232.0 7245.0 7231.0 7235.0 237230 XBTZ18
Where the datatypes are:
Open float64
High float64
Low float64
Close float64
Volume int64
Instrument object
dtype: object
Now, I am just trying to write this pandas dataframe to a SQL data on Amazon AWS.
My table is structured as such
'Date' -- > DATETIME (Primary Key, Not Null, Unique)
'Open' -- > DECIMAL
'High' -- > Decimal
'Low' -- > Decimal
'Close' -- > Decimal
'Volume' -- > INT
'INSTRUMENT' -- > VarChar(45) (Primary Key, Not Null)
Now I would like to write my dataframe to SQL database:
df_for_db = data_dump[['Date', 'Open', 'High', 'Low', 'Close', 'Volume', 'Instrument']]
#df_for_db['Date'] = df['Date'].astype(pd.Timestamp)
#write the dataframe
df_for_db.to_sql(name='hourlyData', con=engine, if_exists = 'replace', index=False)
but I get this error.
TypeError: Cannot cast DatetimeIndex to dtype datetime64[us]
The datatypes as seen above should be compatible, right? What am I missing? THank you
