Python to mysql 'Timestamp' object has no attribute 'translate'

Question

I'm trying to load a pandas dataframe to a mysql table using Sqlalchemy.

I connect using; engine = create_engine("mysql+pymysql://user:password@ip:port/db")

I am then simply running;

df.to_sql(con=engine, name='Table', if_exists='append', index=False, chunksize=10000);

I keep getting the error

AttributeError: 'Timestamp' object has no attribute 'translate'

This worked fine when I used older versions and did this via pymysql, not sqlalchemy

I can't find anything online to help, any ideas.

thanks,

Answer 1

I converted my timestamp object into ts.to_pydatetime() before passing it to the cursor and that solved the attribute error.

See this answer for different ways of converting timestamps: Converting between datetime, Timestamp and datetime64

Answer 2

Check the datatypes of your data frame using df.dtypes and make sure your columns are the correct datatype. If you have a column that you expect to be timestamps, make sure it's datatype is datetime64[ns]. If it is object (or even something else), you can probably convert it using the pandas.to_datetime() function.

I was just having this same issue. In my case there was a column I was trying to write that contained timestamps but it's datatype was object. After conversion with df[colname] = pd.to_datetime(df[colname]) I was able to successfully write it using the pymysql driver and sqlalchemy.