python: SQLAlchemy (ORM) Simple example using mysql in Ubuntu 24.04

mysql sql script:

create table School  表
(
  `SchoolId` char(5) NOT NULL comment'主鍵primary key，學校編號',
    `SchoolName` nvarchar(500) NOT NULL DEFAULT '' comment' 學校名稱',
    `SchoolTelNo`  varchar(8)  NULL DEFAULT '' comment'電話號碼',
  PRIMARY KEY (SchoolId)   #主鍵
)COMMENT='學校表 School Table' DEFAULT CHARSET=utf8;
 
create table Teacher
(
	`TeacherId` char(5) NOT NULL comment'主鍵primary key，老师編號',
    `TeacherFirstName` nvarchar(100) NOT NULL DEFAULT '' comment' 名',
	`TeacherLastName` nvarchar(20) NOT NULL DEFAULT '' comment' 姓',
    `TeacherGender` char(2) NOT NULL DEFAULT '' comment'性別',
    `TeacherTelNo`  varchar(8)  NULL DEFAULT '' comment'電話號碼',
	 `TeacherSchoolId`  char(5) NOT NULL DEFAULT '' comment'外鍵 foreign key 學校ID',
     PRIMARY KEY (TeacherId),   #主鍵
     FOREIGN KEY(TeacherSchoolId) REFERENCES School(SchoolId)  #外鍵
)COMMENT='老師表Teacher Table' DEFAULT CHARSET=utf8;

　　

 项目结构

 

领域层（Domain Layer）

# encoding: utf-8
# 版权所有 2025 ©涂聚文有限公司 ®
# 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
# 描述：
# Author    : geovindu,Geovin Du 涂聚文.
# IDE       : PyCharm 2023.1 python 3.11
# OS        : Ubuntu 24.0
# database  : mysql 9.0 sql server 2019, postgreSQL 17.0  oracle 21c Neo4j
# Datetime  : 2025/2/20 20:23
# User      : geovindu
# Product   : PyCharm
# Project   : pyMySqlDDDOrmDemo
# File      : domain/entities/school.py
# explain   : 学习
'''
create table School  表
(
  `SchoolId` char(5) NOT NULL comment'主鍵primary key，學校編號',
    `SchoolName` nvarchar(500) NOT NULL DEFAULT '' comment' 學校名稱',
    `SchoolTelNo`  varchar(8)  NULL DEFAULT '' comment'電話號碼',
  PRIMARY KEY (SchoolId)   #主鍵
)COMMENT='學校表 School Table' DEFAULT CHARSET=utf8;
'''
 
 
 
class School:
    """
    领域层（Domain Layer）
    定义业务实体
    """
    def __init__(self, school_id, school_name, school_tel_no):
        """
        :param school_id: 學校編號
        :param school_name: 學校名稱
        :param school_tel_no:電話號碼
        """
        self.school_id = school_id
        self.school_name = school_name
        self.school_tel_no = school_tel_no
 
 
# encoding: utf-8
# 版权所有 2025 ©涂聚文有限公司 ®
# 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
# 描述：
# Author    : geovindu,Geovin Du 涂聚文.
# IDE       : PyCharm 2023.1 python 3.11
# OS        : Ubuntu 24.0
# database  : mysql 9.0 sql server 2019, postgreSQL 17.0  oracle 21c Neo4j
# Datetime  : 2025/2/20 20:27
# User      : geovindu
# Product   : PyCharm
# Project   : pyMySqlDDDOrmDemo
# File      : domain/repositories/school.py
# explain   : 学习
from abc import ABC, abstractmethod
from typing import List
from ..entities.school import School
 
 
class SchoolRepository(ABC):
    """
    领域层（Domain Layer）
    存储库接口
    """
    @abstractmethod
    def add(self, school: School):
        pass
 
    @abstractmethod
    def update(self, school: School):
        pass
 
    @abstractmethod
    def delete(self, school_id: str):
        """
        删除
        :param school_id:
        :return:
        """
        pass
 
    def get_schoolall(self) -> List[School]:
        """
        所有
        :return:
        """
        pass
 
    @abstractmethod
    def get_all(self, page: int, page_size: int, search_query: str = "") -> List[School]:
        """
        分页
        :param page:
        :param page_size:
        :param search_query:
        :return:
        """
 
        pass
 
    @abstractmethod
    def get_total_count(self, search_query: str = "") -> int:
        pass

　　

