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

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. — AlwaysLearning
– AlwaysLearning, Commented Aug 29, 2020 at 5:59
@AlwaysLearning It only deletes the first time, it doesn't perform the looping. I can see that not all are deleted as they should. — guyguyguy12345
– guyguyguy12345, Commented Aug 29, 2020 at 15:31
What is the point of query? Are you intending to delete all rows in that table on that date? If so, why the loop? — Parfait
– Parfait, Commented Aug 30, 2020 at 1:10

AlwaysLearning · Accepted Answer · 2020-08-30 00:01:51Z

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.

Collectives™ on Stack Overflow

Execute SQL while loop with pyodbc

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related