Executing stored procedures in sqlalchemy

Question

I am using a raw_connection in sqlalchemy to execute some SQL that runs a stored procedure.

the stored proc selects a parameter ID at the end. How can I catch this ID?

the python code is:

import sqlalchemy as sa

SQLCommand = """
    DECLARE @Msg varbinary(max) = CAST(%s AS varbinary(max) )
    EXECUTE dbo.usp_procedure @Type = 32, @header = %s, @store = @Msg
    """ % (Datacontent, var)


Engine = sa.create_engine(connection_string)
Connection = Engine.raw_connection()
cursor = Connection.cursor()
cursor.execute(SQLCommand)
return_key = list(cursor.fetchall())
Connection.commit()
Connection.close()

I thought return_key would contain the return code from usp_procedure but it errors out and I get:

No results.  Previous SQL was not a query.

The procedure has as a final step:

SELECT @ParamID

I want to be able to read this code back in my python code

I don't use `sql-server`, so I cannot check the real syntax, but I'd suggest you use an output parameter in your stored procedure, similar to [this SO question](http://stackoverflow.com/questions/191644/how-do-you-get-output-parameters-from-a-stored-procedure-in-python). If you just do a `print` in your stored procedure it will be more difficult to get back the value. Hope it helps. — lrnzcig, Jun 15 '15 at 11:43
Ok got the dev to amend the procedures to have an output parameter but still having issues. — KillerSnail, Jun 17 '15 at 01:53

score 1 · Answer 1 · answered Jun 17 '15 at 09:56

I don't have sql-serverand tested this only for oracle, however this is too long for a comment.

Created this simple stored procedure,

CREATE OR REPLACE PROCEDURE PROCEDURE1(inp IN VARCHAR2, outp OUT VARCHAR2) AS 
BEGIN
  IF (inp = 'true') THEN
    outp := '1';
    RETURN;
  END IF;
  IF (inp = 'false') THEN
    outp := '0';
    RETURN;
  END IF;
  outp := NULL;
  RETURN;
END PROCEDURE1;

and tested it with the following code:

Connection = c.manager.engine.raw_connection()
cursor = Connection.cursor()
result = cursor.callproc("PROCEDURE1", ['true', ''])
print(result[1])
result = cursor.callproc("PROCEDURE1", ['false', ''])
print(result[1])

The results are 1 and 0 as expected.

I've been browsing around and I'd expect that callproc is available for sql-server e.g. here but not honestly I'm not what sqlalchemy will be using. Give it a try, hope it helps.

MS SQL `pyodbc` does not implement `.callproc` so you need to work with `.execute`. That said the answer is correct for oracle alright (+1), and works for Postgres too (i.e. the way I used it). — grochmal, Oct 11 '17 at 11:13

Executing stored procedures in sqlalchemy

1 Answers1