python: more Layer Architecture and its Implementation in SQLite

https://github.com/dahlia/awesome-sqlalchemy

Python Design Patterns
This project contains some of the most popular design patterns implemented in Python.

Repository Pattern
Service Layer
Unit of Work Pattern
Commands
Message/Events

https://github.com/madalinpopa/py-patterns

sqlite3:

CREATE TABLE DuStudentList (
    StudentId   INTEGER PRIMARY KEY AUTOINCREMENT,
    StudentName TEXT NOT NULL,
    StudentNO   TEXT NOT NULL,
    StudentBirthday DATETIME
     
);

Model:

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

"""
StudentListInfo.py
学生类
date 2023-06-16
edit: Geovin Du,geovindu, 涂聚文
ide:  PyCharm 2023.1 python 3.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
        """
 
        :param StudentName:
        :param StudentNO:
        :param StudentBirthday:
        """
        self._StudentName=None
        self._StudentNO=None
        self._StudentBirthday=None
        self._StudentId=None
        self._age=0 #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,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 __del__(self):
        """
 
        :return:
        """
        #print(f"{self._StudentName}")
 
    @property
    def StudentId(self):
        """
 
        :return:
        """
        return self._StudentId
 
    @StudentId.setter
    def StudentId(self,id):
        """
 
        :param id:
        :return:
        """
        self._StudentId=id
 
    @property
    def StudentName(self):
        """
 
        :return:
        """
        return self._StudentName
 
    @StudentName.setter
    def StudentName(self,name):
        """
 
        :param name:
        :return:
        """
        self._StudentName=name
 
    @property
    def StudentBirthday(self):
        """
 
        :return:
        """
        return self._StudentBirthday
 
    @StudentBirthday.setter
    def StudentBirthday(self,day):
        """
 
        :param day:
        :return:
        """
        self._StudentBirthday=day
 
 
 
    @property
    def StudentNO(self):
        """
 
        :return:
        """
        return self._StudentNO
 
    @StudentNO.setter
    def StudentNO(self,no):
        """
 
        :param no:
        :return:
        """
        self._StudentNO=no
 
 
 
 
 
    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
 
    @property
    def Age(self):
        """
 
        :return:
        """
        return self._age
 
    @Age.setter
    def Age(self,age):
        """
 
        :param age:
        :return:
        """
        self._age=age
 
 
    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

100

101

102

103

104

105

106

107

108

109

110

111

112

"""
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
from Model.StudentListInfo import StudentList
from UtilitieDB.SQLiteHelper import SqliteHelper
from Interface.IStudentList import IStudentList
 
