使用 SQLAlchemy 处理递归关系

在使用 SQLAlchemy 经典映射（数据映射器模式）将现有数据模型映射到现有数据库时，遇到了一个递归关系的问题。尝试按照文档的建议以及模仿文档中的示例来映射，但仍然无法解决问题。最终出现错误：

sqlalchemy.exc.NoForeignKeysError: Could not determine join condition
  between parent/child tables on relationship Person.neighbors - there
  are no foreign keys linking these tables.  Ensure that referencing
  columns are associated with a ForeignKey or ForeignKeyConstraint, or
  specify a 'primaryjoin' expression.

问题的原因在于有两个外键指向同一个字段，导致 SQLAlchemy 不知道如何选择。

2、解决方案 为了解决这个问题，使用了 foreign_keys 参数来指定外键。在两个关系中，都使用 foreign_keys 参数来指定两个外键列。这样，SQLAlchemy 就可以知道如何连接这两个表。

properties = {'neighbors':
              relationship(Neighbor, backref="r_person",
                           foreign_keys=neighbor.c.name,),
              'neighbors_of':
              relationship(Neighbor, backref="r_neigbor",
                           foreign_keys=neighbor.c.neighbor,)
              }

修改后的代码如下：

# Domain Model:
class Person(object):
    def __init__(self, name, neighbors=[]):
        self.name = name
        self.neighbors = neighbors


class Neighbor(object):
    def __init__(self, r_neigbor, distance):
        self.r_neigbor = r_neigbor
        self.distance = distance


# Database Schema / ORM objects:
metadata = MetaData()

person = Table(
    'person', metadata,
    Column('name', String(20), primary_key=True),
)

neighbor = Table(
    'neighbor', metadata,
    Column('name', String(20), ForeignKey(person.c.name), primary_key=True),
    Column('neighbor', String(20), ForeignKey(person.c.name), primary_key=True),
    Column('distance', Integer),
)


# Data Mappings
def datamap():
    properties = {'neighbors':
                  relationship(Neighbor, backref="r_person",
                               foreign_keys=neighbor.c.name,),
                  'neighbors_of':
                  relationship(Neighbor, backref="r_neigbor",
                               foreign_keys=neighbor.c.neighbor,)
                  }
    mapper(Person, person, properties=properties)
    mapper(Neighbor, neighbor)


# Unit Test (just to see if we don't throw errors in this case)
class TestMaps(TestCase):
    def test_map(self):
        # Setup
        session = self.get_session()

        # Exercise
        datamap()
        model = self.build_model()
        session.add(model)
        session.flush()

    def build_model(self):
        bill = Person('Bill')
        jack = Person('Jack')
        bill.neighbors = [Neighbor(jack, 10)]
        return bill

    def get_session(self):
        from sqlalchemy.orm import sessionmaker
        from sqlalchemy import create_engine
        engine = create_engine('sqlite:///:memory:', echo=True)
        metadata.create_all(engine, tables=[person, neighbor])
        Session = sessionmaker(bind=engine)
        return Session()

通过使用 foreign_keys 参数，解决了外键冲突的问题，SQLAlchemy 能够正确地映射递归关系。