python - 导入数据显示0000-00-00格式

Question

从MySQL中的Excel中导入的数据(只有日期和execution_period dd.mm.yyyy)显示0000-00-00。我尝试将这些列的 dtype 更改为日期时间(通过使用 pd.to_datetime())，然后将其导出到 Excel 中。

df['columnName'] = pd.to_datetime(df['columnName'])

它没有帮助。一般来说，我正在尝试将数据从 Excel 导入 MySQL。

query = """
INSERT INTO sanction (
    id,
    organization_type, organization, date,
    decision_number, penalty_type, penalty_way,
    penalty, violation, execution_period,
    article, note, type_npa,
    department, uploaded_date)
VALUES (
    null,
    %s, %s, %s,
    %s, %s, %s,
    %s, %s, %s,
    %s, %s, %s,
    %s, %s)
"""

for r in range(1, sheet.nrows):
    organization_type = sheet.cell(r, 1).value
    organization = sheet.cell(r, 2).value
    date = sheet.cell(r, 3).value
    decision_number = sheet.cell(r, 4).value
    penalty_type = sheet.cell(r, 5).value
    penalty_way = sheet.cell(r, 6).value
    penalty = sheet.cell(r, 7).value
    violation = sheet.cell(r, 8).value
    execution_period = sheet.cell(r, 9).value
    article = sheet.cell(r, 10).value
    note = sheet.cell(r, 11).value
    type_npa = sheet.cell(r, 12).value
    department = sheet.cell(r, 13).value
    uploaded_date = datetime.now().strftime("%Y-%m-%d %H:%M")

values = (
    # the first value of the INSERT statement will be NULL
    organization_type, organization, date,              # 3 elements
    decision_number, penalty_type, penalty_way,         # 3 elements
    penalty, violation, execution_period,               # 3 elements
    article, note, type_npa,                            # 3 elements
    department, uploaded_date,                          # 2 elements
)
mycursor.execute(query, values)

Answer 1

插入数据中的 '0000-00-00' 值表示 MySQL 未能将您传递的值转换为日期。

将字符串转换为 date datatype 时, MySQL 基本上假定格式为 yyyy-mm-dd(或 yyyy-mm-dd hh24:mi:ss for datetime)，并添加了一些灵 active 。它无法识别您的输入格式 (dd.mm.yyyy)。来自文档:

Invalid DATE, DATETIME, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00').

一种解决方案是在查询中使用 the STR_TO_DATE() function 进行显式转换，比如:

INSERT INTO sanction (
    id,
    organization_type, organization, date,
    decision_number, penalty_type, penalty_way,
    penalty, violation, execution_period,
    article, note, type_npa,
    department, uploaded_date)
VALUES (
    null,
    %s, %s, STR_TO_DATE(%s, '%d.%m.%Y'),
    %s, %s, %s,
    %s, %s, %s,
    %s, %s, %s,
    %s, %s)