When I insert a date from a file that has it formatted "dd/mm/yy" into my database table with the date formatted "yy/mm/dd" the date is wrong:
Instead of getting 2019:04:11 I get 2011:04:19.
I want to keep the database format ("yy/mm/dd")
I have tried:
actualdate = DATE_FORMAT(j[0], '%y-%m-%d')
cursor.execute(actualdate)
but it tells me error: name 'DATE_FORMAT' is not defined
import mysql.connector
sql = mysql.connector.connect(host='',user='',password='',db='')
cursor = sql.cursor()
f = open("C:\Cumulus\data\Apr19log.txt","r")
st=[i.strip().split(',') for i in f.readlines()]
actualdate = DATE_FORMAT(j[0], '%y-%m-%d')
cursor.execute(actualdate)
sqllist = "INSERT INTO station_fenelon (variable, date, time,
outside_temp, outside_humidity) VALUES (%s, %s, %s, %s, %s)"
record = [(i+1, j[0], j[1], j[2], j[3]) for i, j in enumerate(st)]
cursor.executemany(sqllist, record)
sql.commit()
error: name 'DATE_FORMAT' is not defined
DATE_FORMAT()
is a MySQL function and you call it directly in your python script so you get an error message that is not defined.You should remove this line.
You could use STR_TO_DATE
to convert your string to a date
sqllist = "INSERT INTO station_fenelon (variable, date, time,
outside_temp, outside_humidity) VALUES (%s, STR_TO_DATE(%s,'%d/%m/%Y'), %s, %s, %s)"
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments