SQLAlchemy读写DataFrame时特殊字符编码异常，如何批量修复？

问题根源 #

你遇到的â\x80\x99是典型的编码双重转换错误：数据库中存储的是UTF-8编码的右单引号（’，Unicode U+2019），但读取时被错误地用Windows-1252编码解码，导致原本的UTF-8字节被解析成了乱码字符。终端/Notebook可能会自动转码让你看起来显示正常，但字符串内部的编码已经错乱，写入回数据库时就会显示异常。

解决方案 #

1. 从根源修复：读取时指定正确编码 #

最彻底的解决方法是在建立SQLAlchemy连接时，强制指定数据库连接的字符集为utf8mb4（支持所有UTF-8字符，包括特殊符号），避免读取时的编码错误。

示例（MySQL+pymysql）：

from sqlalchemy import create_engine

# 连接字符串中添加charset=utf8mb4参数
engine = create_engine('mysql+pymysql://username:password@host:port/database?charset=utf8mb4')
con = engine.connect()

# 重新读取数据
q = """select * from table_name"""
data = pd.read_sql_query(q, con)

示例（SQL Server+pyodbc）：

engine = create_engine('mssql+pyodbc://username:password@dsn_name?charset=utf8')
# 或者在ODBC连接字符串中指定CharacterSet=UTF-8
engine = create_engine('mssql+pyodbc:///?odbc_connect=DRIVER={SQL Server};SERVER=host;DATABASE=db;UID=user;PWD=pass;CharacterSet=UTF-8')

2. 批量修复已读取的乱码数据 #

如果已经读取了乱码数据，可以通过编码转换批量修复所有字符串列：

def fix_garbled_text(s):
    if isinstance(s, str):
        try:
            # 将乱码字符串先按Windows-1252编码回字节，再用UTF-8解码还原原字符
            return s.encode('windows-1252').decode('utf-8')
        except (UnicodeEncodeError, UnicodeDecodeError):
            # 转换失败则保留原字符串
            return s
    return s

# 遍历所有字符串类型的列，批量修复
for col in data.select_dtypes(include=['object']).columns:
    data[col] = data[col].apply(fix_garbled_text)

3. 确保写入时编码正确 #

只要连接时指定了正确的utf8mb4字符集，pandas.to_sql会自动使用正确的编码写入数据，无需额外配置。测试修复后的数据写入：

df = pd.DataFrame({'test': [data.iloc[0,8]]})
df.to_sql(
    name='tableTest',
    con=con,
    if_exists='replace',
    index=False
)
con.commit()

验证 #

修复后，你可以打印字符串的原始字节来确认：

print(repr(data.iloc[0,8]))
# 正常输出应为：'Tyler’s way'（注意是右单引号’，不是普通的'）

内容的提问来源于stack exchange，提问作者frank