SQLAlchemy eager loading recursive model

Question

How can you write the model that it eager loads the parents and children of a certain role recursively. So not only the child of the role you are fetching now but also it's children.

Do you risk ending in an infinite loop or does SQLAlchemy have the logic to detect these?

The SQLAlchemy model is as follows:

from sqlalchemy.ext.declarative import declarative_base


roles_parents = Table(
'roles_parents', Base.metadata,
Column('role_id', Integer, ForeignKey('roles.id')),
Column('parent_id', Integer, ForeignKey('roles.id'))
)


Base = declarative_base()
class Role(Base):

    __tablename__ = 'roles'

id = Column(Integer, primary_key=True)
name = Column(String(20))
parents = relationship(
    'Role',
    secondary=roles_parents,
    primaryjoin=(id == roles_parents.c.role_id),
    secondaryjoin=(id == roles_parents.c.parent_id),
    backref=backref('children', lazy='joined'),
    lazy='joined'
)

def get_children(self):
    logger.log_dbg("get_children(self) with name: "  + self.name)
    for child in self.children:
        yield child
        for grandchild in child.get_children():
            yield grandchild

@staticmethod
def get_by_name(name):
    logger.log_dbg("get_by_name( " + name + " )")
    with DBManager().session_scope() as session:
        role = session.query(Role).options(joinedload(
            Role.children).joinedload(Role.parents)).filter_by(
            name=name).first()
        # role = session.query(Role).filter_by(name=name).first()
        session.expunge_all()
        return role

You can see that I tried to enable eager loading on the parents relationship via an attribute in the relationship and via options in the query where I fetch a Role.

The reason for needing this eager load (and session.expunge_all()), is that the session is lost when trying to get the child via get_children().

Due to the eager load, get_children no longer fails when accessing the child role of this role. However, it still fails when trying to fetch the grandchild. So eager loading seems to work for the child role but does not eager load its children.

There is a github repository hierarchical-data that provides several options of reading and writing parent-child related models using Flask and SQLALchemy. — Sohaib Farooqi

Ricardo Stuven Ricardo Stuven · Accepted Answer · 2019-04-05T03:51:17

A solution based on this post:

def recursive_expunge(obj, dbSession):
    def _recursive_expunge(_obj):
        _instance_state = sqlalchemy.inspection.inspect(_obj)
        if _instance_state.detached or _instance_state.transient:
          return
        _mapper = _instance_state.mapper
        try:
            dbSession.expunge(_obj)
        except sqlalchemy.orm.exc.UnmappedInstanceError:
            pass
        except sqlalchemy.exc.InvalidRequestError:
            pass
        if _mapper:
            _loaded_rels = [i for i in _mapper.relationships.items() if i[0] not in _instance_state.unloaded]
            for (_name, _rel) in _loaded_rels:
                _loaded_rel_data = getattr(_obj, _name)
                if _loaded_rel_data:
                    if not _rel.uselist:
                        _recursive_expunge(_loaded_rel_data)
                    else:
                        for _i in _loaded_rel_data:
                            _recursive_expunge(_i)
    _recursive_expunge(obj)

SQLAlchemy eager loading recursive model

2 Answers