如何从Python中的存储过程中获取输出参数?

I've googled around a bit, but maybe I didn't put the correct magik incantation into the search box.

我在google上搜索了一下，但也许我没有把正确的权限输入到搜索框中。

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.

有人知道如何从Python中的存储过程中获取输出参数吗?我正在使用pymssql调用一个存储过程，我不确定返回输出参数的正确语法。我不认为我可以使用任何其他的db模块，因为我正在运行一个Linux box来连接到一个MS服务器上的mssql数据库。

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

9 个解决方案

#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:

我不是python专家，但是在简要地阅读了DB-API 2.0之后，我认为您应该使用光标的“callproc”方法:

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.

然后，您将在“an_out_param”变量中获得返回值(out param)。

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

如果您不能或不想修改原始的过程并访问数据库，您可以编写一个简单的包装程序，该程序可以从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:

您可以编写这样的包装器，可以从python中轻松调用:

CREATE PROC GetNextNumberWrap
AS
    DECLARE @RNextNumber int
    EXEC GetNextNumber @RNextNumber
    SELECT @RNextNumber
GO

Then you could call it from python like so:

然后你可以从python中调用它:

import pymssql
con = pymssql.connect(...)
cur = con.cursor()
cur.execute("EXEC GetNextNumberWrap")
next_num = cur.fetchone()[0]

#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

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

看起来在freetds上实现的每个python dbapi库(pymssql、pyodbc等)在连接到Microsoft SQL Server 7 SP3或更高版本时都不能访问输出参数。

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

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

#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:

我能够使用Python从SQL存储过程中获取输出值。我找不到很好的帮助来获取Python中的输出值。我自己理解了Python语法，所以我认为这值得在这里发表:

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

#6

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

您还可以使用SELECT而不是EXECUTE。EXECUTE (iirc)基本上是一个不实际获取任何东西的SELECT(只会产生副作用)。

#7

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/

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

#8

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

#9

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.

pymssql v2。x对callproc的支持有限。它使用pymssql.output()参数语法支持输出参数。但是，请注意，只有当存储过程不返回结果集时，才能使用callproc检索输出参数。

For stored procedures that do not return a result set

Given the T-SQL stored procedure

给出了T-SQL存储过程。

CREATE PROCEDURE [dbo].[myDoubler] 
    @in int = 0, 
    @out int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @out = @in * 2;
END

the Python code

Python代码

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

注意，callproc返回带有存储过程分配的输出参数值的参数元组(在本例中为foo[1])。

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:

Python代码:

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