python: DDD using postgeSQL and SQL Server

 

postgreSQL

注意：

?
1
2
3
4
5
6
7
# psycopg 2 驱动的连接字符串
#engine = create_engine('postgresql://post:geovindu@localhost:5433/TechnologyGame')
#Session = sessionmaker(bind=engine)
 
# 使用 psycopg3 驱动的连接字符串
#engine = create_engine('postgresql+psycopg://user:geovindu@localhost:5432/TechnologyGame')
#Session = sessionmaker(bind=engine)

　　

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
create table School  -- 創建表
(
    SchoolId char(5) NOT NULL PRIMARY KEY,   
    SchoolName varchar(500) NOT NULL DEFAULT '', 
    SchoolTelNo  varchar(8)  NULL DEFAULT '' 
);
 
create table Teacher  -- 創建表
(
   TeacherId char(5) NOT NULL ,
    TeacherFirstName varchar(100) NOT NULL DEFAULT '', 
    TeacherLastName varchar(20) NOT NULL DEFAULT '',
    TeacherGender char(2) NOT NULL DEFAULT '',
    TeacherTelNo  varchar(8)  NULL DEFAULT '',
     TeacherSchoolId  char(5) NOT NULL DEFAULT '', 
     PRIMARY KEY (TeacherId),   -- 主鍵
     FOREIGN KEY(TeacherSchoolId) REFERENCES School(SchoolId)  -- 外鍵
);  

　　

 项目结构：

 

 

?
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
# 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/6 22:51
# User      : geovindu
# Product   : PyCharm
# Project   : pypostgreSQLDDDOrmDemo
# File      : teacher.py
# explain   : 学习
from sqlalchemy import create_engine, Column, String, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, declarative_base, relationship
 
 
Base = declarative_base()
 
 
''' '''
class SchoolModel(Base):
    """
    必须小写字母
    """
    __tablename__ = 'school'
    schoolid = Column(String(5), primary_key=True)
    schoolname = Column(String(500), default='')
    schooltelno = Column(String(8), default='')
    teachers = relationship("TeacherModel", back_populates="school")
     
 
class TeacherModel(Base):
    """
    必须小写字母
    """
    __tablename__ = 'teacher'
    teacherid = Column(String(5), primary_key=True)
    teacherfirstname = Column(String(100), default='')
    teacherlastname = Column(String(20), default='')
    teachergender = Column(String(2), default='')
    teachertelno = Column(String(8), default='')
    teacherschoolid = Column(String(5), ForeignKey(SchoolModel.schoolid))
    school = relationship("SchoolModel", back_populates="teachers")

　　

?
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
# 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/6 22:58
# User      : geovindu
# Product   : PyCharm
# Project   : pypostgreSQLDDDOrmDemo
# File      : teacher.py
# explain   : 学习
from domain.repositories.teacher import TeacherRepository
from infrastructure.database.postgresqlHelper import PostgresqlHelper
from infrastructure.model.teacher import TeacherModel
from domain.entities.teacher import Teacher
 
