读后笔记 -- FastAPI 构建Python微服务 Chapter5：连接到关系型数据库

5.2 数据库连接准备

本章所有的数据库：PostgreSQL 14 windows exe 安装版本，https://www.enterprisedb.com/postgresql-tutorial-resources-training-1?uuid=140fdf8e-34e6-4b1b-ac32-532e5ac826c4&campaignId=Product_Trial_PostgreSQL_14

注意：安装时，Locale 选择 “English, US”，如果选择 Default/China，可能会遇到 编码问题导致无法连接数据库

该版本可与 Navicat 15 版本配合使用 （下载及破解地址：https://www.jb51.net/article/199525.htm） 

 

---

5.3 使用 SQLAlchemy 实现 同步 CRUD 事务

SQLAlchemy: 目前流行的 ORM库，可以在任何基于 Python 的应用程序和数据库平台间建立通信。

1）安装包：

      pip install SQLAlchemy

      pip intall psycopg2          # 安装数据库驱动

2）项目结构：

The Application
├── api/                                # 接口包                  
│   └── admin.py                        # router，接口定义，运行事务
│   └── ...                       
├── db_config/                          # 数据库配置                
│   └── sqlalchemy_connect.py           # 设置数据库连接，创建会话工厂，创建 Base 类    
├── models/                             # 模型层                 
│   └── data
│       └── sqlalchemy_models.py        # 继承 Base，构建模型，通过 relationships() 映射表关系               
│   └── requests
│       └── sigup.py                    # request 数据结构                                   
├── repository/                         # 存储库包                 
│   └── sqlalchemy
│       └── signup.py                   # 创建连接 CRUD    
└── main.py                             # 生成 app，app.include_router(api 的 router)  

3）具体实现：

3.1) 定义数据库

from sqlalchemy import create_engine, text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import sessionmaker, declarative_base

# 设置数据库连接
DB_URL = "postgresql://postgres:postgres@127.0.0.1:5432/postgres"

# engine 是一个全局对象，在整个应用程序只能创建一次
engine = create_engine(DB_URL)

# 初始化会话工厂，通过 sqlalchemy.orm 模块的 sessionmaker() 绑定上面创建的 engine
#   将 autocommit=False，表示不会立即提交。如立即生效，需显式调用，如 session.commit()
#   autoflash=False,不自动刷新
#   应用程序可以通过 SessionFactory() 调用创建多个会话，但原则上：一般每个 APIRouter() 只有一个会话
SessionFactory = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# 创建 Base 类，供 models 调用
Base = declarative_base()


# 单元测试
if __name__ == '__main__':
    session = SessionFactory()
    try:
        # 使用文本 SQL 查询来获取 PostgreSQL 的版本信息
        version_query = text("SELECT version();")
        result = session.execute(version_query)

        # 获取第一条查询结果并打印
        version_info = result.fetchone()
        print(f"Database Connection Successful! PostgreSQL Version: {version_info[0]}")

    except SQLAlchemyError as e:
        print(f"Error connecting to the database: {e}")
    finally:
        # 关闭会话以释放资源
        session.close()

   上述代码的详细介绍：

在 SQLAlchemy 中，sessionmaker 是一个工厂函数，用于创建数据库会话（Session）的配置和实例化模板。当你需要与数据库进行交互时，如 CRUD 操作时，会话提供了一个上下文管理和数据一致性控制的便利方式。
1. autocommit=False:
　　意味着由 Session 管理的数据库连接不会自动提交事务。默认情况下，SQLAlchemy 的 Session 在执行数据库操作后不会立即提交事务；你需要显式调用 session.commit() 来确认更改。
   这样可以在多个操作之间保持原子性，如果其中一个操作失败，可选择回滚（session.rollback()）以撤销所有更改，保证数据的一致性。
2. autoflush=False:
　　指示 Session 不应在执行查询之前自动刷新 pending（待处理）的变更到数据库。默认情况下，当执行查询时，Session 会先检查是否有未提交的变更（例如对象的属性修改），并自动执行 flush 操作将这些变更同步到数据库中，
　　以确保查询结果能反映出最新的状态。关闭自动刷新可以提升特定场景下的性能，或者当你需要更细粒度控制何时刷新变更时，但这也要求更小心地管理数据同步。
3. bind=engine:
　　将之前创建的数据库引擎（engine）绑定到 SessionFactory 上。数据库引擎是 SQLAlchemy 用来实际与数据库进行通信的核心对象，它封装了连接池和 Dialect（用于理解如何与特定数据库类型交流）。
　　通过将引擎绑定到 SessionFactory，确保每个由这个工厂创建的 Session 实例都会自动使用正确的数据库连接配置。

在 SQLAlchemy 中，Base = declarative_base() 的作用是创建一个基类（Base class），它是所有映射到数据库表的 ORM 类（即模型类）的基类。这一行代码是使用 SQLAlchemy 的声明式方式定义 ORM 模型的基础。
具体来说，declarative_base() 函数做了以下几件事：
1. 初始化元数据（Metadata）: SQLAlchemy 使用元数据来跟踪所有表、列、索引等数据库结构的信息。通过调用 declarative_base()，会为你的模型创建一个全局的元数据对象。
2. 创建基类: 返回的 Base 类是你自定义模型类需要继承的基类。这个基类包含了将类属性映射到数据库表字段、处理表关系等功能的方法和属性。
3. 配置ORM映射: 当你定义一个继承自 Base 的子类时，比如定义一个表示用户的数据模型 class User(Base), SQLAlchemy 会自动解析该类中的属性（如列定义、表名等），并设置相应的ORM映射关系。
　　　　　　　　 这意味着你不需要手动编写大量的映射代码，而只需关注定义 Python 类和它们的属性。
4. 提供便利方法: Base 类还包含了一些便利方法，如 query 方法，用于方便地从数据库查询数据，以及与数据库会话（Session）交互的方法。

 3.2）创建模型类及表映射关系

from sqlalchemy import Column, Integer, String, Date, ForeignKey, Float, Time
from sqlalchemy.orm import relationship

from db_config.sqlalchemy_connect import Base


