使用触发器的单次插入/单次触发在SQL Server表中插入多行

2022-04-24 00:00:00 python pyodbc sql-server triggers

我想要什么

能够使用Python在SQL Server中将多行插入到表中,并且仅在插入所有行后才触发INSTEAD OF INSERT触发器。

背景

我的表上有一个INSTEAD OF INSERT触发器,它对以前看不到的数据执行操作。

通常的操作模式是定期一次插入多行。

当我从python插入(使用pyodbc)时,如果我使用:

cursor.executemany('INSERT INTO table_name VALUE (?, ?, ...)', list_of_lists)

触发器将针对每一行触发--这不是我想要的。

我当前正在使用:

sql = 'INSERT INTO table_name VALUES (...),(...)...;'  # string built with format-strings
cursor.execute(sql)

这在理论上为我打开了SQL注入的大门,但我的环境受到了限制,因此实际上不是问题。
另外,我不知道查询字符串的最大限制是多少。 我尝试使用the method in this post找出极限,但返回的结果为空。所以我不确定是没有限制还是未知。

我一次插入的行数预计不会太多(不超过1-2k行,少量列)。因此,我不认为我会达到极限,但我不想稍后感到惊讶。

然后是BULK INSERT选项和FIRE_TRIGGERS选项,但这需要将我的数据作为文件上载到运行SQL Server的计算机。这是我希望避免的复杂性。

有没有更好的选择,或者我当前的解决方案还行吗?


解决方案

似乎可以使用表类型参数来执行此操作。我要指出的是,我的Python不是很好,所以我不知道您是否可以在不使用的情况下使用存储过程完成此。

不管怎样,首先让我们创建一个示例表:

CREATE TABLE dbo.SomeTable (ID int IDENTITY(1,1),
                            SomeString varchar(10),
                            SomeInt int,
                            SomeDate date,
                            SomeGUID uniqueidentifier);
GO

,然后是一个示例类型。我有意省略SomeGUID

CREATE TYPE dbo.SomeType AS table (SomeString varchar(10),
                                   SomeInt int,
                                   SomeDate date);
然后,您需要创建一个接受上述TYPE作为参数的过程。在下面的代码中,我为SomeGUID定义了整个数据集的静态值,以演示在对最终结果集中的proc的一次调用中插入所有行:

CREATE OR ALTER PROC dbo.SomeProc @SomeData dbo.SomeType READONLY AS
BEGIN

    DECLARE @UID uniqueidentifier = NEWID();
    
    INSERT INTO dbo.SomeTable (SomeString, SomeInt, SomeDate, SomeGUID)
    SELECT SomeString,
           SomeInt,
           SomeDate,
           @UID
    FROM @SomeData;
END;
GO

现在是蟒蛇。为此,我编写了下面的简单脚本,参考this answer以获得一些帮助。我已经包含了完整的工作解决方案的整个脚本,但显然有些脚本不适用于您的脚本,并且您可能没有使用环境文件:

#!/usr/bin/env python3
#Import the bare minimums for this to work
import pyodbc, os
from dotenv import load_dotenv

#Get the details from my environemental file
load_dotenv()
SQLServer = os.getenv('SQL_SERVER')
SQLDatabase = os.getenv('SQL_DATABASE')
SQLLogin = os.getenv('SQL_LOGIN')
SQLPassword = os.getenv('SQL_PASSWORD')

#Create the full connection string
SQLConnString = 'Driver={ODBC Driver 17 for SQL Server};Server=' + SQLServer + ';Database=' + SQLDatabase + ';UID='+ SQLLogin +';PWD=' + SQLPassword

#Define the data that is going to be inserted into the table.
MyData = [('abc',1,'20200527'),('def',2,'20210527')]
#Turn it into a tuple. explained in https://stackoverflow.com/a/61156422/2029983
params = (MyData,) 

#And then execute the procedure, passing params as the parameters; which is a table
with pyodbc.connect(SQLConnString,timeout=20) as sqlConn:
    with sqlConn.cursor() as cursor:
        cursor.execute('EXEC dbo.SomeProc ?;', params)
        sqlConn.commit()

然后,当我运行SELECT * FROM dbo.SomeTable时,我得到以下数据:

ID 字符串 SomeInt 某个日期 某些GUID
1 ABC 1 2020-05-27 945a3656-54ee-47a2-962c-7a34c7f50635
2 定义 2 2021-05-27 945a3656-54ee-47a2-962c-7a34c7f50635

相关文章