将CSV导入MySQL时遭遇UnicodeDecodeError错误求助

问题详情 #

将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_transaction2")

csv_data = csv.reader(open('dbo_iclock_transaction.csv'))
header = next(csv_data)


print('Importing the CSV Files')
for row in csv_data:
  
    punch_time = datetime.strptime(row[2], '%d/%m/%Y %H:%M').strftime('%Y-%m-%d %H:%M:%S')
    
    cursor.execute("""INSERT INTO wp_dbo_iclock_transaction2
                                  (id, emp_code, punch_time)
                                       VALUES (%s,%s,%s)""",
                                       (row[0], row[1], punch_time))

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

报错信息 #

Traceback (most recent call last):
  File "D:\Server Backup\time_import\import.py", line 13, in <module>
    header = next(csv_data)
  File "C:\Program Files\Python311\Lib\encodings\cp874.py", line 23, in decode
    return codecs.charmap_decode(input,self.errors,decoding_table)[0]
UnicodeDecodeError: 'charmap' codec can't decode byte 0x8a in position 310: character maps to <undefined>

猜测CSV文件为UTF-8编码，寻求解决办法。

解决办法 #

问题根源是打开文件时未指定编码，Windows默认使用系统编码（此处为cp874）读取，与CSV实际编码（UTF-8）不匹配导致解码失败。

核心修复 #

打开CSV文件时明确指定encoding='utf-8'参数：

csv_data = csv.reader(open('dbo_iclock_transaction.csv', encoding='utf-8'))

若文件存在少量无法解码的特殊字符，可添加errors='ignore'忽略错误（按需使用）：

csv_data = csv.reader(open('dbo_iclock_transaction.csv', encoding='utf-8', errors='ignore'))

优化建议 #

使用with语句管理文件，自动释放资源，避免潜在的文件泄漏问题，优化后的完整代码：

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

print('Importing the CSV Files')
with open('dbo_iclock_transaction.csv', encoding='utf-8') as csv_file:
    csv_data = csv.reader(csv_file)
    header = next(csv_data)
    
    for row in csv_data:
        punch_time = datetime.strptime(row[2], '%d/%m/%Y %H:%M').strftime('%Y-%m-%d %H:%M:%S')
        cursor.execute("""INSERT INTO wp_dbo_iclock_transaction2
                              (id, emp_code, punch_time)
                                   VALUES (%s,%s,%s)""",
                                   (row[0], row[1], punch_time))

conn.commit()
cursor.close()
conn.close()  # 补充关闭数据库连接，避免资源占用
print('Done')

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