Execute SQL while loop with pyodbc

Question

I wrote a fairly simple SQL while loop and tried to submit it via pyodbc cursor. But it didn't work, while working perfectly fine in SQL Server Management Studio.

My understanding is that one cannot pass more than one statement with the cursor. But then how does one execute a SQL while loop? I know I can do the below query with the while loop inside the python by cursor.rowcount, but my question is about generic queries with various SQL functions (like while here).

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

• AlwaysLearning: What do you mean it doesn't work? What error message do you get? Also, why use conn.autocommit=True and cursor.commit()? Choose one or the other.
• guyguyguy12345: @AlwaysLearning It only deletes the first time, it doesn't perform the looping. I can see that not all are deleted as they should.
• Parfait: What is the point of query? Are you intending to delete all rows in that table on that date? If so, why the loop?

Answer 1

Try testing your rowcount condition after the commit transaction; statement. The following works for me...

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

Which outputs...

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

Executing select count(1) from StackOverflow.dbo.DeleteExample in SSMS returns a count of 0.