python: sql server using Model,BLL,DAL

sql script:

/*
学生类:姓名、年龄、学号、成绩

班级类:班级名称、学生列表显示所有学生
根据学号查找学生
添加一个学生
删除一个学生(学生对象、学号)
根据学号升序排序
根据成绩降序排序

*/

--学生表
DROP TABLE StudentList
GO
create table StudentList
(
	 StudentId INT IDENTITY(1,1) PRIMARY KEY,
	 StudentName nvarchar(50),
	 StudentNO varchar(50),                    --学号
	 StudentBirthday datetime				   --学生生日
	 

)
go

insert into StudentList(StudentName,StudentNO,StudentBirthday) values(N'刘三','001','2007-12-27')
go
insert into StudentList(StudentName,StudentNO,StudentBirthday) values(N'王二','002','2008-2-14')
go

select * from StudentList
go


--课程名称
DROP TABLE Course
GO
create table Course
(
   CourseId INT IDENTITY(1,1) PRIMARY KEY,
   CourseName nvarchar(50)					--课程名称
)
go

insert into Course(CourseName) values(N'语文')
go
insert into Course(CourseName) values(N'英语')
go
insert into Course(CourseName) values(N'数学')
go

select * from Course
go




--班表名称
DROP TABLE GradeClass
GO
create table GradeClass
(
	ClassId INT IDENTITY(1,1) PRIMARY KEY,
	ClassName nvarchar(50)				--班级名称
)
go

insert into GradeClass(ClassName) values(N'二年级一班')
go

insert into GradeClass(ClassName) values(N'二年级二班')
go

select * from GradeClass
go



--成绩表
DROP TABLE StudentScore
GO
create table StudentScore
(
	 ScoreId INT IDENTITY(1,1) PRIMARY KEY,
	 StudentId int
		Foreign Key REFERENCES StudentList(StudentId),  --学生ID 外键
	 CourseId int
	    Foreign Key REFERENCES Course(CourseId),      --课程ID 外键
	 Score float									  --成绩
)
go

insert into StudentScore(StudentId,CourseId,Score) values(1,1,90)
insert into StudentScore(StudentId,CourseId,Score) values(1,2,56)
insert into StudentScore(StudentId,CourseId,Score) values(1,3,80)
go

insert into StudentScore(StudentId,CourseId,Score) values(2,1,92)
insert into StudentScore(StudentId,CourseId,Score) values(2,2,83)
insert into StudentScore(StudentId,CourseId,Score) values(2,3,78)
go

select * from StudentScore
go




---班级学生
DROP TABLE StudentClass
GO
create table StudentClass
(
	StudentClassId INT IDENTITY(1,1) PRIMARY KEY,
	StudentId int 
		Foreign Key REFERENCES StudentList(StudentId),   --学生ID 外键
	ClassId int 
		Foreign Key REFERENCES GradeClass(ClassId),		--班级ID 外键
)
go

insert into StudentClass(StudentId,ClassId) values(1,1)
go
insert into StudentClass(StudentId,ClassId) values(2,1)
go
select * from StudentClass
go

-- 成绩视图
select a.*,b.StudentNO,b.StudentName,b.StudentBirthday,c.CourseName from StudentScore as a--,as a,StudentList as b,Course as c StudentClass as d,GradeClass as f  
LEFT JOIN StudentList as b on a.StudentId=b.StudentId 
LEFT JOIN Course as c on a.CourseId =c.CourseId
LEFT JOIN StudentClass as d on d.StudentId=b.StudentId
go

-- 班级视图
select a.*,b.StudentNO,b.StudentNO,b.StudentName,d.ClassName from StudentClass as a
left join StudentList as b on a.StudentId=b.StudentId
left join GradeClass as d on a.ClassId=d.ClassId
go

　　

"""
StudentListInfo.py
学生类
date 2023-06-16
edit: Geovin Du,geovindu, 涂聚文
ide:  PyCharm 2023.1 python 11
"""

import datetime
import sys
import os

class StudentList(object):
    """
    学生类
    """
    def __init__(self,StudentName:str, StudentNO:str,StudentBirthday:datetime.datetime):
        self._StudentName=StudentName
        self._StudentNO=StudentNO
        self._StudentBirthday=StudentBirthday
        self._StudentId=0
        self._age=0

    def __del__(self):
        print(f"{self._StudentName}")

    def setStudentName(self,StudentName):
        self._StudentName=StudentName

    def getStudentName(self):
        return self._StudentName

    def setStudentNO(self,StudentNO):
        self._StudentNO=StudentNO

    def getStudentNO(self):
        return self._StudentNO

    def setStudentId(self,StudentId):
        self._StudentId=StudentId

    def getStudentId(self):
        return  self._StudentId

    def setStudentBirthday(self,StudentBirthday):
        self._StudentBirthday=StudentBirthday

    def setAge(self,age):
        self._age=age

    def getAge(self):
        return  self._age

    def __str__(self):
        return f"{self._StudentId},{self._StudentName},{self._StudentNO},{self._StudentBirthday}{self._age}"



