python: more Layer Architecture and its Implementation in SQLite

CREATE TABLE DuStudentList (
	StudentId	INTEGER PRIMARY KEY AUTOINCREMENT,
	StudentName	TEXT NOT NULL,
	StudentNO	TEXT NOT NULL,
	StudentBirthday	DATETIME
	
);

"""
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 Common

class StudentList(object):
    """
    学生实体类
    """



    def __init__(self,StudentId:int,StudentName:str, StudentNO:str,StudentBirthday:datetime.datetime):
        """

        :param StudentName:
        :param StudentNO:
        :param StudentBirthday:
        """
        self._StudentName=StudentName
        self._StudentNO=StudentNO
        self._StudentBirthday=StudentBirthday
        self._StudentId=StudentId
        self._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=StudentName
        self._StudentNO=StudentNO
        self._StudentBirthday=StudentBirthday
        self._StudentId=StudentId
        self._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 = StudentName


    def getStudentName(self):
        """

        :return:
        """
        return self._StudentName

    def setStudentNO(self,StudentNO):
        """

        :param StudentNO:
        :return:
        """
        self._StudentNO=StudentNO


    def getStudentNO(self):
        """

        :return:
        """
        return self._StudentNO

    def setStudentId(self,StudentId):
        """

        :param StudentId:
        :return:
        """
        self._StudentId=StudentId


    def getStudentId(self):
        """

        :return:
        """
        return  self._StudentId

    def setStudentBirthday(self,StudentBirthday):
        """

        :param StudentBirthday:
        :return:
        """
        self._StudentBirthday = StudentBirthday
        dage =date.today().year-StudentBirthday.year# Common.Commond.calculate_age(StudentBirthday)
        self._age=dage


    def getStudentBirthday(self):
        """

        :return:
        """
        return self._StudentBirthday

    def setAge(self,age):
        """

        :param age:
        :return:
        """
        dage=1 #Common.Commond.calculate_age(StudentBirthday)
        self._age = age


    def getAge(self):
        """

        :return:
        """
        return self._age

    def __str__(self):
        """

        :return:
        """
        return f"{self._StudentId},{self._StudentName},{self._StudentNO},{self._StudentBirthday}{self._age}"

"""
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.SQLiteHelper
import Interface.IStudentList

class 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
        select StudentId,StudentName,StudentNO,StudentBirthday,(julianday('now')-julianday(StudentBirthday))/365 as Age from DuStudentList;
        :return:
        """

        myms = UtilitieDB.SQLiteHelper.SqliteHelper()
        row=myms.execute("select *,(julianday('now')-julianday(StudentBirthday))/365 as Age from DuStudentList;")
        return row

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

        :param order:  studenName desc/asc
        :return:
        """
        students=[]
        myms = UtilitieDB.SQLiteHelper.SqliteHelper()
        strsql=f"select *,(julianday('now')-julianday(StudentBirthday))/365 as Age from DuStudentList order by {order};"
        row=myms.execute(f"select *,(julianday('now')-julianday(StudentBirthday))/365 as Age from DuStudentList order by {order};")
        return row

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

        :param StudentId: 主键ID
        :return:
        """
        myms = UtilitieDB.SQLiteHelper.SqliteHelper()
        row=myms.execute(f'select * from DuStudentList where StudentId={StudentId};')
        return row


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




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

        :param info:学生实体类
        :return:
        """
        myms = UtilitieDB.SQLiteHelper.SqliteHelper()
        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 delSql(self,StudentId:int):
        """
        sql语句删除
        :param StudentId: 主键ID
        :return:
        """
        myms = UtilitieDB.SQLiteHelper.SqliteHelper()
        where={f"StudentId":StudentId}
        myms.deleteByKeyValues("DuStudentList",where)

"""
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.StudentListInfo

class IStudentList(ABC):
    """

    """

    @classmethod
    def __subclasshook__(cls, subclass):
        return (hasattr(subclass, 'load_data_source') and
                callable(subclass.load_data_source) and
                hasattr(subclass, 'extract_text') and
                callable(subclass.extract_text) or
                NotImplemented)

    @abstractmethod
    def selectSql(self):
        """

        :return:
        """
        pass

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

        :param order:
        :return:
        """
        pass

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

        :param StudentId:
        :return:
        """
        pass




    @abstractmethod
    def addSql(self, info: Model.StudentListInfo.StudentList):
        """

        :param info:
        :return:
        """
        pass




    @abstractmethod
    def editSql(self, info: Model.StudentListInfo.StudentList):
        """

        :param info:
        :return:
        """
        pass



    @abstractmethod
    def delSql(self, StudentId: int):
        """

        :param StudentId:
        :return:
        """
        pass

"""
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.AbstractFactory

class 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 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],i[4]))
        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 addSql(cls,info:Model.StudentListInfo.StudentList):
        """

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





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

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



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

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

"""
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.StudentListBLL

class StudentUi(object):

    global tree
    stubll = BLL.StudentListBLL.StudentBll()

    def __init__(self):
        self.name="geovindu"

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

    def delete(cls):
        #global tree
        curItem = cls.tree.focus()
        val=cls.tree.item(curItem)['values'][0]  #id
        print(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.master
        window.title("学生管理")
        # win = Tk()
        screenWidth = window.winfo_screenwidth()
        screenHeight = window.winfo_screenheight()
        width=800
        height=600
        x=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=1
        for 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()