使用SQLAlchemy将数据写入PostgreSQL时触发UnicodeDecodeError问题排查求助

我最近在尝试用Python的SQLAlchemy库把CSV文件里的数据导入PostgreSQL数据库，但每次执行下面这行代码时都会触发编码错误：

main.drop_duplicates().to_sql('main', engine, if_exists='replace', index=False)

错误信息是：UnicodeDecodeError: 'utf-8' codec can't decode byte 0xbb in position 79: invalid start byte

我已经做了以下尝试来解决，但都没有效果：

• 修改postgresql.conf里的client_encoding = utf8配置
• 尝试在数据库URI后添加/不添加?charset=utf8参数
• 给pd.read_csv()加上encoding_errors="replace"参数
• 跳过CSV文件，直接在Pandas里手动创建测试数据（代码如下）
• 用chardet工具检测CSV文件编码，结果显示为utf-8

手动创建的测试数据代码 #

data = {
    'PROJECT_NUMBER': ['R01HL093399', 'R01HL093399', 'R01HL093399', 'R01HL093399', 'R01HL095924'],
    'PATENT_ID': [8840150, 10022225, 9283301, 10293082, 9556432],
    'PMC_ID': [3132660, 3132660, 3132660, 3132660, 4121125],
    'UNIQUE_ID': ['R01HL093399_8840150_3132660', 'R01HL093399_10022225_3132660', 'R01HL093399_9283301_3132660', 'R01HL093399_10293082_3132660', 'R01HL095924_9556432_4121125']
}

df = pd.DataFrame(data)

PostgreSQL数据库与表创建语句 #

CREATE DATABASE Post_db
    WITH 
    OWNER = admin
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    TEMPLATE = template0;

CREATE TABLE main (
    PROJECT_NUMBER VARCHAR(255),
    PRODUCT_ID INT,
    CUSTOMERID INT,
    UNIQUE_ID VARCHAR(255) PRIMARY KEY,
    UNIQUE (PRODUCT_ID),
    UNIQUE (CUSTOMERID)
);

完整Python代码 #

import pandas as pd
import os
from sqlalchemy import create_engine
from sqlalchemy.engine import URL

DATA_PATH = '../../Data/'
CSV_FILE = 'customer_data.csv'

url = URL.create(
    drivername="postgresql",
    username="admin",
    host="localhost",
    database="Post_db",
    password="PASSWORT"
)

engine = create_engine(url)
df = pd.read_csv(os.path.join(DATA_PATH, CSV_FILE), encoding='utf8')
df.drop_duplicates().to_sql('main', engine, if_exists='replace', index=False)

CSV文件内容示例 #

表格预览：

原始CSV文本：

,PROJECT_NUMBER,PRODUCT_ID,CUSTOMERID,UNIQUE_ID
0,R01HL093399,8840150,3132660,R01HL093399_8840150_3132660
1,R01HL093399,10022225,3132660,R01HL093399_10022225_3132660
2,R01HL093399,9283301,3132660,R01HL093399_9283301_3132660
3,R01HL093399,10293082,3132660,R01HL093399_10293082_3132660
4,R01HL095924,9556432,4121125,R01HL095924_9556432_4121125

更奇怪的是，即使我只对手动创建的测试数据执行to_sql命令，还是会触发完全一样的错误，实在摸不清问题出在哪，希望有人能帮我排查一下！

备注：内容来源于stack exchange，提问作者Ben