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

Answer 1

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