""" 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/
输出:
标签:sqlalchemy,return,python,self,StudentId,ORM,student,def,cls From: https://www.cnblogs.com/geovindu/p/17542521.html