# 定义模型类，继承 Base 类
class Login(Base):
    __tablename__ = "login"
    # primary_key: 设置表主键
    id = Column(Integer, primary_key=True, index=True)
    username = Column(String, unique=False, index=False)
    password = Column(String, unique=False, index=False)
    ...

    # 通过 relationship() 建立表关联
    trainers = relationship('Profile_Trainers', back_populates="login", uselist=False)
    members = relationship('Profile_Members', back_populates="login", uselist=False)


class Profile_Trainers(Base):
    __tablename__ = "profile_trainers"
    # ForeignKey() 将模型类链接到其父类的引用键列对象 login.id
    id = Column(Integer, ForeignKey('login.id'), primary_key=True, index=True, )
    firstname = Column(String, unique=False, index=False)
    lastname = Column(String, unique=False, index=False)
    age = Column(Integer, unique=False, index=False)
    ...

    login = relationship('Login', back_populates="trainers")
    gclass = relationship('Gym_Class', back_populates="trainers")

    相关字段详细介绍：

relationship: 用于定义两个（或多个）ORM 类（通常是表）之间的关联关系：
   ralationship('Profile_Trainers'): 指定了与当前模型类建立关系的另一个ORM类的名称。在当前类中定义的 trainers 属性将关联到 Profile_Trainers 类的实例。
back_populates="login": 用来指定双向关系。在 Profile_Trainers 类中应该也有一个 login 属性，它使用 relationship 指向当前类。back_populates 确保双方都能访问对方，也就是说，
　　　　　　　　　　　　　　 如果你从 Profile_Trainers 实例访问其 login 属性，你可以直接获取到与之关联的主体实体实例。
uselist=False:  表明与 Profile_Trainers 的关联是一个一对一的关系，而不是一对多。因此，trainers 属性将直接存储一个 Profile_Trainers 的实例，而不是一个包含多个实例的列表。
                Default 为 True，创建一个列表来存储关联的对象集合。

 3.3）sqlalchemy Session 创建 CRUD 事务

from typing import Any, Dict

from sqlalchemy import desc
from sqlalchemy.orm import Session

from models.data.sqlalchemy_models import Signup, Login, Profile_Members, Attendance_Member


# 单表 CRUD 查询
class SignupRepository:

    def __init__(self, sess: Session):
        self.sess: Session = sess

    def insert_signup(self, signup: Signup) -> bool:
        try:
            # add() 处理对象的值，所以是 signup。也可以看 add() 方法，传入的是 instance
            self.sess.add(signup)
            self.sess.commit()
            print(signup.id)
        except:
            return False
        return True

    def update_signup(self, id: int, details: Dict[str, Any]) -> bool:
        try:
            # 执行各种查询动作时，如 query, fileter, order_by, group_by, count(), sum() 都是传入的类，主要是为了构建查询灵活性
            self.sess.query(Signup).filter(Signup.id == id).update(details)
            self.sess.commit()
        except:
            return False
        return True

    def delete_signup(self, id: int) -> bool:
        try:
            self.sess.query(Signup).filter(Signup.id == id).delete()
            self.sess.commit()
        except:
            return False
        return True

    def get_all_signup(self):
        return self.sess.query(Signup).all()

    def get_all_signup_where(self, username: str):
        return self.sess.query(Signup.username, Signup.password).filter(Signup.username == username).all()

    def get_all_signup_sorted_desc(self):
        return self.sess.query(Signup.username, Signup.password).order_by(desc(Signup.username)).all()

    def get_signup(self, id: int):
        return self.sess.query(Signup).filter(Signup.id == id).one_or_none()


# 连接查询1
class LoginMemberRepository:
    def __init__(self, sess: Session):
        self.sess: Session = sess

    def join_login_members(self):
        # query() 放入多张表，filter() 放入条件，覆盖 ON 条件
        return self.sess.query(Login, Profile_Members).filter(Login.id == Profile_Members.id).all()


# 连接查询2
class MemberAttendanceRepository:
    def __init__(self, sess: Session):
        self.sess: Session = sess

    # 通过 join() 方法实现内连接
    def join_member_attendance(self):
        return self.sess.query(Profile_Members, Attendance_Member).join(Attendance_Member).all()

    # 通过 outerjoin() 方法实现外连接
    def outer_join_member(self):
        return self.sess.query(Profile_Members, Attendance_Member).outerjoin(Attendance_Member).all()

 注意上例代码中的几个要点（具体可以看跳转的源代码）：

• 执行各种查询动作时，诸如 query, fileter, order_by, group_by, count(), sum() 都是传入的类，目的是为了构建查询灵活性。example：self.sess.query(Signup).filter(Signup.id == id).update(details)
• add() 的参数是对象，example：self.sess.add(signup)
• 多表关联条件查询：query() 放入多张表，filter() 放入条件，覆盖 ON 条件。example： self.sess.query(Login, Profile_Members).filter(Login.id == Profile_Members.id).all()

3.4）API 接口 运行事务

from typing import List

from fastapi import APIRouter, Depends
from fastapi.responses import JSONResponse
from sqlalchemy.orm import Session

from db_config.sqlalchemy_connect import SessionFactory
from models.data.sqlalchemy_models import Signup
from models.requests.signup import SignupReq
from repository.sqlalchemy.signup import SignupRepository

router = APIRouter()

def sess_db():
    # 如前面所说，最好是每个 router 仅创建一个 session
    db = SessionFactory()
    try:
        # 通过 yield db 将函数转变为一个 生成器函数
        yield db
    finally:
        db.close()

@router.post("/signup/add")
def add_signup(req: SignupReq, sess: Session = Depends(sess_db)):
    repo: SignupRepository = SignupRepository(sess)
    signup = Signup(id=req.id, username=req.username, password=req.password)
    result = repo.insert_signup(signup)
    if result is True:
        return signup
    else:
        return JSONResponse(content={"message": "create signup problem encountered"}, status_code=500)

@router.get("/signup/list", response_model=List[SignupReq])
def list_signup(sess: Session = Depends(sess_db)):
    repo: SignupRepository = SignupRepository(sess)
    result = repo.get_all_signup()
    return result

4）其他：定义属性的类：

from pydantic import BaseModel

class SignupReq(BaseModel):
    id: int
    username: str
    password: str

    class Config:
        # Config内部类用来配置模型的行为。"from_attributes=True" 告诉Pydantic从ORM模型中直接获取属性值，而不是调用getter方法。
        from_attributes = True

 

