如何在SQLAlchemy中从现有关系创建新关系？

Question

我在使用sqlalchemy处理嵌套关系时遇到了一些困难。我有一个这样的模型：

engine = create_engine('sqlite:///tvdb.db', echo=True)
Base = declarative_base(bind=engine)

episodes_writers = Table('episodes_writers_assoc', Base.metadata,
    Column('episode_id', Integer, ForeignKey('episodes.id')),
    Column('writer_id', Integer, ForeignKey('writers.id')),
    Column('series_id', Integer, ForeignKey('episodes.series_id'))

class Show(Base):
    __tablename__ = 'shows'

    id = Column(Integer, primary_key = True)
    episodes = relationship('Episode', backref = 'shows')

class Episode(Base):
    __tablename__ = 'episodes'

    id = Column(Integer, primary_key = True)
    series_id = Column(Integer, ForeignKey('shows.id'))
    writers = relationship('Writer', secondary = 'episodes_writers',
                           backref = 'episodes')

class Writer(Base):
    __tablename__ = 'writers'

    id = Column(Integer, primary_key = True)
    name = Column(Unicode)

在这个模型中，节目（shows）和剧集（episodes）之间是一对多的关系，而剧集和编剧（writers）之间是多对多的关系。不过，我现在想在节目和编剧之间建立一个多对多的关系，前提是编剧与某个节目的剧集有关联。我尝试在关联表中添加另一列，但这导致了以下异常：

sqlalchemy.exc.ArgumentError: Could not determine join condition between parent/child tables on relationship Episode.writers.  Specify a 'primaryjoin' expression.  If 'secondary' is present, 'secondaryjoin' is needed as well.

显然，我在关系声明上做错了什么。我该如何创建正确的主连接（primary joins），以实现我想要的效果呢？

Answer 1

错误信息里已经给出了修复的建议。总之，你不需要一个会让你的数据结构变得不规范的列；writer.shows可以用作一个关联代理。