python: Call SQL Server Stored Procedure in Python

geovindu

已于 2023-06-17 18:51:04 修改

阅读量79

点赞数

分类专栏： Python 文章标签： python 开发语言

于 2023-06-17 18:46:49 首次发布

本文链接：https://blog.csdn.net/geovindu/article/details/131263485

版权

Python 专栏收录该内容

44 篇文章 0 订阅

订阅专栏

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))