使用pyodbc循环时执行SQL

Question

我写了一个相当简单的SQLwhile循环，并试图通过pyodbc游标提交它。但它并没有起作用，尽管它在SQLServerManagementStudio中运行得非常好。

我的理解是，不能用游标传递多个语句。但是，如何执行SQL while循环呢？我知道我可以通过cursor.rowcount在python内部使用while循环进行以下查询，但我的问题是关于使用各种SQL函数的通用查询(如这里的while(。

conn = get_output_conn(env=ENVIRONMENT)
conn.autocommit=True
cursor = conn.cursor()
query = """WHILE 1 = 1
BEGIN
BEGIN TRANSACTION;
DELETE TOP(2000)
FROM table with(holdlock)
WHERE ReportDate = '2020-08-23';
IF @@ROWCOUNT < 1 BREAK;
COMMIT TRANSACTION;
END"""
cursor.execute(query)
cursor.commit()

Answer 1

尝试在commit transaction;语句之后测试您的rowcount条件。以下对我有效…

import pyodbc
conn = pyodbc.connect(
autoCommit=False,
driver="/usr/local/lib/libtdsodbc.so",
tds_version="7.4",
database="StackOverflow",
port=...,
server="...",
user="...",
password="..."
)
query1 = """drop table if exists dbo.DeleteExample;"""
cursor1 = conn.cursor()
cursor1.execute(query1)
cursor1.commit()
cursor1.close()
query2 = """
select cast('2020-08-23' as date) as ReportDate
into dbo.DeleteExample
from sys.objects a, sys.objects b"""
cursor2 = conn.cursor()
cursor2.execute(query2)
# About 10,000 rows depending on your database
print(cursor2.rowcount, "rows inserted")
cursor2.commit()
cursor2.close()
query3 = """
declare @RowCount int;
while 1=1
begin
begin transaction t1;
delete top (2000)
from dbo.DeleteExample
where ReportDate = '2020-08-23';
set @RowCount = @@RowCount;
commit transaction t1;
if @RowCount < 1 break;
end"""
cursor3 = conn.cursor()
cursor3.execute(query3)
# "2000" which only is the first rowcount...
print(cursor3.rowcount, "rows deleted")
cursor3.commit()
cursor3.close()

哪些输出。。。

% python ./example.py
(10609, 'rows inserted')
(2000, 'rows deleted')

在SSMS中执行select count(1) from StackOverflow.dbo.DeleteExample将返回计数0。