more Layer Architecture in Python and postgreSQL17.0

postgreSQL script:

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

　　

model:

# encoding: utf-8
# 版权所有 2024 ©涂聚文有限公司
# 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎？
# 描述：
# Author    : geovindu,Geovin Du 涂聚文.
# IDE       : PyCharm 2023.1 python 3.11
# OS        : windows 10
# Datetime  : 2024/10/12 21: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/11/13  22:22
# 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/11/13  22:22
# 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.postgreSQLHelper import PostgreSqlHelper
from postgresqlinterface.school import ISchool
 
class SchoolDal(ISchool):
    """
    """
    myms = PostgreSqlHelper()
 
    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/11/13  22:22
# 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 postgresqlfactory.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)
 

　　

调用：

    sc=SchoolBll()
    for li in sc.selectSql():
        print(li.SchoolId,li.SchoolName,li.SchoolTelNo)