class StudentDal(IStudentList):
    """
    数据业务处理层  学生
    数据库连接可以放在这里，通过配置读取数据连接参数
    """
 
    def __init__(self):
        """
        构造函数，方法
        :param strserver:
        :param struser:
        :param strpwd:
        :param strdatabase:
        """
        self._strserver = ""
        self._struser = ""
        self._strpwd = ""
        self._strdatabase =""
 
    def selectSql(self):
        """
        查询数据 self._strserver, self._struser, self._strpwd, self._strdatabase
        select StudentId,StudentName,StudentNO,StudentBirthday,(julianday('now')-julianday(StudentBirthday))/365 as Age from DuStudentList;
        :return:
        """
 
        myms = SqliteHelper()
        row=myms.execute("select *,(julianday('now')-julianday(StudentBirthday))/365 as Age from DuStudentList;")
        return row
 
    def selectSqlOrder(self,order:str)->list:
        """
 
        :param order:  studenName desc/asc
        :return:
        """
        students=[]
        myms = SqliteHelper()
        strsql=f"select *,(julianday('now')-julianday(StudentBirthday))/365 as Age from DuStudentList order by {order};"
        row=myms.execute(f"select *,(julianday('now')-julianday(StudentBirthday))/365 as Age from DuStudentList order by {order};")
        return row
 
    def selectIdSql(self,StudentId:int):
        """
 
        :param StudentId: 主键ID
        :return:
        """
        myms = SqliteHelper()
        row=myms.execute(f'select * from DuStudentList where StudentId={StudentId};')
        return row
 
 
    def addSql(self,info:StudentList)->int:
        """
        添加，要考虑添加返回ID值
        :param info:学生实体类
        :return:
        """
        myms=SqliteHelper()
        column=("StudentName","StudentNO","StudentBirthday")
        vales=[info.StudentName,info.StudentNO,info.StudentBirthday]
        k=myms.insertByColumnaAndValues("DuStudentList",column,vales)
        return k
 
 
 
    def editSql(self,info:StudentList):
        """
 
        :param info:学生实体类
        :return:
        """
        myms = SqliteHelper()
        args = {"StudentName":f"{info.getStudentName()}","StudentNO":f"{info.getStudentNO()}","StudentBirthday":f"{info.getStudentBirthday()}"}  #"StudentId":6
        where = f"StudentId={info.getStudentId()}" #
        #print(args,where)
        k=myms.updateByKeyValues("DuStudentList",where,args)
        return k
 
 
 
 
    def delSql(self,StudentId:int):
        """
        sql语句删除
        :param StudentId: 主键ID
        :return:
        """
        myms = SqliteHelper()
        where={f"StudentId":StudentId}
        k=myms.deleteByKeyValues("DuStudentList",where)
        print(k)
        return k

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
from Model.StudentListInfo import StudentList
 
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(self)-> list[StudentList]:
        """
 
        :return:
        """
        pass
 
    @abstractmethod
    def selectSqlOrder(self, order: str) -> list[StudentList]:
        """
 
        :param order:
        :return:
        """
        pass
 
    @abstractmethod
    def selectIdSql(self, StudentId: int)->StudentList:
        """
 
        :param StudentId:
        :return:
        """
        pass
 
 
 
 
    @abstractmethod
    def addSql(self, info: StudentList)->int:
        """
 
        :param info:
        :return:
        """
        pass
 
 
 
 
    @abstractmethod
    def editSql(self, info: StudentList)->int:
        """
 
        :param info:
        :return:
        """
        pass
 
 
 
    @abstractmethod
    def delSql(self, StudentId: int)->int:
        """
 
        :param StudentId:
        :return:
        """
        pass

BLL

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

"""
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
from Model.StudentListInfo import StudentList
from Factory.AbstractFactory import AbstractFactory
 
class StudentBll(object):
    """
    学生信息操作业务类
    """
 
 
    dal=AbstractFactory.createStudentList  # 这里不能打括号
 
    """
    类属性 操作DAL
    """
    def __init__(self):
        """
 
        """
        self._name = "geovindu"
 
 
 
    def __del__(self):
        print(f"{self._name}挂失了")
 
    def selectSql(cls)->list[StudentList]:
        """
        元组数据
        :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(StudentList(i[0],i[1],i[2],i[3],i[4]))
                 
        '''
        for StudentId,StudentName,StudentNO,StudentBirthday,Age in data[0]:
            stu=StudentList()
            stu.StudentId=StudentId
            stu.StudentName = StudentName
            stu.StudentNO=StudentNO
            stu.StudentBirthday=StudentBirthday
            stu.Age=Age
            students.append(stu)
        return students
 
    def selectSqlOrder(cls, order: str)->list[StudentList]:
        """
        元组数据
        :param order: studenName desc/asc
        :return:
        """
        studentsorder = []
        students=[]
        data = cls.dal().selectSqlOrder(order)
        #print("bll",data[0])
        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 data[0]:
            row=[]
            for i in obj:
                #print('i',i)
                row.append(i)
            student=StudentList()
            student.StudentId=row[0]
            student.StudentName=row[1]
            student.StudentNO=row[2]
            student.StudentBirthday=row[3]
            student.Age=row[4]
            students.append(student)  #StudentList(i[0], i[1], i[2], i[3],i[4])
        '''
        for StudentId,StudentName,StudentNO,StudentBirthday,Age in data[0]:
            stu=StudentList()
            stu.StudentId=StudentId
            stu.StudentName = StudentName
            stu.StudentNO=StudentNO
            stu.StudentBirthday=StudentBirthday
            stu.Age=Age
            students.append(stu)
 
        return students
 
 
    def selectIdSql(cls,StudentId:int)->StudentList:
        """
 
        :param StudentId:学生ID
        :return:
        """
        student = None
        stu=None
        data = cls.dal().selectIdSql(StudentId)
        if len(data)>0:
            (students)=data[0]  # 如C# 强制转换
            print(students[0])
            '''
            for ii in students:
                row=[]
                for i in ii:
                    row.append(i)
                student = StudentList()
                student.StudentId=row[0]
                student.StudentName=row[1]
                student.StudentNO=row[2]
                student.StudentBirthday=row[3]
                #student.Age=row[4]
             '''
            stu = StudentList()
            StudentId,StudentName,StudentNO,StudentBirthday= students[0]
            stu.StudentId=StudentId
            stu.StudentName = StudentName
            stu.StudentNO=StudentNO
            stu.StudentBirthday=StudentBirthday
        return stu
 
 
 
 
 
 
    def addSql(cls,info:StudentList)->int:
        """
 
        :param info:学生实体类
        :return:
        """
        return cls.dal().addSql(info)
 
 
 
 
 
    def editSql(cls,info:StudentList)->int:
        """
 
        :param info:学生实体类
        :return:
        """
        #print(info)
        return cls.dal().editSql(info)
 
 
 
    def delSql(cls, StudentId: int)->int:
        """
 
        :param StudentId:
        :return:
        """
        if cls.selectIdSql(StudentId) is not None:
           return cls.dal().delSql(StudentId)
        else:
           return -1