基础设施层（Infrastructure Layer）

# encoding: utf-8
# 版权所有 2025 ©涂聚文有限公司 ®
# 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
# 描述：
# Author    : geovindu,Geovin Du 涂聚文.
# IDE       : PyCharm 2023.1 python 3.11
# OS        : Ubuntu 24.0
# database  : mysql 9.0 sql server 2019, postgreSQL 17.0  oracle 21c Neo4j
# Datetime  : 2025/2/20 20:46
# User      : geovindu
# Product   : PyCharm
# Project   : pyMySqlDDDOrmDemo
# File      : infrastructure/model/school.py
# explain   : 学习
 
from sqlalchemy import create_engine, Column, String, ForeignKey
from sqlalchemy.orm import sessionmaker, declarative_base, relationship
 
 
 
Base = declarative_base()
 
 
class SchoolModel(Base):
    """
    基础设施层（Infrastructure Layer）
    数据库交互
    分开会报错
    """
    __tablename__ = 'School'
    SchoolId = Column(String(5), primary_key=True)
    SchoolName = Column(String(500), nullable=False, default='')
    SchoolTelNo = Column(String(8), nullable=True, default='')
    teachers = relationship("TeacherModel", back_populates="school")
 
class TeacherModel(Base):
    """
    基础设施层（Infrastructure）
    数据库交互
    """
    __tablename__ = 'Teacher'
    TeacherId = Column(String(5), primary_key=True)
    TeacherFirstName = Column(String(100), nullable=False, default='')
    TeacherLastName = Column(String(20), nullable=False, default='')
    TeacherGender = Column(String(2), nullable=False, default='')
    TeacherTelNo = Column(String(8), nullable=True, default='')
    TeacherSchoolId = Column(String(5), ForeignKey('School.SchoolId'), nullable=False, default='')
    school = relationship("SchoolModel", back_populates="teachers")
 
 
# encoding: utf-8
# 版权所有 2025 ©涂聚文有限公司 ®
# 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
# 描述：
# Author    : geovindu,Geovin Du 涂聚文.
# IDE       : PyCharm 2023.1 python 3.11
# OS        : Ubuntu 24.0
# database  : mysql 9.0 sql server 2019, postgreSQL 17.0  oracle 21c Neo4j
# Datetime  : 2025/2/20 20:46
# User      : geovindu
# Product   : PyCharm
# Project   : pyMySqlDDDOrmDemo
# File      : infrastructure/repositories/school.py
# explain   : 学习
from sqlalchemy import create_engine, Column, String, ForeignKey
from sqlalchemy.orm import sessionmaker, declarative_base, relationship
from domain.entities.school import School
from ..model.school import SchoolModel
from ..database.mysqlHelper import MysqlHeler
from typing import List
class SchoolRepository:
    """
    基础设施层（Infrastructure Layer）
    实现存储库接口
    """
    def __init__(self):
        """
        """
        self._Session = MysqlHeler()
 
 
 
    def add(self, school: School):
        """
        :param school:
        :return:
        """
        session = self._Session.getSession()
        school_model = SchoolModel(SchoolId=school.school_id, SchoolName=school.school_name,
                                   SchoolTelNo=school.school_tel_no)
        session.add(school_model)
        session.commit()
        session.close()
 
    def update(self, school: School):
        """
        :param school:
        :return:
        """
        session = self._Session.getSession()
        school_model = session.query(SchoolModel).filter_by(SchoolId=school.school_id).first()
        if school_model:
            school_model.SchoolName = school.school_name
            school_model.SchoolTelNo = school.school_tel_no
            session.commit()
        session.close()
 
    def delete(self, school_id: str):
        """
        :param school_id:
        :return:
        """
        session = self._Session.getSession()
        school_model = session.query(SchoolModel).filter_by(SchoolId=school_id).first()
        if school_model:
            session.delete(school_model)
            session.commit()
        session.close()
 
    def get_schoolall(self) -> List[School]:
        """
        查询所有所学校
        :return:
        """
        session = self._Session.getSession()
        query = session.query(SchoolModel).all()
        '''
        li=[School(school.SchoolId, school.SchoolName, school.SchoolTelNo) for school in query]
        for i in li:
            print(i.school_id,i.school_name,i.school_tel_no)
        print("query", [School(school.SchoolId, school.SchoolName, school.SchoolTelNo) for school in query])
        '''
        session.close()
        return [School(school.SchoolId, school.SchoolName, school.SchoolTelNo) for school in query]
 
    def get_all(self, page: int, page_size: int, search_query: str = "") -> List[School]:
        """
        :param page:
        :param page_size:
        :param search_query:
        :return:
        """
        session = self._Session.getSession()
        query = session.query(SchoolModel)
        if search_query:
            query = query.filter(
                (SchoolModel.SchoolId.contains(search_query)) |
                (SchoolModel.SchoolName.contains(search_query)) |
                (SchoolModel.SchoolTelNo.contains(search_query))
            )
        offset = (page - 1) * page_size
        school_models = query.offset(offset).limit(page_size).all()
        session.close()
        return [School(school.SchoolId, school.SchoolName, school.SchoolTelNo) for school in school_models]
 
    def get_total_count(self, search_query: str = ""):
        """
        :param search_query:
        :return:
        """
        session = self._Session.getSession()
        query = session.query(SchoolModel)
        if search_query:
            query = query.filter(
                (SchoolModel.SchoolId.contains(search_query)) |
                (SchoolModel.SchoolName.contains(search_query)) |
                (SchoolModel.SchoolTelNo.contains(search_query))
            )
        count = query.count()
        session.close()
        return count

　　