---

5.4 使用 SQLAlchemy 实现异步 CRUD 事务

1）安装包：

      pip install SQLAlchemy

      pip install aiopg

      pip intall asyncpg          # 安装数据库驱动

2）项目结构：

The Application
├── api/                                # 接口层    
│   └── __init__.py         
│   └── attendance.py                   # router，接口定义，运行事务
│   └── ...                       
├── db_config/                          # 数据库配置
│   └── __init__.py              
│   └── sqlalchemy_async_connect.py     # 设置数据库连接，创建会话工厂，创建 Base 类    
├── models/                             # 模型层          
│   └── data
│       └── __init__.py         
│       └── sqlalchemy_async_models.py  # 继承 Base，构建模型，通过 relationships() 映射表关系               
│   └── requests
│       └── __init__.py    
│       └── attendance.py               # request 数据结构 
│   └── __init__.py                                           
├── repository/                         # 存储库包  
│   └── __init__.py                   
│   └── sqlalchemy
│       └── __init__.py    
│       └── attendance.py               # 创建 CRUD    
│── __init__.py
└── main.py                             # 生成 app，app.include_router(api 的 router)     

3）具体实现：

3.1) 定义数据库

import asyncio

from sqlalchemy import select, func
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import sessionmaker, declarative_base

# 使用 "+asyncpg" 表明将 asyncpg 作为数据库核心驱动，此为异步连接
DB_URL = "postgresql+asyncpg://postgres:postgres@127.0.0.1:5432/postgres"


# create_async_engine: 创建异步的数据引擎
#   future=True: 使用 SQLAlchemy 2.0 的新API风格, 
#   echo=True: SQL 语句在控制台打印，便于调试
engine = create_async_engine(DB_URL, future=True, echo=True)

# 创建会话工厂
#   1. expire_on_commit=False: 使模型实例及其属性在事务期间可访问，即使在 commit() 之后
#       异步下，所有实体类及其列对象仍可被其他进程访问，即使在事务提交之后（这点与同步环境不同）
#   2. class_ 带有类名 AsyncSession，该实体将控制 CRUD 事务。
AsyncSessionFactory = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)

# 创建 Base 类，供 models 下的类继承，定义数据结构
Base = declarative_base()


# ut，验证数据库异步连接
async def check_database_connection():
    session = AsyncSessionFactory()
    try:
        version_query = select(func.version())
        result = await session.execute(version_query)

        # 获取第一条查询结果并打印
        version_info = result.scalar_one()
        print(f"Database Connection Successful! PostgreSQL Version: {version_info}")

    except SQLAlchemyError as e:
        print(f"Error connecting to the database: {e}")
    finally:
        # 关闭会话以释放资源
        await session.close()


if __name__ == '__main__':
    asyncio.run(check_database_connection())

3.2）创建模型类及表映射关系

from sqlalchemy import Column, Integer, ForeignKey, Time, Date, String, Float
from sqlalchemy.orm import relationship

from db_config.sqlalchemy_async_connect import Base


class Signup(Base):
    __tablename__ = "signup"
    id = Column(Integer, primary_key=True, index=True)
    username = Column(String, unique=False, index=False)
    password = Column(String, unique=False, index=False)


class Login(Base):
    # 在 SQLAlchemy 中，使用 __tablename__ 是一种约定，用于定义 ORM（对象关系映射）模型类所对应的数据库表名
    __tablename__ = "login"
    id = Column(Integer, primary_key=True, index=True)
    username = Column(String, unique=False, index=False)
    password = Column(String, unique=False, index=False)
    date_approved = Column(Date, unique=False, index=False)
    user_type = Column(Integer, unique=False, index=False)

    trainers = relationship("Profile_Trainers", back_populates="login", uselist=False)
    members = relationship("Profile_Members", back_populates="login", uselist=False)


class Profile_Trainers(Base):
    __tablename__ = "profile_trainers"
    id = Column(Integer, ForeignKey("login.id"), primary_key=True, index=True, )
    firstname = Column(String, unique=False, index=False)
    lastname = Column(String, unique=False, index=False)
    age = Column(Integer, unique=False, index=False)
    position = Column(String, unique=False, index=False)
    tenure = Column(Float, unique=False, index=False)
    shift = Column(Integer, unique=False, index=False)

    login = relationship("Login", back_populates="trainers")
    gclass = relationship("Gym_Class", back_populates="trainers")


class Profile_Members(Base):
    __tablename__ = "profile_members"
    id = Column(Integer, ForeignKey("login.id"), primary_key=True, index=True)
    firstname = Column(String, unique=False, index=False)
    lastname = Column(String, unique=False, index=False)
    age = Column(Integer, unique=False, index=False)
    height = Column(Float, unique=False, index=False)
    weight = Column(Float, unique=False, index=False)
    membership_type = Column(String, unique=False, index=False)
    trainer_id = Column(Integer, ForeignKey("profile_trainers.id"), unique=False, index=False)

    login = relationship("Login", back_populates="members")
    attendance = relationship("Attendance_Member", back_populates="members")
    gclass = relationship("Gym_Class", back_populates="members")


class Attendance_Member(Base):
    __tablename__ = "attendance_member"
    id = Column(Integer, primary_key=True, index=True)
    member_id = Column(Integer, ForeignKey("profile_members.id"), unique=False, index=False)
    timeout = Column(Time, unique=False, index=False)
    timein = Column(Time, unique=False, index=False)
    date_log = Column(Date, unique=False, index=False)

    members = relationship("Profile_Members", back_populates="attendance")


class Gym_Class(Base):
    __tablename__ = "gym_class"
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, unique=False, index=False)
    member_id = Column(Integer, ForeignKey("profile_members.id"), unique=False, index=False)
    trainer_id = Column(Integer, ForeignKey("profile_trainers.id"), unique=False, index=False)
    approved = Column(Integer, unique=False, index=False)

    trainers = relationship("Profile_Trainers", back_populates="gclass")
    members = relationship("Profile_Members", back_populates="gclass")

3.3）sqlalchemy Session 创建 CRUD 事务

from datetime import datetime
from typing import Dict, Any

from sqlalchemy import insert, update, delete, select
from sqlalchemy.orm import Session

