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?

Answer 1

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