应用层（Application Layer）

# encoding: utf-8
# 版权所有 2025 ©涂聚文有限公司 ®
# 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
# 描述：
# Author    : geovindu,Geovin Du 涂聚文.
# IDE       : PyCharm 2023.1 python 3.11
# OS        : Ubuntu 24.0
# database  : mysql 9.0 sql server 2019, postgreSQL 17.0  oracle 21c Neo4j
# Datetime  : 2025/2/20 20:04
# User      : geovindu
# Product   : PyCharm
# Project   : pyMySqlDDDOrmDemo
# File      : application/services/school.py
# explain   : 学习
 
from typing import List
from domain.entities.school import School
from domain.repositories.school import SchoolRepository
 
 
class SchoolService:
    """
    应用层（Application Layer）
    封装业务逻辑。
    """
    def __init__(self, repository: SchoolRepository):
        """
        :param repository:
        """
        self.repository = repository
 
    def add_school(self, school: School):
        """
        :param school:
        :return:
        """
        self.repository.add(school)
 
    def update_school(self, school: School):
        """
        :param school:
        :return:
        """
        self.repository.update(school)
 
    def delete_school(self, school_id: str):
        """
        :param school_id:
        :return:
        """
        self.repository.delete(school_id)
 
    def get_schoolall(self) -> List[School]:
        """
        所有
        :return:
        """
        return self.repository.get_schoolall()
 
 
    def get_schools(self, page: int, page_size: int, search_query: str = "") -> List[School]:
        """
        :param page:
        :param page_size:
        :param search_query:
        :return:
        """
        return self.repository.get_all(page, page_size, search_query)
 
    def get_total_school_count(self, search_query: str = "") -> int:
        """
        :param search_query:
        :return:
        """
        return self.repository.get_total_count(search_query)

　　

表现层（Presentation Layer）

