如何通过PyODBC优化SQL Server 50万条Excel数据插入速度？

方案1：使用executemany()批量插入 #

逐行execute()的核心问题是每一行都要和数据库建立一次交互，50万次网络往返会极大拖慢速度。executemany()可以一次性发送多行数据，大幅减少网络开销，是对原有代码最直接的优化。

代码示例： #

import pyodbc
import pandas as pd
from tqdm import tqdm

cnxn = pyodbc.connect('DRIVER={SQL Server};Server=DESKTOP\MS2019;\
Database=203-Temp;Port=1433;User ID=sa;Password=1234;TrustServerCertificate=True')
cnxn.autocommit = False
cursor = cnxn.cursor()

# 读取Excel数据并填充空值（注意根据字段类型调整填充值，数值型用0，字符串型用'0'）
data_df = pd.read_excel('a.xlsx').fillna(value='0')

# 转换为元组列表，每个元组对应一行数据
data_tuples = [tuple(row) for row in data_df[['col1','col2','col3','col4','col5','col6','col7','col8','col9','col10']].values]

# 定义插入SQL
insert_sql = """
INSERT INTO Temp (UID, Name, ShName, GID, GLink, GName, GAbout, PUID, PUnID, Date)
VALUES (?,?,?,?,?,?,?,?,?,?)
"""

# 分批次执行，建议设置1000-5000行/批，平衡内存占用和插入速度
batch_size = 2000
for i in tqdm(range(0, len(data_tuples), batch_size), desc="Inserting Data Into Database"):
    batch = data_tuples[i:i+batch_size]
    cursor.executemany(insert_sql, batch)
    cnxn.commit()  # 每批提交一次，避免事务过大导致性能问题

cursor.close()
cnxn.close()

方案2：使用pandas to_sql 配合fast_executemany=True #

pandas内置的to_sql方法可以直接将DataFrame写入数据库，配合SQLAlchemy引擎的fast_executemany=True参数，底层会自动优化批量插入逻辑，代码更简洁，无需手动处理数据格式转换。

代码示例： #

import pandas as pd
from sqlalchemy import create_engine

# 创建SQLAlchemy连接引擎，注意连接字符串格式
conn_str = 'mssql+pyodbc://sa:1234@DESKTOP\MS2019/203-Temp?driver=SQL+Server&TrustServerCertificate=yes'
engine = create_engine(conn_str, fast_executemany=True)

# 读取Excel数据并填充空值
data_df = pd.read_excel('a.xlsx').fillna(value='0')

# 映射DataFrame列到数据库表字段（如果列名和表字段完全一致可省略此步骤）
data_df = data_df.rename(columns={
    'col1': 'UID',
    'col2': 'Name',
    'col3': 'ShName',
    'col4': 'GID',
    'col5': 'GLink',
    'col6': 'GName',
    'col7': 'GAbout',
    'col8': 'PUID',
    'col9': 'PUnID',
    'col10': 'Date'
})

# 写入数据库，chunksize控制批次大小
data_df.to_sql(
    name='Temp',
    con=engine,
    if_exists='append',  # 根据需求选择：append追加、replace覆盖、fail报错
    index=False,
    chunksize=2000
)

方案3：MSSQL原生BULK INSERT（速度最快） #

对于超大数据量，MSSQL的BULK INSERT是最优选择，它直接读取本地或共享文件导入数据库，几乎是最快的批量导入方式。步骤为先将Excel转成CSV，再执行BULK INSERT命令。

代码示例： #

import pandas as pd
import pyodbc

# 1. 将Excel数据导出为CSV（用|作为分隔符，避免和数据中的逗号冲突；utf-8-sig编码兼容中文）
data_df = pd.read_excel('a.xlsx').fillna(value='0')
csv_path = 'temp_data.csv'
data_df.to_csv(csv_path, index=False, encoding='utf-8-sig', sep='|')

# 2. 连接数据库执行BULK INSERT
cnxn = pyodbc.connect('DRIVER={SQL Server};Server=DESKTOP\MS2019;\
Database=203-Temp;Port=1433;User ID=sa;Password=1234;TrustServerCertificate=True')
cursor = cnxn.cursor()

# 注意：CSV路径需要是数据库服务器能访问的路径，本地运行需确保SQL Server服务账号有权限读取该文件
bulk_sql = f"""
BULK INSERT Temp
FROM '{csv_path.replace('\\', '\\\\')}'
WITH (
    FIELDTERMINATOR = '|',  # 和CSV导出的分隔符一致
    ROWTERMINATOR = '\\n',
    FIRSTROW = 2,  # 跳过CSV表头
    CODEPAGE = '65001',  # 对应UTF-8编码
    TABLOCK  # 启用表锁，大幅提升导入速度
)
"""

cursor.execute(bulk_sql)
cnxn.commit()

cursor.close()
cnxn.close()

额外优化建议 #

• 禁用索引和约束：插入前先禁用表的非聚集索引、外键约束，插入完成后再重建，避免插入时频繁更新索引和检查约束：

  -- 禁用索引
  ALTER INDEX ALL ON Temp DISABLE;
  -- 禁用外键约束（如果存在）
  ALTER TABLE Temp NOCHECK CONSTRAINT ALL;
  
  -- 插入完成后恢复
  ALTER INDEX ALL ON Temp REBUILD;
  ALTER TABLE Temp CHECK CONSTRAINT ALL;
  

• 调整批次大小：根据内存情况调整批次，太小增加往返次数，太大可能导致内存溢出，1000-5000行/批是通用合理范围。
• 使用TABLOCK：在插入语句中添加WITH (TABLOCK)提示，减少锁竞争，提升并发插入效率。

内容的提问来源于stack exchange，提问作者JANI-COBIN