GUI

"""
StudentUI.py
读文件类
date 2023-06-24
edit: Geovin Du,geovindu, 涂聚文
ide:  PyCharm 2023.1 python 11
 
"""
 
import datetime
import sys
import os
from tkinter import ttk
from tkinter import *
from tkinter.ttk import *
from ttkbootstrap import Style  # pip install ttkbootstrap
import random
import Model.StudentListInfo
import BLL.StudentListBLL
 
class StudentUi(object):
 
    global tree
    stubll = BLL.StudentListBLL.StudentBll()
 
    def __init__(self):
        self.name="geovindu"
 
    def __del__(self):
        print(f"{self.name}")
 
    def delete(cls):
        #global tree
        curItem = cls.tree.focus()
        val=cls.tree.item(curItem)['values'][0]  #id
        print(val)
        print(cls.tree.selection())
        cls.tree.delete(cls.tree.selection())
        cls.stubll.delSql(val)  #需要删除关联的数据才可以删除
 
        #cls.stubll.delSql()
 
    def main(cls):
        """
        窗体绑定数据
        :return:
        """
 
 
        style=Style(theme='darkly') #定义窗口样式
        window=style.master
        window.title("学生管理")
        # win = Tk()
        screenWidth = window.winfo_screenwidth()
        screenHeight = window.winfo_screenheight()
        width=800
        height=600
        x=int((screenWidth-width)/2)
        y=int((screenHeight-height)/2)
        window.geometry('{}x{}+{}+{}'.format(width,height,x,y))
        #Treeview 控件
        cls.tree=ttk.Treeview(master=window,style='success.Treeview',height=25,show='headings')
        cls.tree.pack()
        #定义列
        cls.tree['columns']=("StudentId","StudentName","StudentNO","StudentBirthday","Age")
        #设置列属性，列不显示
        cls.tree.column("StudentId",width=150,minwidth=100,anchor=S)
        cls.tree.column("StudentName", width=150, minwidth=100, anchor=S)
        cls.tree.column("StudentNO", width=150, minwidth=100, anchor=S)
        cls.tree.column("StudentBirthday", width=150, minwidth=100, anchor=S)
        cls.tree.column("Age", width=150, minwidth=100, anchor=S)
        #设置表头
        cls.tree.heading("StudentId",text="序号")
        cls.tree.heading("StudentName", text="姓名")
        cls.tree.heading("StudentNO", text="学号")
        cls.tree.heading("StudentBirthday", text="出生日期")
        cls.tree.heading("Age", text="年龄")
        # stubll = BLL.StudentListBLL.StudentBll()
        geovindu = cls.stubll.selectSqlOrder("Age asc")  # list()
        #treeView控件绑定数据
        i=1
        for Model.StudentListInfo.StudentList in geovindu:
            cls.tree.insert("",i,text="2",values=(Model.StudentListInfo.StudentList.getStudentId(),Model.StudentListInfo.StudentList.getStudentName(),Model.StudentListInfo.StudentList.getStudentNO(),Model.StudentListInfo.StudentList.getStudentBirthday(),Model.StudentListInfo.StudentList.getAge()))
            i+=1
        #删除按钮
        ttk.Button(window,text="删除",style='success,TButton',command=cls.delete).pack(side='left',padx=5,pady=10)
        window.mainloop()