What is wrong with this SQL statement in Python?

Question

I am using Python and a MySQL database and am attempting to itterate through rows in a CSV file and insert them in my database. I have the following:

import mysql.connector
import pandas as pd

mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="root",
    database="mydb")

cursor = mydb.cursor()
cursor.execute("SET FOREIGN_KEY_CHECKS=0")
csv_data = pd.read_csv("file path")
sql = "INSERT INTO table (ID, SecondID, StartDate, EndDate) VALUES (%s, %s, %s, %s)"
for index, row in csv_data.iterrows():
    cursor.execute(sql, row)
cursor.execute("SET FOREIGN_KEY_CHECKS=1")
mydb.commit()
cursor.close()
mydb.close()

I can't see what's wrong with the SQL. Getting the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%s, %s, %s, %s)'

NOTE - The rest of the code seems to work okay and the SQL works fine if I insert specific values but when I try to use the %s construct it fails yet other responses I have seen appear to recommend this as the correct syntax.

Please help- what am I doing wrong?

anyway, why there are %s without any variables ? eg "%s"%(string) — user8560167
– user8560167, Commented Oct 4, 2020 at 9:08
If your table really named table, the table name must be quoted by backtics — Slava Rozhnev
– Slava Rozhnev, Commented Oct 4, 2020 at 9:14

user14136989 · Accepted Answer · 2020-10-04 09:15:12Z

1

I think you better use pandas to_sql function.
I'm not sure whether mysql.connector works so i'll use sqlalchemy.
It looks like that:

ENGINE = sqlalchemy.create_engine('mysql+pymysql://root:root@localhost:3306/mydb')
with ENGINE.connect() as connection:
    ENGINE.execute("SET FOREIGN_KEY_CHECKS=0")
    csv_data.to_sql('table_name', connection, if_exists='append', index=False)
    ENGINE.execute("SET FOREIGN_KEY_CHECKS=1")

answered Oct 4, 2020 at 9:15

user14136989

763 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

David A. · Accepted Answer · 2020-10-04 09:17:13Z

0

Look like that the problem is that you are invoking the query without scape the value.

The execute function its getting a class and not an array

for index, row in csv_data.iterrows():
    cursor.execute(sql, row)

You should generate an array with all the values and then invoke the query.

Something like:

for index, row in csv_data.iterrows():
    params = map(lambda x : x.value, row)
    cursor.execute(sql,params)

Be carefull, the size of the array has to be the same size as the values params. In this case 4

answered Oct 4, 2020 at 9:17

David A.

3411 silver badge6 bronze badges

Comments

graduallearner2021 · Accepted Answer · 2020-10-04 09:36:22Z

0

Thank you that was very helpful, I made one minor change and it works perfectly now. Here is the final solution I used:

import pandas as pd
import sqlalchemy

engine = sqlalchemy.create_engine('mysql+pymysql://root:root@localhost:3306/mydb')

csv_data = pd.read_csv("file path")
engine.execute("SET FOREIGN_KEY_CHECKS=0")

with engine.connect() as connection:
    csv_data.to_sql('table', connection, if_exists='append', index=False)

engine.execute("SET FOREIGN_KEY_CHECKS=1")

answered Oct 4, 2020 at 9:36

graduallearner2021

153 bronze badges

Collectives™ on Stack Overflow

What is wrong with this SQL statement in Python?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related