Python脚本无法将非英文字符正确写入SQL数据库求助

问题现象 #

使用Pandas读取包含中文、西里尔字符的CSV文件，导入SQL Server后，所有非ASCII字符均显示为???。

源CSV数据 #

Last Name   First Name  Middle Name Full Legal Name
last擺田手1    first擺田手1   middle擺田手1  fulllegal1擺田手
Last擺田手2    first擺田手2   middle擺田手2  fulllegal擺田手
lastname6БдД    firstname6БдД   middlename6БдД  fulllegal6БдД
lastname7БдД    firstname7БдД   middlename7БдД  fulllegalname7БдД
lastname8БдД    firstname8БдД   middlename8БдД  fulllegalname8БдД

数据库中错误显示结果 #

LastName    FirstName   MiddleName  FullLegalName
last???1    first???1   middle???1  fulllegal1???
Last???2    first???2   middle???2  fulllegal2???
lastname???3    firstname???3   middlename???3  fulllegal3???
lastname???4    firstname???4   middlename???4  fulllegalname4???
lastname???5    firstname???5   middlename???5  fulllegalname5???

原实现代码 #

from sqlalchemy import create_engine
import pandas as pd
import datetime
 
def ImportDataToDB():
    servername = 'CWQ00xxxxx.xxxxxx.com'
    dbname = 'AbbottRPA'
    Input_file = 'C:/Users/AKARAPX/Desktop/Corptesting/SOX Workday Report (DRS) 2023-11-13 05_56 CST.csv'
    table1 = 'CAWD_TX_Main'
    CreatedBy = 'AKARAPX'
  
    try:
        conn = create_engine('mssql+pyodbc://@' + servername + '/' + dbname + '?trusted_connection=yes&driver=ODBC+Driver+17+for+SQL+Server')

        #print('Connection successful')

        df_csvdata = pd.read_csv(Input_file, encoding="utf-8")

        columns_to_keep = ['Last Name','First Name','Middle Name',]
        df_csvdata = df_csvdata[columns_to_keep]
 
        df_csvdata.to_sql(table1, con = conn, schema = 'rpa', if_exists='append', index=False)
        print('Data inserted successfully')
   

    except Exception as e:
        print(f"An error occurred: {e}")

ImportDataToDB()

解决方案 #

1. 检测并使用CSV文件的实际编码 #

默认指定utf-8可能与文件实际编码不符，先用chardet库检测编码：

import chardet
with open(Input_file, 'rb') as f:
    encoding_result = chardet.detect(f.read())
print(f"检测到的文件编码: {encoding_result['encoding']}")

将检测到的编码传入pd.read_csv，例如：

df_csvdata = pd.read_csv(Input_file, encoding=encoding_result['encoding'])

2. 确保SQL Server表列使用Unicode类型 #

SQL Server中VARCHAR仅支持ASCII和特定代码页字符，必须改用NVARCHAR类型存储多语言字符：

• 查看表列类型：

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'CAWD_TX_Main' AND TABLE_SCHEMA = 'rpa';

• 修改列类型为NVARCHAR（根据实际需求调整长度）：

ALTER TABLE rpa.CAWD_TX_Main 
ALTER COLUMN [Last Name] NVARCHAR(255);

ALTER TABLE rpa.CAWD_TX_Main 
ALTER COLUMN [First Name] NVARCHAR(255);

ALTER TABLE rpa.CAWD_TX_Main 
ALTER COLUMN [Middle Name] NVARCHAR(255);

3. 优化SQLAlchemy连接配置 #

在连接字符串中显式指定Unicode支持，确保驱动正确传递字符：

conn = create_engine('mssql+pyodbc://@' + servername + '/' + dbname + '?trusted_connection=yes&driver=ODBC+Driver+17+for+SQL+Server&charset=utf8')

4. 强制指定数据类型（可选） #

通过dtype参数强制Pandas使用Unicode类型写入数据库，避免自动推断错误：

from sqlalchemy.types import NVARCHAR

# 定义列对应的Unicode类型
dtype_config = {
    'Last Name': NVARCHAR(length=255),
    'First Name': NVARCHAR(length=255),
    'Middle Name': NVARCHAR(length=255)
}

# 写入时指定dtype
df_csvdata.to_sql(table1, con=conn, schema='rpa', if_exists='append', index=False, dtype=dtype_config)

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