SqlAlchemy CTE recursive sample

cte.sqlalchemy.py

from sqlalchemy.orm import sessionmaker, relationship, aliased
from sqlalchemy import cast, Integer, Text, Column, ForeignKey, literal, null
from sqlalchemy.sql import column, label

class Catalog(Base):
    __tablename__ = 'catalog'
    id = Column(String, primary_key=True)
    parentid = Column(String, ForeignKey('catalog.id'))
    name = Column(String)
    parent = relationship("Catalog", remote_side=[id])

 
hierarchy = session.query(
            Catalog, literal(0).label('level'))\
            .filter(Catalog.parentid == null())\
            .cte(name="hierarchy", recursive=True)
           
parent = aliased(hierarchy, name="p")
children = aliased(Catalog, name="c")
hierarchy = hierarchy.union_all(
            session.query(
                children,
                (parent.c.level + 1).label("level"))
            .filter(children.parentid == parent.c.id))
            
result = session.query(Catalog, hierarchy.c.level)\
        .select_entity_from(hierarchy).all()