from models.data.sqlalchemy_async_models import Attendance_Member


class AttendanceRepository:
    def __init__(self, sess: Session):
        self.sess: Session = sess

    async def insert_attendance(self, attendance: Attendance_Member) -> bool:
        try:
            # 方式1：用 values() 投影所有列值以插入
            sql = insert(Attendance_Member).values(id=attendance.id,
                                                   member_id=attendance.member_id,
                                                   timein=datetime.strptime(attendance.timein, "%H:%M").time(),
                                                   timeout=datetime.strptime(attendance.timeout, "%H:%M").time(),
                                                   date_log=attendance.date_log)
            # 添加执行选项，告诉 session 始终使用 fetch() 来同步模型属性值和数据库中的更新值
            sql.execution_options(synchronize_session="fetch")
            # 使用 execute() 来运行最终的 insert()并自动提交更改，注意，基于 sessionmaker() 配置的 expire_on_commit = False
            await self.sess.execute(sql)

            # # 方式2：类似 同步的处理逻辑，通过 sess.add() 添加记录
            # attendance.timein = datetime.strptime(attendance.timein, "%H:%M").time()
            # attendance.timeout = datetime.strptime(attendance.timeout, "%H:%M").time()
            # self.sess.add(attendance)
            # await self.sess.flush()
        except Exception as e:
            print(f"except with {e}")
            return False
        return True

    async def update_attendance(self, id: int, details: Dict[str, Any]) -> bool:
        try:
            details["timeout"] = datetime.strptime(details["timeout"], "%H:%M")
            details["timein"] = datetime.strptime(details["timein"], "%H:%M")
            sql = update(Attendance_Member).where(Attendance_Member.id == id).values(**details)
            sql.execution_options(synchronize_session="fetch")
            await self.sess.execute(sql)

        except:
            return False
        return True

    async def delete_attendance(self, id: int) -> bool:
        try:
            sql = delete(Attendance_Member).where(Attendance_Member.id == id)
            sql.execution_options(synchronize_session="fetch")
            await self.sess.execute(sql)
        except:
            return False
        return True

    async def get_all_attendance(self):
        q = await self.sess.execute(select(Attendance_Member))
        return q.scalars().all()

    async def get_attendance(self, id: int):
        q = await self.sess.execute(select(Attendance_Member).where(Attendance_Member.member_id == id))
        return q.scalars().all()

    async def check_attendance(self, id: int):
        q = await self.sess.execute(select(Attendance_Member).where(Attendance_Member.id == id))
        return q.scalar()

3.4）API 接口 运行事务

from fastapi import APIRouter

from db_config.sqlalchemy_async_connect import AsyncSessionFactory
from models.data.sqlalchemy_async_models import Attendance_Member
from models.requests.attendance import AttendanceMemberReq
from repository.sqlalchemy.attendance import AttendanceRepository

router = APIRouter()


@router.post("/attendance/add")
async def add_attendance(req: AttendanceMemberReq):
    # 因为连接的 AsyncEngine 需要在每个 commit() 事务后关闭，所以由 AsynSessionFactory() 创建的 AsyncSession 需要异步 with 上下文管理器
    async with AsyncSessionFactory() as sess:
        # AsyncSession 创建后，只会在服务调用其 begin() 开始执行所有 CRUD 事务，并且在事务执行后需要关闭，所以需要另一个异步 with 上下文管理器
        async with sess.begin():
            repo = AttendanceRepository(sess)
            attendance = Attendance_Member(id=req.id,
                                           member_id=req.member_id,
                                           timein=req.timein,
                                           timeout=req.timeout,
                                           date_log=req.date_log)
            return await repo.insert_attendance(attendance)


@router.patch("/attendance/update")
async def update_attendance(id: int, req: AttendanceMemberReq):
    async with AsyncSessionFactory() as sess:
        async with sess.begin():
            repo = AttendanceRepository(sess)
            attendance_dict = req.model_dump(exclude_unset=True)
            return await repo.update_attendance(id, attendance_dict)


@router.delete("/attendance/delete/{id}")
async def delete_attendance(id: int):
    async with AsyncSessionFactory() as sess:
        async with sess.begin():
            repo = AttendanceRepository(sess)
            return await repo.delete_attendance(id)


@router.get("/attendance/list")
async def list_attendance():
    async with AsyncSessionFactory() as sess:
        async with sess.begin():
            repo = AttendanceRepository(sess)
            return await repo.get_all_attendance()


@router.get("/attendance/{id}")
async def get_attendance(id: int):
    async with AsyncSessionFactory() as sess:
        async with sess.begin():
            repo = AttendanceRepository(sess)
            return await repo.get_attendance(id)

 

---

5.6 使用 Pony 实现同步 CRUD

SQLAlchemy的优点包括：

• 成熟度高，功能丰富。
• 社区活跃，文档齐全。
• 支持多种数据库，包括PostgreSQL, MySQL, SQLite等。
• 提供了高级特性如会话管理、事务支持等。

Pony ORM的优点包括：

• 语法简洁，易于学习。
• 动态模型创建。
• 更加Pythonic的API。

1）安装包：

pip install pony
pip install psycopg2

2）项目结构：

The Application
├── api/                                # 接口包                  
│   └── __init__.py         
│   └── member.py                       # router，接口定义，运行事务
│   └── ...                       
├── db_config/                          # 数据库配置                
│   └── __init__.py              
│   └── pony_connect.py                 # 设置数据库连接，创建 Database() 连接后的实例    
├── models/                             # 模型层                 
│   └── data
│       └── __init__.py         
│       └── pony_models.py              # 构建模型，映射表关系               
│   └── requests
│       └── __init__.py    
│       └── member.py                   # request 数据结构                    
│   └── __init__.py                                           
├── repository/                         # 存储库包                 
│   └── __init__.py                   
│   └── pony
│       └── __init__.py    
│       └── members.py                  # 创建连接 CRUD    
│── __init__.py
└── main.py                             # 生成 app，app.include_router(api 的 router)    

3）具体实现：

3.1) 定义数据库

from pony.orm import Database

db = Database("postgres", host="localhost", port="5432", user="postgres", password="postgres", database="postgres")

3.2）创建模型类及表映射关系

from datetime import date, time
from pony.orm import PrimaryKey, Required, Optional, Set
from db_config.pony_connect import db

