SQLAlchemy Executing Stored Procedure with Output

Question

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.

Thanks. I tried something similar above. I cant use callproc since odbc doesnt have it, so I used execute. I keep getting "SystemError: <class 'pyodbc.Error'> returned a result with an error set". code here — Nicholas G
– Nicholas G, Commented Aug 31, 2022 at 19:38

Gord Thompson · Accepted Answer · 2022-08-31 21:59:56Z

1

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

answered Aug 31, 2022 at 21:59

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Nicholas G Over a year ago

Good point about the anonymous code. I'll be sure to specify everything in the future. Can I ask how you knew or discovered to use the syntax above (":p1" and a param dictionary) rather than the "?" and param list in the linked posts? Thanks for the help.

Gord Thompson Over a year ago

docs.sqlalchemy.org/en/14/tutorial/…

Nicholas G Over a year ago

Altered question for stored procedure to include insert statement because it wont execute when stored procedure is called from sqlalchemy.

Nicholas G Over a year ago

Solution: need to set autocommit to True. See my edits above for pyodbc and sqlalchemy syntax.

Collectives™ on Stack Overflow

SQLAlchemy Executing Stored Procedure with Output

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related