Not all parameters were used in the SQL statement using dataframe.to_sql

Question

I am having trouble diagnosing an error using pandas dataframe.to_sql

I first get my connection using connection = mysql.connector.connect

Then I try to insert my data into the mysql database using

df.to_sql('SData', con = connection, if_exists = 'append', chunksize = 1000)

I get the error:

Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': Not all parameters were used in the SQL statement

The SData table and the df have the same fields/names except the table has a primary key that is auto incremented so the only thing I can think of is that not all the fields match because of the primary key...

I'm also not understanding why there would be a select statement in the to_sql unless it is checking field names or something of that nature?

The data fields are:

Database:
SID Primary key
SSID int(foreign key)
FirstValue decimal
SecondValue decimal

DF:
FirstValue 
SecondValue 
SSID

While typing this I realized the dataframe is not the same order. Wouldnt think that would be an issue however will look into this

Thanks

Answer 1

The problem was I was trying to use mysql.connector as the connection(engine) and had to use sqlalchemy create_engine with mysql+pymysql.

I then had to_sql creating an index field so had to set index=False, and it worked.

Here is the new string:

import pymysql
engine = create_engine("mysql+pymysql://" + Cfg.username + ":" + Cfg.password + "@" + Cfg.host + "/" + Cfg.database)
df.to_sql('SData', con = engine, if_exists = 'append',index = False, chunksize = 1000)

Thanks