Importing data from CSV to MySQL using python

Question

I am currently working on a schoolproject, and im trying to import data from a CSV file to MySQL using python. This is my code so far:

import mysql.connector
import csv

mydb = mysql.connector.connect(host='127.0.0.1', user='root', password='abc123!', db='jd_university')

cursor = mydb.cursor()

with open('C:/Users/xxxxxx/Downloads/Students.csv') as csvfile:
    reader = csv.DictReader(csvfile, delimiter=',')
    for row in reader:
        cursor.execute('INSERT INTO Student (First_Name, Last_Name, DOB, Username, Password, Phone_nr,'
                       'Email, StreetName_nr, ZIP) '
                       'VALUES("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s")',
                       row)

mydb.commit()
cursor.close()

When i run this, i get this error: "mysql.connector.errors.DataError: 1292 (22007): Incorrect date value: '%s' for column 'DOB' at row 1"

The date format used in the CSV file are yyyy-mm-dd

Any tips on this would help greatly!

Can you please show us the first three rows of sample data? Does it include a header row containing the column names? — Ben
– Ben, Commented Apr 23, 2020 at 15:12
You should log some sample data in you question like print(row) for debug purpose.When finish remove it is ok. — user4985526
– user4985526, Commented Apr 24, 2020 at 9:36

AKX · Accepted Answer · 2020-04-23 15:14:29Z

1

You don't need to quote the %s placeholders.
Since you're using DictReader, you will need to name the columns in your row expression (or not use DictReader and hope for the correct order, which I'd not do).

Try this:

import mysql.connector
import csv

mydb = mysql.connector.connect(
    host="127.0.0.1", user="root", password="abc123!", db="jd_university"
)

cursor = mydb.cursor()

with open("C:/Users/xxxxxx/Downloads/Students.csv") as csvfile:
    reader = csv.DictReader(csvfile, delimiter=",")
    for row in reader:
        values = [
            row["First_Name"],
            row["Last_Name"],
            row["DOB"],
            row["Username"],
            row["Password"],
            row["Phone_nr"],
            row["Email"],
            row["StreetName_nr"],
            row["ZIP"],
        ]
        cursor.execute(
            "INSERT INTO Student (First_Name, Last_Name, DOB, Username, Password, Phone_nr,"
            "Email, StreetName_nr, ZIP) "
            "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)",
            values,
        )

mydb.commit()
cursor.close()

answered Apr 23, 2020 at 15:14

AKX

171k16 gold badges146 silver badges228 bronze badges

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

3 Comments

pythonn00b Over a year ago

Thanks! This seems to be working, the error i got earlier is now gone. However, if the ZIP column in the database is an INT value, how would i fix that?

AKX Over a year ago

Replace that row["ZIP"] with int(row["ZIP"]) so you're doing the conversion before you pass the values to MySQL.

pythonn00b Over a year ago

Thanks so much! Data is now imported to MySQL

Ayush · Accepted Answer · 2020-04-23 15:13:54Z

0

Validate the datatype for DOB field in your data file and database column. Could be a data issue or table definition issue.

answered Apr 23, 2020 at 15:13

Ayush

6532 gold badges7 silver badges21 bronze badges

Collectives™ on Stack Overflow

Importing data from CSV to MySQL using python

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related