Description of how to execute stored procedures and get return values in Python

This article mainly introduces the method of executing stored procedure and retrieving the return value of stored procedure in Python, summarizes and analyzes the common methods of Python call stored procedure and related operation considerations, and the need of friends can refer to the following

This example describes how to execute stored procedures in Python and get the return value of a stored procedure. Share to everyone for your reference, as follows:

How do stored procedures execute in Pathon? You can use the following methods:

The stored procedure definition is basically as follows:

ALTER  procedure [dbo]. [MYSP] @Station varchar, @SN varchar (@Info varchar) output, @Msg varchar ($) Output

1. Using Adodbapi

From adodbapi import connectserver = ' dbserver ' user = ' username ' password = ' password ' database = ' database ' sp = ' SP ' station = ' station ' sn = ' sn ' try:  db = Connect (' provider=sqloledb.1;D ata source=%s;initial catalog=%s;/            User id=%s; password=%s; '% (  Server, database, user, password)) except Exception, E:  print eelse:  cur = db.cursor ()  msg = Cur.callproc (sp, (station, SN)) #参数与存储过程有关  If Len (msg) > 1:    if msg[-1] is None: the      print ' sn is OK. Can is test at this station '    else:      print msg[-1]finally:  try:db.close ()  Except:pass

2. Using pymssql

From pymssql import connectserver = ' dbserver ' user = ' user ' password = ' password ' database = ' database ' sp = ' sp ' station = ' s Tation ' sn = ' sn ' sql = [' Set NOCOUNT on ']sql.append (' Declare @Msg varchar ') sql.append (' Declare @return_value varchar ') sql.append ("Exec @return_value =%s @Station = '%s ', @SN = '%s ', @Info = ' @Info ', @Msg = @Msg output") sql.append (' Sele CT @Msg, @return_value ') sql = '/n '. Join (SQL)% (SP, station, SN) def ffchk (server, user, password, database, SQL):  TR  Y:    db = connect (host = server,          database = database,          user = user,          password = password,          login_timeout =    cur = db.cursor ()    cur.execute (SQL)  except Exception, E:    print e  else:    Cur.nextset () # You have to add this to get a value from the FETCH function   NOTE: This statement is an error in the pymssql version of Python2.7!!!    print Cur.fetchone ()  finally:    try:db.close ()    Except:pass

Summarize:

1. Adodbapi simple, do not write a string of SQL statements, but can not get the return error Code, can only fetch the returned errors, or I could not find the method

2. Pymssql In contrast to the first, you can fetch the return value and the error message, but write the SQL statement.

Here's a note on how to use pymssql

Cur.execute ("Exec stored procedure name @ parameter 1=xxx, @ parameter 2= ' YYY ' ...")
Cur.fetchone () #如果该存储过程中有返回结果集, this sentence can be called, if the stored procedure is return, then pymssql does not support, cannot get, this sentence error
Conn.commit () #如果没有此句, the data does not actually take effect, such as a stored procedure that executes an insert, without a commit, and does not actually insert the data

Typically, this three-step process can address many of the requirements for using stored procedures