随记-SQLAlchemy ORM 梳理

2023-12-25 21:54:14

随记

最近在使用 SQLAlchemy 2.0 的过程中，有一些不适应的地方，所以想梳理一下 SQLAlchemy ORM 的使用。
关于旧版的使用以及其他信息请参考另一篇文章Flask 初探七, 这里就不过多赘述，直接进入主题。

One To Many

单向

官方版本


class Parent(Base):
    __tablename__ = "parent_table"

    id: Mapped[int] = mapped_column(primary_key=True)
    # TODO relationship()
    # Parent --> Child
    children: Mapped[List["Child"]] = relationship()


class Child(Base):
    __tablename__ = "child_table"

    id: Mapped[int] = mapped_column(primary_key=True)
    parent_id: Mapped[int] = mapped_column(ForeignKey("parent_table.id"))

CompanyToApp


class CompanyInfoModel(db.Model):
    __tablename__ = "company_info"
    __table_args__ = {"comment": "公司表"}

    code = mapped_column(String(200), comment="公司编码", nullable=False, index=True)
    name = mapped_column(String(200), comment="公司名", nullable=False, index=True)
    detail = mapped_column(String(500), comment="描述", default="", nullable=False)
    
    # 官方版本使用 Mapped[List["AppInfoModel"]] 指定类型
    # app:Mapped[List["AppInfoModel"]] = relationship(back_populates="company")

    # 为了和上面的code、name 形式同一，我使用了下面这种方式，
    # TODO　单向绑定 company --> app
    app = relationship("AppInfoModel")


class AppInfoModel(db.Model):
    __tablename__ = "app_info"
    __table_args__ = {"comment": "应用表"}

    name = mapped_column(String(100), comment="应用名", nullable=False, index=True)
    secret = mapped_column(String(200), comment="secret", nullable=False, index=True)
    detail = mapped_column(String(500), comment="描述", default="", nullable=False)
    
    # 官方版本
    # company_id:Mapped[BIGINT] = mapped_column(ForeignKey("company_info.id"), index=True)

    # 单向绑定 或者 双向绑定，ForeignKey 是不变的
    company_id = mapped_column(BIGINT, ForeignKey("company_info.id"), index=True)

双向

官方版本


class Parent(Base):
    __tablename__ = "parent_table"

    id: Mapped[int] = mapped_column(primary_key=True)

    # TODO relationship(back_populates="parent")
    # 双向绑定，在 Parent 使用 back_populates 指定 Child 的属性 parent
    children: Mapped[List["Child"]] = relationship(back_populates="parent")


class Child(Base):
    __tablename__ = "child_table"

    id: Mapped[int] = mapped_column(primary_key=True)
    # ForeignKey 是不变的
    parent_id: Mapped[int] = mapped_column(ForeignKey("parent_table.id"))

    # 双向绑定，在 Child 增加 Parent 类型的属性 parent ，通过 back_populates 关联 children
    parent: Mapped["Parent"] = relationship(back_populates="children")

CompanyToApp


class CompanyInfoModel(db.Model):
    __tablename__ = "company_info"
    __table_args__ = {"comment": "公司表"}

    code = mapped_column(String(200), comment="公司编码", nullable=False, index=True)
    name = mapped_column(String(200), comment="公司名", nullable=False, index=True)
    detail = mapped_column(String(500), comment="描述", default="", nullable=False)
     
    # 双向绑定
    app = relationship("AppInfoModel", back_populates="company")


class AppInfoModel(db.Model):
    __tablename__ = "app_info"
    __table_args__ = {"comment": "应用表"}

    name = mapped_column(String(100), comment="应用名", nullable=False, index=True)
    secret = mapped_column(String(200), comment="secret", nullable=False, index=True)
    detail = mapped_column(String(500), comment="描述", default="", nullable=False) 

    # 单向绑定 或者 双向绑定，ForeignKey 是不变的
    company_id = mapped_column(BIGINT, ForeignKey("company_info.id"), index=True)
    
    # 双向绑定
    company = relationship("CompanyInfoModel", back_populates="app")

小结

ForeignKey 在多的一方
不管单向还是双向 ForeignKey 都是必须的
单向绑定：多的一方使用 relationship 即可，双向绑定需要 relationship + back_populates
双向绑定：
- 在类 CompanyInfoModel 增加类型为 AppInfoModel 的属性 app
- 在类 AppInfoModel 增加类型为 CompanyInfoModel 的属性 company
- 使用 back_populates 指向关联类的关联属性
  - CompanyInfoModel.app --> AppInfoModel.company --> CompanyInfoModel
  - AppInfoModel.company --> CompanyInfoModel.app = AppInfoModel

Many To Many 间接

间接单向

官方


