SQL Server通过Python批量插入如何让INSTEAD OF INSERT触发器仅触发一次

方案说明 #

SQL Server的INSTEAD OF INSERT触发器本身是按INSERT语句执行次数触发，单次INSERT语句无论插入多少行，触发器只会触发一次。你之前用executemany默认逐行执行INSERT所以触发器多次触发，拼接多值INSERT的方案能满足触发次数要求，但存在SQL注入、特殊字符语法报错、语句长度不可控的隐患，推荐用以下更稳妥的方案：

方案1：表值参数（TVP，最优）

这是SQL Server原生支持的批量传参方案，无注入风险、无长度限制，单次提交所有数据，触发器仅触发一次，不需要上传文件到数据库服务器。

1. 先在SQL Server中创建和插入表结构匹配的自定义表类型：

CREATE TYPE dbo.YourTableType AS TABLE 
(
    列1 类型,
    列2 类型,
    -- 和你要插入的目标表字段一一对应
);

2. Python端代码示例：

import pyodbc

conn = pyodbc.connect('你的数据库连接字符串')
cursor = conn.cursor()

# 你的批量数据
list_of_lists = [
    (1, '数据1'),
    (2, '数据2'),
    # 最多支持上万行数据，没有长度限制
]

# 单次提交插入，触发器仅触发一次
cursor.execute("""
    INSERT INTO 目标表 (列1, 列2)
    SELECT 列1, 列2 FROM ?
""", (list_of_lists, 'dbo.YourTableType'))

conn.commit()
cursor.close()
conn.close()

方案2：参数化分批次多值插入

如果不想提前在数据库创建自定义表类型，可以用该方案，完全避免SQL注入和语法报错问题：
SQL Server单条查询最多支持2100个参数，你可以按每行字段数计算单批次最大行数，分批次提交即可，每个批次对应一次INSERT，触发器每个批次触发一次。

import pyodbc

conn = pyodbc.connect('你的数据库连接字符串')
cursor = conn.cursor()
all_data = [...] # 你的所有批量数据
col_count = len(all_data[0])
batch_size = 2100 // col_count # 保证单批次参数不超过2100上限

for i in range(0, len(all_data), batch_size):
    batch = all_data[i:i+batch_size]
    # 生成参数占位符
    placeholders = ', '.join([f"({','.join(['?']*col_count)})" for _ in batch])
    sql = f"INSERT INTO 目标表 (列1,列2,列N) VALUES {placeholders}"
    # 展开批量数据作为参数，完全参数化无注入风险
    cursor.execute(sql, [item for row in batch for item in row])

conn.commit()
cursor.close()
conn.close()

现有方案评估 #

你当前的字符串拼接方案如果仅在内部管控环境使用、且输入数据完全可控，短期可以跑通，但长期不推荐：一旦输入数据包含单引号等特殊字符会直接触发SQL语法错误，后续如果数据量上涨也可能触达语句长度限制，换成上面两个方案可以彻底规避这些风险。

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