python: pymssql stored procedures insert output
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
哲学管理(学)人生, 文学艺术生活, 自动(计算机学)物理(学)工作, 生物(学)化学逆境, 历史(学)测绘(学)时间, 经济(学)数学金钱(理财), 心理(学)医学情绪, 诗词美容情感, 美学建筑(学)家园, 解构建构(分析)整合学习, 智商情商(IQ、EQ)运筹(学)生存.---Geovin Du(涂聚文)