pypyodbc-在循环中执行存储过程时，游标状态无效 [英] pypyodbc - Invalid cursor state when executing stored procedure in a loop

问题描述

我有一个使用pypyodbc与MSSQL数据库进行交互的python程序.存储过程在MSSQL中创建，并通过python运行.如果我只执行一次存储过程(通过python)，就没有问题.但是，当它在for循环中多次执行时，出现以下错误:

I have a python program which uses pypyodbc to interact with MSSQL database. A stored procedure is created in MSSQL and is run via python. If I execute the stored procedure only once (via python), there are no problems. However, when it is executed multiple times within a for loop, I get the following error:

pypyodbc.ProgrammingError :("24000"，"[24000] [Microsoft] [SQL Server Native Client 11.0]无效的游标状态")

pypyodbc.ProgrammingError: ('24000', '[24000] [Microsoft][SQL Server Native Client 11.0]Invalid cursor state')

我的python代码详细信息如下:

My python code details are below:

connection_string_prod = 'Driver={SQL Server Native Client 11.0};Server=PSCSQCD1234.TEST.AD.TEST.NET\SQLINS99,2222;Database=Test;Uid=python;Pwd=python;'

connection_prod = pypyodbc.connect(connection_string_prod)

cursor_prod = connection_prod.cursor()

get_files_to_load_query = "Get_Files_To_Load"

files_to_load = cursor_prod.execute(get_files_to_load_query)

for file_name in files_to_load:
    load_query = "Load_Query_Stored_Proc @File_Name = '" + file_name + "'"
    cursor_prod.execute(load_query)

cursor_prod.commit()
cursor_prod.close()
connection_prod.close()

在某些帖子中，建议在SQL存储过程的顶部使用"SET NOCOUNT ON".我已经做过了，对解决这个问题没有帮助.

In some posts it was suggested to use "SET NOCOUNT ON" at the top of the SQL stored procedure. I've done that already and it did not help with this issue.

以下是导致问题的简单存储过程的代码:

Below is a code of a simple stored procedure that is causing the issue:

CREATE PROCEDURE [dbo].[Test]
AS
SET NOCOUNT ON

INSERT INTO Test(a)
SELECT 1

为什么仅在 循环中执行存储过程会导致问题?

Why executing the stored procedure within a for loop only causes an issue?

请告知.

谢谢！

推荐答案

您正在使用cursor_prod值来控制for循环，然后在该循​​环中使用同一光标对象 来运行存储过程，从而使用于控制循环的游标的先前状态无效.循环的第一个迭代是光标被覆盖的地方，这就是为什么直到第二次尝试通过循环后才遇到错误的原因.

You are using cursor_prod values to control the for loop and then using that same cursor object inside the loop to run the stored procedure, thus invalidating the previous state of the cursor for controlling the loop. The first iteration of the loop is where the cursor gets overwritten, which is why you don't encounter the error until you try to go through the loop a second time.

您不需要创建第二个连接，但是您需要使用第二个 cursor 来执行存储过程.或者，您可以使用.fetchall将所有文件名填充到files_to_load 列表中，然后遍历该列表，从而释放光标来运行存储过程.

You don't need to create a second connection, but you do need to use a second cursor to execute the stored procedure. Or, you could use .fetchall to stuff all of the file names into a files_to_load list and then iterate over that list, freeing up the cursor to run the stored procedure.

这篇关于pypyodbc-在循环中执行存储过程时，游标状态无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！