如何用Python+pyodbc实现SQL Server多行UPSERT（插入或更新）操作

嘿，我来帮你搞定这个问题！你的原始代码直接拼接SQL字符串，不仅有SQL注入风险，还没法处理主键重复的场景。在SQL Server里实现UPSERT（记录存在就更新，不存在就插入）有两种实用的方式，结合pyodbc给你详细说明：

方法1：用MERGE语句（兼容所有支持的SQL Server版本） #

MERGE是SQL Server实现UPSERT的标准玩法，能根据你指定的匹配条件（比如主键相等）自动判断是插入还是更新。

代码示例 #

import pyodbc

table_name = 'my_table'
# 先明确表的列名（假设主键是id，另外两列是col2、col3）
columns = ['id', 'col2', 'col3']
insert_values = [(1,2,3),(2,2,4),(3,4,5)]

# 建立连接和游标
cnxn = pyodbc.connect(...)
cursor = cnxn.cursor()

# 构造MERGE的SQL语句
merge_sql = f"""
MERGE INTO {table_name} AS target
-- 把要插入的多行数据作为临时数据源
USING (VALUES {', '.join(['(?, ?, ?)']*len(insert_values))}) AS source ({', '.join(columns)})
-- 指定匹配条件：主键id相等
ON target.id = source.id
-- 匹配到重复记录时，更新指定列
WHEN MATCHED THEN
    UPDATE SET col2 = source.col2, col3 = source.col3
-- 没匹配到就插入新记录
WHEN NOT MATCHED THEN
    INSERT ({', '.join(columns)}) VALUES ({', '.join(columns)});
"""

# 把二维的参数列表扁平化，pyodbc需要按顺序传入所有参数
flat_params = [val for row in insert_values for val in row]
cursor.execute(merge_sql, flat_params)
# 别忘了提交事务！
cnxn.commit()

划重点 #

• 参数化查询：用?当占位符，既避免了SQL注入，pyodbc还能自动处理数据类型转换，比直接拼字符串靠谱多了。
• 明确列名：一定要写出具体的列名，别偷懒用*，不然表结构一变代码就崩了，逻辑也更清晰。
• 兼容旧版本：不管你用的是SQL Server 2016还是2019，这个方法都能正常工作。

方法2：用INSERT ... ON CONFLICT（仅SQL Server 2022+） #

如果你用的是SQL Server 2022或更新的版本，那可以用更简洁的ON CONFLICT语法，和PostgreSQL的写法类似：

代码示例 #

import pyodbc

table_name = 'my_table'
columns = ['id', 'col2', 'col3']
insert_values = [(1,2,3),(2,2,4),(3,4,5)]

cnxn = pyodbc.connect(...)
cursor = cnxn.cursor()

# 构造UPSERT语句
upsert_sql = f"""
INSERT INTO {table_name} ({', '.join(columns)})
VALUES {', '.join(['(?, ?, ?)']*len(insert_values))}
-- 指定冲突的主键列，冲突时更新用EXCLUDED取待插入的值
ON CONFLICT (id) DO UPDATE SET
    col2 = EXCLUDED.col2,
    col3 = EXCLUDED.col3;
"""

flat_params = [val for row in insert_values for val in row]
cursor.execute(upsert_sql, flat_params)
cnxn.commit()

划重点 #

• EXCLUDED关键字：这个关键字代表原本要插入的那条冲突记录，直接用它来获取更新的值，写法非常简洁。
• 版本限制：这个语法只有SQL Server 2022及以后支持，要是你的数据库版本低，还是老老实实用MERGE。

额外提醒 #

• 事务一定要提交：执行完SQL后别忘了调用cnxn.commit()，要是出错了可以用cnxn.rollback()回滚。
• 必须有主键/唯一约束：UPSERT的前提是表上有主键或者唯一约束，不然数据库根本不知道啥是“重复键”。
• 大数据量分批处理：如果要插入个几万几十万条，最好分批处理，别一次性传太多参数，避免性能问题。

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