SQLAlchemy 关系

关系型数据库最重要的概念就是关系，下面将介绍如何利用 SQLAlchemy 创建关系。

relationship()

relationship() 常用参数说明：

• argument 映射类，或实际类 Mapper 实例，表示关系的目标。
• secondary 在多对多的的关系中指定关联表。
• back_populates 定义反向引用，用于建立双相关系心，在关系两侧都必须显示定义关系属性。
• backref back_populates 的简化版，不过建议显式声明反向引用，因为显式总比隐式好
• uselist 一个布尔值，指示此属性应作为列表还是标量加载。一对一关系需要设置为 False。
• primaryjoin 指定多对多关系中的一级联结条件。
• secondaryjoin 指定多对多关系中的二级联结条件。
• lazy 指定如何加载相关记录。
• order_by 指定加载相关纪录史的排序方式。
• remote_side 自关联时需要用到
• cascade 设置级联操作。

如果你使用了 relationship 建立了关系，那么 lazy 起到了非常重要的作用，因为这会涉及到一些查询性能的问题。

其中 lazy 的可选值：

• select 如果没有调 relationship 对应的字段，则不会获取多的这一边的数据，一旦调用此属性，则获取所有对应的数据，返回列表。（默认值）
• joined 将 relationship 对应的字段查找回来的数据，通过 join 的方式加到主表数据中。
• subquery 类似于 joined，不过将使用子查询。
• immediate 不管是否调用 relationship 对应的字段，都会则获取所有对应的数据，返回列表。
• dynamic 不直接加载记录，而是返回一个包含相关记录的 query 对象，一遍再继续附加查询函数对结果进行过滤。lazy="dynamic" 只可以用在一对多和多对多关系中，不可以用在一对一和多对一中。

cascade 以逗号分隔的级联规则列表，用于确定会话操作应如何从父级“级联”到子级。默认为 False。

cascade 的可选值：

• save-update 在添加一条数据的时候，会把其他和他相关联的数据都添加到数据库中。（默认）
• delete 表示当删除某一个模型中的数据的时候，是否也删掉使用 relationship 和他关联的数据。
• delete-orphan 表示当对一个ORM对象解除了父表中的关联对象的时候，自己便会被删除掉。当然如果父表中的数据被删除，自己也会被删除。这个选项只能用在一对多上，不能用在多对多以及多对一上。并且还需要在子模型中的 relationship 中，增加一个 single_parent=True 的参数。
• merge 当在使用 session.merge，合并一个对象的时候，会将使用了 relationship 相关联的对象也进行 merge 操作。（默认）
• expunge 移除操作的时候，会将相关联的对象也进行移除。这个操作只是从 session 中移除，并不会真正的从数据库中删除。
• all 是对 save-update, merge, refresh-expire, expunge, delete 几种的缩写。

一对多

在一个表中有一条记录，在另外一个表中有多条记录与之相匹配。一对多典型的示例即客户和订单的关系，一个客户可以创建多个订单，而一个订单只能对应一个客户。

在 SQLAlchemy 中订单表通过外键 ForeignKey 来引用客户表，客户表通过 relationship() 方法来关联订单表。

在 SQLAlchemy 中如果用 relationship() 将两个表建立的双向连接，其实一对多和多对一就是一回事。

class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column(String(20), unique=True)

    order = relationship('Order', backref='user')


class Order(Base):
    __tablename__ = 'order'

    id = Column(Integer, primary_key=True)
    number = Column(Integer)

    user_id = Column(ForeignKey('user.id'))

SQLAlchemy 提供的 backref 让我们可以只需要定义一个关系：

order = relationship('Order', backref='user')

添加了这个就可以不用再在 Order 类中定义 relationship 了！

但这里我还是喜欢用显式定义，因为这样可以很直观地从 Order 类定义中看到有 user 这个属性。

class User(Base):

    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column(String(20), unique=True)

    order = relationship('Order', back_populates='user')

class Order(Base):

    __tablename__ = 'order'

    id = Column(Integer, primary_key=True)
    number = Column(Integer)

    user_id = Column(ForeignKey('user.id'))

    user = relationship('User', back_populates='order')

简而言之，back_populates 的使用必须要两个表都要使用，当 id 指定了相应的用户，他们就相当于两个电脑通信了，可以共享对方的相应的属性。

如果调用 Order.user 返回单个用户，而不是用户列表，可以这样写（因为一个订单只对应一个用户）：

user = relationship('User', back_populates='order', uselist=False)

一对一

在 User 中我们只定义了几个必须的字段， 但通常用户还有很多其他信息，但这些信息可能不是必须填写的，我们可以把它们放到另一张 UserInfo 表中，这样 User 和 UserInfo 就形成了一对一的关系。你可能会奇怪一对一关系为什么不在一对多关系前面？ 那是因为一对一关系是基于一对多定义的：

