Python SQLAlchemy 使用输出执行存储过程

我正在try 执行一个存储过程，并使用PYTHON的SQLALCHEMY模块访问它的输出.

我找到了this post个，但不能让它运行得没有错误.

SQL Stored Procedure个

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

Python个个

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)

The error it returned个

TypeError:‘BindParameter’类型的对象没有len()

上述异常是以下异常的直接原因:

SystemError:&lt；类‘pyodbc.Error’&gt；返回了设置了错误的结果

Edit:多亏了mkleehammer's github，我成功地让它与pyodbc一起工作.如果有人知道，我仍然想知道如何使用SqlalChemy来做到这一点.

pyodbc code个

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语句，则Edit2:Gord的解决方案不起作用.它返回求和的值，但不执行INSERT语句.我try 将INSERT作为普通命令和字符串中的INSERT，然后使用EXEC(@Query).我还确保授予用户对NumbersTable的SELECT和INSERT权限.

我还try 了上面的pyodbc代码，但没有执行INSERT命令，但返回了正确的总和.

SOLUTION:我发现有this post个人表示需要将自动提交设置为True.这既适用于pyodbc代码，也适用于sqlalChemy代码.

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;

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

Python个个

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中.

Executing Stored Procedure in SQL as same user个

EXECUTE AS login = 'UserTest'

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

这将输出总和并将该行插入到NumbersTable中.