I've googled around a bit, but maybe I didn't put the correct magik incantation into the search box.
Does anyone know how to get output parameters from a stored procedure in Python? I'm using pymssql to call a stored procedure, and I'm not sure of the correct syntax to get the output parameter back. I don't think I can use any other db modules since I'm running this from a Linux box to connect to a mssql database on a MS Server.
import pymssql
con = pymssql.connect(host='xxxxx',user='xxxx',password='xxxxx',database='xxxxx')
cur = con.cursor()
query = "EXECUTE blah blah blah"
cur.execute(query)
con.commit()
con.close()
The Output Parameters in Stored Procedures are used to return some value or values. A Stored Procedure can have any number of output parameters. The simple logic is this — If you want to return 1 value then use 1 output parameter, for returning 5 values use 5 output parameters, for 10 use 10, and so on.
Output parameter is a parameter whose value is passed out of the stored procedure/function module, back to the calling PL/SQL block. An OUT parameter must be a variable, not a constant. It can be found only on the left-hand side of an assignment in the module.
You can use one or more RETURN statements in a stored procedure. The RETURN statement can be used anywhere after the declaration blocks within the SQL-procedure-body. To return multiple output values, parameters can be used instead. Parameter values must be set prior to the RETURN statement being executed.
To call a stored procedure from a Python application, use ibm_db. callproc function. The procedure that you call can include input parameters (IN), output parameters (OUT), and input and output parameters (INOUT).
pymssql v2.x offers limited support for callproc
. It supports OUTPUT parameters using the pymssql.output()
parameter syntax. Note, however, that OUTPUT parameters can only be retrieved with callproc
if the stored procedure does not also return a result set. That issue is discussed on GitHub here.
Given the T-SQL stored procedure
CREATE PROCEDURE [dbo].[myDoubler]
@in int = 0,
@out int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT @out = @in * 2;
END
the Python code
import pymssql
conn = pymssql.connect(
host=r'localhost:49242',
database='myDb',
autocommit=True
)
crsr = conn.cursor()
sql = "dbo.myDoubler"
params = (3, pymssql.output(int, 0))
foo = crsr.callproc(sql, params)
print(foo)
conn.close()
produces the following output
(3, 6)
Notice that callproc
returns the parameter tuple with the OUTPUT parameter value assigned by the stored procedure (foo[1]
in this case).
If the stored procedure returns one or more result sets and also returns output parameters, we need to use an anonymous code block to retrieve the output parameter value(s):
Stored Procedure:
ALTER PROCEDURE [dbo].[myDoubler]
@in int = 0,
@out int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT @out = @in * 2;
-- now let's return a result set, too
SELECT 'foo' AS thing UNION ALL SELECT 'bar' AS thing;
END
Python code:
sql = """\
DECLARE @out_value INT;
EXEC dbo.myDoubler @in = %s, @out = @out_value OUTPUT;
SELECT @out_value AS out_value;
"""
params = (3,)
crsr.execute(sql, params)
rows = crsr.fetchall()
while rows:
print(rows)
if crsr.nextset():
rows = crsr.fetchall()
else:
rows = None
Result:
[('foo',), ('bar',)]
[(6,)]
If you cannot or don't want to modify the original procedure and have access to the database you can write a simple wrapper procedure that is callable from python.
For example, if you have a stored procedure like:
CREATE PROC GetNextNumber
@NextNumber int OUTPUT
AS
...
You could write a wrapper like so which is easily callable from python:
CREATE PROC GetNextNumberWrap
AS
DECLARE @RNextNumber int
EXEC GetNextNumber @RNextNumber
SELECT @RNextNumber
GO
Then you could call it from python like so:
import pymssql
con = pymssql.connect(...)
cur = con.cursor()
cur.execute("EXEC GetNextNumberWrap")
next_num = cur.fetchone()[0]
I'm not a python expert but after a brief perusing of the DB-API 2.0 I believe you should use the "callproc" method of the cursor like this:
cur.callproc('my_stored_proc', (first_param, second_param, an_out_param))
Then you'll have the result in the returned value (of the out param) in the "an_out_param" variable.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With