I am trying to execute a stored procedure and access its output using python's sqlalchemy module.

I found this post, but cannot get it to run without error.

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: object of type 'BindParameter' has no len()

The above exception was the direct cause of the following exception:

SystemError: <class 'pyodbc.Error'> returned a result with an error set

Edit: I have managed to get it working with pyodbc thanks to mkleehammer's github. I would still like to know how to do it with sqlalchemy if anyone knows.

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

Edit2: Gord's solution doesn't work if the stored procedure contains an insert statement. It returns the summed value, but the insert statement does not execute. I tried INSERT as a normal command as well as inside a string then using EXEC(@query). I also made sure that the user was granted SELECT and INSERT permissions for NumbersTable.

I also tried the pyodbc code above and the insert command did not execute, but the proper sum was returned.

SOLUTION: I found this post saying that auto commit needs to be set to True. This worked for both the pyodbc and sqlalchemy code.

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)

This returns 3, but the row is not inserted into 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

This outputs the sum and inserts the row into NumbersTable.

CodePudding user response：

Just use a slightly modified version the same anonymous code block from your plain-pyodbc solution:

import sqlalchemy as db

engine = db.create_engine("mssql pyodbc://scott:tiger^5HHH@mssql_199")

query = """\
SET NOCOUNT ON;
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.begin() as conn:
    result = conn.execute(db.text(query), params).scalar()
    print(result)  # 3