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(涂聚文)
浙公网安备 33010602011771号