# encoding: utf-8
# 版权所有 2025 ©涂聚文有限公司 ®
# 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
# 描述：
# Author    : geovindu,Geovin Du 涂聚文.
# IDE       : PyCharm 2023.1 python 3.11
# OS        : Ubuntu 24.0
# database  : mysql 9.0 sql server 2019, postgreSQL 17.0  oracle 21c Neo4j
# Datetime  : 2025/2/20 20:47
# User      : geovindu
# Product   : PyCharm
# Project   : pyMySqlDDDOrmDemo
# File      : presentation/views/school.py
# explain   : 学习
 
 
import tkinter as tk
from tkinter import ttk
 
 
class SchoolView(object):
    """
    表现层（Presentation Layer）
    包含视图和控制器，处理用户界面和操作。
    """
 
    def __init__(self, parent):
        """
        :param parent:
        """
        self.frame = tk.Frame(parent)
 
        # 搜索框
        self.search_frame = ttk.Frame(self.frame)
        self.search_frame.pack(pady=10)
        self.search_entry = ttk.Entry(self.search_frame)
        self.search_entry.pack(side=tk.LEFT, padx=5)
        self.search_button = ttk.Button(self.search_frame, text="Search")
        self.search_button.pack(side=tk.LEFT)
 
        # Treeview
        self.tree = ttk.Treeview(self.frame, columns=('SchoolId', 'SchoolName', 'SchoolTelNo'), show='headings')
        self.tree.heading('SchoolId', text='School ID')
        self.tree.heading('SchoolName', text='School Name')
        self.tree.heading('SchoolTelNo', text='School Tel No')
        self.tree.pack(pady=10)
 
        # 操作按钮
        self.button_frame = ttk.Frame(self.frame)
        self.button_frame.pack(pady=10)
        self.add_button = ttk.Button(self.button_frame, text="Add")
        self.add_button.pack(side=tk.LEFT, padx=5)
        self.edit_button = ttk.Button(self.button_frame, text="Edit")
        self.edit_button.pack(side=tk.LEFT, padx=5)
        self.delete_button = ttk.Button(self.button_frame, text="Delete")
        self.delete_button.pack(side=tk.LEFT, padx=5)
 
        # 分页按钮
        self.pagination_frame = ttk.Frame(self.frame)
        self.pagination_frame.pack(pady=10)
        self.prev_button = ttk.Button(self.pagination_frame, text="Previous")
        self.prev_button.pack(side=tk.LEFT, padx=5)
        self.page_label = ttk.Label(self.pagination_frame, text="Page 1 of 1")
        self.page_label.pack(side=tk.LEFT, padx=5)
        self.next_button = ttk.Button(self.pagination_frame, text="Next")
        self.next_button.pack(side=tk.LEFT, padx=5)
 
    def clear_tree(self):
        """
        :return:
        """
        for item in self.tree.get_children():
            self.tree.delete(item)
 
    def populate_tree(self, schools):
        """
        :param schools:
        :return:
        """
        for school in schools:
            self.tree.insert('', 'end', values=(school.school_id, school.school_name, school.school_tel_no))
 
    def update_page_label(self, current_page, total_pages, tatol,pagesize):
        """
        :param current_page:
        :param total_pages:
        :param tatol
        :param pagesize
        :return:
        """
        self.page_label.config(text=f"Page {current_page} of {total_pages}  {pagesize}/total:{tatol}")
 
    def open_add_window(self, save_callback):
        """
        :param save_callback:
        :return:
        """
        top = tk.Toplevel(self.frame)
        top.title("Add School")
        top.iconbitmap("favicon.ico")
        ttk.Label(top, text="School ID:").grid(row=0, column=0, padx=5, pady=5)
        id_entry = ttk.Entry(top)
        id_entry.grid(row=0, column=1, padx=5, pady=5)
 
        ttk.Label(top, text="School Name:").grid(row=1, column=0, padx=5, pady=5)
        name_entry = ttk.Entry(top)
        name_entry.grid(row=1, column=1, padx=5, pady=5)
 
        ttk.Label(top, text="School Tel No:").grid(row=2, column=0, padx=5, pady=5)
        tel_entry = ttk.Entry(top)
        tel_entry.grid(row=2, column=1, padx=5, pady=5)
 
        def save_school():
            """
            :return:
            """
            school_id = id_entry.get()
            school_name = name_entry.get()
            school_tel_no = tel_entry.get()
            if school_id and school_name and school_tel_no:
                save_callback(school_id, school_name, school_tel_no)
                top.destroy()
 
        ttk.Button(top, text="Save", command=save_school).grid(row=3, column=0, columnspan=2, pady=10)
 
    def open_edit_window(self, school_id, school_name, school_tel_no, update_callback):
        """
        :param school_id:
        :param school_name:
        :param school_tel_no:
        :param update_callback:
        :return:
        """
        top = tk.Toplevel(self.frame)
        top.title("Edit School")
        #top.iconbitmap("favicon.ico")
        ttk.Label(top, text="School ID:").grid(row=0, column=0, padx=5, pady=5)
        id_entry = ttk.Entry(top)
        id_entry.insert(0, school_id)
        id_entry.config(state='readonly')
        id_entry.grid(row=0, column=1, padx=5, pady=5)
 
        ttk.Label(top, text="School Name:").grid(row=1, column=0, padx=5, pady=5)
        name_entry = ttk.Entry(top)
        name_entry.insert(0, school_name)
        name_entry.grid(row=1, column=1, padx=5, pady=5)
 
        ttk.Label(top, text="School Tel No:").grid(row=2, column=0, padx=5, pady=5)
        tel_entry = ttk.Entry(top)
        tel_entry.insert(0, school_tel_no)
        tel_entry.grid(row=2, column=1, padx=5, pady=5)
 
        def update_school():
            """
            :return:
            """
            new_school_name = name_entry.get()
            new_school_tel_no = tel_entry.get()
            if new_school_name and new_school_tel_no:
                update_callback(school_id, new_school_name, new_school_tel_no)
                top.destroy()
 
        ttk.Button(top, text="Update", command=update_school).grid(row=3, column=0, columnspan=2, pady=10)
 
 