"""
表关系定义：
表关系       Parent       Child
一对一       Optional     Required / Optional   
一对多       Set          Required
多对一       Set          Set
"""


class Signup(db.Entity):
    _table_ = "signup"
    id = PrimaryKey(int)
    username = Required(str, unique=True, max_len=100, nullable=False)
    password = Required(str, unique=False, max_len=100, nullable=False)


class Login(db.Entity):
    _table_ = "login"
    id = PrimaryKey(int)
    username = Required(str)
    password = Required(str)
    date_approved = Required(date)
    user_type = Required(int)

    trainers = Optional("Profile_Trainers", reverse="id")
    members = Optional("Profile_Members", reverse="id")


class Profile_Trainers(db.Entity):
    _table_ = "profile_trainers"
    id = PrimaryKey("Login", reverse="trainers")
    firstname = Required(str)
    lastname = Required(str)
    age = Required(int)
    position = Required(str)
    tenure = Required(float)
    shift = Required(int)

    members = Set("Profile_Members", reverse="trainer_id")
    gclass = Set("Gym_Class", reverse="trainer_id")


class Profile_Members(db.Entity):
    _table_ = "profile_members"
    id = PrimaryKey("Login", reverse="members")
    firstname = Required(str)
    lastname = Required(str)
    age = Required(int)
    height = Required(float)
    weight = Required(float)
    membership_type = Required(str)
    trainer_id = Required("Profile_Trainers", reverse="members")

    attendance = Set("Attendance_Member", reverse="member_id")
    gclass = Set("Gym_Class", reverse="member_id")


class Attendance_Member(db.Entity):
    _table_ = "attendance_member"
    id = PrimaryKey(int)
    member_id = Required("Profile_Members", reverse="attendance")
    timeout = Required(time)
    timein = Required(time)
    date_log = Required(date)


class Gym_Class(db.Entity):
    _table_ = "gym_class"
    id = PrimaryKey(int)
    name = Required(str)
    member_id = Required("Profile_Members", reverse="gclass")
    trainer_id = Required("Profile_Trainers", reverse="gclass")
    approved = Required(int)


# pony 需要执行 generate_mapping() 来实现到实际表的所有实体映射
db.generate_mapping()

注意：在最后，需要 执行 generate_mapping() 来实现到实际表的所有实体映射 db.generate_mapping()

3.3）pony db_session 创建 CRUD 事务

from typing import Dict, Any

from pony.orm import db_session, left_join

from models.data.pony_models import Profile_Members, Login
from models.requests.members import ProfileMembersReq


class MemberRepository:
    def insert_member(self, details: Dict[str, Any]) -> bool:
        try:
            # db_session是一个特殊的上下文管理器，用来界定一个数据库事务的边界。这意味着在这个代码块中执行的所有数据库操作将被组合成一个事务，
            #           要么全部成功提交，要么在发生错误时全部回滚，以保持数据的一致性
            with db_session:
                Profile_Members(**details)
        except:
            return False
        return True

    def update_member(self, id: int, details: Dict[str, Any]) -> bool:
        try:
            with db_session:
                # Pony 内置了对 JSON 的支持，所以查到的对象将自动转换为支持 JSON 的对象
                profile = Profile_Members[id]
                profile.id = details["id"]
                profile.firstname = details["firstname"]
                profile.lastname = details["lastname"]
                profile.age = details["age"]
                profile.membership_type = details["membership_type"]
                profile.height = details["height"]
                profile.weight = details["weight"]
                profile.trainer_id = details["trainer_id"]
        except:
            return False
        return True

    def delete_member(self, id: int) -> bool:
        try:
            with db_session:
                Profile_Members[id].delete()
        except:
            return False
        return True

    def get_all_member(self):
        with db_session:
            members = Profile_Members.select()
            result = [ProfileMembersReq.from_orm(m) for m in members]
            return result

    def get_member(self, id: int):
        with db_session:
            login = Login.get(lambda l: l.id == id)
            member = Profile_Members.get(lambda m: m.id == login)
            result = ProfileMembersReq.from_orm(member)
        return result


class MemberGymClassRepository:
    def join_member_class(self):
        with db_session:
            # generator_args是一个生成器表达式，它遍历Profile_Members集合中的每个成员(m)，然后对于每个成员，遍历其关联的健身课程(gclass)。
            #               这为后续的查询准备了需要进行连接操作的数据源
            generator_args = (m for m in Profile_Members for g in m.gclass)
            # 调用了Pony ORM的left_join函数来执行左外连接操作。这意味着查询结果将包含所有Profile_Members记录，即使它们没有关联的健身课程记录
            # 生成的joins对象包含了这次查询的结果集，每个结果都是一个元组，包含来自左右表（即会员信息和会员的课程信息）的相关数据行
            joins = left_join(generator_args)
            # 对查询结果集中的每个元素(m)使用列表推导式来创建ProfileMembersReq对象
            result = [ProfileMembersReq.model_validate(m) for m in joins]

            return result

3.4）API 接口 运行事务

from fastapi import APIRouter
from fastapi.responses import JSONResponse

from models.requests.members import ProfileMembersReq
from repository.pony.members import MemberRepository, MemberGymClassRepository

router = APIRouter()


@router.post("/member/add")
def add_member(req: ProfileMembersReq):
    repo = MemberRepository()

    mem_profile = dict()
    mem_profile["id"] = req.id
    mem_profile["firstname"] = req.firstname
    mem_profile["lastname"] = req.lastname
    mem_profile["age"] = req.age
    mem_profile["height"] = req.height
    mem_profile["weight"] = req.weight
    mem_profile["membership_type"] = req.membership_type
    mem_profile["trainer_id"] = req.trainer_id

    result = repo.insert_member(mem_profile)
    if result is True:
        return req
    else:
        return JSONResponse(content={"message": "create profile encountered problem"}, status_code=500)


@router.patch("/member/update")
def update_member(id: int, req: ProfileMembersReq):
    mem_profile_dict = req.dict(exclude_unset=True)
    repo = MemberRepository()
    result = repo.update_member(id, mem_profile_dict)
    if result is True:
        return req
    else:
        return JSONResponse(content={'message': 'update profile problem encountered'}, status_code=500)