from typing import Optional

from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import relationship


class Base(DeclarativeBase):
    pass


class Association(Base):
    __tablename__ = "association_table"
    left_id: Mapped[int] = mapped_column(ForeignKey("left_table.id"), primary_key=True)
    right_id: Mapped[int] = mapped_column(
        ForeignKey("right_table.id"), primary_key=True
    )
    extra_data: Mapped[Optional[str]]
    child: Mapped["Child"] = relationship()


class Parent(Base):
    __tablename__ = "left_table"
    id: Mapped[int] = mapped_column(primary_key=True)
    children: Mapped[List["Association"]] = relationship()


class Child(Base):
    __tablename__ = "right_table"
    id: Mapped[int] = mapped_column(primary_key=True)

UserGroup2User


class UserUserGroupRelateModel(db.Model):
    __tablename__ = "user_usergroup_relate"
    __table_args__ = {"comment": "User_UserGroup_关联表"}

    user_id = mapped_column(ForeignKey("user_info.id"), primary_key=True, index=True)
    user_group_id = mapped_column(ForeignKey("user_group_info.id"), primary_key=True, index=True) 
    
    # 多对多 单向 官方
    # relate_user:Mapped[List["UserInfoModel"]] = relationship()
    # 多对多 单向 UserUserGroupRelateModel.relate_user 是 UserInfoModel 类型
    # UserGroupInfoModel.users_relate --> UserUserGroupRelateModel.relate_user --> UserInfoModel
    relate_user = relationship("UserInfoModel")


class UserGroupInfoModel(db.Model):
    __tablename__ = "user_group_info"
    __table_args__ = {"comment": "用户组表"}
 
    name = mapped_column(String(100), comment="用户组 组名", nullable=False, index=True) 
 
    # 多对多 间接 单向 官方
     # users_relate:Mapped[List["UserUserGroupRelateModel"]] = relationship()
    # 多对多 单向 UserGroupInfoModel.users_relate 是 UserUserGroupRelateModel 类型
    # UserGroupInfoModel.users_relate --> UserUserGroupRelateModel.relate_user --> UserInfoModel
    users_relate = relationship("UserUserGroupRelateModel")
 

class UserInfoModel(db.Model):
    __tablename__ = "user_info"
    __table_args__ = {"comment": "用户表"}

    code = mapped_column(String(200), comment="用户编码", nullable=False, index=True)
    name = mapped_column(String(100), comment="用户名", nullable=False, index=True)
    phone = mapped_column(String(100), comment="手机号", nullable=False, default="")
    email = mapped_column(String(100), comment="电子邮件", nullable=False, default="")

间接双向

官方

from typing import Optional

from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import relationship


class Base(DeclarativeBase):
    pass


class Association(Base):
    __tablename__ = "association_table"
    left_id: Mapped[int] = mapped_column(ForeignKey("left_table.id"), primary_key=True)
    right_id: Mapped[int] = mapped_column(
        ForeignKey("right_table.id"), primary_key=True
    )
    extra_data: Mapped[Optional[str]]
    child: Mapped["Child"] = relationship(back_populates="parents")
    parent: Mapped["Parent"] = relationship(back_populates="children")


class Parent(Base):
    __tablename__ = "left_table"
    id: Mapped[int] = mapped_column(primary_key=True)
    children: Mapped[List["Association"]] = relationship(back_populates="parent")


class Child(Base):
    __tablename__ = "right_table"
    id: Mapped[int] = mapped_column(primary_key=True)
    parents: Mapped[List["Association"]] = relationship(back_populates="child")

UserGroup2User



class UserUserGroupRelateModel(db.Model):
    __tablename__ = "user_usergroup_relate"
    __table_args__ = {"comment": "User_UserGroup_关联表"}

    user_id = mapped_column(ForeignKey("user_info.id"), primary_key=True, index=True)
    user_group_id = mapped_column(ForeignKey("user_group_info.id"), primary_key=True, index=True) 
    
    # 多对多 间接双向 官方
    # relate_user:Mapped["UserInfoModel"] = relationship(back_populates="groups_relate")
    # relate_user_group:Mapped["UserGroupInfoModel"] = relationship(back_populates="users_relate") 
    # 多对多 间接单向 UserUserGroupRelateModel.relate_user 是 UserInfoModel 类型
    relate_user = relationship("UserInfoModel", back_populates="groups_relate")
    relate_user_group = relationship("UserGroupInfoModel", back_populates="users_relate")
 



