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)
标签:info,Layer,SchoolId,return,Python,self,python,def,cls From: https://www.cnblogs.com/geovindu/p/18531195