python: more Layer Architecture in Python and sql server 2019

sql server:

create table School  -- 創建表
(
   SchoolId char(5) NOT NULL PRIMARY KEY,    
    SchoolName nvarchar(500) NOT NULL DEFAULT '', 
    SchoolTelNo  varchar(8)  NULL DEFAULT '' , 
  
); 
go

　　

model:

# encoding: utf-8
# 版权所有 2024 ©涂聚文有限公司
# 许可信息查看：言語成了邀功的功臣，還需要行爲每日來值班嗎？
# 描述：
# Author    : geovindu,Geovin Du 涂聚文.
# IDE       : PyCharm 2023.1 python 3.11
# OS        : windows 10
# Datetime  : 2024/10/12 22:53
# User      : geovindu
# Product   : PyCharm
# Project   : IctGame
# File      : model/school.py
# explain   : 学习

class SchoolInfo(object):
    """
    學校實體對象
    https://docs.python.org/3/library/functions.html
    https://docs.python.org/3/howto/descriptor.html
    """

    def __init__(self):
        """
        構造
        """
        self._SchoolId = None
        """
        primary key
        """
        self._SchoolName = None
        """
        學校名稱
        """
        self._SchoolTelNo = None
        """
        學校電號碼
        """

    @property
    def SchoolId(self):
        """
        primary key
        """
        return self._SchoolId

    @SchoolId.setter
    def SchoolId(self, schoolId):
        """
        primary key
        :param SchoolId:
        :return:
        """
        self._SchoolId = schoolId

    @property
    def SchoolName(self):
        """
        學校名稱
        """
        return self._SchoolName

    @SchoolName.setter
    def SchoolName(self, schoolName):
        """
        學校名稱
        :param SchoolName:
        :return:
        """
        self._SchoolName = schoolName

    @property
    def SchoolTelNo(self):
        """
        學校電話
        """
        return self._SchoolTelNo

    @SchoolTelNo.setter
    def SchoolTelNo(self, schoolTelNo):
        """
        學校電話 賦值
        :param SchoolTelNo:
        :return:
        """
        self._SchoolTelNo = schoolTelNo

    '''
    def ToString(self):
        """
        顯示一行實體對象
        :return:
        """
        return "{0}\t{1}\t{2}".format(self._SchoolId, self._SchoolName, self._SchoolTelNo)



    def __del__(self):
        print('{}对象已经被销毁'.format(self.__name__))

    '''
# test

　　

IDAL:

# encoding: utf-8
# 版权所有 2024 ©涂聚文有限公司
# 许可信息查看：言語成了邀功的功臣，還需要行爲每日來值班嗎？
# 描述：
# Author    : geovindu,Geovin Du 涂聚文.
# IDE       : PyCharm 2023.1 python 3.11
# OS        : windows 10
# Datetime  : 2024/10/31 20:40
# User      : geovindu
# Product   : PyCharm
# Project   : IctGame
# File      : school.py
# explain   : 学习

from __future__ import annotations
from abc import ABC, abstractmethod
import os
import sys
from model.school import SchoolInfo


