使用Python导入CSV至MySQL遇MySQLdb.OperationalError问题求助

问题根源 #

你遇到的1292错误，是因为CSV里的日期格式11/01/2021 11:56不符合MySQL datetime字段的默认规范——MySQL默认只接受YYYY-MM-DD HH:MM:SS或YYYY-MM-DD格式的日期字符串，直接插入非标准格式会触发校验错误。

可行解决方案 #

方案1：Python内转换日期格式（推荐） #

用Python的datetime模块解析CSV中的日期字符串，转换为MySQL支持的格式后再插入。修改后的代码如下：

import MySQLdb
import csv
from datetime import datetime

conn = MySQLdb.connect(host="192.168.2.20", user="xxx", password="xxxx", database="admin_mt")

cursor = conn.cursor()
cursor.execute("TRUNCATE TABLE wp_dbo_iclock_transaction")
csv_data = csv.reader(open('wp_dbo_iclock_transaction.csv'))
header = next(csv_data)

print('Importing the CSV Files')
for row in csv_data:
    print(row)
    # 解析原日期格式，转换为MySQL兼容格式
    original_date = row[2]
    # 若你的日期是"日/月/年"格式，把"%m/%d/%Y"改成"%d/%m/%Y"
    formatted_date = datetime.strptime(original_date, "%m/%d/%Y %H:%M").strftime("%Y-%m-%d %H:%M:%S")
    row[2] = formatted_date
    cursor.execute(
        "INSERT INTO wp_dbo_iclock_transaction (id, emp_code, punch_time, name) VALUES (%s,%s,%s,%s)", row)

conn.commit()
cursor.close()
print('Done')

方案2：临时调整MySQL的sql_mode（不推荐生产环境） #

如果不想修改代码，可以临时关闭MySQL的严格日期校验，在连接时添加初始化命令：

conn = MySQLdb.connect(
    host="192.168.2.20", 
    user="xxx", 
    password="xxxx", 
    database="admin_mt",
    init_command="SET sql_mode=''"
)

这种方法会降低数据校验的严格性，可能导致无效日期被插入，仅适合临时测试场景。

方案3：用MySQL原生语句批量导入（大文件高效） #

如果CSV文件体积较大，Python循环插入效率低，可直接用MySQL的LOAD DATA INFILE语句，让数据库自动处理日期转换：

LOAD DATA INFILE '/你的文件路径/wp_dbo_iclock_transaction.csv'
INTO TABLE wp_dbo_iclock_transaction
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
IGNORE 1 ROWS
(id, emp_code, @punch_time, name)
SET punch_time = STR_TO_DATE(@punch_time, '%m/%d/%Y %H:%M');

注意：根据实际日期格式，将%m/%d/%Y调整为%d/%m/%Y（如果是日/月/年的顺序）。

内容的提问来源于stack exchange，提问作者mr. zatan