SQLAlchemy 数据表自关联

.

我们说数据表关系时，默认说的是数据表之间的关系「一对多、一对一、多对多等等」。而在实际应用中常常会遇到数据表内的关联，比如现在互联中的一个名词「关注者」和「被关注者」，他们都在用户范围内，只是两个用户之间的关系。

关系是描述现实世界的实体及其之间各种联系的单一的数据结构。

对于使用 SQLAlchemy 建立数据表之间的关系前面的文章 SQLAlchemy 定义关系 已经进行了介绍，今天主要看单个数据表之内的关联。

数据表内的一对多关系

数据表自关联的一对多关系，典型的就是父亲和子女的关系。我们通过在表中引用父亲的 id 来实现，然后通过反向链接来获取子女的信息。在这里我们使用 user 和其关注者来实现数据类。

class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column(String(100))
    followed_id = Column(Integer, ForeignKey("user.id"))
    followers = relationship("User", remote_side=[id], backref='user')

在以上示例中，可以通过 follwers 来获取被关注者的信息，而被关注者可以通过反向链接来获取关注者的信息，在这里我们假设一个用户只能关注一个人，一个用户可以被多个用户关注。

使用 SQLAlchemy 的完整示例代码如下：

# -*- coding:utf-8 -*-

from sqlalchemy import (
    create_engine,
    Column,
    Integer,
    String,
    DateTime,
    Text,
    ForeignKey,
    Table,
    MetaData,
)
from sqlalchemy.orm import relationship,backref,sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column(String(100))
    followed_id = Column(Integer, ForeignKey("user.id"))
    followed = relationship("User", remote_side=[id], backref='user')

engine = create_engine('sqlite:///./test.sqlite')
db_session = sessionmaker(bind=engine)
session = db_session()
Base.metadata.create_all(engine)

user1 = User(name='user1')
user2 = User(name='user2')
user3 = User(name='user3')
session.add(user1)
user2.followed = user1
session.add(user2)
user3.followed = user1
session.add(user3)
session.commit()

user = session.query(User).filter(User.name == 'user2').first()
print(user.name + 'followed:')
print(user.followed.name)

user = session.query(User).filter(User.name == 'user1').first()
print(user.name + 'followers:')
for n in user.user:
    print(n.name)
user4 = User(name='user4')
user4.followed = user
session.add(user4)
session.commit()

user = session.query(User).filter(User.name == 'user1').first()
print(user.name + 'followers:')
for n in user.user:
    print(n.name)

以上示例的运行结果如下：

user2followers:
user1
user1followed:
user2
user3
user1followed:
user2
user3
user4

数据表内的多对多关系

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

followers = Table('followers', Base.metadata,
    Column('follower_id', Integer, ForeignKey('user.id')),
    Column('followed_id', Integer, ForeignKey('user.id'))
)

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

此时需要借助 relationship 的 primaryjoin 参数和 secondaryjoin 参数。primaryjoin 表达式描述了左表和连接表之间的连接, secondaryjoin 描述了连接表和右表之间的连接.

class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column(String(100))
    followed = relationship(
        'User',
        secondary=followers,

        # primaryjoin 指明了右侧对象关联到左侧实体（关注者）的条件
        # 也就是根据左侧实体查找出对应的右侧对象
        # 执行 user.followed 时候就是这样的查找
        primaryjoin=(followers.c.follower_id == id),

        # secondaryjoin 指明了左侧对象关联到右侧实体（被关注者）的条件
        # 也就是根据右侧实体找出左侧对象
        # 执行 user.followers 时候就是这样的查找
        secondaryjoin=(followers.c.followed_id == id),

        # backref 定义了右侧实体如何访问该关系
        # 也就是根据右侧实体查找对应的左侧对象
        # 在左侧，关系被命名为 followed
        # 在右侧使用 followers 来表示所有左侧用户的列表，即粉丝列表
        backref=backref('followers', lazy='dynamic'),
        lazy='dynamic'
        )

在使用中我们可以通过 followed 来获取关注者列表，通过 followers 来获取被关注则列表。但是实际上 followed 和 followers 是两个不同的 SQL 语句，我们可以通过 print 来打印者两个语句，语句的内容如下：

1. user.followed 内容如下

   SELECT user.id AS user_id, user.name AS user_name
   FROM user, followers
   WHERE followers.follower_id = ? AND followers.followed_id = user.id
   

2. user.followers 内容如下

   SELECT user.id AS user_id, user.name AS user_name
   FROM user, followers
   WHERE followers.followed_id = ? AND followers.follower_id = user.id
   

   `