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:

"""
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

"""
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

"""
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()

　　

输出：

 

 

 

 

 

 

写好了返回值（变量)数据类型时，调用时，代码就好写多了，会自动弹出来。否则，是摸瞎。，有定义其数据类型，代码就好写多了。