CODEWITHSUNDEEP
pythonmysqlpython-3.xcsv
pythonn00b
pythonn00b

Reputation: 57

Importing data from CSV to MySQL using python

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!

Upvotes: 0

Views: 207

Answers (2)

AKX
AKX

Reputation: 169397

  • 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()

Upvotes: 1

Ayush
Ayush

Reputation: 623

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

Upvotes: 0

Related Questions