错误“以前的 SQL 不是查询"在 Python 中? [英] Error "Previous SQL was not a query" in Python?

问题描述

我试图在 Python 中调用存储过程，但它一直给我以下错误.该过程是用 SQL Server 2008 编写的，我使用 PyODBC 调用该方法并向其传递参数.

I am trying to call a stored procedure in Python but it keeps giving me the following error. The procedure is written in SQL Server 2008 and I am using PyODBC to call the method and pass parameters to it.

import pyodbc
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER='+serveripaddr+';DATABASE='+database+';UID='+userid+';PWD='+password+'')
cursor = cnxn.cursor()
cursor.execute("{call p_GetTransactionsStats('KENYA', '41')}")
rows = cursor.fetchall()

最后一行导致以下异常:

The last line results in the following exception:

ProgrammingError: No results.  Previous SQL was not a query.

这里可能有什么问题?

推荐答案

这是发生的事情.存储过程包含几个步骤.当它从 SQL Server Management Studio 执行时，很容易看到每个步骤如何产生一个单独的消息，例如 "(3 row(s)fluence)"，并且只有最后一步产生响应.

Here's what happens. The stored procedure contains several steps. When it is executed from the SQL Server Management studio, it is easy to see how each step results in a separate message such as "(3 row(s) affected)", and only the very last step produces the response.

显然，当通过 pyodbc 游标调用时，这些单独的步骤中的每一个都会生成一个单独的 resultset，其中所有结果集，但最后一个，不包含任何数据可以通过 fetchall() 读取.

Apparently, when invoked via pyodbc cursor, each of those separate steps produces a separate resultset, where all the resultsets, but the very last one, contain no data that could be read via fetchall().

因此，解决问题的一种选择是使用 nextset() 迭代这些结果集，直到找到确实产生结果的结果集，例如:

Hence, one option to solve the problem is to iterate over these resultsets using nextset() until you find one which does produce the result, e.g.:

while cursor.nextset():   # NB: This always skips the first resultset
    try:
        results = cursor.fetchall()
        break
    except pyodbc.ProgrammingError:
        continue

一个更好的选择是，正如在不同的答案中提到的，使用 SET NOCOUNT ON; 指令，这似乎阻止了所有中间的空 (# rows impacted) 结果集.该指令可以简单地添加到 proc 调用之前，例如:

A nicer option is, as mentioned in a different answer, to use the SET NOCOUNT ON; directive, which seems to prevent all of the intermediate, empty (# rows affected) resultsets. The directive can be simply prepended to the proc invocation, for example:

cursor.execute("set nocount on; exec MyStoredProc ?", some_parameter)
results = cursor.fetchall()

这篇关于错误“以前的 SQL 不是查询"在 Python 中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！