使用Pandas和SQLAlchemy读取SQL Server表时的编码问题

问题场景 #

使用Pandas结合SQLAlchemy读取SQL Server表并导出为Parquet文件时，大部分表流程正常，但读取某张表时触发编码错误：

'utf-16-le' codec can't decode bytes in position 46-47: illegal UTF-16 surrogate

相关代码如下：

for tbl in table:
    df = pd.read_sql_table(f'{tbl[0]}', mssql_engine())
    filename = (tbl[0]+'.parquet')
    path = (tmppath+filename)

    df.to_parquet(path, compression=None)

尝试在create_engine中设置charset=latin1（对应目标表中某列的排序规则SQL_Latin1_General_CP1_CI_AI），但错误依然存在：

def mssql_engine(user=sqluser, password=sqlpassword, host=sqlserver, db=sqldatabase):
    engine = create_engine(
        f'mssql+pyodbc://{user}:{password}@{host}/{db}?driver=SQL+Server&charset=latin1')
    return engine

目标表中出问题的列信息：

{'name': 'Column', 'type': VARCHAR(length=2, collation='SQL_Latin1_General_CP1_CI_AI'), 'nullable': True, 'default': None, 'autoincrement': False}

原因分析 #

SQLAlchemy的charset参数对pyodbc驱动的编码控制有限，pyodbc会优先根据SQL Server列的排序规则自动处理字符编码转换，但在某些情况下（比如列中存在不符合UTF-16规则的字节序列），会错误地将Latin1编码的字节按UTF-16解析，导致解码失败。

解决方案 #

方案1：强制pyodbc返回字节并手动解码 #

修改SQLAlchemy连接参数，关闭自动Unicode转换，让pyodbc直接返回字节数据，再手动用Latin1解码目标列：

def mssql_engine(user=sqluser, password=sqlpassword, host=sqlserver, db=sqldatabase):
    engine = create_engine(
        f'mssql+pyodbc://{user}:{password}@{host}/{db}?driver=SQL+Server&unicode_results=False')
    return engine

读取表后对目标列进行解码：

df = pd.read_sql_table(f'{tbl[0]}', mssql_engine())
# 仅对受影响的Latin1编码列处理
df['Column'] = df['Column'].str.decode('latin1')

方案2：通过SQL查询显式转换编码 #

使用read_sql_query替代read_sql_table，在SQL语句中显式指定列的编码转换：

query = f"""
SELECT 
    CONVERT(VARCHAR(2), [Column] COLLATE SQL_Latin1_General_CP1_CI_AI) AS [Column],
    -- 列出表中其他列
    [OtherColumn1], [OtherColumn2]
FROM {tbl[0]}
"""
df = pd.read_sql_query(query, mssql_engine())

方案3：忽略解码错误（临时应急） #

如果无需保留非法字节的内容，可以在解码时忽略错误：

df = pd.read_sql_table(f'{tbl[0]}', mssql_engine())
df['Column'] = df['Column'].apply(
    lambda x: x.encode('latin1').decode('utf-16-le', errors='ignore') 
    if pd.notnull(x) else x
)

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