python: more Layer Architecture and its Implementation in SQL Server 2019

 

SQLAlchemy-2.0.17

Oracel,SQL server, PostgreSQL,MySQL，SQLite都玩过。

python.exe -m pip install --upgrade pip

pip install pymssql
pip install pymysql
pip install pyodbc
pip install DBUtils
pip install xlrd
pip install xlwt
pip install xlutils
pip install xlwings
pip install XlsxWriter
pip install openpyxl
pip install pandas
pip install pandasql

pip install win32com
pip install SQLAlchemy
pip install pyspark
pip install pyinstaller 打包执行exe文件的包
pip install fbs 打包
pip install pdfplumber pdf
pip install pillow image
pip install zope.interface

win:
Tkinter （自带）
PyQT
WxPython

pip install ttkbootstrap
pip install PyQt5
pip install PyQt5-tools
pip install wxPython

Web:
Django
Tomado
Flask

sql server:

--学生表
DROP TABLE DuStudentList
GO
create table DuStudentList
(
     StudentId INT IDENTITY(1,1) PRIMARY KEY,
     StudentName nvarchar(50),
     StudentNO varchar(50),                    --学号
     StudentBirthday datetime                  --学生生日      
 
)
go

　　

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

        :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 __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}")

    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 UtilitieDB.MsSQLHelper
import Interface.IStudentList

class StudentDal(Interface.IStudentList.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
        :return:
        """

        myms = UtilitieDB.MsSQLHelper.MsSqlHelper()
        row=myms.execute('select *,DATEDIFF(hour,StudentBirthday,GETDATE())/8766 as Age from DuStudentList;')
        return row

    def selectSqlOrder(self,order:str)->list:
        """

        :param order:  studenName desc/asc
        :return:
        """
        students=[]
        myms = UtilitieDB.MsSQLHelper.MsSqlHelper()
        strsql=f"select * from DuStudentList order by {order};"
        row=myms.execute(f'select *,DATEDIFF(hour,StudentBirthday,GETDATE())/8766 as Age from DuStudentList order by {order};')
        return row

    def selectIdSql(self,StudentId:int):
        """

        :param StudentId: 主键ID
        :return:
        """
        myms = UtilitieDB.MsSQLHelper.MsSqlHelper()
        row=myms.execute(f'select * from DuStudentList where StudentId={StudentId};')
        return row
    def selectProc(self):
        """
        存储过程
        :return:
        """
        myms = UtilitieDB.MsSQLHelper.MsSqlHelper()
        args = ()
        row = myms.executeCallProc("proc_Select_StudentListAll",args)
        return row

    def selectIdProc(self,StudentId:int):
        """
        存储过程
        :param StudentId: 主键ID
        :return:
        """
        myms = UtilitieDB.MsSQLHelper.MsSqlHelper()
        args = (StudentId,)
        row = myms.executeCallProc('dbo.proc_Select_StudentList', args)
        return row

    def addSql(self,info:Model.StudentListInfo.StudentList):
        """
        添加，要考虑添加返回ID值
        :param info:学生实体类
        :return:
        """
        myms=UtilitieDB.MsSQLHelper.MsSqlHelper();
        column=("StudentName","StudentNO","StudentBirthday")
        vales=[info.getStudentName(),info.getStudentNO(),info.getStudentBirthday()]
        myms.insertByColumnaAndValues("dbo.DuStudentList",column,vales)

    def addProc(self,info:Model.StudentListInfo.StudentList):
        """
        添加，要考虑添加返回ID值
        :param info:学生实体类
        :return:
        """
        myms = UtilitieDB.MsSQLHelper.MsSqlHelper();
        args=(info.getStudentName(),info.getStudentNO(),info.getStudentBirthday())
        myms.insertCallProc("dbo.proc_Insert_StudentList",args)

    def addOutProc(self,info:Model.StudentListInfo.StudentList):
        """
        添加，要考虑添加返回ID值
        :param info:学生实体类
        :return: 返回增加的学生的ID
        """
        id=0
        myms = UtilitieDB.MsSQLHelper.MsSqlHelper();
        outid = pymssql.output(int)
        args = (info.getStudentName(), info.getStudentNO(), info.getStudentBirthday(),outid)
        print(args)
        id=myms.insertOutCallProc("dbo.proc_Insert_StudentListOutput", args)
        return id
    def editSql(self,info:Model.StudentListInfo.StudentList):
        """

        :param info:学生实体类
        :return:
        """
        myms = UtilitieDB.MsSQLHelper.MsSqlHelper();
        args = {"StudentName":f"{info.getStudentName()}","StudentNO":f"{info.getStudentNO()}","StudentBirthday":f"{info.getStudentBirthday()}"}  #"StudentId":6
        where = f"StudentId={info.getStudentId()}" #
        #print(args,where)
        myms.updateByKeyValues("DuStudentList",where,args)

    def editProc(self, info: Model.StudentListInfo.StudentList):
        """

        :param info: 学生实体类
        :return:
        """
        myms = UtilitieDB.MsSQLHelper.MsSqlHelper();
        args = (info.getStudentId(),info.getStudentName(),info.getStudentNO(),info.getStudentBirthday())
        myms.updateProc("[dbo].[proc_Update_StudentList]",args)


    def delSql(self,StudentId:int):
        """
        sql语句删除
        :param StudentId: 主键ID
        :return:
        """
        myms = UtilitieDB.MsSQLHelper.MsSqlHelper();
        where={f"StudentId":StudentId}
        myms.deleteByKeyValues("DuStudentList",where)

    def delProc(self, studentId):
        """
        删除 存储过程 删除多个ID，后面增加
        :param StudentId: 主键ID
        :return:
        """
        myms = UtilitieDB.MsSQLHelper.MsSqlHelper();
        args =studentId
        myms.deleteProc("dbo.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(self):
        """

        :return:
        """
        pass

    @abstractmethod
    def selectSqlOrder(self, order: str) -> list:
        """

        :param order:
        :return:
        """
        pass

    @abstractmethod
    def selectIdSql(self, StudentId: int):
        """

        :param StudentId:
        :return:
        """
        pass

    @abstractmethod
    def selectProc(self):
        """

        :return:
        """
        pass

    @abstractmethod
    def selectIdProc(self, StudentId: int):
        """

        :param StudentId:
        :return:
        """
        pass

    @abstractmethod
    def addSql(self, info: Model.StudentListInfo.StudentList):
        """

        :param info:
        :return:
        """
        pass

    @abstractmethod
    def addProc(self, info: Model.StudentListInfo.StudentList):
        """

        :param info:
        :return:
        """
        pass

    @abstractmethod
    def addOutProc(self, info: Model.StudentListInfo.StudentList):
        """

        :param info:
        :return:
        """
        pass

    @abstractmethod
    def editSql(self, info: Model.StudentListInfo.StudentList):
        """

        :param info:
        :return:
        """
        pass

    @abstractmethod
    def editProc(self, info: Model.StudentListInfo.StudentList):
        """

        :param info:
        :return:
        """
        pass

    @abstractmethod
    def delSql(self, StudentId: int):
        """

        :param StudentId:
        :return:
        """
        pass

    @abstractmethod
    def delProc(self, studentId):
        """

        :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
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)

　　

UI

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

　　

输出：

 

 

 java