# encoding: utf-8
# 版权所有 2025 ©涂聚文有限公司 ®
# 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
# 描述：
# Author    : geovindu,Geovin Du 涂聚文.
# IDE       : PyCharm 2023.1 python 3.11
# OS        : Ubuntu 24.0
# database  : mysql 9.0 sql server 2019, postgreSQL 17.0  oracle 21c Neo4j
# Datetime  : 2025/2/20 20:47
# User      : geovindu
# Product   : PyCharm
# Project   : pyMySqlDDDOrmDemo
# File      : presentation/controllers/school.py
# explain   : 学习
 
import tkinter as tk
from application.services.school import SchoolService
from domain.entities.school import School
 
 
class SchoolController(object):
    """
    表现层（Presentation Layer）
    包含视图和控制器，处理用户界面和操作。
    """
    def __init__(self, service: SchoolService, view):
        """
        :param service:
        :param view:
        """
        self.service = service
        self.view = view
        self.current_page = 1
        self.page_size = 10
        self.search_query = ""
        self.total_pages = 1
 
        self.view.search_button.config(command=self.search)
        self.view.add_button.config(command=self.add)
        self.view.edit_button.config(command=self.edit)
        self.view.delete_button.config(command=self.delete)
        self.view.prev_button.config(command=self.prev_page)
        self.view.next_button.config(command=self.next_page)
 
        self.load_data()
 
    def load_data(self):
        """
        :return:
        """
        schools = self.service.get_schools(self.current_page, self.page_size, self.search_query)
        total_count = self.service.get_total_school_count(self.search_query)
        self.total_pages = (total_count + self.page_size - 1) // self.page_size
 
        self.view.clear_tree()
        self.view.populate_tree(schools)
        self.view.update_page_label(self.current_page, self.total_pages,total_count,self.page_size)
 
        self.view.prev_button.config(state=tk.NORMAL if self.current_page > 1 else tk.DISABLED)
        self.view.next_button.config(state=tk.NORMAL if self.current_page < self.total_pages else tk.DISABLED)
 
    def search(self):
        """
        :return:
        """
        self.search_query = self.view.search_entry.get()
        self.current_page = 1
        self.load_data()
 
    def add(self):
        """
        :return:
        """
 
        def save_callback(school_id, school_name, school_tel_no):
            new_school = School(school_id, school_name, school_tel_no)
            self.service.add_school(new_school)
            self.load_data()
 
        self.view.open_add_window(save_callback)
 
    def edit(self):
        """
        :return:
        """
 
        selected_item = self.view.tree.selection()
        if selected_item:
            values = self.view.tree.item(selected_item, 'values')
            school_id, school_name, school_tel_no = values
 
            def update_callback(school_id, new_school_name, new_school_tel_no):
                updated_school = School(school_id, new_school_name, new_school_tel_no)
                self.service.update_school(updated_school)
                self.load_data()
 
            self.view.open_edit_window(school_id, school_name, school_tel_no, update_callback)
 
    def delete(self):
        """
        :return:
        """
        selected_item = self.view.tree.selection()
        if selected_item:
            school_id = self.view.tree.item(selected_item, 'values')[0]
            self.service.delete_school(school_id)
            self.load_data()
 
    def prev_page(self):
        """
        :return:
        """
        if self.current_page > 1:
            self.current_page -= 1
            self.load_data()
 
    def next_page(self):
        """
        :return:
        """
        if self.current_page < self.total_pages:
            self.current_page += 1
            self.load_data()

