使用pymysql和xlrd将xlsx文件加载到表时，日期值不正确

Question

(非常(初级python用户。我正试图使用xlrd和pymysql-python库将xlsx文件加载到MySQL表中，但我收到了一个错误：

pymysql.err.InternalError:(1292，"第1行'invoice_date'列的日期值不正确：'43500'"(

我的表的invoice_date的数据类型是DATE。我的xlsx文件中此字段的格式也是Date。如果我将表的数据类型更改为varchar，情况会很好，但我更喜欢将数据作为日期加载到表中，而不是事后转换。你知道我为什么会犯这个错误吗？在我的xlxs文件中，xlrd或pymysql似乎将'2/4/2019'读取为'%43500'

import xlrd
import pymysql as MySQLdb
# Open workbook and define first sheet
book = xlrd.open_workbook("2019_Complete.xlsx")
sheet = book.sheet_by_index(0)
# MySQL connection
database = MySQLdb.connect (host="localhost", user="root",passwd="password", db="vendor")
# Get cursor, which is used to traverse the databse, line by line
cursor = database.cursor()
# INSERT INTO SQL query
query = """insert into table values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"""
# Create a For loop to iterate through each row in the XLS file, starting at row 2 to skip the headers
for r in range(1, sheet.nrows):
lp = sheet.cell(r,0).value
pallet_lp = sheet.cell(r,1).value
bol = sheet.cell(r,2).value
invoice_date = sheet.cell(r,3).value
date_received = sheet.cell(r,4).value
date_repaired = sheet.cell(r,5).value
time_in_repair = sheet.cell(r,6).value
date_shipped = sheet.cell(r,7).value
serial_number = sheet.cell(r,8).value
upc = sheet.cell(r,9).value
product_type = sheet.cell(r,10).value
product_description = sheet.cell(r,11).value
repair_code = sheet.cell(r,12).value
condition = sheet.cell(r,13).value
repair_cost = sheet.cell(r,14).value
parts_cost = sheet.cell(r,15).value
total_cost = sheet.cell(r,16).value
repair_notes = sheet.cell(r,17).value
repair_cap = sheet.cell(r,18).value
complaint = sheet.cell(r,19).value
delta = sheet.cell(r,20).value
# Assign values from each row
values = (lp, pallet_lp, bol, invoice_date, date_received, date_repaired, time_in_repair, date_shipped, serial_number, upc, product_type, product_description, repair_code, condition, repair_cost, parts_cost, total_cost, repair_notes, repair_cap, complaint, delta)
# Execute sql Query
cursor.execute(query, values)
# Close the cursor
cursor.close()
# Commit the transaction
database.commit()
# Close the database connection
database.close()
# Print results
print ("")
columns = str(sheet.ncols)
rows = str(sheet.nrows)
print ("I just imported " + columns + " columns and " + rows + " rows to MySQL!")

Answer 1

您可以看到这个答案来获得更详细的解释，但基本上Excel将日期视为相对于1899-12-31的数字，因此要将日期值转换为实际日期，您需要将该数字转换为MySQL接受的ISO格式日期。您可以使用date.fromordinal和date.isoformat来执行此操作。例如：

dval = 43500
d = date.fromordinal(dval + 693594)
print(d.isoformat())

输出：

2019-02-04