Recursive CTE Postgres

sqlachemy_recursive_cte.markdown

Recursive CTE/Postgres

Setup

from sqlalchemy import (String,
                        Integer,
                        engine_from_config,
                        Column,
                        ForeignKey)
from sqlalchemy.orm import sessionmaker, relationship, backref
from sqlalchemy.sql.functions import concat
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class City(Base):
    __tablename__ = 'citys'
    id = Column(Integer, primary_key=True)
    level = Column(Integer, nullable=False)
    name = Column(String, nullable=False)
    parent_id = Column(Integer, ForeignKey('citys.id'))
    children = relationship("City", backref=backref('parent', remote_side=[id]))
 
config = {
    "sqlalchemy.url" : "postgresql:///test",
    "sqlalchemy.echo" : False,
}
engine = engine_from_config(config)
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
session = sessionmaker(bind=engine)()

Example Data

root = City(id=410000, level=1, name='河南省')
child1 = City(id=410100, level=2, name='郑州市', parent_id=410000)
child2 = City(id=410102, level=3, name='二七区', parent_id=410100)
// or using this way
child1 = City(id=410100, level=2, name='郑州市', parent=root)
child2 = City(id=410102, level=3, name='二七区', parent=child1)
session.add_all([root, child1, child2])
session.commit()

Recursive CTE

cte = session.query(City.id, 
                    City.level, 
                    City.name, 
                    City.name.label("full_name"), 
                    City.parent_id).filter(City.id == 410000).cte(name="lmy", recursive=True)
                    
cte_union_query = session.query(City.id, 
                                City.level, 
                                City.name,
                                concat(cte.c.full_name, '->', City.name).label('full_name'), 
                                City.parent_id).join(cte, cte.c.id == City.parent_id)
q = session.query(cte.union(cte_union_query))
for i in q.all():
    print(i)
    


(410000, 1, '河南省', '河南省', None)
(410100, 2, '郑州市', '河南省->郑州市', 410000)
(410102, 3, '二七区', '河南省->郑州市->二七区', 410100)