如何快速将大数据帧插入MS SQL表？to_sql无插入无报错解决

1. 修复连接字符串错误 #

你的连接字符串参数分隔符使用错误，连续的?会导致参数解析失败，无法建立有效连接。正确格式如下：

from sqlalchemy import create_engine  # 补充缺失的导入
import pandas as pd

# 方式1：使用&分隔参数
engine = create_engine(
    "mssql+pyodbc://server1/<database>?driver=ODBC+Driver+17+for+SQL+Server&trusted_connection=yes"
)

# 方式2：用connect_args传递参数（更清晰）
engine = create_engine(
    "mssql+pyodbc://server1/<database>",
    connect_args={
        "driver": "ODBC Driver 17 for SQL Server",
        "trusted_connection": "yes"
    }
)

2. 手动控制事务与连接 #

若自动提交机制未生效，可显式管理事务确保数据写入：

# 自动处理事务（推荐）
with engine.begin() as conn:
    df.to_sql(
        "<db_table_name>",
        conn,
        if_exists="append",
        chunksize=10000  # 根据内存情况调整
    )

# 显式事务控制
conn = engine.connect()
trans = conn.begin()
try:
    df.to_sql("<db_table_name>", conn, if_exists="append", chunksize=10000)
    trans.commit()
except Exception as e:
    trans.rollback()
    raise  # 抛出异常便于排查问题
finally:
    conn.close()

3. 优化20万行数据的插入速度 #

针对大数据量，开启fast_executemany=True可将插入速度提升数十倍（需pyodbc 4.0.19及以上版本）：

engine = create_engine(
    "mssql+pyodbc://server1/<database>?driver=ODBC+Driver+17+for+SQL+Server&trusted_connection=yes",
    fast_executemany=True
)

with engine.begin() as conn:
    df.to_sql(
        "<db_table_name>",
        conn,
        if_exists="append",
        chunksize=20000  # 合理设置chunk大小，平衡内存占用与写入效率
    )

额外排查点 #

• 确认DataFrame的列数据类型与SQL表的列类型完全匹配，避免隐式转换失败导致数据静默丢弃。
• 检查SQL表是否存在主键约束、唯一约束，若DataFrame中有重复值会导致插入失败但无明显报错，可查看SQL Server的错误日志确认。

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