@router.delete("/member/delete/{id}")
def delete_member(id: int):
    repo = MemberRepository()
    result = repo.delete_member(id)
    if result is True:
        return JSONResponse(content={'message': 'update profile successful'}, status_code=201)
    else:
        return JSONResponse(content={'message': 'update profile problem encountered'}, status_code=500)


@router.get("/member/list")
def list_members():
    repo = MemberRepository()
    return repo.get_all_member()


@router.get("/member/get/{id}")
def get_member(id:int):
    repo = MemberRepository()
    return repo.get_member(id)


@router.get("/member/classes/list")
def list_members_class():
    repo = MemberGymClassRepository()
    return repo.join_member_class()

4. 其他： @field_validator 对变量的处理

from typing import Any

from pydantic import BaseModel, field_validator


class GymClassReq(BaseModel):
    id: int
    member_id: int
    trainer_id: int
    approved: int

    class Config:
        from_attributes = True


class ProfileMembersReq(BaseModel):
    id: Any
    firstname: str
    lastname: str
    age: int
    height: float
    weight: float
    membership_type: str
    trainer_id: Any

    @field_validator('trainer_id', mode="before")
    def trainer_object_to_int(cls, values):
        if isinstance(values, int):
            return values
        else:
            return values.id.id

    @field_validator('id', mode="before")
    def member_id_to_int(cls, values):
        if isinstance(values, int):
            return values
        else:
            return values.id

    class Config:
        from_attributes = True

 

---

5.6 使用 Peewee ORM 完成 异步 CRUD 事务

Peewee ORM 优缺点介绍：

Peewee ORM 是一个轻量级的 Python ORM 框架，广泛应用于需要数据库操作的项目中。以下是 Peewee ORM 的主要优缺点：
优点:
　　轻量级与易用性：Peewee 设计简洁，体积小，学习曲线相对平缓，易于上手。它提供了Django风格的API，使得开发者能够快速熟悉并开始使用。
　　灵活性与兼容性：支持多种数据库后端，包括 SQLite、MySQL、PostgreSQL 等，适用于不同的项目需求和环境。同时，它允许直接执行原生SQL，给予开发者更大的灵活性。
　　高性能：相比一些全功能的ORM，如 SQLAlchemy，Peewee 因其轻量设计，在某些场景下可以提供更快的执行速度和更低的内存占用。
　　代码简洁：Peewee 的模型定义和查询语法较为直观，可以写出简洁、易于理解的代码。它还支持链式查询，使得构建复杂查询变得简单。
　　易于集成：由于其轻量级和模块化设计，Peewee 很容易与任何Web框架或非Web项目集成，不像某些ORM与特定框架绑定紧密。
缺点:
　　缺乏高级特性：相比于 SQLAlchemy 这样的全面ORM，Peewee 在某些高级特性和复杂数据库操作方面可能不够强大，例如自动迁移工具和复杂的数据映射功能。
　　文档和社区：虽然 Peewee 的文档相对完善，但由于其轻量级定位，可能不如某些大型ORM框架那样拥有庞大的社区和丰富的第三方资源。
　　自动化管理不足：Peewee 不支持自动化 schema 迁移，意味着当数据库模式发生变化时，需要手动调整或借助第三方工具来同步模型与数据库结构。
　　定制化限制：对于那些需要高度定制数据库交互逻辑的项目，Peewee 可能提供的配置选项和扩展性不如某些其他ORM框架。

总结而言，Peewee ORM 特别适合那些寻求简单、快速解决方案的小型项目或对性能有严格要求的应用。如果你的项目不需要高级ORM特性，或者你偏好简洁的工具链，Peewee 将是一个不错的选择。然而，如果你的项目规模较大，
需要复杂的数据库管理功能，可能需要考虑更全面的ORM解决方案。

 

1）安装包：

pip install peewee
pip install psycopg2

2）项目结构：

The Application
├── api/                                # 接口层    
│   └── __init__.py         
│   └── login.py                        # router，接口定义，运行事务
│   └── ...                       
├── db_config/                          # 数据库配置
│   └── __init__.py              
│   └── peewee_connect.py               # 设置数据库连接
├── models/                             # 模型层          
│   └── data
│       └── __init__.py         
│       └── peewee_models.py            # 构建模型      
│   └── data
│       └── __init__.py         
│       └── login.py                    # request 数据结构              
│   └── __init__.py                                           
├── repository/                         # 存储库层  
│   └── __init__.py                   
│   └── peewee
│       └── __init__.py    
│       └── login.py                    # 创建连接 CRUD  
│── __init__.py
└── main.py                             # 生成 app，app.include_router(api 的 router)      

3）具体实现：

3.1) 定义数据库

# 用于在不同的上下文中（例如，在异步函数或多个线程中）存储连接状态
from contextvars import ContextVar

# _ConnectionState是Peewee内部用于管理数据库连接状态的类，而PostgresqlDatabase则是用于连接 PostgreSQL 数据库的具体类。
from peewee import _ConnectionState, PostgresqlDatabase

# 初始化了数据库连接状态的默认值，如是否关闭(closed)、连接对象(conn)、上下文(ctx)以及事务信息(transactions)
db_state_default = {"closed": None, "conn": None, "ctx": None, "transactions": None}
# ContextVar实例，其默认值为db_state_default的浅拷贝。这个变量用于跨上下文存储数据库连接的状态信息
db_state = ContextVar("db_state", default=db_state_default.copy())


class PeeweeConnectionState(_ConnectionState):
    def __init__(self, **kwargs):
        # 在构造方法中，使用super().__setattr__设置一个名为_state的属性，将其值设为前面定义的ContextVar实例db_state，这样可以在类实例间共享状态
        super().__setattr__("_state", db_state)
        super().__init__(**kwargs)

    # __setattr__和__getattr__方法使得可以像操作普通属性一样操作_state所指向的上下文变量中的数据，实现了对数据库连接状态的灵活管理和访问
    def __setattr__(self, name, value):
        self._state.get()[name] = value

    def __getattr__(self, name):
        return self._state.get()[name]


# 对于 Peewee，可以仅创建 database，表会在后续自动生成
db = PostgresqlDatabase("fcms3", user="postgres", password="postgres", host="localhost", port=5432)

