title: Using SQLAlchemy with multiple self-referential foreign keys
body: <p>What if I had something like a double linked list in a relational database, for example:</p>
  
  <pre><code>node_id    left_id    right_id
  
  1          null       2
  2          1          3
  3          2          null
  </code></pre>
  
  <p>Then I have some SQLAlchemy code like the following:</p>
  
  <pre><code>class NodeClass(Base):
      __tablename__ = 'nodes_table'
      node_id = Column(Integer, primary_key=True)
      left_id = Column(Integer, ForeignKey('nodes_table.node_id'))
      right_id = Column(Integer, ForeignKey('nodes_table.node_id'))
      left = relationship('NodeClass') # Wrong
      right = relationship('NodeClass') # Wrong
  </code></pre>
  
  <p>If I have node_id 2, and I call <code>NodeClass.left</code> I would like to receive node_id 1 in return.  How can I configure the SQLAlchemy relationships to behave this way?</p>
  
  <p>UPDATE:</p>
  
  <p>I will give a second example.  Consider a table of people, and each person has a mother and a father.</p>
  
  <pre><code>person_id    mother_id    father_id
  
  1            null         null
  2            null         null
  3            1            2
  </code></pre>
  
  <p>The SQLAlchemy code:</p>
  
  <pre><code>class PersonClass(Base):
      __tablename__ = 'persons_table'
      person_id = Column(Integer, primary_key=True)
      mother_id = Column(Integer, ForeignKey('persons_table.person_id'))
      father_id = Column(Integer, ForeignKey('persons_table.person_id'))
      mother = relation('PersonClass') # Wrong
      father = relation('PersonClass') # Wrong
  </code></pre>