　调用：

# encoding: utf-8
# 版权所有 2025 ©涂聚文有限公司 ®
# 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
# 描述：先安装： python.exe -m pip install --upgrade pip
#  pip install pymysql
#  pip install sqlalchemy
#  sudo apt install python3-tk
#  pip install pillow
# Author    : geovindu,Geovin Du 涂聚文.
# IDE       : PyCharm 2023.1 python 3.11
# OS        : Ubuntu 24.0
# database  : mysql 9.0 sql server 2019, postgreSQL 17.0  oracle 21c Neo4j
# Datetime  : 2025/2/20 20:21
# User      : geovindu
# Product   : PyCharm
# Project   : pyMySqlDDDOrmDemo
# File      : main.py
# explain   : 学习
'''
DDD
定义业务实体和存储库接口
domain
	--entities
	--repositories
基础设施层（Infrastructure）
实现存储库接口，与数据库交互。
infrastructure
 	--database
	--model
	--repositories
应用层（Application）
封装业务逻辑。
application
	--services
表现层（Presentation）
包含视图和控制器，处理用户界面和操作。
presentation
	--views
	--controllers
'''
 
 
import tkinter as tk
from infrastructure.repositories import SchoolRepository,TeacherRepository    # init 必须配置，才可以这样引用
from application.services import SchoolService, TeacherService
from presentation.views import SchoolView, TeacherView
from presentation.controllers import SchoolController, TeacherController
 
 
if __name__ == '__main__':
    """    
    主程序输出
    """
    root = tk.Tk()
    root.title("学校老师管理系统")
 
    # 创建数据库存储库实例
    school_repository = SchoolRepository()
    teacher_repository = TeacherRepository()
 
    # 创建服务实例
    school_service = SchoolService(school_repository)
    teacher_service = TeacherService(teacher_repository)
 
    # 创建视图实例
    school_view = SchoolView(root)
    teacher_view = TeacherView(root)
    school_view.frame.pack_forget()
    teacher_view.frame.pack_forget()
 
    # 创建控制器实例
    school_controller = SchoolController(school_service, school_view)
    # 需要加上学校的务服实例。便于选择学校
    teacher_controller = TeacherController(teacher_service, school_service, teacher_view)
 
 
 
    # 创建主菜单
    menubar = tk.Menu(root)
    root.config(menu=menubar)
 
    # 创建主菜单下的子菜单
    main_menu = tk.Menu(menubar, tearoff=0)
    menubar.add_cascade(label="管理菜单", menu=main_menu)
 
    # 学校管理子菜单
    school_menu = tk.Menu(main_menu, tearoff=0)
    main_menu.add_cascade(label="学校管理", menu=school_menu)
    school_menu.add_command(label="查看学校信息", command=lambda: (
        school_view.frame.pack(),
        teacher_view.frame.pack_forget()  # 隐藏
    ))
 
    # 老师管理子菜单
    teacher_menu = tk.Menu(main_menu, tearoff=0)
    main_menu.add_cascade(label="老师管理", menu=teacher_menu)
    teacher_menu.add_command(label="查看老师信息", command=lambda: (
        teacher_view.frame.pack(),
        school_view.frame.pack_forget()   # 隐藏
    ))
    #root.iconbitmap(delattr="favicon.ico")
    #root.tk.call('wm', 'iconphoto', root._w, tk.PhotoImage(file='favicon.ico'))
    root.mainloop()
    print('PyCharm,geovindu,Geovin Du,涂聚文！')
 

　　

输出：