[SOLVED] Python 'timestamp' cannot be converted to a MySQL type

Python 'timestamp' cannot be converted to a MySQL type

I have some data in excel that I want to insert into a MySQL table. I read the excel into a Dataframe. The data includes a date column with dates which I convert to datetime to match the MySQL table setup (the date column is of type datetime). When I try to insert the data into the table I get the error:

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

When I print out the date after converting it I do get it in the 'YYYY-MM-DD HH:MM:SS' format that seems to be the format MySQL requires?

2011-05-10 00:00:00

Probably doing something simple wrong, but can't get this to work. Any suggestions?

The SQL of the table:

CREATE TABLE `Weight` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `Date` datetime NOT NULL,
 `Weight` float NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_c

My Python script, a bit extended to incorporate some dummy data:

import pandas as pd
import mysql.connector
import numpy as np

#import csv in dataframe
#df = pd.read_excel('/excel.xlsx')
#dataframe for this question on stackoverflow
startDate = '2011-05-03'
dateList = pd.date_range(startDate, periods=1000).tolist()
df = pd.DataFrame({'Day': dateList,
                'Weight': np.random.normal(loc=68, scale=10, size=(1000,))
              })
df['Day'] = pd.to_datetime(df['Day'],errors='raise')

mydb = mysql.connector.connect(
    host="host",
    user="user",
    port="port",
    passwd="Password",
    database="database_name"
)

mycursor = mydb.cursor()
sql = "INSERT INTO Weight (Date, Weight) values (%s, %s)"

for index, row in df.iterrows() :
    val = (row['Day'], row['Weight'])
    mycursor.execute(sql, val)

To be clear, it fails on the execute(sql, val) part

Solution

Using df.to_sql as suggested by @DeepSpace solved the problem.