sqlalchemy Stored Procedure with Output

Question

I want to call a stored procedure and receive the output parameter in python. I am using sqlAlchemy and can use parameters but do not know how to have the output be read into a variable. I understand that there is a outParam() attribute in sqlAlchemy, but I have not found a useful example.

Here is a simple SQL code for testing:

CREATE PROCEDURE [dbo].[Test]
    @numOne int,
    @numTwo int,
    @numOut int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SET @numOut = @numOne + @numTwo

END

And simple python:

engine = sqlalchemy.create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)
outParam = 0
result = engine.execute('Test ? ,?, ? OUTPUT', [1, 2, outParam])

outParam is still 0. I have tried modifying it with:

outParam = sqlalchemy.sql.outparam("ret_%d", type_=int)

But this produces a "Programming Error." What am I missing?

Z4-tier · Accepted Answer · 2020-01-13 16:24:59Z

2

SQLAlchemy returns a ResultProxy object. Try it like this:

engine = sqlalchemy.create_engine(...)
rproxy = engine.execute(...)
result = rproxy.fetchall()

result should be a list of RowProxy objects that you can treat like dictionaries, keyed on the column names from the query.

If you are looking for a true OUT param, your approach is almost correct. There is just a small error in the first parameter in the call to sqlalchemy.sql.outparam (but it is valid Python syntax). It should be like this:

outParam = sqlalchemy.sql.outparam("ret_%d" % i, type_=int)

Note the change to the first parameter: it just needed a value to substitute into the format string. The first parameter is the key value, and most likely %d is to be replaced with the column index number (which should be in i).

edited Jan 13, 2020 at 16:24

answered Jan 10, 2020 at 21:51

Z4-tier

8,0583 gold badges31 silver badges47 bronze badges

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

2 Comments

DanG Over a year ago

It's a fine work around that works well, but I am more interested in proper interfacing between SQL and Python

Z4-tier Over a year ago

@DanG If you are specifically looking to make the outparam call work, i updated this answer with some info on that. Your approach is almost correct, just a small problem that will only get caught at runtime, and probably only after the query gets passed to the database.

Collectives™ on Stack Overflow

sqlalchemy Stored Procedure with Output

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related