Python - pyodbc 调用带有参数名称的存储过程

问题描述

我需要使用输入参数名称通过pyodbc模块从python2.7调用SqlServer存储过程.

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

然而这行不通.正确的语法是什么?

However this doesn't work. What's the correct syntax?

推荐答案

我在 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 驱动程序，它忽略参数 names 并将参数视为位置参数，产生 ...

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][ODBC SQL Server Driver]转换规范的字符值无效 (0) (SQLExecDirectW)')

DataError: ('22018', '[22018] [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification (0) (SQLExecDirectW)')

好消息(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 调用带有参数名称的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！