python: more Layer Architecture and its Implementation in mysql 8.0

 

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

pip install ttkbootstrap
pip install PyQt5

pip install PyQt5-tools

pip install wxPython

pip install sqlacodegen 使用逆向工程工具自动生成Sqlalchemy Mapping类

Webcam QR code scanner using OpenCV
https://pypi.org/project/web-browser/

pip install OpenCV
pip install webbrowser ( built in )

import webbrowser
webbrowser.open('https://www.python.org')
https://www.geeksforgeeks.org/webcam-qr-code-scanner-using-opencv/

pip install opencv-python
pip install web-browser
pip install pywebview
pip install dbr
pip install qreader

https://www.dynamsoft.com/codepool/opencv-python-webcam-barcode-reader.html
https://github.com/yushulx/webcam-barcode-qrcode-reader-python
https://github.com/Eric-Canas/QReader

https://likegeeks.com/python-gui-examples-tkinter-tutorial/#Get_input_using_Entry_class_Tkinter_textbox
https://docs.python.org/3/library/tk.html
https://zetcode.com/tkinter/menustoolbars/
https://realpython.com/python-menus-toolbars/
https://pythonguides.com/python-tkinter-menu-bar/
https://www.delftstack.com/tutorial/tkinter-tutorial/tkinter-menubar/
https://apidemos.com/tkinter/tkinter-menu/tkinter-menu-create-toolbar.html#ftoc-heading-1

 

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)