class TeacherRepositoryImpl(TeacherRepository):
    """
     
    """
    def __init__(self):
        self.sesion = PostgresqlHelper()
 
    def get_all(self, page=1, page_size=10, keyword=''):
        """
         
        :param page:
        :param page_size:
        :param keyword:
        :return:
        """
        session = self.sesion.getSession()
 
        try:
            offset = (page - 1) * page_size
            query = session.query(TeacherModel)
            print(query)
            #query = query.order_by(TeacherModel.teacherid)
            #total = query.count()
            #print("total", total)
            if keyword:
                query = query.filter(
                    (TeacherModel.teacherfirstname.contains(keyword)) |
                    (TeacherModel.teacherlastname.contains(keyword))
                )
 
            teachers =query.offset(offset).limit(page_size).all() # query.offset(offset).limit(page_size).all()
            print("data teachers",teachers)
            return [Teacher(teacher.teacherid, teacher.teacherfirstname, teacher.teacherlastname, teacher.teachergender, teacher.teachertelno, teacher.teacherschoolid) for teacher in teachers]
 
        except Exception as ex:
            print(ex.__str__())
            print(f"Error fetching teachers: {ex}")
            #return []  # 返回空列表而不是 None
        finally:
            session.close()
 
    def get_by_id(self, teacher_id):
        """
         
        :param teacher_id:
        :return:
        """
        session = self.sesion.getSession()
        try:
            teacher = session.query(TeacherModel).filter_by(teacher_id=teacher_id).first()
            if teacher:
                return Teacher(teacher.teacherid, teacher.teacherfirstname, teacher.teacherlastname, teacher.teachergender, teacher.teachertelno, teacher.teacherschoolid)
            return None
        finally:
            session.close()
 
    def add(self, teacher: Teacher):
        """
         
        :param teacher:
        :return:
        """
        session = self.sesion.getSession()
        try:
            new_teacher = TeacherModel(teacher_id=teacher.teacher_id, first_name=teacher.first_name, last_name=teacher.last_name, gender=teacher.gender, tel_no=teacher.tel_no, school_id=teacher.school_id)
            session.add(new_teacher)
            session.commit()
        except:
            session.rollback()
            raise
        finally:
            session.close()
 
    def update(self, teacher: Teacher):
        """
         
        :param teacher:
        :return:
        """
         
        session = self.sesion.getSession()
        try:
            existing_teacher = session.query(TeacherModel).filter_by(teacher_id=teacher.teacher_id).first()
            if existing_teacher:
                existing_teacher.first_name = teacher.first_name
                existing_teacher.last_name = teacher.last_name
                existing_teacher.gender = teacher.gender
                existing_teacher.tel_no = teacher.tel_no
                existing_teacher.school_id = teacher.school_id
                session.commit()
        except:
            session.rollback()
            raise
        finally:
            session.close()
 
    def delete(self, teacher_id):
        """
         
        :param teacher_id:
        :return:
        """
        session = self.sesion.getSession()
        try:
            teacher = session.query(TeacherModel).filter_by(teacher_id=teacher_id).first()
            if teacher:
                session.delete(teacher)
                session.commit()
        except:
            session.rollback()
            raise
        finally:
            session.close()
 
    def get_total_count(self, keyword=''):
        """
         
        :param keyword:
        :return:
        """
        session = self.sesion.getSession()
        try:
            query = session.query(TeacherModel)
            if keyword:
                query = query.filter(
                    (TeacherModel.first_name.contains(keyword)) |
                    (TeacherModel.last_name.contains(keyword))
                )
            count = query.count()
            return count
        except Exception as e:
            print(f"Error getting total count: {e}")
            return 0
        finally:
            session.close()

　　

 

sql server:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
create table School  -- 創建表
(
    SchoolId char(5) NOT NULL PRIMARY KEY,  -- 學校編號
    SchoolName nvarchar(500) NOT NULL  DEFAULT '',  --  學校名稱',
    SchoolTelNo  varchar(8)  NULL DEFAULT '' , -- 電話號碼   
   
); 
create table Teacher  -- 創建表
(
    TeacherId char(5) NOT NULL , --'主鍵primary key，學生編號',
    TeacherFirstName nvarchar(100) NOT NULL DEFAULT '',  -- ' 名',
    TeacherLastName nvarchar(20) NOT NULL DEFAULT '', -- ' 姓',
    TeacherGender char(2) NOT NULL DEFAULT '', -- '性別',
    TeacherTelNo  varchar(8)  NULL DEFAULT '',  --'電話號碼',
    TeacherSchoolId  char(5) NOT NULL DEFAULT '',  -- '外鍵 foreign key 學校ID',     
     PRIMARY KEY (TeacherId),   -- 主鍵
     FOREIGN KEY(TeacherSchoolId) REFERENCES School(SchoolId)  -- 外鍵
)

　　

项目结构：

 

 

?
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
# 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/2/20 20:46
# User      : geovindu
# Product   : PyCharm
# Project   : pyMsSqlDDDOrmDemo
# File      : infrastructure/model/teacher.py
# explain   : 学习
from sqlalchemy import create_engine, Column, String, ForeignKey
from sqlalchemy.orm import sessionmaker, declarative_base, relationship
from ..database.mssqlHelper import MssqlHelper
 
Base = declarative_base()
 
class SchoolModel(Base):
    """
    基础设施层（Infrastructure）
    数据库交互
    """
    __tablename__ = 'School'
    SchoolId = Column(String(5), primary_key=True)
    SchoolName = Column(String(500), default='')
    SchoolTelNo = Column(String(8), default='')
 
    teachers = relationship("TeacherModel", back_populates="school")
 
 
class TeacherModel(Base):
    """
    基础设施层（Infrastructure）
    数据库交互
    """
    __tablename__ = 'Teacher'
    TeacherId = Column(String(5), primary_key=True)
    TeacherFirstName = Column(String(100), default='')
    TeacherLastName = Column(String(20), default='')
    TeacherGender = Column(String(2), default='')
    TeacherTelNo = Column(String(8), default='')
    TeacherSchoolId = Column(String(5), ForeignKey('School.SchoolId'))
 
    school = relationship("SchoolModel", back_populates="teachers")

　　

