Inserting csv into MySQL database with python library mysql.connector

Question

I have trouble with insert of csv data into MySQL tabel with mysql.connector .

The code I use looks like this :

import mysql.connector
import csv

andreport = 'testowa.csv'
cnx = mysql.connector.connect(
    user='xxxxx',
    password='xxxxx',
    host='xxxxxx',
    database='xxxxx')

cursor = cnx.cursor()

with open(andreport, 'r') as csv_data:
    for row in csv_data:
        cursor.execute(
            "INSERT INTO flex(date, Store, Vendor, Shelf)"
            "VALUES({},{},{},{})", row)
cnx.commit()
cursor.close()
cnx.close()
print("Done")

The error I get :

C:\Users\Iw4n\PycharmProjects\Learning\venv\Scripts\python.exe C:/Users/Iw4n/PycharmProjects/Learning/Orange_android_MySQL_insertion.py
Traceback (most recent call last):
  File "C:/Users/Iw4n/PycharmProjects/Learning/Orange_android_MySQL_insertion.py", line 15, in <module>
    cursor.execute(
  File "C:\Users\Iw4n\PycharmProjects\Learning\venv\lib\site-packages\mysql\connector\cursor.py", line 551, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\Users\Iw4n\PycharmProjects\Learning\venv\lib\site-packages\mysql\connector\connection.py", line 490, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:\Users\Iw4n\PycharmProjects\Learning\venv\lib\site-packages\mysql\connector\connection.py", line 395, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1064 (42000): 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 '},{},{},{})' at line 1

When i wrapped {} into '' , as many rows as were in csv been inserted into datbase as {},{}

same story goes for %s if I use it , i got the same error as above, when it's wrapped in '' , %s is insetred into database. I also found information to add f in fron of "INSERT~ but it did not help.

Can anyone give me some suggestion on how to overcome this and correctly insert data to MySQL ?

Final code that is working as intended :

import mysql.connector
import csv

andreport = 'testowa.csv'
cnx = mysql.connector.connect(
    user='xxxxx',
    password='xxxxx',
    host='xxxxx',
    database='xxxxx')

cursor = cnx.cursor()

with open(andreport, mode='r') as csv_data:
    reader = csv.reader(csv_data, delimiter=';')
    csv_data_list = list(reader)
    for row in csv_data_list:
        cursor.execute("""
                   INSERT INTO flex(
                   date, Agency, MediaSource, Campaign)
                   VALUES(%s,%s,%s,%s)""",
                    (row[0], row[1], row[2], row[3]))
cnx.commit()
cursor.close()
cnx.close()
print("Done")

Adriaan · Accepted Answer · 2019-12-08 10:40:46Z

3

I'm guessing that seems the problem is that you passed one argument (row) instead of four. So try this:

  cursor.execute("""
            INSERT INTO flex(date, Store, Vendor, Shelf)
            VALUES(%s,%s,%s,%s)""",(row[0], row[1], row[2], row[3], ))

edited Dec 8, 2019 at 10:40

Adriaan

18.2k7 gold badges47 silver badges88 bronze badges

answered Dec 7, 2019 at 20:41

Mahrez BenHamad

2,6641 gold badge22 silver badges25 bronze badges

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

7 Comments

Iw4n Over a year ago

Now it throws another error : cnx.commit() SyntaxError: invalid syntax. when I comment it out, it throws same syntaxError for cursor.close() , then if this one is commented out too, cnx.close() gets the error

Mahrez BenHamad Over a year ago

I missed closing the parentheses :/ Try it now

Iw4n Over a year ago

cursor.execute(""" TypeError: not all arguments converted during string formatting

Iw4n Over a year ago

Aight , we are almost there. It processed further but the date format is YYYY-MM-DD and this got splitted as follow : date got value 2, Store got value 0 , Vendor got value 1 and Self got value 9. Got an idea why this happened ?

Iw4n Over a year ago

Fields are terminated by ";"

|

Sam Gaudet · Accepted Answer · 2019-12-07 21:33:31Z

3

Looking at the documentation for MySQLCursor.excute() method, it seems like adding some %s as the parameters in your insert statement might fix this?

import mysql.connector
import csv

andreport = 'testowa.csv'
cnx = mysql.connector.connect(
    user='xxxxx',
    password='xxxxx',
    host='xxxxxx',
    database='xxxxx')

cursor = cnx.cursor()

insert_statement = (
    "INSERT INTO flex(date, Store, Vendor, Shelf)"
    "VALUES (%s, %s, %s, %s)"
)

with open(andreport, mode='r') as csv_data:
    reader = csv.reader(csv_data, delimiter=';')
    csv_data_list = list(reader)
    for row in csv_data_list:
        cursor.execute(insert_statement, row)
cnx.commit()
cursor.close()
cnx.close()
print("Done")

Let me know if this gets you anywhere, or if you see a new error!

Edit: updated CSV reading to convert to a list.

edited Dec 7, 2019 at 21:33

answered Dec 7, 2019 at 21:11

Sam Gaudet

615 bronze badges

7 Comments

Iw4n Over a year ago

I have tried the solution wiht %s and it caused same error. Here are the details mysql.connector.errors.ProgrammingError: 1064 (42000): 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)' at line 1

Sam Gaudet Over a year ago

Aha! So I think you need some combination of adding the %ss and also adjusting the way you are reading the data from your CSV--updating my answer!

Iw4n Over a year ago

The solution suggested by Mahrez BenHamad below, we are almost there. Now for unknown reason the csv gets inserted but incorrectly. The fields are terminated by ";" but first field is date in shape " YYYY-MM-DD" but its get inserted as column date gets 2 , column Store 0, Vendor 1 and Shelf 9"

Sam Gaudet Over a year ago

Yeah, so that seems like it thinks of row as one string, so therefore if the string is like '2019 . . .', then row[0] will be '2', row[1] will be '0', etc.

Sam Gaudet Over a year ago

I think the main problem here is how you are passing data to the execute() method

|

Collectives™ on Stack Overflow

Inserting csv into MySQL database with python library mysql.connector

2 Answers 2

7 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related