Python code for inserting a CSV file into MySQL table not working as expected

Question

I am new to Python and having some basic problems. I am trying to insert into an existing MySQL table (holding) from a csv file (test.csv), skip the header row and then return the number of rows inserted. Both tables have identical column headings.

The code below is not inserting the data from the csv file, but just inserting the values (which are my column heading, I thought I needed to declare these in the values), so this method is wrong as it only seems to add the values manually and not inert data from the csv file. Can someone tell me what I'm doing wrong please?

This is the code:

`import csv    

try:
    mydb = mysql.connector.connect(
                host=localhost, port=5306,
                user="XX",
                passwd="XX",
                database="python_test",
                auth_plugin='mysql_native_password'
            )


     # This skips the first row of the CSV file.

    with open((r'c:\TELS\\Uploaded\test.csv')) as f:
        reader = csv.reader(f)
        #next(reader) # skip header
        data = [r for r in reader]
        data.pop(0) # remove header


    mycur = mydb.cursor()


    query = "INSERT INTO hold (`Name`, `Address`, `Age`, `DOB) VALUES (%s, %s, %s, %S)"

    values = (`Name`, `Address`, `Age`, `DOB`)


    mycur.execute(query, values)

    mydb.commit()


    print(mycur.rowcount, "records inserted.")
    #close the connection to the database.

    mycur.close()`

Willyzekid · Accepted Answer · 2020-05-16 23:46:28Z

1

The content of your values variable is what will be inserted so you need to map it to your data variable.

You could either do it one row at a time...

query = "INSERT INTO hold (`Name`, `Address`, `Age`, `DOB`) VALUES (%s, %s, %s, %s)"
# also notice how i converted your last %S into %s

for row in data:
    mycur.execute(query, row)
    mydb.commit()

mycur.close()

Or use the executemany() function :

query = "INSERT INTO hold (`Name`, `Address`, `Age`, `DOB`) VALUES (%s, %s, %s, %s)"
mycur.executemany(query, data)
mydb.commit()
print(mycur.rowcount, "records inserted.")

mycur.close()

edited May 16, 2020 at 23:46

answered May 16, 2020 at 23:39

Willyzekid

1161 silver badge3 bronze badges

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

1 Comment

user8763797 Over a year ago

Thank you so much, I obviously got a bit confused about the values. Both work a treat!

Collectives™ on Stack Overflow

Python code for inserting a CSV file into MySQL table not working as expected

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related