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:

?
1
2
3
4
5
6
7
8
9
10
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:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
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
"""
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:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
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
"""
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:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
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
"""
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:

?
1
2
3
4
5
6
7
8
9
10
@abstractmethod
    def createStudentList(self)->Interface.IStudentList.IStudentList:
        """
        生成（创建）接口
        :return:
        """
        dals= Interface.IStudentList.IStudentList
        dal=DAL.StudentListDAL.StudentDal()
        dals=dal
        return dals

　　

BLL

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
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
"""
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)

　　

调用：

?
1
2
3
4
5
6
7
8
9
10
11
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)

　　

 

posted @   ®Geovin Du Dream Park™  阅读(8)  评论(0)  编辑  收藏  举报
相关博文：
·  python: more Layer Architecture and its Implementation in SQL Server 2019
·  python: more Layer Architecture and its Implementation in SQLite
·  flask-3
·  4月7日课后总结
·  书生开源大模型训练营-第3讲-作业
阅读排行：
· 25岁的心里话
· 闲置电脑爆改个人服务器（超详细） #公网映射 #Vmware虚拟网络编辑器
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· 零经验选手，Compose 一天开发一款小游戏！
· 一起来玩mcp_server_sqlite，让AI帮你做增删改查！！
历史上的今天：
2010-06-29 asp.net3.5 csharp: How to show HTML content in calendar tooltip?
< 2025年3月 >
日 一 二 三 四 五 六
23 24 25 26 27 28 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31 1 2 3 4 5
点击右上角即可分享
微信分享提示