如何用SQLAlchemy调用带输出参数的存储过程并解决执行问题

我尝试用Python的SQLAlchemy模块调用带输出参数的SQL Server存储过程，获取输出结果时遇到错误，后续给存储过程新增INSERT语句后，又出现Python调用能返回计算结果但数据未插入的异常。

初始SQL存储过程 #

USE [TestDatabase]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[testPython]
    @numOne int,
    @numTwo int,
    @numSum int OUTPUT

AS
BEGIN
    LINENO 0
    SET NOCOUNT ON;

    SET @numSum = @numOne + @numTwo
END
GO

初始SQLAlchemy代码 #

import sqlalchemy as db
engine = db.create_engine(f"mssql+pyodbc://{username}:{password}@{dnsname}/{dbname}?driver={driver}")

with engine.connect() as conn:
    outParam = db.sql.outparam("ret_%d" % 0, type_=int)
    result = conn.execute('testPython ?, ?, ? OUTPUT', [1, 2, outParam])
    print(result)

错误提示 #

TypeError: object of type 'BindParameter' has no len()
上述异常直接导致了以下异常：
SystemError: <class 'pyodbc.Error'> returned a result with an error set

已实现的pyodbc版本 #

借助pyodbc文档，我已经用pyodbc实现了基础功能，但仍想搞懂SQLAlchemy的正确实现方式：

def testsp():
    query = """
    DECLARE @out int;
    EXEC [dbo].[testPython] @numOne = ?, @numTwo = ?, @numSum = @out OUTPUT;
    SELECT @out AS the_output;
    """
    params = (1,2,)

    connection = pyodbc.connect(f'DRIVER={DRIVER_SQL};SERVER={DNSNAME_SQL};DATABASE={DBNAME_SQL};UID={USERNAME_SQL};PWD={PASSWORD_SQL}',auto_commit=True)
    cursor = connection.cursor()
    cursor.execute(query, params)
    rows = cursor.fetchall()
    while rows:
        print(rows)
        if cursor.nextset():
            rows = cursor.fetchall()
        else:
            rows = None
    cursor.close()
    connection.close()

新增INSERT语句后的问题 #

当给存储过程加入INSERT语句后，无论用之前的SQLAlchemy方案还是上述pyodbc代码，都能正常返回求和结果，但INSERT操作并未执行。我试过常规INSERT和动态SQL两种写法，且确认用户拥有NumbersTable的SELECT和INSERT权限。

尝试设置autocommit的解决方案 #

查到资料说需要设置auto_commit为True，我对SQLAlchemy和pyodbc代码都做了修改，但问题依旧：

更新后的SQL存储过程 #

USE [TestDatabase]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[testPython]
    @numOne int,
    @numTwo int,
    @numSum int OUTPUT

AS
BEGIN
    LINENO 0

    SET NOCOUNT ON;

    -- 注释的动态SQL尝试
    -- DECLARE @query varchar(max)
    -- SET @query = 'INSERT INTO NumbersTable(num1, num2, numSum)
    --              VALUES(' + numOne + ', ' + numTwo ', ' + numSum')'
    -- print(@query)
    -- EXEC(@query)

    SET @numSum = @numOne + @numTwo

    INSERT INTO NumbersTable(num1, num2, numSum)
    VALUES(@numOne, @numTwo, @numSum)

    
END
GO

更新后的SQLAlchemy代码 #

import sqlalchemy as db

engine = db.create_engine(f"mssql+pyodbc://{username}:{password}@{dnsname}/{dbname}?driver={driver}&autocommit=true")

query = """
DECLARE @out int;
EXEC [dbo].[testPython] @numOne = :p1, @numTwo = :p2, @numSum = @out OUTPUT;
SELECT @out AS the_output;
"""
params = dict(p1=1,p2=2)

with engine.connect() as conn:
    result = conn.execute(db.text(query), params).scalar()
    print(result)

这段代码能返回计算结果3，但NumbersTable中没有新增数据。

SQL端直接测试结果 #

用同一用户在SQL Server中直接执行存储过程，既能得到求和结果，也能成功插入数据：

EXECUTE AS login = 'UserTest'

DECLARE @out int
EXEC [dbo].[testPython] @numOne = 0, @numTwo = 0, @numSum = @out OUTPUT
SELECT @out as the_output

内容的提问来源于stack exchange，提问作者Nicholas G