0

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!

0

1 Answer 1

1

Turns out the culprit here was pyodbc. Switching to pymssql fixed it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.