python: Call SQL Server Stored Procedure

 

sql script:

DROP TABLE InsuranceMoney
GO
create table InsuranceMoney
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	InsuranceName nvarchar(50), 
	InsuranceCost float,
	IMonth int  
 )
 go

 insert into InsuranceMoney(InsuranceName, InsuranceCost, IMonth) values ('',200,1)
 go


 insert into InsuranceMoney(InsuranceName,InsuranceCost,IMonth) OUTPUT INSERTED.ID VALUES('geovindu',20,12);
 go

 select * from InsuranceMoney


 IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Insert_InsuranceMoney')
DROP PROCEDURE proc_Insert_InsuranceMoney
GO
CREATE PROCEDURE proc_Insert_InsuranceMoney
(
    --@BookKindID Int,
    @InsuranceName NVarChar(1000),
    @InsuranceCost float,
    @IMonth Int
)
AS
INSERT INTO InsuranceMoney
(
    [InsuranceName] ,
    [InsuranceCost],
    [IMonth]
)
    VALUES
(
    @InsuranceName ,
    @InsuranceCost,
    @IMonth
)
GO

exec proc_Insert_InsuranceMoney '养老',500,3
go

 select * from InsuranceMoney


IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Select_InsuranceMoney')
DROP PROCEDURE proc_Select_InsuranceMoney
GO
CREATE PROCEDURE proc_Select_InsuranceMoney
(
    @ID Int
)
AS
SELECT * FROM InsuranceMoney WHERE ID = @ID
GO

　　

"""
SQLServerDAL.py
SQL Server 数据库操作
date 2023-06-13
edit: Geovin Du,geovindu, 涂聚文
ide: PyCharm 2023.1 python 11
参考：https://learn.microsoft.com/zh-cn/sql/connect/python/pymssql/step-3-proof-of-concept-connecting-to-sql-using-pymssql?view=sql-server-ver16
DROP TABLE InsuranceMoney
GO
create table InsuranceMoney
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	InsuranceName nvarchar(50),
	InsuranceCost float,
	IMonth int
 )
 go

"""
import os
import sys
from pathlib import Path
import re
import pymssql  #sql server
import Insurance
import MsSQLHelper

