"""
StudengMaping.py ORM (Object Relational Mapping)
学生表实体类对象关系映射 one-one one-more more-one more-more
date 2023-06-23
edit: Geovin Du,geovindu, 涂聚文
ide: PyCharm 2023.1 python 11
sqlalchemy 2.0.1.6
https://docs.sqlalchemy.org/en/20/core/type_basics.html
https://github.com/sqlalchemy/sqlalchemy/blob/main/lib/sqlalchemy/dialects/mysql/base.py
https://www.fullstackpython.com/sqlalchemy-dialects-mysql-examples.html
https://docs.sqlalchemy.org/en/20/orm/basic_relationships.html
https://improveandrepeat.com/2021/07/python-friday-78-relationship-patterns-in-sqlalchemy-core/
https://auth0.com/blog/sqlalchemy-orm-tutorial-for-python-developers/
https://docs.sqlalchemy.org/en/20/orm/relationship_persistence.html
https://improveandrepeat.com/2021/08/python-friday-83-relationship-patterns-in-sqlalchemy-orm/
from sqlalchemy.orm import mapped_column
get,set 不需要了
"""
import datetime
from sqlalchemy.dialects.mysql import INTEGER, VARCHAR,DATETIME,FLOAT
#from sqlalchemy.dialects.mssql
#from sqlalchemy.dialects.postgresql
#from sqlalchemy.dialects.oracle
#from sqlalchemy.dialects.sqlite
#from sqlalchemy.dialects.firebird
#from sqlalchemy.dialects.sybase
from sqlalchemy import Table, MetaData, Column, String, ForeignKey
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from datetime import date
import DBUtility.MySqlHelper
from sqlalchemy.orm import relationship
Base = declarative_base()
class Student(Base):
"""
学生表映射
"""
__tablename__ = 'StudentList'
"""
实际表名称
"""
StudentId = Column(INTEGER, primary_key=True)
"""
学生ID
"""
StudentName = Column(VARCHAR(256), nullable=False)
"""
学生姓名
"""
StudentNO = Column(VARCHAR(256), nullable=False)
"""
学号
"""
StudentBirthday = Column(DATETIME)
"""
学生出生日期
"""
#studentClass = relationship("StudentClass", uselist=False, back_populates="student")
#StudentId: Mapped[int] = mapped_column(primary_key=True)
my = DBUtility.MySqlHelper.MySqlhelper()
'''
def __init__(self,StudentId:int,StudentName:str, StudentNO:str,StudentBirthday:datetime.datetime):
"""
:param StudentId:
:param StudentName:
:param StudentNO:
:param StudentBirthday:
"""
self._StudentName=StudentName
self._StudentNO=StudentNO
self._StudentBirthday=StudentBirthday
self._StudentId=StudentId
'''
def __init__(self):
"""
"""
self.__StudentName=""#cls.StudentName
self.__StudentNO=""#cls.StudentNO
self.__StudentBirthday=datetime.datetime.today()#cls.StudentBirthday
self.__StudentId=0#cls.StudentId
#self.__age=0
'''
def __del__(self):
"""
:return:
"""
print(f"{self._StudentName}")
'''
'''
不可用
@property
def StudentId(self):
"""
:return:
"""
return self.__StudentId
@StudentId.setter
def StudentId(self,studentID):
"""
:param studentID:
:return:
"""
self.__StudentId=studentID
@property
def StudentName(self):
"""
:return:
"""
return self.__StudentName
@StudentName.setter
def StudentName(self,studentName):
"""
:param studentName:
:return:
"""
self.__StudentName=studentName
@property
def StudentNO(self):
"""
:return:
"""
return self.__StudentNO
@StudentNO.setter
def StudentNO(self,studnetNO):
"""
:param studnetNO:
:return:
"""
self.__StudentNO=studnetNO
@property
def StudentBirthday(self):
"""
:return:
"""
return self.__StudentBirthday
@StudentBirthday.setter
def StudentBirthday(self,studentBirthday:datetime.datetime):
"""
:param studentBirthday:
:return:
"""
self.__StudentBirthday=studentBirthday
'''
def setStudentName(self,StudentName):
"""
:param StudentName:
:return:
"""
self.__StudentName = StudentName
self.StudentName=StudentName
def getStudentName(self):
"""
:return:
"""
return self.__StudentName
def setStudentNO(self,StudentNO):
"""
:param StudentNO:
:return:
"""
self.__StudentNO=StudentNO
self.StudentNO=StudentNO
def getStudentNO(self):
"""
:return:
"""
return self.__StudentNO
def setStudentId(self,StudentId):
"""
:param StudentId:
:return:
"""
self.__StudentId=StudentId
self.StudentId=StudentId
def getStudentId(self):
"""
:return:
"""
return self.__StudentId
def setStudentBirthday(self,StudentBirthday):
"""
:param StudentBirthday:
:return:
"""
self.__StudentBirthday = StudentBirthday
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 getAge(self):
return datetime.datetime.today().year-self.__StudentBirthday.year
'''
def CreateTable(cls):
"""
创建表 mysql+pymysql://root:770214@localhost:3306/geovindu?charset=utf8mb4
:return:
"""
'''
str=cls.my.getconnstring()
print(str)
user="root"
password="770214"
host="localhost"
port="3306"
database="geovindu"
url = "mysql+pymysql://{0}:{1}@{2}:{3}/{4}?charset=utf8mb4".format(
user, password, host, port, database
)
print(url)
'''
#engine = create_engine(str,echo=True)
#Base.metadata.drop_all(cls.my.getEngine())
Base.metadata.create_all(cls.my.getEngine())
print('Create table successfully!')
'''
def Add(cls,student):
"""
:param student:
:return:
"""
cls.my.getSessioin().add(student)
cls.my.getSessioin().commit()
cls.my.getSessioin().close()
def AddMore(cls,students:list):
"""
:param students:
:return:
"""
cls.my.getSessioin().add_all(students)
cls.my.getSessioin().commit()
cls.my.getSessioin().close()
def Select(cls,StudentId:int):
"""
:param StudentId:
:return:
"""
students = cls.my.getSessioin().query(cls).filter(cls._StudentId==StudentId).all()
cls.my.getSessioin().close()
return students
def Select(cls):
"""
:return:
"""
students=cls.my.getSessioin().query(cls).all()
cls.my.getSessioin().close()
return students
def Update(cls,student):
"""
:param student:
:return:
"""
updateobje=cls.my.getSessioin().query(cls).filter(cls.StudentId==student.getStudentId()).update({"StudentName":student.getStudentName(),"StudentNO":student.getStudentNO(),"StudentBirthday":student.getStudentBirthday()})
cls.my.getSessioin().commit()
cls.my.getSessioin().close()
def delRecorder(cls,studentId:int):
"""
:param studentId:
:return:
"""
delobje=cls.my.getSessioin().query(cls).filter(cls.StudentId==studentId).delete()
cls.my.getSessioin().commit()
cls.my.getSessioin().close()
'''
def __str__(self):
"""
:return:
"""
return f"{self._StudentId},{self._StudentName},{self._StudentNO},{self._StudentBirthday}"
"""
StudentDAL.py
数据业务处理层 Data Access Layer (DAL)
SQL Server 数据库操作
date 2023-06-21
edit: Geovin Du,geovindu, 涂聚文
ide: PyCharm 2023.1 python 11
https://docs.sqlalchemy.org/en/20/orm/basic_relationships.html
One To One
One To Many
Many To One
Nullable Many-to-One
One To One
Many To Many
Setting Bi-Directional Many-to-many
"""
import DBUtility.MySqlHelper
import Mapping.StudentMaping
import Interface.IStudentList
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
class StudentDal(Interface.IStudentList.IStudentList):
"""
学生表数据处理
"""
my = DBUtility.MySqlHelper.MySqlhelper()
def __init__(self):
self_name=""
def Add(cls,student):
"""
添加一条
:param student:
:return:
"""
try:
#print(student)
session = cls.my.getSessioin()
session.add(student)
session.commit()
session.close()
except Exception as ex:
print(ex)
def AddMore(cls,students:list):
"""
添加多条
:param students:
:return:
"""
session = cls.my.getSessioin()
session.add_all(students)
session.commit()
session.close()
def SelectId(cls,StudentId:int):
"""
根据主健ID查找
:param StudentId:
:return:
"""
session = cls.my.getSessioin()
students = session.query(Mapping.StudentMaping.Student).filter(Mapping.StudentMaping.Student.StudentId==StudentId).all()
session.close()
#print(students)
return students
def Select(cls):
"""
查找
:return:
"""
session = cls.my.getSessioin()
students=session.query(Mapping.StudentMaping.Student).all()
session.close()
return students
def Update(cls,student:Mapping.StudentMaping.Student):
"""
更新
:param student:
:return:
"""
session = cls.my.getSessioin()
updateobje=session.query(Mapping.StudentMaping.Student).filter(Mapping.StudentMaping.Student.StudentId==student.StudentId).update({"StudentName":student.StudentName,"StudentNO":student.StudentNO,"StudentBirthday":student.StudentBirthday})
session.commit()
session.close()
def delRecorder(cls,studentId:int):
"""
删除
:param studentId:
:return:
"""
session = cls.my.getSessioin()
delobje=session.query(Mapping.StudentMaping.Student).filter(Mapping.StudentMaping.Student.StudentId==studentId).delete()
session.commit()
session.close()
"""
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 Mapping.StudentMaping
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 Add(cls,studentClass):
"""
:param order:
:return:
"""
pass
@abstractmethod
def AddMore(cls,studentClasses:list):
"""
:param StudentId:
:return:
"""
pass
@abstractmethod
def SelectId(cls,studentClassId:int)->list:
"""
:return:
"""
pass
@abstractmethod
def Select(cls)->list:
"""
:param StudentId:
:return:
"""
pass
@abstractmethod
def Update(cls,studentClass:Mapping.StudentClassMapping.StudentClass):
"""
:param info:
:return:
"""
pass
@abstractmethod
def delRecorder(cls,studentClassId:int):
"""
:param info:
:return:
"""
pass
"""
StudentBLL.py
业务层 Business Logic Layer (BLL)
date 2023-06-27
edit: Geovin Du,geovindu, 涂聚文
ide: PyCharm 2023.1 python 11
"""
import Mapping.StudentMaping
import Factory.AbstractFactory
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
class Student(object):
"""
学生表业务处理
"""
dal = Factory.AbstractFactory.AbstractFactory.createStudentList
#dal=DAL.StudentDAL.StudentDal()
def __init__(self):
self.name=""
#@classmethod
def CreateTable(slef):
"""
初始化，创建表格
:return:
"""
create = Mapping.StudentMaping.Student()
create.CreateTable()
print("表格添加成功！")
def Add(cls, student):
"""
添加一条
:param student:
:return:
"""
cls.dal().Add(student)
def AddMore(cls,students:list):
"""
添加多条
:param students:
:return:
"""
cls.dal().AddMore(students)
def SelectId(cls,StudentId:int):
"""
根据主键ID查询一条
:param StudentId:
:return:
"""
#print(type(cls.dal))
#cls.dal().SelectId(StudentId)
return cls.dal().SelectId(StudentId)
def Select(cls):
"""
查询所有
:return:
"""
return cls.dal().Select()
def Update(cls, student: Mapping.StudentMaping.Student):
"""
更新一条
:param student:
:return:
"""
cls.dal().Update(student)
def delRecorder(cls, studentId: int):
"""
删除一条
:param studentId:
:return:
"""
cls.dal().delRecorder(studentId)
调用：
# This is a sample Python script.
"""
main.py
edit： geovindu,Geovin Du,涂聚文
date 2023-06-19
ide: PyCharm 2023.1 python 11
Ms SQL Server
BLL、DAL、IDAL、MODEL、DBUtility、DALFactory
Mapping
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
pip install sqlacodegen 使用逆向工程工具自动生成Sqlalchemy Mapping类
sqlacodegen mysql+mysqlconnector://root:password@localhost:3306/test --outfile geovindu.py
# 将本地test数据库逆向生成到geovindu.py代码中
"""
# Press Shift+F10 to execute it or replace it with your code.
# Press Double Shift to search everywhere for classes, files, tool windows, actions, and settings.
import DBUtility.MySqlHelper
import BLL.StudentBLL
import Mapping.StudentMaping
import datetime
from sqlalchemy.ext.declarative import declarative_base
def print_hi(name):
# Use a breakpoint in the code line below to debug your script.
print(f'Hi, {name}') # Press Ctrl+F8 to toggle the breakpoint.
# Press the green button in the gutter to run the script.
if __name__ == '__main__':
print_hi('PyCharm，涂聚文')
#StudentId = 1, StudentName = 'geovindu', StudentNO = '0001', StudentBirthday = datetime.datetime(2007, 12, 5)
#stu=Mapping.StudentMaping.Student()
#stu.CreateTable()
stubll=BLL.StudentBLL.Student()
#stubll.CreateTable()
#添加
student=Mapping.StudentMaping.Student() #StudentId = 1, StudentName = 'geovindu', StudentNO = '0001', StudentBirthday = datetime.datetime(2007, 12, 5)
#这里不赋值，无法添加，否则提示是空值
students=[]
#student.setStudentId(5) 添加ID 不需要赋值
student.setStudentName("艾伟")
student.setStudentNO("0011")
student.setStudentBirthday(datetime.datetime(2007, 8,14))
student1 = Mapping.StudentMaping.Student()
students.append(student)
#student1.setStudentId(12)
student1.setStudentName("何贵为")
student1.setStudentNO("0006")
student1.setStudentBirthday(datetime.datetime(2007, 7,23))
students.append(student1)
for sdd in students:
print(sdd.StudentId,sdd.StudentName,sdd.StudentNO,sdd.StudentBirthday)
#stubll.AddMore(students)
#stubll.Add(student)
#stubll.Add(student)
#修改
'''
student.StudentId=5
student.StudentName="刘杰"
student.StudentNO="0004"
student.StudentBirthday=datetime.datetime(2007, 2,14)
#stubll.Update(student)
'''
#查询
data=stubll.SelectId(1)
for sd in data:
print(sd.StudentId,sd.StudentName,sd.StudentNO,sd.StudentBirthday)
#print(data)
datalist=stubll.Select()
for ls in datalist:
print(ls.StudentId,ls.StudentName,ls.StudentNO,ls.StudentBirthday)
# See PyCharm help at https://www.jetbrains.com/help/pycharm/
输出：
