make python wait for stored procedure to finish executing

Question

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

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

I call this stored procedure inside a loop, and I notice that sometimes, the procedure gets called again before it was finished executing the last time. I know this because if I add the line

time.sleep(1)

after the execute line, everything works fine.

Is there a more elegant and less time-costly way to say, "sleep until the exec is finished"?

Update (Divij's solution): This code is currently not working 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

I know this is old, but I just spent several hours trying to figure out how to make my Python code wait for a stored proc on MSSQL to finish.

The issue is not with asynchronous calls.

The key to resolving this issue is to make sure that your procedure does not return any messages until it's finished running. Otherwise, PYDOBC interprets the first message from the proc as the end of it.

Run your procedure with SET NOCOUNT ON. Also, make sure any PRINT statements or RAISERROR you might use for debugging are muted.

Add a BIT parameter like @muted to your proc and only raise your debugging messages if it's 0.

In my particular case, I'm executing a proc to process a loaded table and my application was exiting and closing the cursor before the procedure finished running because I was getting row counts and debugging messages.

So to summarize, do something along the lines of

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

and PYODBC will wait for the proc to finish.