How to insert date time column from excel into datetime field in mySQL?
I wrote a python script to import data from an Excel spreadsheet into a mysql database. But two columns in the table are the following Excel custom format YYYY-MM-DD HH:MM:SS
. And the mysql table has these columns in the format datetime
. However, when I run the script, I get an error that this format cannot be added to the datetime format in SQL.
Below is my python script:
query = """INSERT INTO table (col1, col2, col3, col4, col5) VALUES (%s, %s, %s, %s, %s)"""
for r in range(1, sheet.nrows):
col1 = sheet.cell(r,0).value
col2 = sheet.cell(r,1).value
col3 = sheet.cell(r,2).value
col4 = sheet.cell(r,3).value
col5 = sheet.cell(r,4).value
#col5 = xlrd.xldate.xldate_as_datetime(sheet.cell(r,4).value, book.datemode)
# Assign values from each row
values = (col1, col2, col3, col4, col5)
# Execute sql Query
cursor.execute(query, values)
# Close the cursor
cursor.close()
# Commit the transaction
con.commit()
Can someone tell me how to insert a date time column from excel into a datetime field in mySQL?
+3
source to share
No one has answered this question yet
Check out similar questions: