CODEWITHSUNDEEP
pythonmysqlpandas
graduallearner2021
graduallearner2021

Reputation: 15

What is wrong with this SQL statement in Python?

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?

Upvotes: 1

Views: 269

Answers (3)

graduallearner2021
graduallearner2021

Reputation: 15

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

Upvotes: 0

David A.
David A.

Reputation: 341

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

Upvotes: 0

user14136989
user14136989

Reputation: 76

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

Upvotes: 1

Related Questions