python: sqlalchemy ORM in mysql

 

"""
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/

　　

输出：