class User(Base):

    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    username = Column(String(64), nullable=False, index=True)
    password = Column(String(64), nullable=False)
    email = Column(String(64), nullable=False, index=True)

    userinfo = relationship('UserInfo', back_populates='user', uselist=False)


class UserInfo(Base):

    __tablename__ = 'userinfo'

    id = Column(Integer, primary_key=True)
    name = Column(String(64))
    qq = Column(String(11))
    phone = Column(String(11))
    link = Column(String(64))

    user_id = Column(ForeignKey('user.id'))

因为一对一关系，返回的肯定是单条记录，所以可以设置 uselist=False 这样返回的就是列表，一般只通过 user 表调用 userinfo 表里的信息，所以可以只在 User 类创建关系就行，如果双向调用，需要在Userinfo 类也创建个关系。

多对多

一个表中的多个记录与另一个表中的多个记录相关联时即产生多对多关系。而我们常用的关系数据库往往不支持直接在两个表之间进行多对多的联接，为了解决这个问题，就需要引入第三个表，将多对多关系拆分为两个一对多的关系，我们称这个表为联接表。大学中选修课和学生之间的关系就是一个典型的多对多关系，一个学生可以选修多个选修课，一个选修课有多个学生学习。

注意点：

• 多对多关系需要建立中间表
• 外键放在中间表上，关系放在学生和课程表上

class Course(Base):
    __tablename__ = 'course'

    id = Column(Integer, primary_key=True)
    name = Column(String(64), unique=False)

    student = relationship('Student', secondary='association_table', back_populates='course')


class Student(Base):
    __tablename__ = 'student'

    id = Column(Integer, primary_key=True)
    name = Column(String(255), nullable=False)

    course = relationship('Course', secondary='association_table', back_populates='student')


class AssociationTable(Base):
    __tablename__ = 'association_table'

    id = Column(Integer, primary_key=True)

    # 外键
    stu_id = Column(ForeignKey('student.id', ondelete='CASCADE', onupdate='CASCADE'))
    cou_id = Column(ForeignKey('course.id', ondelete='CASCADE', onupdate='CASCADE'))

4.4 自关联一对多

领导和员工同属于 staff 表，一个员工隶属于一个领导，一个领导管理多个员工，这种情况属于自关联一对多。

class Staff(Base):
    __tablename__ = 'staff'

    id = Column(Integer(), primary_key=True, autoincrement=True)
    name = Column(String(30), nullable=False)
    up_staff_id = Column(Integer(), ForeignKey('staff.id'))  # 一个员工有一个上级领导
    
    up_staff = relationship('Staff', back_populates='down_staff', remote_side=[id])  # remote_side 用在"一"侧
    down_staff = relationship('Staff', back_populates='up_staff')

自关联一对一

比如现代社会的一夫一妻制，都隶属于 People 表中，一个男的只能有一个老婆，一个女的只能有一个丈夫，当然有不结婚的。

class People(Base):
    __tablename__ = 'people'

    id = Column(Integer(), primary_key=True, autoincrement=True)
    name = Column(String(30), nullable=False)

    other_id = Column(ForeignKey('people.id'), unique=True)
    other = relationship('People', remote_side=[id])

自关联多对多

数据表内自关联多对多关系的实例那就更多了，比如关注者和被关注者的关系。在 SQLAlchemy 中多对多的关系需要借助于关系表来实现，自关联多对多的关系也同样需要关联表，只是关联表中关联的是同一个数据表。

建立关系表后，需要通过 relationship 来建立关系，在两个数据表的多对多关系中，只需要指定 secondary 参数为关系表即可，但是在自关联关系表中的 follower_id 和 follwed_id 指向的是同一个数据表的 id，SQLAlchemy 是无法区分二者的，此时我们需要通过其他的方法来加以区分。

class Followers(Base):
    __tablename__ = 'followers'
    id = Column(Integer, primary_key=True)
    follower_id = Column(ForeignKey('user.id'))  # 关注者
    followed_id = Column(ForeignKey('user.id'))  # 被关注者


class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column(String(100))

    # 我关注了谁
    followed = relationship(
        'User',
        secondary='followers',
        
        # followers 表的两个外键都指向 user.id 所以需要设置 primaryjoin 和 secondaryjoin
        primaryjoin='Followers.follower_id == User.id',
        secondaryjoin='Followers.followed_id == User.id',
        back_populates='follower'
    )

    # 谁关注了我
    follower = relationship(
        'User',
        secondary='followers',

        # followers 表的两个外键都指向 user.id 所以需要设置 primaryjoin 和 secondaryjoin
        primaryjoin='Followers.followed_id == User.id',
        secondaryjoin='Followers.follower_id == User.id',
        back_populates='followed'
    )