How do you get output parameters from a stored procedure in Python?

Question

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

Fibrinolysis

Answer 1

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.

Burkhalter

Argh... unfortunately, I get this error. It looks like pymssql doesn't have callproc. AttributeError: pymssqlCursor instance has no attribute 'callproc' – Fibrinolysis

Try to find another driver that is more DB-API 2.0 compliant. Start here - wiki.python.org/moin/SQL_Server – Burkhalter

It looks like it covers much of the same as this link: ramblings.timgolden.me.uk/2007/09/26/… None of the free/oss ones seem to support callproc yet or don't run on linux. I may need to look into a web-services solution. – Fibrinolysis

I think I read that callproc is not implemented currently for SQL Server when I googled around a bit on this. – Symposiarch

Which library does not implement 'callproc' (besides 'pymssql')? I just checked the adodbapi's source and there it seems to be implemented (I haven't actually tested the implementation tho). – Burkhalter

Will adodbapi work on Linux? It looks like it only runs on Windows. – Fibrinolysis

Just in case someone else see this answer, you must pass to callproc the output params wrapped like this: pymssql.output(int, -1) As described here: github.com/pymssql/pymssql/blob/… – Taveras

Answer 2

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]

Rowlett

I wonder if you can do it without auxiliary sproc, just with conn.execute("DECLARE @RNextNumber int; EXEC GetNextNumber @RNextNumber out; SELECT @RNextNumber"). – Trichology

Answer 3

If you make your procedure produce a table, you can use that result as a substitute for out params.

So instead of:

CREATE PROCEDURE Foo (@Bar INT OUT, @Baz INT OUT) AS
BEGIN
   /* Stuff happens here */
   RETURN 0
END

do

CREATE PROCEDURE Foo (@Bar INT, @Baz INT) AS
BEGIN
   /* Stuff happens here */
   SELECT @Bar Bar, @Baz Baz
   RETURN 0
END

Symposiarch

Answer 4

It looks like every python dbapi library implemented on top of freetds (pymssql, pyodbc, etc) will not be able to access output parameters when connecting to Microsoft SQL Server 7 SP3 and higher.

http://www.freetds.org/faq.html#ms.output.parameters

Rowlett

This answer is obsolete, at least for pymssql. – Glandular

Answer 5

I was able to get an output value from a SQL stored procedure using Python. I could not find good help getting the output values in Python. I figured out the Python syntax myself, so I suspect this is worth posting here:

import sys, string, os, shutil, arcgisscripting
from win32com.client import Dispatch
from adoconstants import *

#skip ahead to the important stuff

conn = Dispatch('ADODB.Connection')
conn.ConnectionString = "Provider=sqloledb.1; Data Source=NT38; Integrated Security = SSPI;database=UtilityTicket"
conn.Open()

#Target Procedure Example: EXEC TicketNumExists @ticketNum = 8386998, @exists output

Cmd = Dispatch('ADODB.Command')
Cmd.ActiveConnection = conn

Cmd.CommandType = adCmdStoredProc
Cmd.CommandText = "TicketNumExists"

Param1 = Cmd.CreateParameter('@ticketNum', adInteger, adParamInput)
Param1.Value = str(TicketNumber)
Param2 = Cmd.CreateParameter('@exists', adInteger, adParamOutput)

Cmd.Parameters.Append(Param1)
Cmd.Parameters.Append(Param2)

Cmd.Execute()

Answer = Cmd.Parameters('@exists').Value

Enosis

Answer 6

2016 update (callproc support in pymssql 2.x)

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.

For stored procedures that do not return a result set

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

For stored procedures that return a result set

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,)]

Glandular

Answer 7

You might also look at using SELECT rather than EXECUTE. EXECUTE is (iirc) basically a SELECT that doesn't actually fetch anything (, just makes side-effects happen).

Antoninus

Answer 8

You can try to reformat query:

import pypyodc

connstring = "DRIVER=SQL Server;"\
             "SERVER=servername;"\
             "PORT=1043;"\
             "DATABASE=dbname;"\
             "UID=user;"\
             "PWD=pwd"

conn = pypyodbc.connect(connString)
cursor = conn.cursor()

query="DECLARE @ivar INT \r\n" \
      "DECLARE @svar VARCHAR(MAX) \r\n" \
      "EXEC [procedure]" \
      "@par1=?," \
      "@par2=?," \
      "@param1=@ivar OUTPUT," \
      "@param2=@svar OUTPUT \r\n" \
      "SELECT @ivar, @svar \r\n"
par1=0
par2=0
params=[par1, par2]
result = cursor.execute(query, params)
print result.fetchall()

[1]https://amybughunter.wordpress.com/tag/pypyodbc/

Gabelle

Answer 9

Here's how I did it, the key is to declare output parameter first:

import cx_Oracle as Oracle

conn = Oracle.connect('xxxxxxxx')
cur = conn.cursor()

idd = cur.var(Oracle.NUMBER)
cur.execute('begin :idd := seq_inv_turnover_id.nextval; end;', (idd,))
print(idd.getvalue())

Alcock

Answer 10

I use pyodbc and then convert the pyodbc rows object to a list. Most of the answers show a query declaring variables as part of the query. But I would think you declare your variables as part of the sp, thus eliminating an unnecessary step in python. Then, in python, all you have to do is pass the parameters to fill in those variables.

Here is the function I use to convert the pyodbc rows object to a usable list (of lists) (note that I have noticed pyodbc sometimes adds trailing spaces, so I account for that which works well for me):

def convert_pyodbc(pyodbc_lst):
'''Converts pyodbc rows into usable list of lists (each sql row is a list),
   then examines each list for list elements that are strings,
   removes trailing spaces, and returns a usable list.'''
usable_lst = []
for row in pyodbc_lst:
    e = [elem for elem in row]
    usable_lst.append(e)
for i in range(0,len(usable_lst[0])):
    for lst_elem in usable_lst:
        if isinstance(lst_elem[i],str):
            lst_elem[i] = lst_elem[i].rstrip()
return usable_lst

Now if I need to run a stored procedure from python that returns a results set, I simply use:

strtdate = '2022-02-21'
stpdate = '2022-02-22'

conn = mssql_conn('MYDB')
cursor = conn.cursor()

qry = cursor.execute(f"EXEC mystoredprocedure_using_dates 
'{strtdate}','{stpdate}' ")
results = convert_pyodbc(qry.fetchall())

cursor.close()
conn.close()

And sample results which I then take and write to a spreadsheet or w/e:

[[datetime.date(2022, 2, 21), '723521', 'A Team Line 1', 40, 9], 
[datetime.date(2022, 2, 21), '723522', 'A Team Line 2', 15, 10], 
[datetime.date(2022, 2, 21), '723523', 'A Team Line 3', 1, 5], 
[datetime.date(2022, 2, 21), '723686', 'B Team Line 1', 39, 27], 
[datetime.date(2022, 2, 21), '723687', 'B Team Line 2', 12, 14]]

Bernhard