python调用带output参数的sql server的存储过程

背 景

  场景是这样的,我需要抽取某张表(mytable)数据，但是因为某些原因这张表的数据每次抽取前都需要先把其他表的数据先join处理后insert到这张表内；然再ETL到其他目标数据库；
  我的思路就是：

1. 写个SP来刷新数据；
2. 写个Python脚本ETL数据前先调用SP刷新数据，且打印出受影响数量，再ETL到目标服务器；
3. 自动化部署脚本。

  这里就重点讲讲python调用有返回值的SP的运用，整个SP的语句如下；

-- =============================================  
-- Author:  rowyet
-- Create date: 2020-0623 
-- Description: 信息变更
-- =============================================  
-- @ret 为output参数，记录受影响的行数
CREATE PROCEDURE dbo.result_refresh (@ret int output)  
AS  
BEGIN  
  
 set @ret = -1;  

 insert into mytable (  
 col1,  
 col2,  
 col3,  
 valid_status ,  
 create_time ,  
 last_update_time   
 )  
 select distinct  
  md.col1,   
  md.col2,   
  md.col3,  
  0,  
  getdate(),  
  getdate()  
 from dbo.table1 b  
 inner join dbo.table2  ml 
         on b.id= ml.bid
 inner join dbo.table3 md 
         on ml.mlid= md.mdid
 where b.valid_status >= 0  
  and ml.valid_status >= 0  

 set @ret = @@ROWCOUNT;  -- 将全局变量受影响的行数赋值给到@ret
END  

python调用流程

  这里我们使用python的pymssql包来调用SP,其实原理很简单，当我们在SQL Server的官方客户端软件SSMS上执行带OUTPUT参数的存储过程的时候，最官方的SQL语句如下；

USE [dw_love]
GO

DECLARE	@return_value int,
		@ret int

EXEC	@return_value = [dbo].[result_refresh]
		@ret = @ret OUTPUT

SELECT	@ret as N'@ret'

SELECT	'Return Value' = @return_value

GO

  那就好办了，那只要我们在python代码内拼接出这个sql代码，然后再交给pymssql包的执行函数去执行，再接收返回结果就行了，以下是具体的python代码，脚本文件名call_mssql_refresh_sp；

import pymssql

#call_mssql_refresh_sp文件

# 自定义SP的函数
def call_refresh_stage_id_sp(server, user, password, database, sql):
    #connect to mssql db
    try :
      db =pymssql.connect(server,user,password,database) #连接到sql server数据库
      cur = db.cursor() #获取该数据库连接下的环境变量
      cur.execute(sql) #执行语句
    except Exception as e:
      print(e)
    else :
      result = cur.fetchall()  #get result
      for i in result:
        print("新增数量和返回值:")
        print(i)  #list result

    finally :
      try :
        db.commit()  
        #一定要commit，pymssql包默认是需要手动commit的，否则事务不生效，此处坑死我了，因为python一直不生效，打印出来的语句在ssms上跑怎么跑怎么生效
        db.close() #关闭数据库连接
      except : pass



if __name__ =="__main__":

    #config message
    server='10.168.27.110'
    db='db_mon'
    user='dw_love'
    password='iloveyou123!'


    sp = '[dbo].[result_refresh]'  # SP name 最好带上[]符号，SQL SERVER的特性
    # 拼接调用SP的SQL SERVER语句
    sql=[]
    sql.append('DECLARE @return_value int,@ret int')
    sql.append('SELECT  @ret = 0' )
    sql.append("exec @return_value = %s   @ret = @ret output" )
    sql.append("select @ret 'new stageid amount', @return_value 'return_value'")

    sql = '\n'.join(sql) % (sp)
    print(sql)  #打印下自己拼接的sql语句
    call_refresh_stage_id_sp(server,user,password,db,sql) #调用SP函数

  利用python call_mssql_refresh_sp来调用脚本，结果如下；

DECLARE @return_value int,@ret int
SELECT  @ret = 0
exec @return_value = [assemble].[prc_StageRefresh]   @ret = @ret output
select @ret 'new stageid amount', @return_value 'return_value'
新增数量和返回值:
(248254, 0)

  整个python利用pymssql的调用带output参数的sql server的存储过程就圆满完成了。