# 将数据库的连接状态管理替换为新定义的PeeweeConnectionState实例，使得数据库连接能够在不同上下文中正确地管理和切换，
#       特别适合处理并发请求或在需要精细控制资源生命周期的场景中使用
db._state = PeeweeConnectionState()

3.2）创建模型类及表映射关系

from peewee import Model, CharField, DateField, IntegerField, ForeignKeyField, FloatField

from db_config.peewee_connect import db


class Signup(Model):
    username = CharField(unique=False, index=False)
    password = CharField(unique=False, index=False)

    # 每个领域类各有一个嵌套的 Meta 类，它注册了对 database 和 db_table 的引用，将 Meta 类映射到 模型类
    # 在这里还可以设置其他属性如 primary_key, indexes, constraints
    class Meta:
        database = db
        db_table = "signup"


class Login(Model):
    username = CharField(unique=False, index=False)
    password = CharField(unique=False, index=False)
    date_approved = DateField(unique=False, index=False)
    user_type = IntegerField(unique=False, index=False)

    class Meta:
        database = db
        db_table = "login"


class Profile_Trainers(Model):
    # 通过 ForeignKeyField 声明外键属性，参数的含义是：
    #   父模型的名称
    #   backref 参数：引用子记录（一对一关系）或 一组子对象（一对多 或 多对一）
    #   unique： True（一对一关系），False（非一对一关系）
    login = ForeignKeyField(Login, backref="trainer", unique=True)
    firstname = CharField(unique=False, index=False)
    lastname = CharField(unique=False, index=False)
    age = CharField(unique=False, index=False)
    position = CharField(unique=False, index=False)
    tenure = FloatField(unique=False, index=False)
    shift = IntegerField(unique=False, index=False)

    class Meta:
        database = db
        db_table = 'profile_trainers'


# step2: 在定义了所有模型（包括它们的关系）后，还需要从 Peewee 的数据库实例中调用下面方法进行表映射：
#   建立连接
db.connect()
#   根据其模型类的列表进行模式生成，此处将会在存在 database 的情况下，自动生成表
db.create_tables([Signup, Login, Profile_Trainers], safe=True)

3.3）peewee 创建 CRUD 事务

from datetime import date

from models.data.peewee_models import Login


class LoginRepository:
    def insert_login(self, id: int, user: str, passwd: str, approved: date, type: int) -> bool:
        try:
            Login.create(id=id, username=user, password=passwd, date_approved=approved, user_type=type)
        except Exception as e:
            print(e)
            return False
        return True

3.4）API 接口 运行事务

"""
由于 Peewee 的数据库连接是在模型层设置的，因此 APIRouter 或 FastAPI 不需要额外的要求来运行 CRUD 事务。
API 服务可以轻松地访问所有存储库类，而无须从 db 实例中调用方法或指令。
"""
from fastapi import APIRouter
from fastapi.responses import JSONResponse

from models.requests.login import LoginReq
from repository.peewee.login import LoginRepository

router = APIRouter()


@router.post("/login/add")
async def add_login(req: LoginReq):
    repo = LoginRepository()
    result = repo.insert_login(req.id, req.username, req.password, req.date_approved, req.user_type)
    if result is True:
        return req
    else:
        return JSONResponse(content={"message": "create login problem encountered"}, status_code=500)

 

---

5.8 应用 CQRS 设计模式

CQRS （Command Query Responsibility Segregation）是一种微服务设计模式，优点：

• 分离查询事务（读取）与 插入/更新/删除 （写入），减少了这些事务组合一起的访问，提供更少的流量和更快的执行；
• 在 API 服务 和 存储库层间创建了一个松散耦合的特性；

1）安装包：

      pip install pony

      pip intall psycopg2          # 安装数据库驱动

2）项目结构：

## 4. Pony 应用 CQRS 设计模式
      调用路径： api.trainers -> crqs -> repository -> models

### 项目结构
```
The Application
├── api/                                # 接口包                  
│   └── __init__.py         
│   └── trainers.py                     # 访问处理程序
│   └── ...            
├── crqs/                               # crqs，分离查询（query）和写入（insert, update, delete）操作
│   └── trainers/
│       └── command/                    # 创建命令处理程序
│           └── __init__.py
│           └── create_handlers.py
│       └── query                       # 创建查询处理程序
│           └── __init__.py
│           └── query_handlers.py
│       └── __init__.py         
│   └── __init__.py              
│   └── commands.py                     # 创建命令类
│   └── handlers.py                     # 定义 处理程序接口，一个查询，一个命令事务  
│   └── queries.py                      # 创建查询类               
├── db_config/                          # 数据库配置                
│   └── __init__.py              
│   └── pony_connect.py                 # 设置数据库连接，创建 Database() 连接后的实例   
├── models/                             # 模型层                 
│   └── data
│       └── __init__.py         
│       └── pony_models.py              # 构建模型，映射表关系          
│   └── requests
│       └── __init__.py    
│       └── trainers.py                 # request 数据结构                    
│   └── __init__.py                                           
├── repository/                         # 存储库包                 
│   └── __init__.py                   
│   └── pony_crqs
│       └── __init__.py    
│       └── trainers.py                 # 创建连接 CRUD    
│── __init__.py
└── main.py                             # 生成 app，app.include_router(api 的 router)                                 
```

3）具体实现：

3.1) 定义数据库

from pony.orm import Database

db = Database("postgres", host="localhost", port="5432", user="postgres", password="postgres", database="postgres")

3.2）定义处理程序接口

class IQueryHandler:
    pass

class ICommandHandler:
    pass

3.3）创建命令和查询类

from typing import Dict, Any


class ProfileTrainerCommand:
    def __init__(self):
        self._details: Dict[str, Any] = dict()

    # 使用 @property 装饰器定义了 details 属性的getter方法，意味着可以直接通过实例访问 _details，如 instance.details，而不需要调用方法
    @property
    def details(self):
        return self._details

    # 使用 @details.setter 装饰器定义了 details 属性的setter方法，名为 details，这允许你通过赋值语句修改 _details 的值
    # 当你执行 instance.details = some_dict 时，setter方法会被触发，它将接收到的新字典 some_dict 直接赋值给 _details，从而更新存储的详情信息
    @details.setter
    def details(self, details):
        self._details = details