class ISchool(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(cls):
        """

        :return:
        """
        pass

    @abstractmethod
    def selectSqlCount(cls) -> list:
        """
        查询数据 总数
        :return:
        """
        pass

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

        :param order:
        :return:
        """
        pass

    @abstractmethod
    def selectSort(cls,field:str,isOrder:bool)->list:
        """

        :param field SchoolId
        :param order:  desc/asc
        :return:
        """
        pass

    @abstractmethod
    def selectIdSql(cls, SchoolId: str):
        """

        :param StudentId:
        :return:
        """
        pass

    @abstractmethod
    def selectProc(cls):
        """

        :return:
        """
        pass

    @abstractmethod
    def selectIdProc(cls, SchoolId: str):
        """

        :param SchoolId:
        :return:
        """
        pass

    @abstractmethod
    def addSql(cls, info: SchoolInfo):
        """

        :param info:
        :return:
        """
        pass

    @abstractmethod
    def addProc(cls, info: SchoolInfo):
        """

        :param info:
        :return:
        """
        pass

    @abstractmethod
    def addOutProc(cls, info: SchoolInfo):
        """

        :param info:
        :return:
        """
        pass

    @abstractmethod
    def editSql(cls, info: SchoolInfo):
        """

        :param info:
        :return:
        """
        pass

    @abstractmethod
    def editProc(cls, info: SchoolInfo):
        """

        :param info:
        :return:
        """
        pass

    @abstractmethod
    def delSql(cls, SchoolId: str):
        """

        :param SchoolId:
        :return:
        """
        pass

    @abstractmethod
    def delProc(cls, SchoolId:str):
        """

        :param SchoolId:
        :return:
        """
        pass

　　

DAL:

# encoding: utf-8
# 版权所有 2024 ©涂聚文有限公司
# 许可信息查看：言語成了邀功的功臣，還需要行爲每日來值班嗎？
# 描述：
# Author    : geovindu,Geovin Du 涂聚文.
# IDE       : PyCharm 2023.1 python 3.11
# OS        : windows 10
# Datetime  : 2024/10/31 20:40
# User      : geovindu
# Product   : PyCharm
# Project   : IctGame
# File      : school.py
# explain   : 学习

import os
import sys
from pathlib import Path
import re
import pymssql  #sql server
from model.school import SchoolInfo
from DBUtility.MsSQLHelper import MsSqlHelper
from sqlserverinterface.school import ISchool

class SchoolDal(ISchool):
    """

    """
    myms = MsSqlHelper()

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

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


        row=cls.myms.execute("select * from School;")
        #cls.myms.close()
        return row

    def selectSqlCount(cls)->list:
        """
        查询数据 总数
        :return:
        """

        row=cls.myms.execute("select count(*) as total from School;")
        #cls.myms.close()
        return row[0]


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

        :param order:  SchoolId desc/asc
        :return:
        """
        students=[]

        strsql=f"select * from School order by {order};"
        row=cls.myms.execute(f"select * from School order by {order};")
        return row

    def selectSort(cls,field:str,isOrder:bool)->list:
        """

        :param field SchoolId
        :param order:  desc/asc
        :return:
        """
        order='desc'
        if isOrder==True:
            order='desc'
        else:
            order='asc'
        strsql=f"select * from School order by {field} {order};"
        row=cls.myms.execute(f"select * from School order by {field} {order};")
        #cls.myms.close()
        return row


    def selectIdSql(cls,SchoolId:str)->list:
        """

        :param StudentId: 主键ID
        :return:
        """

        row=cls.myms.execute(f"select * from School where SchoolId='{SchoolId}';")
        #cls.myms.close()
        return row

    def selectProc(cls)->list:
        """
        存储过程
        :return:
        """

        args = ()
        row = cls.myms.executeCallProc("procSelectSchoolAll",args)
        return row

    def selectIdProc(cls,SchoolId:str)->list:
        """
        存储过程
        :param SchoolId: 主键ID
        :return:
        """

        args = (SchoolId,)
        row = cls.myms.executeCallProc('procSelectSchool', args)
        return row

    def addSql(cls,info:SchoolInfo)->int:
        """
        添加，要考虑添加返回ID值
        :param info:实体类
        :return:
        """

        column=("SchoolId","SchoolName","SchoolTelNo")
        vales=[info.SchoolId,info.SchoolName,info.SchoolTelNo]
        return cls.myms.insertByColumnaAndValues("School",column,vales)

    def addProc(cls,info:SchoolInfo)->int:
        """
        添加，要考虑添加返回ID值
        :param info:实体类
        :return:
        """

        args=[info.SchoolId,info.SchoolName,info.SchoolTelNo]
        return cls.myms.insertCallProc("procInsertSchool",args)

    def addOutProc(cls,info:SchoolInfo) -> int:
        """
        添加，要考虑添加返回ID值
        :param info:实体类
        :return: 返回增加的ID
        """
        id = 0
        try:

            outid =('int',) #输出，元组类型
            print(info)
            args = [info.SchoolName, info.SchoolTelNo,outid]
            print(args)
            result=cls.myms.insertOutCallProc("procInsertSchoolOutput", args)
            print(result)
            id = result
        except Exception as ex:
            print(ex)
        return id

    def editSql(cls,info:SchoolInfo)->int:
        """

        :param info:实体类
        :return:
        """

        args = {"SchoolId":f"{info.SchoolId}","SchoolName":f"{info.SchoolName()}","SchoolTelNo":f"{info.SchoolTelNo}"}  #"StudentId":6
        where = f"SchoolId={info.SchoolId}" #
        #print(args,where)
        return cls.myms.updateByKeyValues("School",where,args)

    def editProc(cls, info: SchoolInfo)->int:
        """

        :param info: 实体类
        :return:
        """

        args = [info.SchoolId,info.SchoolName,info.SchoolTelNo]
        return cls.myms.updateProc("procUpdateSchool",args)


    def delSql(cls,SchoolId:str)->int:
        """
        sql语句删除
        :param SchoolId: 主键ID
        :return:
        """

        where={f"SchoolId":SchoolId}
        return cls.myms.deleteByKeyValues("School",where)

    def delProc(cls, SchoolId:str)->int:
        """
        删除 存储过程 删除多个ID，后面增加
        :param SchoolId: 主键ID
        :return:
        """

        args =SchoolId
        k=cls.myms.deleteProc("procDuDeleteSchool", args)

        return k

　　

BLL:

# encoding: utf-8
# 版权所有 2024 ©涂聚文有限公司
# 许可信息查看：言語成了邀功的功臣，還需要行爲每日來值班嗎？
# 描述：
# Author    : geovindu,Geovin Du 涂聚文.
# IDE       : PyCharm 2023.1 python 3.11
# OS        : windows 10
# Datetime  : 2024/10/31 20:40
# User      : geovindu
# Product   : PyCharm
# Project   : IctGame
# File      : school.py
# explain   : 学习

import os
import sys
from pathlib import Path
import re
import pymssql  #sql server
from datetime import date
from model.school import SchoolInfo
from field.school import SchoolField
from sqlserverfactory.AbstractFactory import AbstractFactory


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

    dal=AbstractFactory.createSchool
    """
    类属性 接口DAL
    """
    def __init__(self):
        """

        """
        self.__name = "SchoolBll"
        self._field = SchoolField()

    def __del__(self):
        print(f"{self.__name} ERASE MEMORY")


    def select(self) -> list[SchoolInfo]:
        """

        :return:
        """

        students = []

        data = self.dal().selectSql()
        if len(data) > 0:
            for SchoolId, SchoolName, SchoolTelNo in data[0]:
                info = SchoolInfo()
                info.SchoolId = SchoolId
                info.SchoolName = SchoolName
                info.SchoolTelNo = SchoolTelNo
                students.append(info)
        return students

    def selectSql(cls) -> list[SchoolInfo]:
        """
        元组数据
        :return: list 列表
        """
        students = []

        data = cls.dal().selectSql()
        if len(data) > 0:
            for SchoolId,SchoolName,SchoolTelNo in data[0]:
                info=SchoolInfo()
                info.SchoolId=SchoolId
                info.SchoolName=SchoolName
                info.SchoolTelNo=SchoolTelNo
                students.append(info)
        return students

    def show(self)-> int:
        """

        :return:
        """
        students = []
        k=-1
        data = self.dal().selectSql()
        if len(data) > 0:
            for SchoolId,SchoolName,SchoolTelNo in data[0]:
                info = SchoolInfo()
                info.SchoolId = SchoolId
                info.SchoolName = SchoolName
                info.SchoolTelNo = SchoolTelNo
                students.append(info)
                k=1

            print("┌{:<10s}┬{:<70s}┬{:<20s}┐".format("─" * 10, "─" * 70, "─" * 20))
            print(self._field.Title)
            print("├{:<10s}┼{:<70s}┼{:<20s}┤".format("═"*10, "═"*70, "═"*20))
            i=1
            for info in data:
                print("│{:<10s}│{:<70s}│{:<20s}│".format(info.SchoolId.center(10), " {}".format(info.SchoolName.strip()),
                                                         info.SchoolTelNo.center(20)))  # rjust  ljust
                if i<len(data):
                    print("├{:<10s}┼{:<70s}┼{:<20s}┤".format("─" * 10, "─" * 70, "─" * 20))
                i=i+1

            print("└{:<10s}┴{:<70s}┴{:<20s}┘".format("─" * 10, "─" * 70, "─" * 20))


        return k


    def selectSqlCount(cls) -> int:
        """
        查询数据 总数
        :return:
        """
        #print(cls.dal().selectSqlCount()[0][0])
        total=cls.dal().selectSqlCount()[0][0]
        return total

    def Count(self) -> int:
        """
        查询数据 总数
        :return:
        """
        total=self.dal().selectSqlCount()[0][0]
        return total

    def selectSqlOrder(cls, order: str) -> list[SchoolInfo]:
        """
        元组数据
        :param order: SchoolName desc/asc
        :return:
        """
        students=[]
        data = cls.dal().selectSqlOrder(order)
        if len(data) > 0:
            for SchoolId,SchoolName,SchoolTelNo in data[0]:
                info=SchoolInfo()
                info.SchoolId=SchoolId
                info.SchoolName=SchoolName
                info.SchoolTelNo=SchoolTelNo
                students.append(info)
        return students


    def selectSort(cls,field:str,isOrder:bool)->list[SchoolInfo]:
        """

        :param field SchoolId
        :param order:  desc/asc
        :return:
        """
        students=[]
        data = cls.dal().selectSort(field,isOrder)
        if len(data) > 0:
            for SchoolId, SchoolName, SchoolTelNo in data[0]:
                info = SchoolInfo()
                info.SchoolId = SchoolId
                info.SchoolName = SchoolName
                info.SchoolTelNo = SchoolTelNo
                students.append(info)
        return students

    def display(self,data:list[SchoolInfo]):
        """

        :param data:
        :return:
        """
        if len(data) > 0:
            print("┌{:<10s}┬{:<70s}┬{:<20s}┐".format("─" * 10, "─" * 70, "─" * 20))
            print(self._field.Title)
            print("├{:<10s}┼{:<70s}┼{:<20s}┤".format("═"*10, "═"*70, "═"*20))
            i=1
            for info in data:
                print("│{:<10s}│{:<70s}│{:<20s}│".format(info.SchoolId.center(10), " {}".format(info.SchoolName.strip()),
                                                         info.SchoolTelNo.center(20)))  # rjust  ljust
                if i<len(data):
                    print("├{:<10s}┼{:<70s}┼{:<20s}┤".format("─" * 10, "─" * 70, "─" * 20))
                i=i+1

            print("└{:<10s}┴{:<70s}┴{:<20s}┘".format("─" * 10, "─" * 70, "─" * 20))

    def selectIdSql(cls,SchoolId:str) -> list[SchoolInfo]:
        """

        :param SchoolId:ID
        :return:
        """
        students = []
        data = cls.dal().selectIdSql(SchoolId)
        #print(data)
        if len(data)>0:
            for SchoolId,SchoolName,SchoolTelNo in data[0]:
                info = SchoolInfo()
                info.SchoolId = SchoolId
                info.SchoolName = SchoolName
                info.SchoolTelNo = SchoolTelNo
                students.append(info)
        return students


    def selectProc(cls) -> list[SchoolInfo]:
        """

        :return:
        """
        students=[]
        data = cls.dal().selectProc()
        #print(data)
        if len(data) > 0:
            for SchoolId,SchoolName,SchoolTelNo in data:
                info = SchoolInfo()
                info.SchoolId = SchoolId
                info.SchoolName = SchoolName
                info.SchoolTelNo = SchoolTelNo
                students.append(info)
        return students

    def selectIdProc(cls,StudentId:str) -> list[SchoolInfo]:
        """

        :param StudentId:
        :return:
        """
        students = []
        data = cls.dal().selectIdProc(StudentId)
        if len(data) > 0:
            for SchoolId,SchoolName,SchoolTelNo in data:
                info = SchoolInfo()
                info.SchoolId = SchoolId
                info.SchoolName = SchoolName
                info.SchoolTelNo = SchoolTelNo
                students.append(info)
        return students

    def addSql(cls,info:SchoolInfo) -> int:
        """

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


    def add(self,info:SchoolInfo) -> int:
        """

        :param info:实体类
        :return:
        """
        return self.dal().addSql(info)


    def addProc(cls,info:SchoolInfo) -> int:
        """

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

    def addOutProc(cls,info:SchoolInfo)->int:
        """

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

    def editSql(cls,info:SchoolInfo) -> int:
        """

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

    def edit(self,info:SchoolInfo) -> int:
        """

        :param info:实体类
        :return:
        """
        #print(info)
        return self.dal().editSql(info)

    def editProc(cls, info: SchoolInfo) -> int:
        """

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

    def delSql(cls, SchoolId: str) -> int:
        """

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

    def delinfo(self, SchoolId: str) -> int:
        """

        :param SchoolId:
        :return:
        """
        return self.dal().delSql(SchoolId)

    def delProc(cls, SchoolId:str) -> int:
        """

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

Tkinter
PyQt or PySide
PyQt/PySide with QML
Kivy
PySimpleGUI
WxPython
PyGObject (GTK+)
Remi
pip install pyqt6 or pip install pyside6
pip install pyqt5 
pip install kivy
pip install pysimplegui
pip install wxpython
https://www.pythonguis.com/faq/which-python-gui-library/