class UserGroupInfoModel(db.Model):
    __tablename__ = "user_group_info"
    __table_args__ = {"comment": "用户组表"}
 
    name = mapped_column(String(100), comment="用户组 组名", nullable=False, index=True) 
 
    # 多对多 间接双向 官方
    # users_relate:Mapped[List["UserUserGroupRelateModel"]] = relationship(back_populates="relate_user_group")
    # 多对多 间接双向 
    # 间接双向 约等于 两个单向
    # UserGroupInfoModel.users_relate 是 UserUserGroupRelateModel 类型
    # UserUserGroupRelateModel.relate_user_group 是 UserGroupInfoModel 类型
    # UserGroupInfoModel.users_relate --> UserUserGroupRelateModel.relate_user_group --> UserGroupInfoModel
    users_relate = relationship("UserUserGroupRelateModel", back_populates="relate_user_group")
 

class UserInfoModel(db.Model):
    __tablename__ = "user_info"
    __table_args__ = {"comment": "用户表"}

    code = mapped_column(String(200), comment="用户编码", nullable=False, index=True)
    name = mapped_column(String(100), comment="用户名", nullable=False, index=True)
    phone = mapped_column(String(100), comment="手机号", nullable=False, default="")
    email = mapped_column(String(100), comment="电子邮件", nullable=False, default="")

    # 多对多 间接双向 官方
    # groups_relate:Mapped[List["UserUserGroupRelateModel"]] = relationship(back_populates="relate_user")
    # 多对多 间接双向 
    # 间接双向 约等于 两个单向
    # UserInfoModel.groups_relate 是 UserUserGroupRelateModel 类型
    # UserUserGroupRelateModel.relate_user 是 UserInfoModel 类型
    # UserInfoModel.groups_relate --> UserUserGroupRelateModel.relate_user --> UserInfoModel
    groups_relate = relationship("UserUserGroupRelateModel", back_populates="relate_user")

间接（关联对象）小结

多对多需要借助第三方 UserUserGroupRelateModel
单向只需要 relationship，双向需要 relationship + back_populates
多对多的单向，单向只需要 relationship
- 在类 UserGroupInfoModel 增加类型为 UserUserGroupRelateModel 的属性 users_relate
- 在类 UserUserGroupRelateModel 增加类型为 UserInfoModel 的属性 relate_user
- UserGroupInfoModel.users_relate --> UserUserGroupRelateModel.relate_user -->UserInfoModel
多对多的双向
- UserGroupInfoModel.users_relate --> UserUserGroupRelateModel.relate_user --> UserInfoModel
  - 在类 UserGroupInfoModel 增加类型为 UserUserGroupRelateModel 的属性 users_relate
  - 在类 UserUserGroupRelateModel 增加类型为 UserInfoModel 的属性 relate_user
- UserInfoModel.groups_relate --> UserUserGroupRelateModel.relate_user_group --> UserGroupInfoModel
  - 在类 UserInfoModel 增加类型为 UserUserGroupRelateModel 的属性 groups_relate
  - 在类 UserUserGroupRelateModel 增加类型为 UserGroupInfoModel 的属性 relate_user_group
- 通过 back_populates 关联对应类型的关联属性
  - UserGroupInfoModel.users_relate --> UserUserGroupRelateModel.relate_user_group --> UserGroupInfoModel
    - 类 UserGroupInfoModel的属性 users_relate 是 UserUserGroupRelateModel 类型
    - 类 UserUserGroupRelateModel的属性 relate_user_group 是 UserGroupInfoModel 类型
  - UserInfoModel.groups_relate --> UserUserGroupRelateModel.relate_user --> UserInfoModel
    - 类 UserInfoModel的属性 groups_relate 是 UserUserGroupRelateModel 类型
    - 类 UserUserGroupRelateModel的属性 relate_user 是 UserInfoModel 类型

Many To Many 间接 + 直接

Many To Many 直接


class UserUserGroupRelateModel(db.Model):
    __tablename__ = "user_usergroup_relate"
    __table_args__ = {"comment": "User_UserGroup_关联表"}

    user_id = mapped_column(ForeignKey("user_info.id"), primary_key=True, index=True)
    user_group_id = mapped_column(ForeignKey("user_group_info.id"), primary_key=True, index=True) 
    

class UserGroupInfoModel(db.Model):
    __tablename__ = "user_group_info"
    __table_args__ = {"comment": "用户组表"}
 
    name = mapped_column(String(100), comment="用户组 组名", nullable=False, index=True) 
 
    # # 多对多 双向绑定 官方
    # user:Mapped[List["UserInfoModel"]] = relationship(secondary="user_usergroup_relate", back_populates="group")
    # 多对多 双向直接
    user = relationship("UserInfoModel",secondary="user_usergroup_relate", back_populates="group")
 

