python: pymssql stored procedures insert output

发布时间 2023-06-17 19:24:06作者: ®Geovin Du Dream Park™

sql script:

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Insert_BookKindOut')
DROP PROCEDURE proc_Insert_BookKindOut
GO
CREATE PROCEDURE proc_Insert_InsuranceMoneyOut
(
    @InsuranceName NVarChar(1000),
    @InsuranceCost float,
    @IMonth Int,
    @ID Int output
)
AS
Begin
INSERT INTO InsuranceMoney
(
    [InsuranceName] ,
    [InsuranceCost],
    [IMonth]
)
    VALUES
(
    @InsuranceName,
    @InsuranceCost,
    @IMonth
)
SELECT @ID=@@IDENTITY
END
GO

  

    def insertOutProc(self,iobject):
        """
        插入操作 返回值 存储过程 https://github.com/pymssql/pymssql/issues/441
        python pymssql stored procedures insert output geovindu
        param:iobject 输入保险类
        :return:
        """

        conn = pymssql.connect(
            server=self._strserver,
            user=self._struser,
            password=self._strpwd,
            database=self._strdatabase
        )
        cursor = conn.cursor()
        #cursor.callproc("")
        outid=pymssql.output(int)
        args=(iobject.getInsuranceName(), iobject.getInsuranceCost(), iobject.getIMonth(), outid)  #pymssql.output
        #cursor.execute(f"exec 存储过程名称 @参数1='xxx',@参数2='xxx',@参数3='xxx',@参数4='xxx'")
        newout=cursor.callproc("dbo.proc_Insert_InsuranceMoneyOut",args)
        print(newout[3])  #  ('医疗', 900, 3, 221)
        conn.commit()
        conn.close()
        return newout[3]

  

sql = """INSERT INTO [database].[file name].[table name] ([column1],[column2],[column3],[column4]...)
                                  VALUES({value1},{value2},'{value3}',{value4}...);
                                                """.format(
    value1=Parameter 1,
    value2=Parameter 2,
    value3=Parameter 3,
    value4=Parameter 3,
    ...)
cursor.execute(sql)
conn.commit()
conn.close()

import pymssql
"""Configuration"""
server='xxx'
user ='xxx'
password='xxx'
database='xxx'
"""Connect to the database"""
conn = pymssql.connect(server, user, password, database)
cursor = conn.cursor()
cursor.execute(f"exec stored procedure name @Parameter1='xxx',@Parameter2='xxx',@Parameter3='xxx',@Parameter4='xxx'")
result = cursor.fetchall()  #Get the result set
for i in result:
  print(i)  #Traverse and print the data of the query result set