?
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
# 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/2/20 20:47
# User      : geovindu
# Product   : PyCharm
# Project   : pyMsSqlDDDOrmDemo
# File      : infrastructure/repositories/teacher.py
# explain   : 学习
 
from sqlalchemy import create_engine, Column, String, ForeignKey
from sqlalchemy.orm import sessionmaker, declarative_base, relationship
from domain.entities.teacher import Teacher
from domain.entities.school import School
from ..database.mssqlHelper import MssqlHelper
from ..model.teacher import TeacherModel
from sqlalchemy.exc import SQLAlchemyError
 
class TeacherRepository:
    """
    基础设施层（Infrastructure）
    实现存储库接口
    """
    def __init__(self):
        """
 
        """
        self._Session = MssqlHelper()
 
    def add(self, teacher: Teacher):
        """
 
        :param teacher:
        :return:
        """
        session = self._Session.getSession()
        teacher_model = TeacherModel(TeacherId=teacher.teacher_id, TeacherFirstName=teacher.first_name,
                                     TeacherLastName=teacher.last_name, TeacherGender=teacher.gender,
                                     TeacherTelNo=teacher.tel_no, TeacherSchoolId=teacher.school_id)
        session.add(teacher_model)
        session.commit()
        session.close()
 
    def update(self, teacher: Teacher):
        """
 
        :param teacher:
        :return:
        """
        session = self._Session.getSession()
        teacher_model = session.query(TeacherModel).filter_by(TeacherId=teacher.teacher_id).first()
        if teacher_model:
            teacher_model.TeacherFirstName = teacher.first_name
            teacher_model.TeacherLastName = teacher.last_name
            teacher_model.TeacherGender = teacher.gender
            teacher_model.TeacherTelNo = teacher.tel_no
            teacher_model.TeacherSchoolId = teacher.school_id
            session.commit()
        session.close()
 
    def update_teacher_and_school(self, teacher_id, first_name, last_name, gender, tel_no, school_id, school_name,
                                  school_tel_no):
        """
 
        :param teacher_id:
        :param first_name:
        :param last_name:
        :param gender:
        :param tel_no:
        :param school_id:
        :param school_name:
        :param school_tel_no:
        :return:
        """
        session = self._Session.getSession()
        try:
            # 更新老师信息
            teacher = session.query(Teacher).filter_by(TeacherId=teacher_id).first()
            if teacher:
                teacher.TeacherFirstName = first_name
                teacher.TeacherLastName = last_name
                teacher.TeacherGender = gender
                teacher.TeacherTelNo = tel_no
                teacher.TeacherSchoolId = school_id
 
            # 更新关联学校信息
            school = session.query(School).filter_by(SchoolId=school_id).first()
            if school:
                school.SchoolName = school_name
                school.SchoolTelNo = school_tel_no
 
            session.commit()
        except Exception as e:
            session.rollback()
            raise e
        finally:
            session.close()
 
    def delete(self, teacher_id: str):
        """
 
        :param teacher_id:
        :return:
        """
        session = self._Session.getSession()
        teacher_model = session.query(TeacherModel).filter_by(TeacherId=teacher_id).first()
        if teacher_model:
            session.delete(teacher_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()
        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_size
        query = 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 [Teacher(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 = ""):
        """
 
        :param search_query:
        :return:
        """
        session = self._Session.getSession()
        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

　　

 

posted @   ®Geovin Du Dream Park™  阅读(13)  评论(0)  编辑  收藏  举报
相关博文：
·  python: DDD+ORM using　oracle 21c
·  more Layer Architecture in Python and postgreSQL17.0
·  Postgresql
·  SQLAlchemy完全入门
·  PostgreSQL
阅读排行：
· 25岁的心里话
· 闲置电脑爆改个人服务器（超详细） #公网映射 #Vmware虚拟网络编辑器
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· 零经验选手，Compose 一天开发一款小游戏！
· 一起来玩mcp_server_sqlite，让AI帮你做增删改查！！
历史上的今天：
2022-03-06 sql server: create Trigger using del,insert,update
2022-03-06 google orgchart
2020-03-06 HtmlAgility 抓取网页上的数据
2013-03-06 SQL Server Dates or Calendar Table for PowerPivot
< 2025年3月 >
日 一 二 三 四 五 六
23 24 25 26 27 28 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 1 2 3 4 5
点击右上角即可分享
微信分享提示