让python等待存储过程完成执行 [英] make python wait for stored procedure to finish executing

问题描述

我有一个使用 pyodbc 调用 MSSQL 存储过程的 python 脚本，如下所示:

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.

有没有更优雅、更省时的方式来表达一直睡到 exec 结束"?

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

更新(Divij 的解决方案):此代码目前对我不起作用:

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()

推荐答案

这是我的解决方法:

在数据库中，我创建了一个名为 RunningStatus 的表，只有一个字段，status，它是一个 bit，只有一行, 初始设置为 0.

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

在我的存储过程开始时，我执行了这一行

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

update RunningStatus set status = 1;

在存储过程的最后，

update RunningStatus set status = 0;

在我的 Python 脚本中，我打开了一个指向同一个数据库的新连接和游标.在我的 execute 行之后，我只需添加

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

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

你需要建立一个新的连接和游标，因为任何来自旧连接的调用都会中断存储过程并可能导致status永远不会回到0.

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

它有点笨拙，但对我来说很好用！

It's a little janky but it's working great for me!

这篇关于让python等待存储过程完成执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！