class UserInfoModel(db.Model):
    __tablename__ = "user_info"
    __table_args__ = {"comment": "用户表"}

    code = mapped_column(String(200), comment="用户编码", nullable=False, index=True)
    name = mapped_column(String(100), comment="用户名", nullable=False, index=True)
    phone = mapped_column(String(100), comment="手机号", nullable=False, default="")
    email = mapped_column(String(100), comment="电子邮件", nullable=False, default="")

    # # 多对多 双向直接 官方
    # group:Mapped[List["UserGroupInfoModel"]] = relationship(secondary="user_usergroup_relate", back_populates="user")
    # 多对多 双向直接
    group = relationship("UserGroupInfoModel", secondary="user_usergroup_relate", back_populates="user")

Many To Many 间接+直接

    
class UserUserGroupRelateModel(db.Model):
    __tablename__ = "user_usergroup_relate"
    __table_args__ = {"comment": "User_UserGroup_关联表"}

    user_id = mapped_column(ForeignKey("user_info.id"), primary_key=True, index=True)
    user_group_id = mapped_column(ForeignKey("user_group_info.id"), primary_key=True, index=True) 
    
    # 多对多 间接双向 
    relate_user = relationship("UserInfoModel", back_populates="groups_relate")
    relate_user_group = relationship("UserGroupInfoModel", back_populates="users_relate")
 



class UserGroupInfoModel(db.Model):
    __tablename__ = "user_group_info"
    __table_args__ = {"comment": "用户组表"}
 
    name = mapped_column(String(100), comment="用户组 组名", nullable=False, index=True) 
 
    # 多对多 间接双向 
    users_relate = relationship("UserUserGroupRelateModel", back_populates="relate_user_group")
 
    # 多对多 双向直接
    user_list = relationship("UserInfoModel",secondary="user_usergroup_relate", back_populates="group_list")
 

class UserInfoModel(db.Model):
    __tablename__ = "user_info"
    __table_args__ = {"comment": "用户表"}

    code = mapped_column(String(200), comment="用户编码", nullable=False, index=True)
    name = mapped_column(String(100), comment="用户名", nullable=False, index=True)
    phone = mapped_column(String(100), comment="手机号", nullable=False, default="")
    email = mapped_column(String(100), comment="电子邮件", nullable=False, default="")

    # 多对多 间接双向 
    groups_relate = relationship("UserUserGroupRelateModel", back_populates="relate_user")

    # 多对多 双向直接
    group_list = relationship("UserGroupInfoModel", secondary="user_usergroup_relate", back_populates="user_list")

小结

直接或者间接都必须存在第三方 UserUserGroupRelateModel
- 直接：可以跳过第三方，直接关联另一方
  - UserGroupInfoModel.user_list[0] --> UserInfoModel
    - 类 UserGroupInfoModel的属性 user_list （的每一项都）是 UserInfoModel 类型
  - UserInfoModel.group_list[0] --> UserGroupInfoModel
    - 类 UserInfoModel的属性 group_list 是 UserGroupInfoModel 类型
  - back_populates 关联另一方的关联属性
    - UserGroupInfoModel.user_list back_populates UserInfoModel.group_list
    - UserInfoModel.group_list back_populates UserGroupInfoModel.user_list
- 间接：必须通过第三方才能获取另一方
  - UserGroupInfoModel.users_relate --> UserUserGroupRelateModel.relate_user --> UserInfoModel
  - UserInfoModel.groups_relate --> UserUserGroupRelateModel.relate_user_group --> UserGroupInfoModel
两者存在冲突，即通过直接方式修改的数据，在同一个session 里没办法同步到间接，反之亦然。

When using this ORM model to make changes, changes made to Parent.children will not be coordinated with changes made to Parent.child_associations or Child.parent_associations in Python; while all of these relationships will continue to function normally by themselves, changes on one will not show up in another until the Session is expired, which normally occurs automatically after Session.commit().

Additionally, if conflicting changes are made, such as adding a new Association object while also appending the same related Child to Parent.children, this will raise integrity errors when the unit of work flush process proceeds.
如果关联对象没有特殊的属性，建议选择直接方式
- 直接方式的 model 只需要在两个多方添加属性
- 访问数据是可以直接跳过第三方，直接得到另一方的列表 UserGroupInfoModel.user_list
- 避免存在直接+间接时可能造成的影响

Source Code

到此结? DragonFangQy 2023.12.25

文章来源:https://blog.csdn.net/f704084109/article/details/135209085
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！

随记-SQLAlchemy ORM 梳理

随记

One To Many

单向

官方版本

CompanyToApp

双向

官方版本

CompanyToApp

小结

Many To Many 间接

间接单向

官方

UserGroup2User

间接双向

官方

UserGroup2User

文章目录

间接（关联对象） 小结

Many To Many 间接 + 直接

Many To Many 直接

Many To Many 间接+直接

小结

间接（关联对象）小结