python - "' 表 ' object has no attribute ' id '"on SQLAlchemy 关系与一个表上的两个外键

Question

我已经使用 SQLAlchemy 跨三个独立的类建立了关系，并使用关联表建立多对多关系。最小示例:

from sqlalchemy import *    
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import configure_mappers, relationship

Base = declarative_base()

teams_users = Table(
    'teams_users', Base.metadata,
    Column('team_id', ForeignKey('teams.id')),
    Column('user_id', ForeignKey('users.id'))
)

class User(Base):
    __tablename__ = 'users'
    # No autoincrement, since we're using externally-generated UIDs
    id = Column(Integer, primary_key=True, autoincrement=False)
    teams = relationship('Team', secondary=teams_users, back_populates="users")    

class Team(Base):
    __tablename__ = 'teams'
    id = Column(Integer, primary_key=True, autoincrement=True, nullable=False)
    game_id = Column(Integer, ForeignKey('games.id'), nullable=False)
    games = relationship("Game", foreign_keys='games.id')
    users = relationship("User", secondary='teams_users', back_populates="teams")

class Game(Base):
    __tablename__ = 'games'
    id = Column(Integer, primary_key=True, autoincrement=True, nullable=False)
    team1_id = Column(Integer, ForeignKey('teams.id'))
    team2_id = Column(Integer, ForeignKey('teams.id'))
    team1 = relationship("Team", back_populates="games", foreign_keys=team1_id, uselist=False)
    tean2 = relationship("Team", back_populates="games", foreign_keys=team2_id, uselist=False)

# done declaring, trigger the error
configure_mappers()

尝试查询这些关系中的任何一个都会返回一个 'Table' object has no attribute 'id' 错误:

Traceback (most recent call last):
  File "...", line 35, in <module>
    configure_mappers()
  File "/.../sqlalchemy/orm/mapper.py", line 3033, in configure_mappers
    mapper._post_configure_properties()
  File "/.../sqlalchemy/orm/mapper.py", line 1832, in _post_configure_properties
    prop.init()
  File "/.../sqlalchemy/orm/interfaces.py", line 183, in init
    self.do_init()
  File "/.../sqlalchemy/orm/relationships.py", line 1655, in do_init
    self._process_dependent_arguments()
  File "/.../sqlalchemy/orm/relationships.py", line 1680, in _process_dependent_arguments
    setattr(self, attr, attr_value())
  File "/.../sqlalchemy/ext/declarative/clsregistry.py", line 281, in __call__
    x = eval(self.arg, globals(), self._dict)
  File "<string>", line 1, in <module>
AttributeError: 'Table' object has no attribute 'id'

我以这种方式构建它的目标是我可以轻松地检查每个用户曾经加入过哪些团队。

此外，Game 有两个指向 Team 的外键，因为该项目的用例支持任意规模的团队，但只能支持两个团队。这使我能够获得“team1 won”的结果，并立即引用获胜用户以进行统计跟踪和历史引用。

我在这里做错了什么？

Answer 1

要定义一场比赛与参加比赛的两支球队之间的关系，您只需为 games 表提供外键；一个团队可以参加多场比赛，一对多的关系；完全删除 games_id 列。您得到的异常有点无关紧要，但它无法在不需要该外键的关系中正确配置 foreign_keys='games.id' 参数。

Team 类的关系配置在这里有点棘手，因为 Team.games 属性必须与任一外键相关。这包含在 Handling Multiple Join Paths 下的文档中;你快到了，但这里不需要 uselist 参数:

class Game(Base):
    __tablename__ = 'games'
    id = Column(Integer, primary_key=True, autoincrement=True, nullable=False)
    team1_id = Column(Integer, ForeignKey('teams.id'))
    team2_id = Column(Integer, ForeignKey('teams.id'))
    team1 = relationship("Team", foreign_keys=team1_id)
    team2 = relationship("Team", foreign_keys=team2_id)

请注意，我在这里省略了 back_populates 引用，因为两个关系更新另一个站点上的单个关系会导致两个外键中的一个或另一个被更新为另一个值，从而导致双方同队比赛!

反向关系属性 Team.games 需要自定义 primaryjoin因为您正在寻找 team1_id 或 team2_id 是指向后方的外键的游戏。使用 foreign() annotation帮助 SQLAlchemy 确定何时更新关系(它将监视外键更改)，并使用 lambda 延迟解析列:

class Team(Base):
    __tablename__ = 'teams'
    id = Column(Integer, primary_key=True, autoincrement=True, nullable=False)
    # game_id = Column(Integer, ForeignKey('games.c.id'), nullable=False)
    games = relationship(
        "Game",
        primaryjoin=lambda: or_(
            Team.id == foreign(Game.team1_id),
            Team.id == foreign(Game.team2_id)
        ),
        viewonly=True,
    )
    users = relationship("User", secondary='teams_users', back_populates="teams")

您还可以使 primaryjoin 成为一个字符串，其中包含现在正在 lambda 中执行的表达式，因此 'or_(Team.id == foreign(Game. team1_id), Team.id == foreign(Game.team2_id))'.

同样，没有back_populates，这种类型的关系不能自动更新加载对象之间的关系。如果您需要在提交之前查看反射(reflect)的这些关系，则需要发出 session 刷新。我还添加了 viewonly=True，因为您无法将突变映射到 Team.games 列表以更新数据库(将新游戏添加到列表意味着什么，这个团队是团队 1 还是团队 2？)。

您可能希望添加一个自定义约束表以确保比赛永远不会在双方的同一支球队之间进行:

class Game(Base):
    # ...
    __table_args__ = (
        CheckConstraint(team1_id != team2_id, name='different_teams'),
    )

关系的快速演示:

from itertools import combinations

engine = create_engine('sqlite:///:memory:', echo=False)
Base.metadata.create_all(engine)
session = sessionmaker(bind=engine)()

teams = [Team() for _ in range(3)]
session.add_all(teams)
user = User(id=42, teams=teams)
session.add(user)

games = [Game(team1=t1, team2=t2) for t1, t2 in combinations(teams, 2)]
session.add_all(games)
session.commit()

for team in user.teams:
    print('Team:', team.id, 'games:', [g.id for g in team.games])
for game in session.query(Game):
    print(f'Game {game.id}: team {game.team1.id} vs {game.team2.id}')

哪些输出:

Team: 2 games: [1, 3]
Team: 1 games: [1, 2]
Team: 3 games: [2, 3]
Game 1: team 1 vs 2
Game 2: team 1 vs 3
Game 3: team 2 vs 3