SqlAlchemy Foreign Key propagating down 3 layers

Question

I am trying to update a simple 3 layer relational set of tables.

They are

• Parent
• Child
• GrandChild

The SQLAlchemy code for the model looks like

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    children = relationship("Child", back_populates="parents")


class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    parent_id = Column(Integer, ForeignKey('parent.id'))
    parents = relationship("Parent", back_populates="children")
    grandchildren = relationship("GrandChild",
                                back_populates="grandparent",
                                 )

class GrandChild(Base):
    __tablename__ = 'grandchild'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    parent_id = Column(Integer, ForeignKey('parent.id'))
    child_id = Column(Integer, ForeignKey('child.id'))
    grandparent = relationship("Child", back_populates="grandchildren")

And the Insert code looks like this....

p3 = Parent(name="P3")
c5 = Child(name="C5")
c6 = Child(name="C6")
gc1 = GrandChild(name="gc1")
gc2 = GrandChild(name="gc2")
gc3 = GrandChild(name="gc3")
gc4 = GrandChild(name="gc4")

p3.children = [c5, c6]
c5.grandchildren = [gc1]
c6.grandchildren = [gc2,  gc3, gc4]

session.add_all([p2, p3])

session.commit()

The record is added - and Parent/Child are correctly linked - but GrandChildren are missing the Parent foreign key.

I have struggled in finding the correct mechanism to add this - can anyone point me in the right direction ?

Answer 1

You don't create the relation between grandchilds and parents.
The relationship between a grandchild and a parent isn't implicit in your data model; the parent of a child doesn't automatically become the parent of all the child's grandchildren.

You have to define that relationship explicitly, i.e. add it to the GrandChild:

class GrandChild(Base):
    [...]
    parent = relationship("Parent")

and then create the relation on the instances:

gc1.parent = p3
gc2.parent = p3
gc3.parent = p3
gc4.parent = p3

This will add the records accordingly:

sqlalchemy.engine.base.Engine INSERT INTO grandchild (name, parent_id, child_id) VALUES (?, ?, ?)
sqlalchemy.engine.base.Engine ('gc1', 1, 1)
[...]

However, since the parent-child relationship in your data model doesn't imply any grandchild-parent relationship, you can create a parent without children, that has grandchildren.

sink = Parent(name="SINK")
gc1.parent = sink

print("Name: {}, Parent: {}, Parent.children: {}, Child.parent: {}"
      .format(gc1.name, gc1.parent.name, gc1.parent.children, gc1.grandparent.parents.name))
# Name: gc1, Parent: SINK, Parent.children: [], Child.parent: P3

Based on my understanding of a three-tier-relation, I can't think of a use case where sth. like this would find an application.
If you want an implicit and consistent relationship between an parent and a grandchild through a child, drop the direct relationship between parent and grandchild:

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    children = relationship("Child", back_populates="parent")

    def __repr__(self):
        return "{}(name={})".format(self.__class__.__name__, self.name)

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    parent_id = Column(Integer, ForeignKey('parent.id'))
    parent = relationship("Parent", back_populates="children")
    children = relationship("GrandChild", back_populates="parent")

    # same __repr__()

class GrandChild(Base):
    __tablename__ = 'grandchild'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    child_id = Column(Integer, ForeignKey('child.id'))
    parent = relationship("Child", back_populates="children")

    # same __repr__()

p3 = Parent(name="P3")
c5 = Child(name="C5")
gc1 = GrandChild(name="gc1")    
p3.children = [c5]
c5.children = [gc1]

You can access the grandchild's grandparent through:

print(gc1.parent.parent)
# Parent(name=P3)

The other way around is a bit more tedious though, due to the two one-to-many relationships in the hierarchy:

for child in p3.children:
    for gc in child.children:
        print(p3, child, gc)
# Parent(name=P3) Child(name=C5) GrandChild(name=gc1)