python - 来自 Python 的多行 UPSERT(INSERT 或 UPDATE)

Question

我目前正在使用 python 使用 pyodbc 执行下面的简单查询，以在 SQL 服务器表中插入数据:

import pyodbc

table_name = 'my_table'
insert_values = [(1,2,3),(2,2,4),(3,4,5)]

cnxn = pyodbc.connect(...)
cursor = cnxn.cursor()
cursor.execute(
    ' '.join([
        'insert into',
        table_name,
        'values',
        ','.join(
            [str(i) for i in insert_values]
        )
    ])
)
cursor.commit()

只要没有重复的键，这就应该有效(假设第一列包含键)。但是对于具有重复键的数据(表中已经存在的数据)，它将引发错误。我如何使用 pyodbc 一次性在 SQL 服务器表中插入多行，以便更新具有重复键的数据。

注意:针对单行数据提出了解决方案，但是，我想一次插入多行(避免循环)!

Answer 1

这可以使用 MERGE 来完成.假设您有一个键列 ID , 和两列 col_a和 col_b (您需要在更新语句中指定列名)，那么该语句将如下所示:

MERGE INTO MyTable as Target
USING (SELECT * FROM 
       (VALUES (1, 2, 3), (2, 2, 4), (3, 4, 5)) 
       AS s (ID, col_a, col_b)
      ) AS Source
ON Target.ID=Source.ID
WHEN NOT MATCHED THEN
INSERT (ID, col_a, col_b) VALUES (Source.ID, Source.col_a, Source.col_b)
WHEN MATCHED THEN
UPDATE SET col_a=Source.col_a, col_b=Source.col_b;

您可以在 rextester.com/IONFW62765 上试一试.

基本上，我正在创建一个 Source使用您想要的值列表“即时”表 upsert .当您然后合并 Source表与 Target , 你可以测试 MATCHED每行上的条件 ( Target.ID=Source.ID )(而仅使用简单的 IF <exists> INSERT (...) ELSE UPDATE (...) 条件时，您将被限制在一行中)。

在 python 中使用 pyodbc ，它应该看起来像这样:

import pyodbc

insert_values = [(1, 2, 3), (2, 2, 4), (3, 4, 5)]
table_name = 'my_table'
key_col = 'ID'
col_a = 'col_a'
col_b = 'col_b'

cnxn = pyodbc.connect(...)
cursor = cnxn.cursor()
cursor.execute(('MERGE INTO {table_name} as Target '
                'USING (SELECT * FROM '
                '(VALUES {vals}) '
                'AS s ({k}, {a}, {b}) '
                ') AS Source '
                'ON Target.ID=Source.ID '
                'WHEN NOT MATCHED THEN '
                'INSERT ({k}, {a}, {b}) VALUES (Source.{k}, Source.{a}, Source.{b}) '
                'WHEN MATCHED THEN '
                'UPDATE SET {k}=Source.{a}, col_b=Source.{b};'
                .format(table_name=table_name,
                        vals=','.join([str(i) for i in insert_values]),
                        k=key_col,
                        a=col_a,
                        b=col_b)))
cursor.commit()

您可以在 MERGE 上阅读更多内容在SQL Server docs .