sql server 使用 pyodbc 返回值 [英] sql server return value with pyodbc
问题描述
我正在尝试使用 pyodbc
运行一些存储过程并使用以下代码获取单个返回值:
I am trying to run some stored proc with pyodbc
and get the single return value using following code:
conn = pyodbc.connect("driver={SQL Server};server=MyServer;database=MyDB;trusted_connection=true")
cursor = conn.cursor()
SQL_command = """
DECLARE @ret INT
EXEC @ret = [dbo].proc_mySP
@group= 0
, @description =?
SELECT @ret
"""
cursor.execute(SQL_command, description)
retValue = cursor.fetchall()
存储过程的框架如下:
-- SOME CODE
-- ......
-- EXEC another_sp
-- DECLARE @RET INT
-- SELECT @RET as retValue
-- ......
上面的sql在sql server中运行正常,但是当上面的Python代码调用它时,它给出了错误信息:
The above sql works fine in sql server, however, when it was called by the above Python code, it gives error messages:
pyodbc.ProgrammingError: ('24000', '[24000] [Microsoft][ODBC SQL服务器驱动程序]无效的游标状态 (0) (SQLNumResultCols)')
pyodbc.ProgrammingError: ('24000', '[24000] [Microsoft][ODBC SQL Server Driver]Invalid cursor state (0) (SQLNumResultCols)')
我可以知道我的代码有什么问题吗?
May I know what is wrong with my code?
非常感谢.
推荐答案
尝试通过 pyodbc 运行多语句 T-SQL 脚本可能会出现问题.即使这在 SSMS 中运行良好
Trying to run multi-statement T-SQL scripts via pyodbc can be problematic. Even though this works fine in SSMS
DECLARE @tbl AS TABLE (retVal INT);
INSERT INTO @tbl (retVal)
EXEC [dbo].proc_mySP
@group = 37,
@description = 'foo';
SELECT retVal FROM @tbl;
以下 Python 代码...
the following Python code ...
sql = """\
DECLARE @tbl AS TABLE (retVal INT);
INSERT INTO @tbl (retVal)
EXEC [dbo].proc_mySP
@group = 37,
@description = ?;
SELECT retVal FROM @tbl;
"""
crsr.execute(sql, ['foo'])
row = crsr.fetchone()
...失败
pyodbc.ProgrammingError:没有结果.以前的 SQL 不是查询.
pyodbc.ProgrammingError: No results. Previous SQL was not a query.
如果存储过程返回一个单列的单行结果集,那么你需要做的就是
If the stored procedure returns a single-row result set with a single column then all you need to do is
import pyodbc
cnxn = pyodbc.connect("DSN=myDb_SQLEXPRESS")
crsr = cnxn.cursor()
sql = """\
EXEC [dbo].proc_mySP
@group = 37,
@description = ?;
"""
crsr.execute(sql, ['foo'])
the_result = crsr.fetchone()[0]
print(the_result)
这篇关于sql server 使用 pyodbc 返回值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!