"""
CourseInfo.py
课程类
date 2023-06-16
edit: Geovin Du,geovindu, 涂聚文
ide:  PyCharm 2023.1 python 11
"""

class Course(object):
    """

    """

    def __init__(self,CourseName:str):
        self._CourseName=CourseName
        self._CourseId=0

    def __del__(self):
        print(f"{self._CourseName}")

    def setCourseId(self,CourseId):
        self._CourseId=CourseId

    def getCourseId(self):
        return self._CourseId

    def setCourseName(self,CourseName):
        self._CourseName=CourseName

    def getCourseName(self):
        return self._CourseName

    def __str__(self):
        return f"CourseId:{self._CourseId},CourseName:{self._CourseName}"

　　

 

"""
StudentDALListDAL.py
数据业务处理层 Data Access Layer (DAL)
SQL Server 数据库操作
date 2023-06-21
edit: Geovin Du,geovindu, 涂聚文
ide: PyCharm 2023.1 python 11
"""

import os
import sys
from pathlib import Path
import re
import pymssql  #sql server
import Model.StudentListInfo
import UtilitieDB.MsSQLHelper

class StudentDal(object):
    """
    数据业务处理层  学生
    数据库连接可以放在这里，通过配置读取数据连接参数
    """

    def __init__(self):
        """
        构造函数，方法
        :param strserver:
        :param struser:
        :param strpwd:
        :param strdatabase:
        """
        self._strserver = ""
        self._struser = ""
        self._strpwd = ""
        self._strdatabase =""

    def selectSql(self):
        """
        查询数据 self._strserver, self._struser, self._strpwd, self._strdatabase
        :return:
        """

        myms = UtilitieDB.MsSQLHelper.MsSqlHelper()
        row=myms.execute('select * from DuStudentList;')
        return row

    def selectSqlOrder(self,order:str)->list:
        """

        :param order:  studenName desc/asc
        :return:
        """
        students=[]
        myms = UtilitieDB.MsSQLHelper.MsSqlHelper()
        strsql=f"select * from DuStudentList order by {order};"
        row=myms.execute(f'select * from DuStudentList order by {order};')
        return row

    def selectIdSql(self,StudentId:int):
        """

        :param StudentId: 主键ID
        :return:
        """
        myms = UtilitieDB.MsSQLHelper.MsSqlHelper()
        row=myms.execute(f'select * from DuStudentList where StudentId={StudentId};')
        return row
    def selectProc(self):
        """
        存储过程
        :return:
        """
        myms = UtilitieDB.MsSQLHelper.MsSqlHelper()
        args = ()
        row = myms.executeCallProc("proc_Select_StudentListAll",args)
        return row

    def selectIdProc(self,StudentId:int):
        """
        存储过程
        :param StudentId: 主键ID
        :return:
        """
        myms = UtilitieDB.MsSQLHelper.MsSqlHelper()
        args = (StudentId,)
        row = myms.executeCallProc('dbo.proc_Select_StudentList', args)
        return row

    def addSql(self,info:Model.StudentListInfo.StudentList):
        """
        添加，要考虑添加返回ID值
        :param info:学生实体类
        :return:
        """
        myms=UtilitieDB.MsSQLHelper.MsSqlHelper();
        column=("StudentName","StudentNO","StudentBirthday")
        vales=[info.getStudentName(),info.getStudentNO(),info.getStudentBirthday()]
        myms.insertByColumnaAndValues("dbo.DuStudentList",column,vales)

    def addProc(self,info:Model.StudentListInfo.StudentList):
        """
        添加，要考虑添加返回ID值
        :param info:学生实体类
        :return:
        """
        myms = UtilitieDB.MsSQLHelper.MsSqlHelper();
        args=(info.getStudentName(),info.getStudentNO(),info.getStudentBirthday())
        myms.insertCallProc("dbo.proc_Insert_StudentList",args)

    def addOutProc(self,info:Model.StudentListInfo.StudentList):
        """
        添加，要考虑添加返回ID值
        :param info:学生实体类
        :return: 返回增加的学生的ID
        """
        id=0
        myms = UtilitieDB.MsSQLHelper.MsSqlHelper();
        outid = pymssql.output(int)
        args = (info.getStudentName(), info.getStudentNO(), info.getStudentBirthday(),outid)
        print(args)
        id=myms.insertOutCallProc("dbo.proc_Insert_StudentListOutput", args)
        return id
    def editSql(self,info:Model.StudentListInfo.StudentList):
        """

        :param info:学生实体类
        :return:
        """
        myms = UtilitieDB.MsSQLHelper.MsSqlHelper();
        args = {"StudentName":f"{info.getStudentName()}","StudentNO":f"{info.getStudentNO()}","StudentBirthday":f"{info.getStudentBirthday()}"}  #"StudentId":6
        where = f"StudentId={info.getStudentId()}" #
        #print(args,where)
        myms.updateByKeyValues("DuStudentList",where,args)

    def editProc(self, info: Model.StudentListInfo.StudentList):
        """

        :param info: 学生实体类
        :return:
        """
        myms = UtilitieDB.MsSQLHelper.MsSqlHelper();
        args = (info.getStudentId(),info.getStudentName(),info.getStudentNO(),info.getStudentBirthday())
        myms.updateProc("[dbo].[proc_Update_StudentList]",args)


    def delSql(self,StudentId:int):
        """
        sql语句删除
        :param StudentId: 主键ID
        :return:
        """
        myms = UtilitieDB.MsSQLHelper.MsSqlHelper();
        where={f"StudentId":StudentId}
        myms.deleteByKeyValues("DuStudentList",where)

    def delProc(self, studentId):
        """
        删除 存储过程 删除多个ID，后面增加
        :param StudentId: 主键ID
        :return:
        """
        myms = UtilitieDB.MsSQLHelper.MsSqlHelper();
        args =studentId
        myms.deleteProc("dbo.proc_Delete_StudentList",args)



