Background: I am building a complex data analysis tool, and part of the process is to run some pretty deep and complex SQL from python. I am using pyodbc to connect to a SQL server instance hosted on Azure. This code has a loop containing a lot of calculation and row creation.
When I execute the SQL code batch in a GUI tool, in this case SQLPro or MSSQL (which I believe uses the JDBC driver), it completes all expected 25 iterations and proceeds to the post-loop code. However, when I execute the same batch in python via pyodbc and cursor.execute, the loop only executes 5 times before completing, and the code afterwards does not execute (or not fully).
The SQL loop code looks like this:
declare @loop_id int = 1
while (select count(*) from dbo.items_remaining) > 0
begin
...
delete from dbo.items_remaining where ...
insert into dbo.step_tracker select getdate(), @loop_id, 'loop complete'
select @loop_id = @loop_id + 1
end
I have investigated every difference and angle I can think of, without success. Ultimately, I need to make the code work when being called from python, but I would happily take any advice I can get for debugging. Thanks!
