Make python wait for the stored procedure to complete execution

Question

I have a python script that uses pyodbc to call an MSSQL stored procedure, for example:

cursor.execute("exec MyProcedure @param1 = '" + myparam + "'") 

I call this stored procedure inside a loop, and sometimes notice that the procedure is called again before it is completed for the last time. I know this because if I add a line

 time.sleep(1) 

after the execution line everything works fine.

Is there a more elegant and less expensive way to say, "Sleep until completion of exec"?

Update (Divij solution): This code does not currently work for me:

 from tornado import gen import pyodbc @gen.engine def func(*args, **kwargs): # connect to db cnxn_str = """ Driver={SQL Server Native Client 11.0}; Server=172.16.111.235\SQLEXPRESS; Database=CellTestData2; UID=sa; PWD=Welcome!; """ cnxn = pyodbc.connect(cnxn_str) cnxn.autocommit = True cursor = cnxn.cursor() for _ in range(5): yield gen.Task(cursor.execute, 'exec longtest') return func() 

Answer 1

Here is my workaround:

In the database, I create a table called RunningStatus with only one field, status , which is bit , and only one row, initially set to 0.

At the beginning of my stored procedure, I execute the line

 update RunningStatus set status = 1; 

And at the end of the stored procedure

 update RunningStatus set status = 0; 

In my Python script, I open a new connection and cursor to the same database. After my execute line, I just add

 while 1: q = status_check_cursor.execute('select status from RunningStatus').fetchone() if q[0] == 0: break 

You need to create a new connection and cursor, because any calls from the old connection will interrupt the stored procedure and could potentially cause status never return to 0.

It's a bit janky, but it works great for me!

Answer 2

There is no built-in python that allows you to wait for an asynchronous call to complete. However, you can achieve this using Tornado IOLoop. The Tornado gen interface allows you to register a function call as Task and return to the next line in your function after the call ends. Here is an example using gen and gen.Task

 from tornado import gen @gen.engine def func(*args, **kwargs) for _ in range(5): yield gen.Task(async_function_call, arg1, arg2) return 

In this example, func execution resumes after async_function_call completed. Thus, subsequent calls to asnyc_function_call will not overlap, and you will not have to pause the main process by calling time.sleep .

Answer 3

I think my path is a little rougher, but at the same time it is much easier to understand:

 cursor = connection.cursor() SQLCommand = ("IF EXISTS(SELECT 1 FROM msdb.dbo.sysjobs J JOIN msdb.dbo.sysjobactivity A ON A.job_id = J.job_id WHERE J.name ='dbo.SPNAME' AND A.run_requested_date IS NOT NULL AND A.stop_execution_date IS NULL) select 'The job is running!' ELSE select 'The job is not running.'") cursor.execute(SQLCommand) results = cursor.fetchone() sresult= str(results) while "The job is not running" in sresult: time.sleep(1) cursor.execute(SQLCommand) results = cursor.fetchone() sresult= str(results) 

while "SPNAME" returns "job not running" from the activity job table, sleep for 1 second and check the result again. this work for sql work, for SP should like in another table

Answer 4

I know this is old, but I spent several hours trying to figure out how to make my Python code wait for the saved process to complete in MSSQL.

The problem is not in asynchronous calls.

The key to solving this problem is to make sure that your procedure does not return any messages until it completes. Otherwise, PYDOBC interprets the first message from proc as its end.

Start the procedure using the SET NOCOUNT ON . In addition, make sure that all PRINT or RAISERROR you can use for debugging are disabled.

Add a BIT parameter such as @muted to your @muted and raise your debug messages only if it is @muted 0 .

In my specific case, I am executing a procedure to process the loaded table, and my application exited and closed the cursor before the procedure was completed, because I received the number of rows and debug messages.

To summarize, do something like

cursor.execute('SET NOCOUNT ON; EXEC schema.proc @muted = 1')

and PYODBC will wait for the process to complete.