python: SQLAlchemy (ORM) Simple example using SQLite

领域层（Domain）：定义了 School 实体类和 SchoolRepository 抽象基类，明确了业务实体和数据访问的契约。
基础设施层（Infrastructure）：通过 SQLAlchemy 实现了 SchoolRepository 类，负责与 SQLite 数据库进行交互，包括增删改查操作。
应用层（Application）：SchoolService 类封装了业务逻辑，调用 SchoolRepository 接口的方法完成具体的业务操作。
表现层（Presentation）：
SchoolView 类负责创建和管理用户界面，包括搜索框、ttk.Treeview 表格、操作按钮和分页按钮。
SchoolController 类处理用户的操作请求，调用 SchoolService 完成相应的业务逻辑，并更新视图。
项目结构：

DDD
定义业务实体和存储库接口
domain
　　--entities
　　--repositories

基础设施层（Infrastructure）
实现存储库接口，与数据库交互。
infrastructure
　　--database
　　--model
　　--repositories
应用层（Application）
封装业务逻辑。
application
　　--services

表现层（Presentation）
包含视图和控制器，处理用户界面和操作。
presentation
　　--views
　　--controllers

 

领域层（Domain）：

?

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

# encoding: utf-8 # 版权所有 2025 ©涂聚文有限公司 ® # 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎 # 描述： # Author    : geovindu,Geovin Du 涂聚文. # IDE       : PyCharm 2023.1 python 3.11 # OS        : windows 10 # database  : mysql 9.0 sql server 2019, poostgreSQL 17.0  oracle 21c Neo4j # Datetime  : 2025/2/19 20:39 # User      : geovindu # Product   : PyCharm # Project   : pySQLiteDDDOrmDemo # File      : domain/entities/school.py # explain   : 学习    class School:     """     领域层（Domain）     """     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        : windows 10 # database  : mysql 9.0 sql server 2019, poostgreSQL 17.0  oracle 21c Neo4j # Datetime  : 2025/2/19 20:40 # User      : geovindu # Product   : PyCharm # Project   : pySQLiteDDDOrmDemo # File      : domain/repositories/schoolRepositories.py # explain   : 学习 from abc import ABC, abstractmethod from typing import List from ..entities.school import School       class SchoolRepository(ABC):     """      领域层（Domain） 接口     """     @abstractmethod     def add(self, school: School):         """         :param school:         :return:         """         pass        @abstractmethod     def update(self, school: School):         """         :param school:         :return:         """         pass        @abstractmethod     def delete(self, school_id: str):         """         :param school_id:         :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:         """         :param search_query:         :return:         """         pass

　　

基础设施层（Infrastructure）：

?

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

# encoding: utf-8 # 版权所有 2025 涂聚文有限公司 # 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎 # 描述： # Author    : geovindu,Geovin Du 涂聚文. # IDE       : PyCharm 2023.1 python 3.11 # os        : windows 10 # database  : mysql 9.0 sql server 2019, poostgreSQL 17.0  Oracle 21c # Datetime  : 2025/2/19 21:30 # User      : geovindu # Product   : PyCharm # Project   : pySQLiteDDDOrmDemo # File      : school.py # explain   : 学习    from sqlalchemy import create_engine, Column, String from sqlalchemy.orm import sessionmaker, declarative_base from domain.entities.school import School from ..database.sqlitehelper import SqliteHelper    Base = declarative_base()       class SchoolModel(Base):     """     基础设施层（Infrastructure）     """     __tablename__ = 'School'     SchoolId = Column(String, primary_key=True)     SchoolName = Column(String, nullable=False)     SchoolTelNo = Column(String, nullable=False)       # encoding: utf-8 # 版权所有 2025 ©涂聚文有限公司 ® # 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎 # 描述： # Author    : geovindu,Geovin Du 涂聚文. # IDE       : PyCharm 2023.1 python 3.11 # OS        : windows 10 # database  : mysql 9.0 sql server 2019, poostgreSQL 17.0  oracle 21c Neo4j # Datetime  : 2025/2/19 21:41 # User      : geovindu # Product   : PyCharm # Project   : pySQLiteDDDOrmDemo # File      : school.py # explain   : 学习    from sqlalchemy import create_engine, Column, String from sqlalchemy.orm import sessionmaker, declarative_base from domain.entities.school import School from ..database.sqlitehelper import SqliteHelper from ..model.school import SchoolModel    class SchoolRepository:     """     基础设施层（Infrastructure）     """        def __init__(self):         """         """         self._session = SqliteHelper()  # Session()           def add(self, school: School):         """         :param school:         :return:         """         session = self._session.getSession()    #  Session()  #         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()    # Session() #         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()    # Session()  #         school_model = session.query(SchoolModel).filter_by(SchoolId=school_id).first()         if school_model:             session.delete(school_model)             session.commit()         session.close()        def get_all(self, page: int, page_size: int, search_query: str = ""):         """         :param page:         :param page_size:         :param search_query:         :return:         """         session = self._session.getSession()  #  Session()  #         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()    # Session()  #         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）：

