Wiki

Clone wiki

sqlalchemy / UsageRecipes / DependentTables

FindDependentTables

Using the foreign keys collection to link parent/child, a basic tree-style traversal with a check for cycles.

def find_dependent_tables(table):
    metadata = table.metadata
    stack = [table]
    result = set()
    while stack:
        t = stack.pop()
        for t2 in metadata.tables.values():
            if t2 in result:
                continue
            for fk in t2.foreign_keys:
                if fk.references(t):
                    result.add(t2)
                    stack.append(t2)
    return result

Example:

from sqlalchemy import Table, Column, Integer, MetaData, ForeignKey

m = MetaData()

t1 = Table('t1', m,
    Column('id', Integer, primary_key=True),
    Column('q', Integer, ForeignKey('t6.id'))
)
t2 = Table('t2', m,
    Column('id', Integer, ForeignKey('t1.id'), primary_key=True),
)
t3 = Table('t3', m,
    Column('id', Integer, ForeignKey('t1.id'), primary_key=True)
)
t4 = Table('t4', m,
    Column('id', Integer, primary_key=True)
)
t5 = Table('t5', m,
    Column('id', Integer, ForeignKey('t2.id'), primary_key=True)
)

t6 = Table('t6', m,
    Column('id', Integer, ForeignKey('t3.id'), primary_key=True)
)
for t in find_dependent_tables(t1):
    print t.name

Note 't1' is listed in this example due to the cycle of t1->t3->t6->t1.

t2
t3
t6
t1
t5

Updated