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

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

From stackoverflow projecktzero

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

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

  Milen A. Radev : Try to find another driver that is more DB-API 2.0 compliant. Start here - http://wiki.python.org/moin/SQL_Server

  projecktzero : It looks like it covers much of the same as this link: http://ramblings.timgolden.me.uk/2007/09/26/using-mssql-from-within-python-25/ 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.

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

  Milen A. Radev : 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).

  projecktzero : Will adodbapi work on Linux? It looks like it only runs on Windows.
  From Milen A. Radev

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

  From Anders Eurenius

• 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
  

  From Tomalak

• 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

  From Paul D. Eden

• 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]
  

  Constantin : I wonder if you can do it without auxiliary sproc, just with conn.execute("DECLARE @RNextNumber int; EXEC GetNextNumber @RNextNumber out; SELECT @RNextNumber").
  From Paul D. Eden

• 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
  

  From M Deitemeyer