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