sql script:
create table GEOVINDU.School --創建表
(
SchoolId char(5) NOT NULL, --
SchoolName nvarchar2(500) NOT NULL,
SchoolTelNo varchar(8) NULL,
PRIMARY KEY (SchoolId) --#主鍵
);create table GEOVINDU.Teacher
(
TeacherId char(5) NOT NULL ,
TeacherFirstName nvarchar2(100) NOT NULL,
TeacherLastName nvarchar2(20) NOT NULL,
TeacherGender char(2) NOT NULL,
TeacherTelNo varchar(8) NULL,
TeacherSchoolId char(5) NOT NULL,
PRIMARY KEY (TeacherId), ---#主鍵
FOREIGN KEY(TeacherSchoolId) REFERENCES School(SchoolId) --#外鍵
);项目结构：
# 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, postgreSQL 17.0 oracle 21c Neo4j
# Datetime : 2025/3/7 20:26
# User : geovindu
# Product : PyCharm
# Project : pyOracleDDDOrmDemo
# File : teacher.py
# explain : 学习
from sqlalchemy import Column, String, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import declarative_base,relationshipBase = declarative_base()class SchoolModel(Base):"""必须小写字母"""__tablename__ = 'school'#__table_args__ = {'schema': 'GEOVINDU'} # 11gschoolid = Column(String(5), primary_key=True)schoolname = Column(String(500), nullable=False)schooltelno = Column(String(8))teachers = relationship("TeacherModel", back_populates="school")class TeacherModel(Base):"""必须小写字母"""__tablename__ = 'teacher'#__table_args__ = {'schema': 'GEOVINDU'} # 11gteacherid = Column(String(5), primary_key=True)teacherfirstname = Column(String(100), nullable=False)teacherlastname = Column(String(20), nullable=False)teachergender = Column(String(2), nullable=False)teachertelno = Column(String(8))teacherschoolid = Column(String(5), ForeignKey(SchoolModel.schoolid), nullable=False)school = relationship("SchoolModel", back_populates="teachers") #backref# 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, postgreSQL 17.0 oracle 21c Neo4j
# Datetime : 2025/3/7 20:26
# User : geovindu
# Product : PyCharm
# Project : pyOracleDDDOrmDemo
# File : teacher.py
# explain : 学习
from infrastructure.database.oracleHelper import OracleHeler
from infrastructure.model.teacher import TeacherModel
from domain.entities.teacher import TeacherEntityclass TeacherRepository:""""""def __init__(self):""""""self.Session = OracleHeler()def get_all_teachers(self) -> list[TeacherEntity]:""":return:"""session = self.Session.get_session()teachers = session.query(TeacherModel).all()session.close()return [TeacherEntity(teacher.teacherid, teacher.teacherfirstname, teacher.teacherlastname,teacher.teachergender, teacher.teachertelno, teacher.teacherschoolid) for teacher inteachers]def add_teacher(self, teacher_entity):""":param teacher_entity::return:"""session = self.Session.get_session()new_teacher = TeacherModel(TeacherId=teacher_entity.TeacherId, TeacherFirstName=teacher_entity.TeacherFirstName,TeacherLastName=teacher_entity.TeacherLastName, TeacherGender=teacher_entity.TeacherGender,TeacherTelNo=teacher_entity.TeacherTelNo, TeacherSchoolId=teacher_entity.TeacherSchoolId)session.add(new_teacher)session.commit()session.close()def update_teacher(self, teacher_entity):""":param teacher_entity::return:"""session = self.Session.get_session()teacher = session.query(TeacherModel).filter_by(TeacherId=teacher_entity.TeacherId).first()if teacher:teacher.TeacherFirstName = teacher_entity.TeacherFirstNameteacher.TeacherLastName = teacher_entity.TeacherLastNameteacher.TeacherGender = teacher_entity.TeacherGenderteacher.TeacherTelNo = teacher_entity.TeacherTelNoteacher.TeacherSchoolId = teacher_entity.TeacherSchoolIdsession.commit()session.close()def delete_teacher(self, teacher_id):""":param teacher_id::return:"""session = self.Session.get_session()teacher = session.query(TeacherModel).filter_by(TeacherId=teacher_id).first()if teacher:session.delete(teacher)session.commit()session.close()def get_all(self, page: int, page_size: int, search_query: str = "")-> list[TeacherEntity]:""":param page::param page_size::param search_query::return:"""session = self.Session.get_session()query = session.query(TeacherModel)if search_query:query = query.filter((TeacherModel.teacherid.contains(search_query)) |(TeacherModel.teacherfirstname.contains(search_query)) |(TeacherModel.teacherlastname.contains(search_query)) |(TeacherModel.teachergender.contains(search_query)) |(TeacherModel.teachertelno.contains(search_query)) |(TeacherModel.teacherschoolid.contains(search_query)))offset = (page - 1) * page_sizequery = query.order_by(TeacherModel.teacherid) # 这里以 TeacherId 为例进行排序，你可以根据实际需求修改排序字段total = query.count()# print(" teacher total", total)teacher_models = query.offset(offset).limit(page_size).all()session.close()return [TeacherEntity(teacher.teacherid, teacher.teacherfirstname, teacher.teacherlastname,teacher.teachergender, teacher.teachertelno, teacher.teacherschoolid)for teacher in teacher_models]def get_total_count(self, search_query: str = "") -> int:""":param search_query::return:"""session = self.Session.get_session()query = session.query(TeacherModel)if search_query:query = query.filter((TeacherModel.teacherid.contains(search_query)) |(TeacherModel.teacherfirstname.contains(search_query)) |(TeacherModel.teacherlastname.contains(search_query)) |(TeacherModel.teachergender.contains(search_query)) |(TeacherModel.teachertelno.contains(search_query)) |(TeacherModel.teacherschoolid.contains(search_query)))count = query.count()session.close()return count