10

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,

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
Grant McKinnon
  • 445
  • 3
  • 7
  • 17

5 Answers5

4

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

H. Tao
  • 237
  • 2
  • 9
4

I encountered the same issue when I tried to insert a very large dataframe(df) into MySQL which has a column(df['date_']) with 'datetime64[ns]' dtype.

The date_ column is initially converted to datetime64[ns] using pd.to_datetime(df[date_]). I found that the datatype of df['date_'] is an object and not datetime64[ns]. So, I took a small chunk of data from the table to debug & found that the dtype was datetime64[ns] which is correct & even the data got inserted into the database. Hence, there were definitely some rows that failed to convert into datetime64[ns] which I believe is the reason behind the error.

However, casting this column as string(shown below) solved the issue for me.

df['date_'] = df['date_'].astype(str)
Ravindra Choochukonda
  • 41
  • 2
3

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.

jonchar
  • 6,183
  • 1
  • 14
  • 19
0

I was using a parametized insert query when I encountered this issue. Converting to pd.to_datetime didnt solve the issue for me, but casting the pandas Timestamp object as a str worked well.

Bjc
  • 93
  • 6
0

I encountered the same problem. Found the best solution was to just transform all timestamp-like columns as string.

df = df.apply(lambda col: col.astype(str) if col.dtype == 'datetime64[ns]' else col)
RDRR
  • 860
  • 13
  • 16