如何用sp_execute_external_script实现Python分块插入SQL Server

要实现分块插入以降低内存消耗，你不能依赖OutputDataSet单次返回完整数据集的方式，而是要通过以下两种可行方案解决：

方案一：Python脚本内用pyodbc分块插入 #

这种方式直接在Python循环中建立SQL连接，逐块写入数据，无需依赖SQL端的额外逻辑，是效率较高的实现方式。

完整脚本示例 #

declare @scr nvarchar(max) = 
N'
import pandas as pd
import pyodbc

# 建立SQL Server连接（根据实际环境修改参数）
conn = pyodbc.connect(
    "DRIVER={ODBC Driver 17 for SQL Server};"
    "SERVER=你的服务器名称;"
    "DATABASE=PANDA;"
    "UID=你的登录账号;"
    "PWD=你的登录密码;"
)

# 分块读取压缩包内的CSV
chunk_iter = pd.read_csv(
    r"C:\panda\test3.zip", 
    names = ["PersonID", "FullName", "PreferredName", "SearchName", "IsPermittedToLogon", "Age"], 
    header = 0, 
    compression = "zip", 
    chunksize=500000
)

# 逐块插入目标表
for chunk in chunk_iter:
    chunk.to_sql(
        name="tbl_sample_csv",
        schema="dbo",
        con=conn,
        if_exists="append",
        index=False,
        method="multi"  # 启用批量插入提升写入效率
    )

conn.close()
'

EXEC sp_execute_external_script
@language = N'Python',
@script = @scr

关键说明 #

• 确保SQL Server的Python扩展环境已安装pyodbc（可通过pip install pyodbc在对应Python环境中安装）
• 若使用Windows身份认证，可将连接字符串替换为"DRIVER={ODBC Driver 17 for SQL Server};SERVER=你的服务器名;DATABASE=PANDA;Trusted_Connection=yes;"
• method="multi"会将单块数据整合成批量插入语句，比单条插入效率提升明显
• if_exists="append"确保每次块数据追加到目标表，避免覆盖已有数据

方案二：SQL循环调用Python脚本处理单块 #

如果无法在Python环境中安装pyodbc，可通过SQL循环控制每次调用Python脚本读取一个数据块，手动跟踪读取位置：

完整脚本示例 #

-- 初始化变量：记录当前读取的起始行与块大小
DECLARE @start_row INT = 1;
DECLARE @chunk_size INT = 500000;

WHILE 1=1
BEGIN
    DECLARE @scr nvarchar(max) = 
    N'
    import pandas as pd

    # 读取指定范围的块数据
    chunk = pd.read_csv(
        r"C:\panda\test3.zip", 
        names = ["PersonID", "FullName", "PreferredName", "SearchName", "IsPermittedToLogon", "Age"], 
        header = 0, 
        compression = "zip", 
        skiprows=' + CAST(@start_row AS NVARCHAR) + ',
        nrows=' + CAST(@chunk_size AS NVARCHAR) + '
    )

    # 仅当块非空时返回数据集
    if not chunk.empty:
        OutputDataSet = chunk
    '

    -- 插入当前块数据到目标表
    INSERT INTO [PANDA].[dbo].[tbl_sample_csv]
    EXEC sp_execute_external_script
    @language = N'Python',
    @script = @scr;

    -- 判断是否还有剩余数据：若插入行数小于块大小，说明已读取完毕
    IF @@ROWCOUNT < @chunk_size
        BREAK;

    -- 更新起始行，准备下一次读取
    SET @start_row = @start_row + @chunk_size;
END

关键说明 #

• 通过skiprows和nrows精准控制每次读取的块范围
• 利用@@ROWCOUNT获取本次插入的行数，以此判断是否终止循环
• 这种方式依赖SQL端的循环逻辑，适合受限环境，但整体效率略低于方案一

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