来自 Python 的多行 UPSERT(插入或更新) [英] Multi-row UPSERT (INSERT or UPDATE) from Python

问题描述

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

I am currently executing the simply query below with python using pyodbc to insert data in SQL server table:

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 Server 表中插入多行，以便简单地更新具有重复键的数据.

This should work as long as there are no duplicate keys (let's assume the first column contains the key). However for data with duplicate keys (data already existing in the table) it will raise an error. How can I, in one go, insert multiple rows in a SQL server table using pyodbc such that data with duplicate keys simply gets updated.

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

Note: There are solutions proposed for single rows of data, however, I would like to insert multiple rows at once (avoid loops)!

推荐答案

这可以使用 MERGE 来完成.假设您有一个键列 ID，以及两列 col_a 和 col_b(您需要在更新语句中指定列名)，那么语句看起来像这样:

This can be done using MERGE. Let's say you have a key column ID, and two columns col_a and col_b (you need to specify column names in update statements), then the statement would look like this:

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 上试一试.

You can give it a try on rextester.com/IONFW62765.

基本上，我正在使用您想要Source表/wiki/upsert" rel="noreferrer">upsert.当您然后将 Source 表与 Target 合并时，您可以测试 MATCHED 条件(Target.ID=Source.ID) 在每一行上(而当仅使用简单的 IF <exists> INSERT (...) ELSE UPDATE (...) 条件时，您将被限制为一行).

Basically, I'm creating a Source table "on-the-fly" using the list of values, which you want to upsert. When you then merge the Source table with the Target, you can test the MATCHED condition (Target.ID=Source.ID) on each row (whereas you would be limited to a single row when just using a simple IF <exists> INSERT (...) ELSE UPDATE (...) condition).

在带有 pyodbc 的 python 中，它应该看起来像这样:

In python with pyodbc, it should probably look like this:

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()

您可以在 SQL Server 文档.

You can read up more on MERGE in the SQL Server docs.

这篇关于来自 Python 的多行 UPSERT(插入或更新)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！