使用pymysql和xlrd将xlsx文件加载到表时，日期值不正确 [英] Incorrect date value when loading xlsx file to table using pymysql and xlrd

问题描述

(非常)初学者python用户在这里.我正在尝试使用xlrd和pymysql python库将xlsx文件加载到MySQL表中，但出现错误:

(Very) beginner python user here. I'm trying to load an xlsx file into a MySQL table using xlrd and pymysql python libraries and I'm getting an error:

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

pymysql.err.InternalError: (1292, "Incorrect date value: '43500' for column 'invoice_date' at row 1")

我的表的 invoice_date 的数据类型为 DATE .我的xlsx文件中此字段的格式也为日期".如果我将表数据类型更改为varchar，则一切正常，但我希望将数据作为日期加载到表中，而不是事后进行转换.关于我为什么会收到此错误的任何想法?看来xlrd或pymysql在我的xlxs文件中读取的'2/4/2019'为'43500'，而mysql由于数据类型不匹配而拒绝了它.

The datatype for invoice_date for my table is DATE. The format for this field on my xlsx file is also Date. Things work fine if I change the table datatype to varchar, but I'd prefer to have the data load into my table as a date instead of converting after the fact. Any ideas as to why I'm getting this error? It appears that xlrd or pymysql is reading '2/4/2019' in my xlxs file as '43500' and mysql is rejecting it due to a datatype mismatch.

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!")

推荐答案

您可以看到 date.fromordinal 和 date.isoformat .例如:

You can see this answer for a more detailed explanation, but basically Excel treats dates as a number relative to 1899-12-31, and so to convert your date value to an actual date you need to convert that number into an ISO format date which MySQL will accept. You can do that using date.fromordinal and date.isoformat. For example:

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

输出:

2019-02-04

这篇关于使用pymysql和xlrd将xlsx文件加载到表时，日期值不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！