如何在SQLAlchemy/SQLite中定义带额外数据的自引用关联表

首先先修正下Friends表的定义（推荐优化，避免冗余主键）：
原代码中同时把id、person_a、person_b设为主键，会形成复合主键，但id本身作为自增主键的意义不大，反而容易导致重复关联记录。更合理的两种方案：

from sqlalchemy import UniqueConstraint

class Friends(base):
    __tablename__ = 'Friends'

    # 方案1：用person_a和person_b作为复合主键（推荐，直接避免同一对用户重复关联）
    person_a = Column('PersonA', Integer, ForeignKey('Person.Id'), primary_key=True)
    person_b = Column('PersonB', Integer, ForeignKey('Person.Id'), primary_key=True)
    status = Column('Status', String())

    # 方案2：保留单独id，给用户对加唯一约束
    # id = Column('Id', Integer, primary_key=True, nullable=False, autoincrement=True)
    # person_a = Column('PersonA', Integer, ForeignKey('Person.Id'), nullable=False)
    # person_b = Column('PersonB', Integer, ForeignKey('Person.Id'), nullable=False)
    # status = Column('Status', String())
    # __table_args__ = (
    #     UniqueConstraint('PersonA', 'PersonB', name='_person_pair_unique'),
    # )

实现关联的具体步骤 #

方式1：直接通过已有的Person实例创建关联

如果已经持有person_y和person_z的实例，直接创建Friends对象并提交即可，SQLAlchemy支持直接传入实例（自动提取主键）或者手动传入id：

# 方式1-1：传入实例（更简洁）
friendship = Friends(
    person_a=person_y,
    person_b=person_z,
    status="befriended"
)

# 方式1-2：手动传入id
# friendship = Friends(
#     person_a=person_y.id,
#     person_b=person_z.id,
#     status="befriended"
# )

session.add(friendship)
session.commit()

方式2：先查询Person记录再创建关联

如果没有现成的实例，可以通过查询获取目标用户后再创建关联：

# 查询获取Karen和Chad的记录
person_y = session.query(Person).filter_by(name='Karen').first()
person_z = session.query(Person).filter_by(name='Chad').first()

# 确保查询到记录再创建关联
if person_y and person_z:
    friendship = Friends(
        person_a=person_y.id,
        person_b=person_z.id,
        status="befriended"
    )
    session.add(friendship)
    session.commit()

最佳实践补充 #

1. 避免重复关联：如果采用复合主键或唯一约束，重复插入同一对用户时会抛出数据库错误。可以先查询是否已存在该关联，或者使用SQLite的INSERT OR IGNORE语法（需要用text语句）：

from sqlalchemy import text

session.execute(
    text("INSERT OR IGNORE INTO Friends (PersonA, PersonB, Status) VALUES (:a_id, :b_id, :status)"),
    {"a_id": person_y.id, "b_id": person_z.id, "status": "befriended"}
)
session.commit()

2. 添加双向关系（方便查询）：可以在Person类中定义关系属性，快速查询某用户的所有朋友关联：

class Person(base):
    __tablename__ = 'Person'

    id = Column('Id', Integer, primary_key=True, nullable=False)
    name = Column('Name', String)

    # 定义双向关联，可通过person.friendships获取所有相关的朋友记录
    friendships = relationship(
        "Friends",
        primaryjoin="or_(Person.id == Friends.person_a, Person.id == Friends.person_b)",
        backref="people"
    )

内容的提问来源于stack exchange，提问作者Comfort Eagle