sql server:

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

model

"""
StudentListInfo.py
学生类
date 2023-06-16
edit: Geovin Du,geovindu, 涂聚文
ide:  PyCharm 2023.1 python 11
"""import datetime
from datetime import date
import sys
import os
import Commonclass StudentList(object):"""学生实体类"""def __init__(self,StudentId:int,StudentName:str, StudentNO:str,StudentBirthday:datetime.datetime):""":param StudentName::param StudentNO::param StudentBirthday:"""self._StudentName=StudentNameself._StudentNO=StudentNOself._StudentBirthday=StudentBirthdayself._StudentId=StudentIdself._age=0 #date.today().year-StudentBirthday.year #date.today().year-StudentBirthday.year #Common.Commond.calculateAge(date(StudentBirthday.year,StudentBirthday.month,StudentBirthday.day))def __init__(self,StudentId:int,StudentName:str, StudentNO:str,StudentBirthday:datetime.datetime,age:int):""":param StudentName::param StudentNO::param StudentBirthday:"""self._StudentName=StudentNameself._StudentNO=StudentNOself._StudentBirthday=StudentBirthdayself._StudentId=StudentIdself._age=age #date.today().year-StudentBirthday.year #date.today().year-StudentBirthday.year #Common.Commond.calculateAge(date(StudentBirthday.year,StudentBirthday.month,StudentBirthday.day))def __del__(self):""":return:"""print(f"{self._StudentName}")def setStudentName(self,StudentName):""":param StudentName::return:"""self._StudentName = StudentNamedef getStudentName(self):""":return:"""return self._StudentNamedef setStudentNO(self,StudentNO):""":param StudentNO::return:"""self._StudentNO=StudentNOdef getStudentNO(self):""":return:"""return self._StudentNOdef setStudentId(self,StudentId):""":param StudentId::return:"""self._StudentId=StudentIddef getStudentId(self):""":return:"""return  self._StudentIddef setStudentBirthday(self,StudentBirthday):""":param StudentBirthday::return:"""self._StudentBirthday = StudentBirthdaydage =date.today().year-StudentBirthday.year# Common.Commond.calculate_age(StudentBirthday)self._age=dagedef getStudentBirthday(self):""":return:"""return self._StudentBirthdaydef setAge(self,age):""":param age::return:"""dage=1 #Common.Commond.calculate_age(StudentBirthday)self._age = agedef getAge(self):""":return:"""return self._agedef __str__(self):""":return:"""return f"{self._StudentId},{self._StudentName},{self._StudentNO},{self._StudentBirthday}{self._age}"

DAL

"""
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
import Interface.IStudentListclass StudentDal(Interface.IStudentList.IStudentList):"""数据业务处理层  学生数据库连接可以放在这里，通过配置读取数据连接参数"""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 *,DATEDIFF(hour,StudentBirthday,GETDATE())/8766 as Age from DuStudentList;')return rowdef 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 *,DATEDIFF(hour,StudentBirthday,GETDATE())/8766 as Age from DuStudentList order by {order};')return rowdef selectIdSql(self,StudentId:int):""":param StudentId: 主键ID:return:"""myms = UtilitieDB.MsSQLHelper.MsSqlHelper()row=myms.execute(f'select * from DuStudentList where StudentId={StudentId};')return rowdef selectProc(self):"""存储过程:return:"""myms = UtilitieDB.MsSQLHelper.MsSqlHelper()args = ()row = myms.executeCallProc("proc_Select_StudentListAll",args)return rowdef selectIdProc(self,StudentId:int):"""存储过程:param StudentId: 主键ID:return:"""myms = UtilitieDB.MsSQLHelper.MsSqlHelper()args = (StudentId,)row = myms.executeCallProc('dbo.proc_Select_StudentList', args)return rowdef 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=0myms = 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 iddef 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":6where = 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 =studentIdmyms.deleteProc("dbo.proc_Delete_StudentList",args)

IDAL

"""
IStudentList.py
接口层 Interface Data Access Layer
IDAL(Interface Data Access Layer)DAL的接口层
date 2023-06-19
edit: Geovin Du,geovindu, 涂聚文
ide:  PyCharm 2023.1 python 11
"""
from __future__ import annotations
from abc import ABC, abstractmethod
import os
import sys
import Model.StudentListInfoclass IStudentList(ABC):""""""@classmethoddef __subclasshook__(cls, subclass):return (hasattr(subclass, 'load_data_source') andcallable(subclass.load_data_source) andhasattr(subclass, 'extract_text') andcallable(subclass.extract_text) orNotImplemented)@abstractmethoddef selectSql(self):""":return:"""pass@abstractmethoddef selectSqlOrder(self, order: str) -> list:""":param order::return:"""pass@abstractmethoddef selectIdSql(self, StudentId: int):""":param StudentId::return:"""pass@abstractmethoddef selectProc(self):""":return:"""pass@abstractmethoddef selectIdProc(self, StudentId: int):""":param StudentId::return:"""pass@abstractmethoddef addSql(self, info: Model.StudentListInfo.StudentList):""":param info::return:"""pass@abstractmethoddef addProc(self, info: Model.StudentListInfo.StudentList):""":param info::return:"""pass@abstractmethoddef addOutProc(self, info: Model.StudentListInfo.StudentList):""":param info::return:"""pass@abstractmethoddef editSql(self, info: Model.StudentListInfo.StudentList):""":param info::return:"""pass@abstractmethoddef editProc(self, info: Model.StudentListInfo.StudentList):""":param info::return:"""pass@abstractmethoddef delSql(self, StudentId: int):""":param StudentId::return:"""pass@abstractmethoddef delProc(self, studentId):""":param studentId::return:"""pass

BLL

"""
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
import Interface.IStudentList
import Factory.AbstractFactoryclass StudentBll(object):"""学生信息操作业务类"""dal=Factory.AbstractFactory.AbstractFactory.createStudentList#dal =DAL.StudentListDAL.StudentDal() #Factory.AbstractFactory.AbstractFactory.createStudentList()#"""类属性 操作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[0],i[1],i[2],i[3],i[4])'''#print(stus)for ii in stus:for i in ii:students.append(Model.StudentListInfo.StudentList(i[0],i[1],i[2],i[3],i[4]))return studentsdef 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],i[4]))return studentsdef selectIdSql(cls,StudentId:int)->list:""":param StudentId:学生ID:return:"""students = []data = cls.dal().selectIdSql(StudentId)students=datafor ii in students:for i in ii:students.append(Model.StudentListInfo.StudentList(i[0],i[1],i[2],i[3]))return studentsdef 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],i[4]))return studentsdef 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 studentsdef 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)

