继续上节 SQLAlchemy 外键的探索.
    假设现在需求改变了, 需要给每个 Album 加个字段表示作词者, 而作词者本身也应是 Artist 的实例 (比如很多演唱者会自己为歌曲作词编曲等等), 这时 Album 会有超过一个关联到 Artist 的外键.
    如果仅仅简单如下处理

class Album(Base):
    __tablename__ = 'album'
    album_id = sqla.Column('id', sqla.Integer, primary_key=True)
    name = sqla.Column('name', sqla.String)
    artist_id = sqla.Column('artist', sqla.ForeignKey('artist.id'))
    artist = sqlorm.relationship(
          Artist,
          backref=sqlorm.backref('albums', cascade='all,delete-orphan'))
    lyricist_id = sqla.Column('lyricist', sqla.ForeignKey('artist.id'))
    lyricist = sqlorm.relationship(
          Artist,
          backref=sqlorm.backref('written_albums', cascade='all,delete-orphan'))


然后添加一点数据进去

def save():
    artist0 = Artist(name='aki misawa')
    artist1 = Artist(name='katou emiri')
    artist2 = Artist(name='yamada hirosi')
    album0 = Album(name='stella musica', artist=artist0, lyricist=artist0)
    album1 = Album(name='hoshikage no ama no hara', artist=artist0,
                   lyricist=artist0)
    album2 = Album(name='jump!', artist=artist1, lyricist=artist2)
    session = Session()
    try:
        session.add(album0)
        session.add(album1)
        session.add(album2)
        session.flush()
        session.commit()
    finally:
        session.close()

if __name__ == '__main__':
    save()


结果运行立即悲剧, SQLAlchemy 会报下面的错误

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


也许这是 SQLAlchemy 的问题, 明明这么明了的外键关系咋就跪了呢.

    好吧, 简单的正确答案是, 手动配置一下外键的 primaryjoin 属性, 如下

class Album(Base):
    __tablename__ = 'album'
    album_id = sqla.Column('id', sqla.Integer, primary_key=True)
    name = sqla.Column('name', sqla.String)
    artist_id = sqla.Column('artist', sqla.ForeignKey('artist.id'))
    artist = sqlorm.relationship(
          Artist, primaryjoin='Artist.artist_id==Album.artist_id',
          backref=sqlorm.backref('albums', cascade='all,delete-orphan'))
    lyricist_id = sqla.Column('lyricist', sqla.ForeignKey('artist.id'))
    lyricist = sqlorm.relationship(
          Artist, primaryjoin='Artist.artist_id==Album.lyricist_id',
          backref=sqlorm.backref('written_albums', cascade='all,delete-orphan'))


    然后再来试试

def save():
    artist0 = Artist(name='aki misawa')
    artist1 = Artist(name='katou emiri')
    artist2 = Artist(name='yamada hirosi')
    album0 = Album(name='stella musica', artist=artist0, lyricist=artist0)
    album1 = Album(name='hoshikage no ama no hara', artist=artist0,
                   lyricist=artist0)
    album2 = Album(name='jump!', artist=artist1, lyricist=artist2)
    session = Session()
    try:
        session.add(album0)
        session.add(album1)
        session.add(album2)
        session.flush()
        session.commit()
    finally:
        session.close()

def list_all():
    session = Session()
    try:
        print '= Artists ='
        for a in session.query(Artist).all():
            print a.name
            for album in a.albums:
                print '- Album (as performer):', album.name
            for album in a.written_albums:
                print '- Album (as lyricist):', album.name
        print ''
        print '= Albums ='
        for a in session.query(Album).all():
            print a.name
            print '+ Perform:', a.artist.name
            print '+ Lyricist:', a.lyricist.name
    finally:
        session.close()

if __name__ == '__main__':
    save()
    list_all()