"""
StudentListBLL.py
业务层 Business Logic Layer (BLL)
date 2023-06-19
edit: Geovin Du,geovindu, 涂聚文
ide:  PyCharm 2023.1 python 11
"""

import os
import sys
from pathlib import Path
import re
import pymssql  #sql server
from datetime import date
import DAL.StudentListDAL
import DAL.ConfigDAL
import Model.StudentListInfo

class StudentBll(object):
    """
    学生信息操作业务类
    """

    dal = DAL.StudentListDAL.StudentDal()
    """
    类属性 操作DAL
    """
    def __init__(self):
        """

        """
        self._name = "geovindu"



    def __del__(self):
        print(f"{self._name}挂失了")

    def selectSql(cls)->list:
        """
        元组数据
        :return: list 学生列表
        """
        students = []
        data = cls.dal.selectSql()
        stus = list(data)  # 如C# 强制转换
        '''
        for a in data:
            for i in a:
                print("II",i)
        '''

        for ii in stus:
            for i in ii:
                students.append(Model.StudentListInfo.StudentList(i[0],i[1],i[2],i[3]))
        return students

    def selectSqlOrder(cls, order: str)->list:
        """
        元组数据
        :param order: studenName desc/asc
        :return:
        """
        studentsorder = []
        students=[]
        data = cls.dal.selectSqlOrder(order)
        (studentsorder) = data # 如C# 强制转换
        '''
        for i in range(len(studentsorder)):
            print("rrr",type(studentsorder[i]))
            for duobj in studentsorder[i]:
                print(type(duobj))
                print(duobj)
        '''
        for obj in studentsorder:
            for i in obj:
                students.append(Model.StudentListInfo.StudentList(i[0], i[1], i[2], i[3]))
        return students


    def selectIdSql(cls,StudentId:int)->list:
        """

        :param StudentId:学生ID
        :return:
        """
        students = []
        data = cls.dal.selectIdSql(StudentId)
        students=data
        for ii in students:
            for i in ii:
                students.append(Model.StudentListInfo.StudentList(i[0],i[1],i[2],i[3]))
        return students


    def selectProc(cls):
        """

        :return:
        """
        students=[]
        data = cls.dal.selectProc()
        for i in data:
            students.append(Model.StudentListInfo.StudentList(i[0],i[1],i[2],i[3]))
        return students

    def selectIdProc(cls,StudentId:int)->list:
        """

        :param StudentId:
        :return:
        """
        students = []
        data = cls.dal.selectIdProc(StudentId)
        for i in data:
            students.append(Model.StudentListInfo.StudentList(i[0],i[1],i[2],i[3]))
        return students

    def addSql(cls,info:Model.StudentListInfo.StudentList):
        """

        :param info:学生实体类
        :return:
        """
        cls.dal.addSql(info)

    def addProc(cls,info:Model.StudentListInfo.StudentList):
        """

        :param info:学生实体类
        :return:
        """
        #print(info)
        cls.dal.addProc(info)

    def addOutProc(cls,info:Model.StudentListInfo.StudentList)->int:
        """

        :param info: 学生实体类
        :return: 返回增加的学生ID
        """
        print(info)
        return cls.dal.addOutProc(info)

    def editSql(cls,info:Model.StudentListInfo.StudentList):
        """

        :param info:学生实体类
        :return:
        """
        #print(info)
        cls.dal.editSql(info)

    def editProc(cls, info: Model.StudentListInfo.StudentList):
        """

        :param info:学生实体类
        :return:
        """
        cls.dal.editProc(info)

    def delSql(cls, StudentId: int):
        """

        :param StudentId:
        :return:
        """
        cls.dal.delSql(StudentId)

    def delProc(cls, StudentId):
        """

        :param StudentId:
        :return:
        """
        cls.dal.delProc(StudentId)