[sqlAlchemy] sqlAlchemy中的relationship()

在学习select和join的时候被relationship相关的东西搞得头大, 感觉看不懂这个就没办法继续下去了
官方教程在这里
先把我们一直在用的两个类拿过来
用户类, 对应user_account表

class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(30))
    fullname: Mapped[Optional[str]]
    addresses: Mapped[List["Address"]] = relationship(back_populates="user")
    def __repr__(self) -> str:
        return f"User(id={self.id!r}, name={self.name!r}, fullname={self.fullname!r})"

地址类, 对应addresses表; 注意这里是一对多的关系, 即一个用户可以有多个地址

class Address(Base):
    __tablename__ = "address"
    id: Mapped[int] = mapped_column(primary_key=True)
    email_address: Mapped[str]
    user_id = mapped_column(ForeignKey("user_account.id"))
    user: Mapped[User] = relationship(back_populates="addresses")
    def __repr__(self) -> str:
        return f"Address(id={self.id!r}, email_address={self.email_address!r})"

有两个东西

1. ForeignKey, 指定一个外键, 将user_account和addresses连接, 以将Address.user_id和user_account表中的主键绑定的方式
2. relationship(), 即Address.user属性将整表连接至User类, 而链接的另一边则要放在User类里, 即User.addresses属性的由来

# User类: addresses是一个Address对象的数组, 绑定了Address类的user属性
addresses: Mapped[List["Address"]] = relationship(back_populates="user")

# Address类: user是一个User对象, 绑定了User类的addresses
user: Mapped[User] = relationship(back_populates="addresses")

这样, orm就可以自动连接两张表了, 在Address这边, Address.user属性指向User的一个实例; 在User这边, User.address属性指向一堆Address的实例。

在relationship()的帮助下, 当我们更新User的数据时, Address也会经由User.addresses进行更新
首先创建一个用户对象, 不指明addresses, 因此为空集

spongebob = User(name="spongebob", fullname="Spongebob Squarepants")
# id会被自动指定

接着我们更新addresses

spongebob.addresses = [
  Address(email_address="ssquarepants@uvm.edu"), 
  Address(email_address="spbsqps@katty.com")
]
# id自动更新, user_id是外键, user是被relationship()绑定的

这样我们可以在User类访问Address的对象

spongebob.addresses[1]

以及在Address类中访问User

spongebob.addresses[1].user

使用commit()提交后, User和Address都会被提交
注意当我们使用s = select(User).where(User.name=="spongebob")返回User数据时, addresses不会被返回

[<User(id=1, name='spongebob', fullname='Spongebob Squarepants')>]

只有当你进行查询spongebob.addresses时, 才会加载

[ <Address(email_address="ssquarepants@uvm.edu")>, 
  <Address(email_address="spbsqps@katty.com")> ]

以上我们说的都是one-to-many情况, 而在many-to-many的情况中, 则还需要一个参数secondary,以及一个核心表关于many-to-many

from __future__ import annotations

class Base(DeclarativeBase):
    pass

# 对于一个核心表来说, 应该使用Column, 而不是mapped_column
association_table = Table(
    "association_table",
    Base.metadata,
    Column("left_id", ForeignKey("left_table.id"), primary_key=True),
    Column("right_id", ForeignKey("right_table.id"), primary_key=True),
)


class Parent(Base):
    __tablename__ = "left_table"

    id: Mapped[int] = mapped_column(primary_key=True)
    children: Mapped[List[Child]] = relationship(secondary=association_table, back_populates="parents")

class Child(Base):
    __tablename__ = "right_table"

    id: Mapped[int] = mapped_column(primary_key=True)
    parents: Mapped[List[Parent]] = relationship(secondary=association_table, back_populates="children")

这里就大概梳理了一下relationship()是干嘛的以及怎么写,具体的细节还是要去看官方文档