Python - pyodbc调用具有参数名称的存储过程 [英] Python - pyodbc call stored procedure with parameter name
问题描述
我需要从 python2.7
通过 pyodbc 调用
SqlServer存储过程
/ code> module with input parameter name。
I need to call a SqlServer stored procedure
from python2.7
via pyodbc
module with input parameter name.
我根据文档按输入参数顺序:
I tried based on documentation by input parameter order:
cursor.execute('{CALL [SP_NAME](?,?)}',
('value', 'value'))
它有效,但是由于存储过程输入参数的顺序总是改变,我需要通过存储过程的参数名称。所以我需要通过名字传递他们。
It works, but I need to pass parameter name of stored procedure
because order of stored procedure input parameter always changes. So I need to pass them by name.
cursor.execute('{CALL [SP_NAME](@param1name,@param2name)}',
('value', 'value'))
工作
推荐答案
我使用SQL Server 2008 R2中的以下存储过程进行了测试:
I tested this using the following stored procedure in SQL Server 2008 R2:
CREATE PROCEDURE [dbo].[breakfast]
@person varchar(50) = 'nobody',
@food varchar(50) = 'tofu'
AS
BEGIN
SET NOCOUNT ON;
SELECT @person + ' likes to eat ' + @food
END
坏消息(CALL)
我发现
sql = """\
{ CALL breakfast (@food=?, @person=?) }
"""
params = ('bacon','Gord')
crsr.execute(sql, params)
给出不一致的结果。
使用 {SQL Server Native Client 10.0}
ODBC驱动程序,它忽略了参数名称并将参数视为位置,生成...
With the {SQL Server Native Client 10.0}
ODBC driver it ignored the parameter names and treated the parameters as positional, yielding ...
bacon likes to eat Gord
...与较旧的 {SQL Server}
ODBC驱动程序我刚刚收到错误
... and with the older {SQL Server}
ODBC driver I just got the error
DataError:('22018','[22018] [Microsoft SQL Server驱动程序]无效的角色值(0)(SQLExecDirectW)')
DataError: ('22018', '[22018] [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification (0) (SQLExecDirectW)')
好消息(EXEC strong>
The Good News ("EXEC")
我发现
sql = """\
EXEC breakfast @food=?, @person=?
"""
params = ('bacon','Gord')
crsr.execute(sql, params)
给了我以下(正确的)结果使用两个ODBC驱动程序
gave me the following (correct) result using both ODBC drivers
Gord likes to eat bacon
这篇关于Python - pyodbc调用具有参数名称的存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!