python:SQLAlchemy(ORM)SimpleexampleusingSQLite

开源代码
2025-08-28 21:06:01

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

项目结构：

领域层（Domain）：

基础设施层（Infrastructure）：

# 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 mit() 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 mit() 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 mit() 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）：

表现层（Presentation）：

# 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()

调用：

# 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,塗聚文，涂聚文')

输出：

标签：

python:SQLAlchemy(ORM)SimpleexampleusingSQLite由讯客互联开源代码栏目发布，感谢您对讯客互联的认可，以及对我们原创作品以及文章的青睐，非常欢迎各位朋友分享到个人网站或者朋友圈，但转载请说明文章出处“python:SQLAlchemy(ORM)SimpleexampleusingSQLite”

上一篇
特力康输电线路杆塔倾斜智能监测装置：创新技术如何提升电网安全

下一篇
汽车零部件工厂如何通过工业一体机实现精准控制