Python code doesn’t run the SQL stored procedure completely

I am not proficient in Python but I have written a python code that executes a stored procedure (SQL server) which within it contains multiple stored procedures therefore it usually takes 5 mins or so to run on SSMS.

I can see the stored procedure runs halfway through without error when I run the Python code which makes me think that somehow it needs more time to execute when coding in python.

I found other posts where people suggested subprocess but I don’t know how to code this. Below is an example of a (not mine) python code to execute the stored procedure.

mydb_lock = pyodbc.connect('Driver={SQL Server Native Client 11.0};'
                       'Server=localhost;'
                       'Database=InterelRMS;'
                       'Trusted_Connection=yes;'
                       'MARS_Connection=yes;'
                       'user=sa;'
                       'password=Passw0rd;')

mycursor_lock = mydb_lock.cursor()
sql_nodes = "Exec IVRP_Nodes"
mycursor_lock.execute(sql_nodes)
mydb_lock.commit()

How can I edit the above code to use the subprocess? Is the subprocess the right choice? Any other method you can suggest?

Many thanks.

Python 2.7 and 3 SQL Server

UPDATE 04/04/2022:

@AlwaysLearning, I tried

 NEWcnxn   = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password+';Connection Timeout=0')

But there was no change. What I noticed is that to check how much of the code it executes, I inserted the following two lines of code right after each other somewhere in the nested procedure where I thought the SP stopped.

INSERT INTO  CheckTable (OrgID,Stage,Created) VALUES(@OrgID,2.5331,getdate()) 
INSERT INTO  CheckTable (OrgID,Stage,Created) VALUES(@OrgID,2.5332,getdate())

Only the first query is completed. I use Azure DB if that helps.

UPDATE 05/04/2022:

I tried what @AlwaysLearning suggested, after my connection, I added, NEWconxn.timeout=4000 and it’s working now

Answer

I tried what @AlwaysLearning suggested, after my connection, I added, NEWconxn.timeout=4000 and it’s working now. Many thanks.

Advertisement

Answer