Python中执行存储过程及获取返回值的方法介绍

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

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