class SQLclass(object):
    """
     Sql server 存储过程操作
    """

    def __init__(self, strserver, struser, strpwd, strdatabase):
        """

        :param strserver:
        :param struser:
        :param strpwd:
        :param strdatabase:
        """
        self._strserver = strserver
        self._struser = struser
        self._strpwd = strpwd
        self._strdatabase = strdatabase

    def selectdu(self):
        myms = MsSQLHelper.MsSqlHelper(self._strserver, self._struser, self._strpwd , self._strdatabase)
        # print(myms)
        # myms.select()
        row=myms.execute('select * from InsuranceMoney;')
        print(row)
        return row
        #while row:
            #print(str(row[0]) + " " + str(row[1]) + " " + str(row[2]))  #返回的是字典


    def select(self):
        """
         查询所有记录
        :return:
        """
        conn = pymssql.connect(

            server=self._strserver,
            user=self._struser,
            password=self._strpwd,
            database=self._strdatabase
            #server='DESKTOP-NQK85G5\GEOVIN2008',
            #user='sa',
            #password='geovindu',
            #database='Student'
        )
        cursor = conn.cursor()
        cursor.execute('select * from InsuranceMoney;')
        row = cursor.fetchone()
        while row:
            print(str(row[0]) + " " + str(row[1]) + " " + str(row[2]))
            row = cursor.fetchone()

    def selectIdProc(self,id):
        """
         查询ID记录 存储过程
        :return:
        """
        conn = pymssql.connect(

            server=self._strserver,
            user=self._struser,
            password=self._strpwd,
            database=self._strdatabase
            #server='DESKTOP-NQK85G5\GEOVIN2008',
            #user='sa',
            #password='geovindu',
            #database='Student'
        )
        cursor = conn.cursor()
        args=(id,)
        cursor.callproc("dbo.proc_Select_InsuranceMoney", args)
        row = cursor.fetchone()
        while row:
            print(str(row[0]) + " " + str(row[1]) + " " + str(row[2]))
            row = cursor.fetchone()

    def insertdu(self,iobject):
        """
        插入操作
        param:iobject 输入保险类
        :return:
        """
        dubojd = iobject
        myms = MsSQLHelper.MsSqlHelper(self._strserver, self._struser, self._strpwd , self._strdatabase)
        #myms.insertbyvalues('InsuranceMoney',)
        columns=('InsuranceName','InsuranceCost','IMonth')
        print(dubojd)
        print(columns)
        val=[dubojd.getInsuranceName(), dubojd.getInsuranceCost(), dubojd.getIMonth()]
        print(val)
        myms.insertbycolumnandvalues("InsuranceMoney",columns,val)


    def insert(self,iobject):
        """
        插入操作
        param:iobject 输入保险类
        :return:
        """

        conn = pymssql.connect(
            server=self._strserver,
            user=self._struser,
            password=self._strpwd,
            database=self._strdatabase
        )
        cursor = conn.cursor()
        #cursor.callproc("")
        cursor.execute(
            "insert into InsuranceMoney(InsuranceName,InsuranceCost,IMonth) OUTPUT INSERTED.ID VALUES ('{0}', {1}, {2})".format(
                iobject.getInsuranceName(), iobject.getInsuranceCost(), iobject.getIMonth()))
        row = cursor.fetchone()
        while row:
            print("Inserted InsuranceMoney ID : " + str(row[0]))
            row = cursor.fetchone()
        conn.commit()
        conn.close()

    def insertProc(self,iobject):
        """
        插入操作  存储过程 http://www.pymssql.org/en/stable/pymssql_examples.html
        https://kontext.tech/article/893/call-sql-server-procedure-in-python
        https://www.programmerall.com/article/493081049/
        param:iobject 输入保险类
        :return:
        """

        conn = pymssql.connect(
            server=self._strserver,
            user=self._struser,
            password=self._strpwd,
            database=self._strdatabase
        )
        cursor = conn.cursor()
        #cursor.callproc("")
        args=(iobject.getInsuranceName(), iobject.getInsuranceCost(), iobject.getIMonth())
        #cursor.execute(f"exec 存储过程名称 @参数1='xxx',@参数2='xxx',@参数3='xxx',@参数4='xxx'")
        cursor.callproc("dbo.proc_Insert_InsuranceMoney",args)
        conn.commit()
        conn.close()

    def insertStr(self,InsuranceName, InsuranceCost, IMonth):
        """
        插入操作
        param:InsuranceName
        param:InsuranceCost
        param:IMonth
        :return:
        """
        conn = pymssql.connect(
            server=self._strserver,
            user=self._struser,
            password=self._strpwd,
            database=self._strdatabase
        )
        cursor = conn.cursor()
        cursor.execute(
            "insert into InsuranceMoney(InsuranceName,InsuranceCost,IMonth) OUTPUT INSERTED.ID VALUES('{0}',{1},{2})".format(
                InsuranceName, InsuranceCost, IMonth))
        row = cursor.fetchone()
        while row:
            print("Inserted InsuranceMoney ID : " + str(row[0]))
            row = cursor.fetchone()
        conn.commit()
        conn.close()

　　

调用：

    s=["医疗",400,1]
    column=('InsuranceName','InsuranceCost','IMonth')
    tbname="InsuranceMoney"
    vls = str(s).replace("[", "").replace("]", "")
    cls = str(column).replace("'", "").replace("'", "")
    sqlstring = f"insert into {tbname} {cls} values ({vls})"
    print(sqlstring)

    ms=SQLServerDAL.SQLclass('DESKTOP-NQK85G5\GEOVIN2008','sa','geovindu','Student')
    #ms.selectdu()
    ms.selectIdProc(3)
    #ms.insertdu(Insurance.Insurance("医疗",400,1))
    ms.insertProc(Insurance.Insurance("医疗",800,5))