UI

"""
StudentUI.py
读文件类
date 2023-06-24
edit: Geovin Du,geovindu, 涂聚文
ide:  PyCharm 2023.1 python 11"""import datetime
import sys
import os
from tkinter import ttk
from tkinter import *
from tkinter.ttk import *
from ttkbootstrap import Style  # pip install ttkbootstrap
import random
import Model.StudentListInfo
import BLL.StudentListBLLclass StudentUi(object):global treestubll = BLL.StudentListBLL.StudentBll()def __init__(self):self.name="geovindu"def __del__(self):print(f"{self.name}")def delete(cls):#global treecurItem = cls.tree.focus()val=cls.tree.item(curItem)['values'][0]  #idprint(val)print(cls.tree.selection())cls.tree.delete(cls.tree.selection())cls.stubll.delSql(val)  #需要删除关联的数据才可以删除#cls.stubll.delSql()def main(cls):"""窗体绑定数据:return:"""style=Style(theme='darkly') #定义窗口样式window=style.masterwindow.title("学生管理")# win = Tk()screenWidth = window.winfo_screenwidth()screenHeight = window.winfo_screenheight()width=100height=600x=int((screenWidth-width)/2)y=int((screenHeight-height)/2)window.geometry('{}x{}+{}+{}'.format(width,height,x,y))#Treeview 控件cls.tree=ttk.Treeview(master=window,style='success.Treeview',height=25,show='headings')cls.tree.pack()#定义列cls.tree['columns']=("StudentId","StudentName","StudentNO","StudentBirthday","Age")#设置列属性，列不显示cls.tree.column("StudentId",width=150,minwidth=100,anchor=S)cls.tree.column("StudentName", width=150, minwidth=100, anchor=S)cls.tree.column("StudentNO", width=150, minwidth=100, anchor=S)cls.tree.column("StudentBirthday", width=150, minwidth=100, anchor=S)cls.tree.column("Age", width=150, minwidth=100, anchor=S)#设置表头cls.tree.heading("StudentId",text="序号")cls.tree.heading("StudentName", text="姓名")cls.tree.heading("StudentNO", text="学号")cls.tree.heading("StudentBirthday", text="出生日期")cls.tree.heading("Age", text="年龄")# stubll = BLL.StudentListBLL.StudentBll()geovindu = cls.stubll.selectSqlOrder("Age asc")  # list()#treeView控件绑定数据i=1for Model.StudentListInfo.StudentList in geovindu:cls.tree.insert("",i,text="2",values=(Model.StudentListInfo.StudentList.getStudentId(),Model.StudentListInfo.StudentList.getStudentName(),Model.StudentListInfo.StudentList.getStudentNO(),Model.StudentListInfo.StudentList.getStudentBirthday(),Model.StudentListInfo.StudentList.getAge()))i+=1#删除按钮ttk.Button(window,text="删除",style='success,TButton',command=cls.delete).pack(side='left',padx=5,pady=10)window.mainloop()

输出“