timestamp object cannot be converted to MYSQL type

Question

I have a dataframe with string dates I convert them to datetime time using pd.to_datetime()

then when I create insedrt query and make cursor.execute() it returns this error to me

"mysql.connector.errors.ProgrammingError: Failed processing format-parameters; Python 'timestamp' cannot be converted to a MySQL type "

df['Date'] = pd.to_datetime(df['Date'])

for i,row in df.iterrows():

                    sql="INSERT INTO db.datestable (Date,Name) VALUES (%s,%s);"
                    
my_cursor.execute(sql, tuple(row))

Does this answer your question? [Python 'timestamp' cannot be converted to a MySQL type](https://stackoverflow.com/questions/53062493/python-timestamp-cannot-be-converted-to-a-mysql-type) — Panagiotis Kanavos, Aug 08 '23 at 08:15
The error complains about the Pandas [Timestamp type](https://pandas.pydata.org/docs/reference/api/pandas.Timestamp.html). You can use [to_pydatetime](https://pandas.pydata.org/docs/reference/api/pandas.Timestamp.to_pydatetime.html) to convert Timestamp values to Python datetime, but using `to_sql` with `if_exists='append` is almost always better than manually executing 100s of INSERTs — Panagiotis Kanavos, Aug 08 '23 at 08:22
I'm not using df.to_sql because sometimes I need to check duplicates to udapted changed records in the table — Carol, Aug 08 '23 at 08:44
Your code isn't checking duplicates though. The duplicate shows how to do it and I posted a link to `to_pydatetime`. Instead of `tuple(row)` use the actual fields, eg `tuple(row['Date'].to_pydatetime(), row['Name'])` — Panagiotis Kanavos, Aug 08 '23 at 08:53
It's possible to make `to_sql` update existing rows, as [this answer shows](https://stackoverflow.com/a/60891952/134204). A faster and more scalable solution would be to insert the data into a staging table and then upsert to the target table. This way, instead of eg 500 individual UPDATEs you'd have a batched insert to the staging table, 1 INSERT with the new data and 1 UPDATE over the existing. Or one INSERT that updates duplicates — Panagiotis Kanavos, Aug 08 '23 at 08:54

Arun Pratap Singh · Answer 1 · 2023-08-08T09:01:29.807

-2

pd.to_datetime() function is converting dates to Python datetime objects, which cannot be directly inserted into a MySQL database. Convert this datetime to mysql compatible date string before insertion.

df['Date'] = pd.to_datetime(df['Date'])

for i,row in df.iterrows():
   # Convert the datetime object to a MySQL-compatible date time string
      date_str = row['Date'].strftime('%Y-%m-%d %H:%M:%S')
    sql = "INSERT INTO db.datestable (Date,Name) VALUES (%s,%s);"
                    
my_cursor.execute(sql, (date_str, row['Name']))

edited Aug 08 '23 at 09:01

answered Aug 08 '23 at 08:07

Arun Pratap Singh

40
3

That's simply wrong. MySQL (the database) *does* support strongly typed parameters. MySQL Connector does support `datetime`. So does SQLAlchemy, which Pandas uses to store dataframes to databases – Panagiotis Kanavos Aug 08 '23 at 08:12
The error complains about [Pandas' Timestamp type](https://pandas.pydata.org/docs/reference/api/pandas.Timestamp.html), not the use of date parameters – Panagiotis Kanavos Aug 08 '23 at 08:19

timestamp object cannot be converted to MYSQL type

1 Answers1