from typing import List

from models.data.pony_models import Profile_Trainers


class ProfileTrainerListQuery:
    def __init__(self):
        self._records: List[Profile_Trainers] = list()

    @property
    def records(self):
        return self._records

    @records.setter
    def records(self, records):
        self._records = records


class ProfileTrainerRecordQuery:

    def __init__(self):
        self._record: Profile_Trainers = None

    @property
    def record(self):
        return self._record

    @record.setter
    def record(self, record):
        self._record = record

3.4）创建命令和查询处理程序

from cqrs.commands import ProfileTrainerCommand
from cqrs.handlers import ICommandHandler
from repository.pony_cqrs.trainers import TrainerRepository


class AddTrainerCommandHandler(ICommandHandler):
    def __init__(self):
        self.repo: TrainerRepository = TrainerRepository()

    def handle(self, command: ProfileTrainerCommand) -> bool:
        result = self.repo.insert_trainer(command.details)
        return result

from cqrs.handlers import IQueryHandler
from cqrs.queries import ProfileTrainerListQuery
from repository.pony_cqrs.trainers import TrainerRepository


class ListTrainerQueryHandler(IQueryHandler):
    def __init__(self):
        self.repo: TrainerRepository = TrainerRepository()
        self.query: ProfileTrainerListQuery = ProfileTrainerListQuery()

    def handle(self) -> ProfileTrainerListQuery:
        data = self.repo.get_all_trainers()
        self.query.records = data
        return self.query

3.5）查询和命令 处理程序相关联的 CRUD 事务

from typing import Dict, Any

from models.data.pony_models import Profile_Trainers, Login
from pony.orm import db_session

from models.requests.trainers import ProfileTrainersReq


class TrainerRepository:

    def insert_trainer(self, details: Dict[str, Any]) -> bool:
        try:
            with db_session:
                Profile_Trainers(**details)
        except Exception as e:
            print(e)
            return False
        return True

    def update_trainer(self, id: int, details: Dict[str, Any]) -> bool:
        try:
            with db_session:
                profile = Profile_Trainers[id]
                profile.id = details["id"]
                profile.firstname = details["firstname"]
                profile.lastname = details["lastname"]
                profile.age = details["age"]
                profile.position = details["position"]
                profile.tenure = details["tenure"]
                profile.shift = details["shift"]
        except:
            return False
        return True

    def delete_trainer(self, id: int) -> bool:
        try:
            with db_session:
                Profile_Trainers[id].delete()
        except Exception as e:
            print(e)
            return False
        return True

    def get_all_trainers(self):
        with db_session:
            trainers = Profile_Trainers.select()
            result = [ProfileTrainersReq.from_orm(m) for m in trainers]
            return result


    def get_trainer(self, id: int):
        with db_session:
            login = Login.get(lambda l: l.id == id)
            trainer = Profile_Trainers.get(lambda m: m.id == login)
            result = ProfileTrainersReq.from_orm(trainer)
        return result

3.6）接口 API 

from fastapi import APIRouter
from fastapi.responses import JSONResponse

from cqrs.commands import ProfileTrainerCommand
from cqrs.queries import ProfileTrainerListQuery
from cqrs.trainers.command.create_handlers import AddTrainerCommandHandler
from cqrs.trainers.command.delete_handlers import DeleteTrainerCommandHandler
from cqrs.trainers.command.update_handlers import UpdateTrainerCommandHandler
from cqrs.trainers.query.query_handlers import ListTrainerQueryHandler
from models.requests.trainers import ProfileTrainersReq

router = APIRouter()


@router.post("/trainer/add")
def add_trainer(req: ProfileTrainersReq):
    handler = AddTrainerCommandHandler()
    mem_profile = dict()
    mem_profile["id"] = req.id
    mem_profile["firstname"] = req.firstname
    mem_profile["lastname"] = req.lastname
    mem_profile["age"] = req.age
    mem_profile["position"] = req.position
    mem_profile["tenure"] = req.tenure
    mem_profile["shift"] = req.shift
    command = ProfileTrainerCommand()
    command.details = mem_profile
    result = handler.handle(command)
    if result is True:
        return req
    else:
        return JSONResponse(content={'message': 'create trainer profile problem encountered'}, status_code=500)


@router.patch("/trainer/update")
def update_trainer(id: int, req: ProfileTrainersReq):
    handler = UpdateTrainerCommandHandler()
    mem_profile = dict()
    mem_profile["id"] = req.id
    mem_profile["firstname"] = req.firstname
    mem_profile["lastname"] = req.lastname
    mem_profile["age"] = req.age
    mem_profile["position"] = req.position
    mem_profile["tenure"] = req.tenure
    mem_profile["shift"] = req.shift
    command = ProfileTrainerCommand()
    command.details = mem_profile
    result = handler.handle(id, command)
    if result is True:
        return req
    else:
        return JSONResponse(content={'message': 'update trainer profile problem encountered'}, status_code=500)


@router.delete("/trainer/delete/{id}")
def delete_delete(id: int):
    handler = DeleteTrainerCommandHandler()
    mem_profile = dict()
    mem_profile["id"] = id
    command = ProfileTrainerCommand()
    command.details = mem_profile
    result = handler.handle(command)
    if result is True:
        return JSONResponse(content={'message': 'profile delete successfully'}, status_code=201)
    else:
        return JSONResponse(content={'message': 'update trainer profile problem encountered'}, status_code=500)


@router.get("/trainer/list")
def list_trainers():
    handler = ListTrainerQueryHandler()
    query: ProfileTrainerListQuery = handler.handle()
    return query.records

3.7）请求类型类

from pydantic import BaseModel, field_validator


class ProfileTrainersReq(BaseModel):
    id: int
    firstname: str
    lastname: str
    age: int
    position: str
    tenure: float
    shift: int

    # 这个 id 必须加，在 query 请求时， id 可能非 int 类型
    @field_validator('id', mode="before")
    def member_id_to_int(cls, values):
        if isinstance(values, int):
            return values
        else:
            return values.id

    class Config:
        from_attributes = True

 

注意：CQRS 不允许 API 服务直接与 CRUD 事务的执行进行交互。

所以，step 4) 的处理程序，连接着 step5) 的 CRUD 事务 和 step6) 的 API 接口实现。