Introduction to methods of executing stored procedures and obtaining return values ​​in Python

This article mainly introduces the method of executing stored procedures and obtaining the return value of stored procedures in Python. It summarizes and analyzes the common methods of calling stored procedures in Python and related operation precautions in the form of examples. Friends in need can refer to the following

The example in this article describes the method of executing stored procedures and obtaining the return value of stored procedures in Python. Share it with everyone for your reference, the details are as follows:

How to execute stored procedures in Pathon? You can use the following method:

The stored procedure definition is basically as follows:

ALTER  procedure [dbo]. [mysp]
 @Station varchar ( 50),
 @SN varchar ( 50),
 @Info varchar ( 500) output ,
@Msg varchar ( 500) output

1. Use adodbapi

from adodbapi import connect
server = 'dbserver'
user = 'username'
password = 'password'
database = 'database'
sp = 'sp'
station = 'station'
sn = 'sn'
try :
  db = connect('Provider=SQLOLEDB.1 ;Data Source=%s;Initial Catalog=%s;/
            User ID=%s;Password=%s;'%(server, database, user, password))
except Exception, e:
  print e
else :
  cur = db.cursor()
  msg = cur.callproc(sp, (station, sn)) #参数与存储过程有关
  if len(msg) > 1 :
    if msg[-1 ] is None :
      print 'sn is ok. Can be test at this station'
    else :
      print msg[-1 ]
finally :
  try : db.close()
  except : pass

2. Using pymssql

from pymssql import connect
server = 'dbserver'
user = 'user'
password = 'password'
database = 'database'
sp = 'sp'
station = 'station'
sn = 'sn'
sql = ['set nocount on' ]
sql.append('declare @Msg varchar(500)' )
sql.append('declare @return_value varchar' )
sql.append("exec @return_value = %s @Station = '%s', @SN = '%s', @Info = '@Info', @Msg = @Msg output" )
sql.append('select @Msg, @return_value' )
sql = '/n' .join(sql) % (sp, station, sn)
def ffchk(server, user, password, database, sql):
  try :
    db = connect(host = server,
          database = database,
          user = user,
          password = password,
          login_timeout = 10 )
    cur = db.cursor()
    cur.execute(sql)
  except Exception, e:
    print e
  else :
    cur.nextset() # 要加上这句才能通过fetch函数取到值   注意：这语句在Python2.7对应的pymssql版本中是错误！！！
    print cur.fetchone()
  finally :
    try : db.close()
    except : pass

Summary:

1. Adodbapi is simple, no need to write a string of sql statement, but I can’t get the returned Error Code, I can only get the returned error message, or maybe I haven’t found the method

2. Contrary to the first one, pymssql can get the return value and error message , but you need to write sql statements.

Here we focus on the method of using pymssql

cur.execute("exec stored procedure name@parameter 1=XXX, @parameter 2='YYY'...")
cur.fetchone() #If there is a result set returned in the stored procedure, you can call this sentence. If the stored procedure is return, pymssql does not support it and cannot be obtained. This sentence is wrong
conn.commit() # If there is no such sentence, the data does not really take effect. For example, the stored procedure executes insert. If there is no commit, the data will not be actually inserted.

Generally, many requirements for using stored procedures can be dealt with through these three steps.

The above is the detailed content of Introduction to methods of executing stored procedures and obtaining return values ​​in Python. For more information, please follow other related articles on the PHP Chinese website!