more Layer Architecture in Python and mysql 9.0

create table School  #創建表
(
    `SchoolId` char(5) NOT NULL ,
    `SchoolName` nvarchar(500) NOT NULL DEFAULT '',
    `SchoolTelNo`  varchar(8)  NULL DEFAULT '' ,       
  PRIMARY KEY (`SchoolId`)   #主鍵
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

# encoding: utf-8
# 版权所有 2024 ©涂聚文有限公司
# 许可信息查看：言語成了邀功的功臣，還需要行爲每日來值班嗎？
# 描述：
# Author    : geovindu,Geovin Du 涂聚文.
# IDE       : PyCharm 2023.1 python 3.11
# OS        : windows 10
# Datetime  : 2024/10/30 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

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

# encoding: utf-8
# 版权所有 2024 ©涂聚文有限公司
# 许可信息查看：言語成了邀功的功臣，還需要行爲每日來值班嗎？
# 描述：
# Author    : geovindu,Geovin Du 涂聚文.
# IDE       : PyCharm 2023.1 python 3.11
# OS        : windows 10
# Datetime  : 2024/10/31 22:00
# User      : geovindu
# Product   : PyCharm
# Project   : IctGame
# File      : mysqldal/school.py
# explain   : 学习
 
import os
import sys
from pathlib import Path
import re
import pymssql  #sql server
from model.school import SchoolInfo
from DBUtility.MySqlHelper import MySqlHelper
from mysqlinterface.school import ISchool
 
class SchoolDal(ISchool):
    """
 
    """
    myms = MySqlHelper()
 
    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

# encoding: utf-8
# 版权所有 2024 ©涂聚文有限公司
# 许可信息查看：言語成了邀功的功臣，還需要行爲每日來值班嗎？
# 描述：
# Author    : geovindu,Geovin Du 涂聚文.
# IDE       : PyCharm 2023.1 python 3.11
# OS        : windows 10
# Datetime  : 2024/10/31 23:00
# User      : geovindu
# Product   : PyCharm
# Project   : IctGame
# File      : mysqldal/school.py
# explain   : 学习
 
import os
import sys
from pathlib import Path
import re
import pymssql  #sql server
from model.school import SchoolInfo
from DBUtility.MySqlHelper import MySqlHelper
from mysqlinterface.school import ISchool
 
class SchoolDal(ISchool):
    """
 
    """
    myms = MySqlHelper()
 
    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