title: How do I specify a nested self-referential adjacency list relationship in SQLAlchemy ORM?
body: <p>I have been tasked to build some web apps for internal use at my company that hide a very ugly schema used by a very old program that is still in use. I am using Python+SQLAlchemy 1.3 and the declarative ORM module. I have to maintain backwards compatibility.</p>
  <p>Essentially, <code>Order</code>s have multiple <code>Line</code>s, and lines have multiple items (referred to by column <code>DELNUM_10</code>). <code>ORDNUM_10</code>, <code>LINNUM_10</code>, and <code>PRTNUM_10</code> together form a composite primary key that uniquely identifies a <code>Part</code> and associated info in an <code>Order</code>.</p>
  <p>In the pictured example, I'd like a Python object like so:</p>
  <pre><code>order = {number: '2000072', lines: {1: [Part{MAT03}, Part{BSKQ03}]...}}
  </code></pre>
  <p><code>PRTNUM_10</code> is a foreign key to another ORM mapped class <code>Part</code>.</p>
  <p><a href="https://i.stack.imgur.com/di3Vn.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/di3Vn.png" alt="Orders table" /></a></p>
  <pre><code>class Part(Base):
    __table__ = Part_Master
    SKU = __table__.columns.PRTNUM_01
    name = __table__.columns.PMDES1_01
    description = __table__.columns.PMDES2_01
    category = __table__.columns.COMCDE_01
  
  Order_Master = Table(
    'Order_Master',
    meta,
    Column('ORDNUM_10', CHAR(length=8), primary_key=True, nullable=False),
    Column('LINNUM_10', CHAR(length=2), primary_key=True, nullable=False),
    Column('DELNUM_10', CHAR(length=2), primary_key=True, nullable=False)
    # more unholy columns
  )
  class Order(Base):
    __table__ = Order_Master
    number = __table__.columns.ORDNUM_10
    line = __table__.columns.LINNUM_10
    subline = __table__.columns.DELNUM_10
    lines = relationship(Order, someOtherThingToMakeItWork)
  </code></pre>
  <p>How can i use <code>relationship()</code> and/or structure the <code>Order</code> class to get this to work?</p>