本文介紹了使用觸發(fā)器的單次插入/單次觸發(fā)在SQL Server表中插入多行的處理方法，對(duì)大家解決問題具有一定的參考價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)吧！
問題描述
我想要什么
能夠使用Python在SQL Server中將多行插入到表中，并且僅在插入所有行后才觸發(fā)INSTEAD OF INSERT觸發(fā)器。
背景
我的表上有一個(gè)INSTEAD OF INSERT觸發(fā)器，它對(duì)以前看不到的數(shù)據(jù)執(zhí)行操作。
通常的操作模式是定期一次插入多行。
當(dāng)我從python插入(使用pyodbc)時(shí)，如果我使用：
cursor.executemany('INSERT INTO table_name VALUE (?, ?, ...)', list_of_lists)
觸發(fā)器將針對(duì)每一行觸發(fā)–這不是我想要的。
我當(dāng)前正在使用：
sql = 'INSERT INTO table_name VALUES (...),(...)...;' # string built with format-strings
cursor.execute(sql)
這在理論上為我打開了SQL注入的大門，但我的環(huán)境受到了限制，因此實(shí)際上不是問題。
另外，我不知道查詢字符串的最大限制是多少。
我嘗試使用the method in this post找出極限，但返回的結(jié)果為空。所以我不確定是沒有限制還是未知。
我一次插入的行數(shù)預(yù)計(jì)不會(huì)太多(不超過1-2k行，少量列)。因此，我不認(rèn)為我會(huì)達(dá)到極限，但我不想稍后感到驚訝。
然后是BULK INSERT選項(xiàng)和FIRE_TRIGGERS選項(xiàng)，但這需要將我的數(shù)據(jù)作為文件上載到運(yùn)行SQL Server的計(jì)算機(jī)。這是我希望避免的復(fù)雜性。
有沒有更好的選擇，或者我當(dāng)前的解決方案還行嗎？
推薦答案
似乎可以使用表類型參數(shù)來執(zhí)行此操作。我要指出的是，我的Python不是很好，所以我不知道您是否可以在不使用的情況下使用存儲(chǔ)過程完成此。
不管怎樣，首先讓我們創(chuàng)建一個(gè)示例表：
CREATE TABLE dbo.SomeTable (ID int IDENTITY(1,1),
SomeString varchar(10),
SomeInt int,
SomeDate date,
SomeGUID uniqueidentifier);
GO
，然后是一個(gè)示例類型。我有意省略SomeGUID：
CREATE TYPE dbo.SomeType AS table (SomeString varchar(10),
SomeInt int,
SomeDate date);
然后，您需要?jiǎng)?chuàng)建一個(gè)接受上述TYPE作為參數(shù)的過程。在下面的代碼中，我為SomeGUID定義了整個(gè)數(shù)據(jù)集的靜態(tài)值，以演示在對(duì)最終結(jié)果集中的proc的一次調(diào)用中插入所有行：
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
現(xiàn)在是蟒蛇。為此，我編寫了下面的簡(jiǎn)單腳本，參考this answer以獲得一些幫助。我已經(jīng)包含了完整的工作解決方案的整個(gè)腳本，但顯然有些腳本不適用于您的腳本，并且您可能沒有使用環(huán)境文件：
#!/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()
然后，當(dāng)我運(yùn)行SELECT * FROM dbo.SomeTable時(shí)，我得到以下數(shù)據(jù)：
| ID | 字符串 | SomeInt | 某個(gè)日期 | 某些GUID |
|---|---|---|---|---|
| 1 | ABC | 1 | 2020-05-27 | 945a3656-54ee-47a2-962c-7a34c7f50635 |
| 2 | 定義 | 2 | 2021-05-27 | 945a3656-54ee-47a2-962c-7a34c7f50635 |
這篇關(guān)于使用觸發(fā)器的單次插入/單次觸發(fā)在SQL Server表中插入多行的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，
