Date format error while inserting values from CSV file into database using python

Question

I have a CSV file that contains the column Registrationdate. It is in format month/day/year. I am trying to read this CSV and insert the data into database. But it shows me a date format error. I know SQL takes data format as year-day-month. But I need a way to convert the whole column in this format before inserting it into the database. Please show me a way to convert the format from mm/dd/y to y-mm-dd.

import csv

with open('STUDENTDATA.csv',newline='',encoding='utf-8') as csvfile:
reader = csv.reader(csvfile)
next(reader, None)

for row in reader:
    sql = "INSERT INTO STUDENTDETAILS (studentid, firstname, lastname, registrationdate, class, section) VALUES ('%s','%s','%s','%s','%s','%s')" % (row[0],row[1],row[2],row[3],row[4],row[5])
    
    try:
        cursor = mydb.cursor()
        cursor.execute(sql)
        print("Value inserted!")
        mydb.commit()
    except Exception as e:
        print(str(e))



1292 (22007): Incorrect date value: '1/13/2021' for column 'REGISTRATIONDATE' 
at row 1
1292 (22007): Incorrect date value: '1/13/2021' for column 'REGISTRATIONDATE' 
at row 1
1292 (22007): Incorrect date value: '1/13/2021' for column 'REGISTRATIONDATE' 
at row 1
1292 (22007): Incorrect date value: '1/13/2021' for column 'REGISTRATIONDATE' 
at row 1
1292 (22007): Incorrect date value: '1/13/2021' for column 'REGISTRATIONDATE' 
at row 1
1292 (22007): Incorrect date value: '1/13/2021' for column 'REGISTRATIONDATE' 
at row 1
1292 (22007): Incorrect date value: '1/13/2021' for column 'REGISTRATIONDATE' 
at row 1
1292 (22007): Incorrect date value: '1/13/2021' for column 'REGISTRATIONDATE' 
at row 1
1292 (22007): Incorrect date value: '1/13/2021' for column 'REGISTRATIONDATE' 
at row 1

Answer 1

The error in your date format. Your date format is in "D/MM/YYYY" where it should be in the form of "YYYY-MM-DD".

*it would be more clear if you provide some data from your dataset.

Else try converting the date format and do let me know if it work or not

#EDIT

Here is how we can convert the date into "YYYY-MM-DD"

data=[]                          #contains all the data from your dataset
formated_date=[]                 #contains formated date in the form of "YYYY-MM-DD"

with open('STUDENTDATA.csv',newline='',encoding='utf-8') as csvfile:     #reading the csv file
    reader = csv.reader(csvfile)
    next(reader, None)
    for row in reader:
        data.append(row)

for i in data:
    a=i[3].split('/')
    newform=f'{a[2]}-{a[0]}-{a[1]}'         #changing the date format
    formated_date.append(newform)           #and saving it to another list

What we have done here we collected all the data from the dataset into a list ie. 'data' and we parsed out the that part which contains date and splitted it by '/' and then we appended a new list ie.'formated_date' which contains the formated date in the date format accepted by sql.

Now we need to connect the code with sql and save it in the database.

#HERE IS THE FINAL CODE

import csv
import mysql.connector

mydb=mysql.connector.connect(host='localhost',user='*******',passwd='*****',database='STUDENTDATA')
cursor=mydb.cursor()

data=[]                          #contains all the data from your dataset
formated_date=[]                 #contains formated date in the form of "YYYY-MM-DD"

with open('STUDENTDATA.csv',newline='',encoding='utf-8') as csvfile:     #reading the csv file
    reader = csv.reader(csvfile)
    next(reader, None)
    for row in reader:
        data.append(row)

for i in data:
    a=i[3].split('/')
    newform=f'{a[2]}-{a[0]}-{a[1]}'         #changing the dateformat
    formated_date.append(newform)           #and saving it to another list

for i in range(len(data)):
    sql="INSERT INTO STUDENTDETAILS (studentid, firstname, lastname, registrationdate, class, section) VALUES ('%s','%s','%s','%s','%s','%s')" % (data[i][0],data[i][1],data[i][2],formated_date[i],data[i][4],data[i][5])
    try:
        cursor = mydb.cursor()             #saving the file into the database
        cursor.execute(sql)
        print("Value inserted!")
        mydb.commit()
    except Exception as e:
        print(str(e)) 

Hope it helped. And do let me know it any questions left.