mysql 8.0:
drop table DuStudentList;
#学生表
create table DuStudentList
(
StudentId INT NOT NULL AUTO_INCREMENT comment'主键id', #自动增加,
StudentName nvarchar(50) comment'学生姓名',
StudentNO varchar(50) comment'学号', #学号
StudentBirthday datetime comment'学生生日', #学生生日
primary key(StudentId)
) comment='学生表';
Model:
"""
StudentListInfo.py
学生类
date 2023-06-16
edit: Geovin Du,geovindu, 涂聚文
ide: PyCharm 2023.1 python 11
"""
import datetime
from datetime import date
import sys
import os
import Common
class StudentList(object):
"""
学生类
"""
def __init__(self,StudentId:int,StudentName:str, StudentNO:str,StudentBirthday:datetime.datetime,age:int):
"""
:param StudentName:
:param StudentNO:
:param StudentBirthday:
"""
self._StudentName=StudentName
self._StudentNO=StudentNO
self._StudentBirthday=StudentBirthday
self._StudentId=StudentId
self._age=age #date.today().year-StudentBirthday.year #date.today().year-StudentBirthday.year #Common.Commond.calculateAge(date(StudentBirthday.year,StudentBirthday.month,StudentBirthday.day))
'''
def __init__(self,StudentId:int,StudentName:str, StudentNO:str,StudentBirthday:datetime.datetime):
"""
:param StudentName:
:param StudentNO:
:param StudentBirthday:
"""
self._StudentName=StudentName
self._StudentNO=StudentNO
self._StudentBirthday=StudentBirthday
self._StudentId=StudentId
self._age=0 #date.today().year-StudentBirthday.year #date.today().year-StudentBirthday.year #Common.Commond.calculateAge(date(StudentBirthday.year,StudentBirthday.month,StudentBirthday.day))
'''
def __del__(self):
"""
:return:
"""
print(f"geovindu")
def setStudentName(self,StudentName):
"""
:param StudentName:
:return:
"""
self._StudentName = StudentName
def getStudentName(self):
"""
:return:
"""
return self._StudentName
def setStudentNO(self,StudentNO):
"""
:param StudentNO:
:return:
"""
self._StudentNO=StudentNO
def getStudentNO(self):
"""
:return:
"""
return self._StudentNO
def setStudentId(self,StudentId):
"""
:param StudentId:
:return:
"""
self._StudentId=StudentId
def getStudentId(self):
"""
:return:
"""
return self._StudentId
def setStudentBirthday(self,StudentBirthday):
"""
:param StudentBirthday:
:return:
"""
self._StudentBirthday = StudentBirthday
dage =date.today().year-StudentBirthday.year# Common.Commond.calculate_age(StudentBirthday)
self._age=dage
def getStudentBirthday(self):
"""
:return:
"""
return self._StudentBirthday
def setAge(self,age):
"""
:param age:
:return:
"""
dage=1 #Common.Commond.calculate_age(StudentBirthday)
self._age = age
def getAge(self):
"""
:return:
"""
return self._age
def __str__(self):
"""
:return:
"""
return f"{self._StudentId},{self._StudentName},{self._StudentNO},{self._StudentBirthday}{self._age}"
DAL:
"""
StudentDALListDAL.py
数据业务处理层 Data Access Layer (DAL)
SQL Server 数据库操作
date 2023-06-21
edit: Geovin Du,geovindu, 涂聚文
ide: PyCharm 2023.1 python 11
"""
import os
import sys
from pathlib import Path
import re
import pymssql #sql server
import Model.StudentListInfo
import DBUtility.MySqlHelper
import Interface.IStudentList
class StudentDal(Interface.IStudentList.IStudentList):
"""
数据业务处理层 学生
数据库连接可以放在这里，通过配置读取数据连接参数
"""
myms=DBUtility.MySqlHelper.MySqlHelper()
def __init__(self):
"""
构造函数，方法
:param strserver:
:param struser:
:param strpwd:
:param strdatabase:
"""
self._strserver = ""
self._struser = ""
self._strpwd = ""
self._strdatabase =""
def selectSql(cls):
"""
查询数据 self._strserver, self._struser, self._strpwd, self._strdatabase
:return:
"""
row=cls.myms.execute('select *,TIMESTAMPDIFF(hour,StudentBirthday,now())/8766 as Age from DuStudentList;')
return row
def selectSqlOrder(cls,order:str)->list:
"""
:param order: studenName desc/asc
:return:
"""
students=[]
strsql=f"select * from DuStudentList order by {order};"
row=cls.myms.execute(f'select *,TIMESTAMPDIFF(hour,StudentBirthday,now())/8766 as Age from DuStudentList order by {order};')
return row
def selectIdSql(cls,StudentId:int):
"""
:param StudentId: 主键ID
:return:
"""
row=cls.myms.execute(f'select * from DuStudentList where StudentId={StudentId};')
return row
def selectProc(cls):
"""
存储过程
:return:
"""
args = ()
row = cls.myms.executeCallProc("proc_Select_StudentListAll",args)
return row
def selectIdProc(cls,StudentId:int):
"""
存储过程
:param StudentId: 主键ID
:return:
"""
args = (StudentId,)
row = cls.myms.executeCallProc('proc_Select_StudentList', args)
return row
def addSql(cls,info:Model.StudentListInfo.StudentList):
"""
添加，要考虑添加返回ID值
:param info:学生实体类
:return:
"""
column=("StudentName","StudentNO","StudentBirthday")
vales=[info.getStudentName(),info.getStudentNO(),info.getStudentBirthday()]
cls.myms.insertByColumnaAndValues("DuStudentList",column,vales)
def addProc(cls,info:Model.StudentListInfo.StudentList):
"""
添加，要考虑添加返回ID值
:param info:学生实体类
:return:
"""
args=(info.getStudentName(),info.getStudentNO(),info.getStudentBirthday())
cls.myms.insertCallProc("proc_Insert_StudentList",args)
def addOutProc(cls,info:Model.StudentListInfo.StudentList):
"""
添加，要考虑添加返回ID值
:param info:学生实体类
:return: 返回增加的学生的ID
"""
id = 0
try:
outid =('int',) #输出，元组类型
print(info)
args = (info.getStudentName(), info.getStudentNO(), info.getStudentBirthday(),outid)
print(args)
result=cls.myms.insertOutCallProc("proc_Insert_StudentListOutput", args)
print(result)
id = result[0]
except Exception as ex:
print(ex)
return id
def editSql(cls,info:Model.StudentListInfo.StudentList):
"""
:param info:学生实体类
:return:
"""
args = {"StudentName":f"{info.getStudentName()}","StudentNO":f"{info.getStudentNO()}","StudentBirthday":f"{info.getStudentBirthday()}"} #"StudentId":6
where = f"StudentId={info.getStudentId()}" #
#print(args,where)
cls.myms.updateByKeyValues("DuStudentList",where,args)
def editProc(cls, info: Model.StudentListInfo.StudentList):
"""
:param info: 学生实体类
:return:
"""
args = (info.getStudentId(),info.getStudentName(),info.getStudentNO(),info.getStudentBirthday())
cls.myms.updateProc("proc_Update_StudentList",args)
def delSql(cls,StudentId:int):
"""
sql语句删除
:param StudentId: 主键ID
:return:
"""
where={f"StudentId":StudentId}
cls.myms.deleteByKeyValues("DuStudentList",where)
def delProc(cls, studentId):
"""
删除 存储过程 删除多个ID，后面增加
:param StudentId: 主键ID
:return:
"""
args =studentId
cls.myms.deleteProc("proc_Delete_StudentList",args)
IDAL:
"""
IStudentList.py
接口层 Interface Data Access Layer
IDAL(Interface Data Access Layer)DAL的接口层
date 2023-06-19
edit: Geovin Du,geovindu, 涂聚文
ide: PyCharm 2023.1 python 11
"""
from __future__ import annotations
from abc import ABC, abstractmethod
import os
import sys
import Model.StudentListInfo
class IStudentList(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 selectSqlOrder(cls, order: str) -> list:
"""
:param order:
:return:
"""
pass
@abstractmethod
def selectIdSql(cls, StudentId: int):
"""
:param StudentId:
:return:
"""
pass
@abstractmethod
def selectProc(cls):
"""
:return:
"""
pass
@abstractmethod
def selectIdProc(cls, StudentId: int):
"""
:param StudentId:
:return:
"""
pass
@abstractmethod
def addSql(cls, info: Model.StudentListInfo.StudentList):
"""
:param info:
:return:
"""
pass
@abstractmethod
def addProc(cls, info: Model.StudentListInfo.StudentList):
"""
:param info:
:return:
"""
pass
@abstractmethod
def addOutProc(cls, info: Model.StudentListInfo.StudentList):
"""
:param info:
:return:
"""
pass
@abstractmethod
def editSql(cls, info: Model.StudentListInfo.StudentList):
"""
:param info:
:return:
"""
pass
@abstractmethod
def editProc(cls, info: Model.StudentListInfo.StudentList):
"""
:param info:
:return:
"""
pass
@abstractmethod
def delSql(cls, StudentId: int):
"""
:param StudentId:
:return:
"""
pass
@abstractmethod
def delProc(cls, studentId):
"""
:param studentId:
:return:
"""
pass
Factory:
@abstractmethod
def createStudentList(self)->Interface.IStudentList.IStudentList:
"""
生成（创建）接口
:return:
"""
dals= Interface.IStudentList.IStudentList
dal=DAL.StudentListDAL.StudentDal()
dals=dal
return dals
BLL
"""
StudentListBLL.py
业务层 Business Logic Layer (BLL)
date 2023-06-19
edit: Geovin Du,geovindu, 涂聚文
ide: PyCharm 2023.1 python 11
"""
import os
import sys
from pathlib import Path
import re
import pymssql #sql server
from datetime import date
import DAL.StudentListDAL
#import DAL.ConfigDAL
import Model.StudentListInfo
import Interface.IStudentList
import Factory.AbstractFactory
class StudentBll(object):
"""
学生信息操作业务类
"""
dal=Factory.AbstractFactory.AbstractFactory.createStudentList
#dal =DAL.StudentListDAL.StudentDal() #Factory.AbstractFactory.AbstractFactory.createStudentList()#
"""
类属性 操作DAL
"""
def __init__(self):
"""
"""
self._name = "geovindu"
def __del__(self):
print(f"{self._name}挂失了")
def selectSql(cls)->list:
"""
元组数据
:return: list 学生列表
"""
students = []
data = cls.dal().selectSql()
stus = list(data) # 如C# 强制转换
'''
for a in data:
for i in a:
print("II",i[0],i[1],i[2],i[3],i[4])
'''
print(stus)
for ii in stus:
for i in ii:
students.append(Model.StudentListInfo.StudentList(i[0],i[1],i[2],i[3],i[4]))
return students
def selectSqlOrder(cls, order: str)->list:
"""
元组数据
:param order: studenName desc/asc
:return:
"""
studentsorder = []
students=[]
data = cls.dal().selectSqlOrder(order)
(studentsorder) = data # 如C# 强制转换
'''
for i in range(len(studentsorder)):
print("rrr",type(studentsorder[i]))
for duobj in studentsorder[i]:
print(type(duobj))
print(duobj)
'''
for obj in studentsorder:
for i in obj:
students.append(Model.StudentListInfo.StudentList(i[0], i[1], i[2], i[3],i[4]))
return students
def selectIdSql(cls,StudentId:int)->list:
"""
:param StudentId:学生ID
:return:
"""
students = []
data = cls.dal().selectIdSql(StudentId)
students=data
for ii in students:
for i in ii:
students.append(Model.StudentListInfo.StudentList(i[0],i[1],i[2],i[3]))
return students
def selectProc(cls):
"""
:return:
"""
students=[]
data = cls.dal().selectProc()
for i in data:
students.append(Model.StudentListInfo.StudentList(i[0],i[1],i[2],i[3],i[4]))
return students
def selectIdProc(cls,StudentId:int)->list:
"""
:param StudentId:
:return:
"""
students = []
data = cls.dal().selectIdProc(StudentId)
for i in data:
students.append(Model.StudentListInfo.StudentList(i[0],i[1],i[2],i[3]))
return students
def addSql(cls,info:Model.StudentListInfo.StudentList):
"""
:param info:学生实体类
:return:
"""
cls.dal.addSql(info)
def addProc(cls,info:Model.StudentListInfo.StudentList):
"""
:param info:学生实体类
:return:
"""
#print(info)
cls.dal().addProc(info)
def addOutProc(cls,info:Model.StudentListInfo.StudentList)->int:
"""
:param info: 学生实体类
:return: 返回增加的学生ID
"""
print(info)
return cls.dal().addOutProc(info)
def editSql(cls,info:Model.StudentListInfo.StudentList):
"""
:param info:学生实体类
:return:
"""
#print(info)
cls.dal().editSql(info)
def editProc(cls, info: Model.StudentListInfo.StudentList):
"""
:param info:学生实体类
:return:
"""
cls.dal().editProc(info)
def delSql(cls, StudentId: int):
"""
:param StudentId:
:return:
"""
cls.dal().delSql(StudentId)
def delProc(cls, StudentId):
"""
:param StudentId:
:return:
"""
cls.dal().delProc(StudentId)
调用：
stubll = BLL.StudentListBLL.StudentBll()
#dd=stubll.addOutProc(StudentListInfo.StudentList(0,"杜村3", "010", "2007-05-28"))
#print("dd",dd)
#查询学生
sl=stubll.selectSql()
for s in sl:
print(s)
#outid=stubll.addOutProc(Model.StudentListInfo.StudentList(0,"陈三","006",datetime.datetime(2007,3,14),5))
#print(outid)
- Implementation Architecture and python Pythonimplementation architecture and python implementation architecture python layer implementation architecture python sqlite implementation specification architecture gic implementation progress python 908 implementation q-learning learning and python encode decode and processing python thead and 运算符 逻辑python and task3and4办法python task3