?

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

# encoding: utf-8 # 版权所有 2025 ©涂聚文有限公司 ® # 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎 # 描述： # Author    : geovindu,Geovin Du 涂聚文. # IDE       : PyCharm 2023.1 python 3.11 # OS        : windows 10 # database  : mysql 9.0 sql server 2019, poostgreSQL 17.0  oracle 21c Neo4j # Datetime  : 2025/2/19 21:10 # User      : geovindu # Product   : PyCharm # Project   : pySQLiteDDDOrmDemo # File      : application/services/schoolServices.py # explain   : 学习    from typing import List from domain.entities.school import School from domain.repositories.schoolRepositories import SchoolRepository       class SchoolService:     """     应用层（Application）     """     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_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_count(self, search_query: str = "") -> int:         """         :param search_query:         :return:         """         return self.repository.get_total_count(search_query)

　　

表现层（Presentation）：

?

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 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255

# encoding: utf-8 # 版权所有 2025 ©涂聚文有限公司 ® # 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎 # 描述： # Author    : geovindu,Geovin Du 涂聚文. # IDE       : PyCharm 2023.1 python 3.11 # OS        : windows 10 # database  : mysql 9.0 sql server 2019, poostgreSQL 17.0  oracle 21c Neo4j # Datetime  : 2025/2/19 18:46 # User      : geovindu # Product   : PyCharm # Project   : pySQLiteDDDOrmDemo # File      : presentation/views/schoolViews.py # explain   : 学习    import tkinter as tk from tkinter import ttk       class SchoolView:     """     表现层（Presentation） UI     """     def __init__(self, root):         self.root = root         self.root.title("School Management")            # 搜索框         self.search_frame = ttk.Frame(root)         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(root, 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(root)         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(root)         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):         for item in self.tree.get_children():             self.tree.delete(item)        def populate_tree(self, schools):         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):         self.page_label.config(text=f"Page {current_page} of {total_pages}")        def open_add_window(self, save_callback):         top = tk.Toplevel(self.root)         top.title("Add School")            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():             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):         top = tk.Toplevel(self.root)         top.title("Edit School")            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():             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 ©涂聚文有限公司 ® # 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎 # 描述： controllers  views 可以分开 # Author    : geovindu,Geovin Du 涂聚文. # IDE       : PyCharm 2023.1 python 3.11 # OS        : windows 10 # database  : mysql 9.0 sql server 2019, poostgreSQL 17.0  oracle 21c Neo4j # Datetime  : 2025/2/19 21:48 # User      : geovindu # Product   : PyCharm # Project   : pySQLiteDDDOrmDemo # File      : presentation/controllers/schoolControllers.py # explain   : 学习    import tkinter as tk from application.services.schoolServices import SchoolService from domain.entities.school import School       class SchoolController:     """     表现层（Presentation）     """     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_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)            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()

　　

调用：

?

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

# encoding: utf-8 # 版权所有 2025 涂聚文有限公司 # 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎 # 描述：  Object-Relational Mapping (ORM) # Author    : geovindu,Geovin Du 涂聚文. # IDE       : PyCharm 2023.1 python 3.11 # os        : windows 10 # database  : mysql 9.0 sql server 2019, poostgreSQL 17.0  Oracle 21c # Datetime  : 2025/2/19 21:02 # User      : geovindu # Product   : PyCharm # Project   : pySQLiteDDDOrmDemo # File      : main.py # explain   : 学习 import tkinter as tk    from Infrastructure.repository.school import SchoolRepository from application.services.schoolServices import SchoolService from presentation.views.schoolViews import SchoolView from presentation.controllers.schoolControllers import SchoolController    if __name__ == '__main__':     """     """     root = tk.Tk()     repository = SchoolRepository()     service = SchoolService(repository)     view = SchoolView(root)     controller = SchoolController(service, view)     root.iconbitmap("favicon.ico")     root.mainloop()     print('PyCharm,geovindu,Geovin Du,塗聚文，涂聚文')

　　

输出：