Question: Extracted all input tables

[netplanet1981]

Hi all,

first of all, many thanks for this amazing parser!

With following code,

from sqlglot import parse_one, exp

stmt = """
WITH derived_table AS (
select col_1 from t_1
)
select *
from derived_table inner join t_2 on derived_table.col_1 = t_2.col_1
"""
for table in parse_one(stmt).find_all(exp.Table):
print(table.name)

I get:

derived_table
t_2
t_1

Is there a way to only extract the "real" input table t_1 and t_2 by omitting the derived_table?

[slp-oozzyy]

hi there, probably not the best solution, but this does the trick :D

for table in tree.find_all(exp.Table):
    if table.name not in [w.alias for w in tree.find_all(exp.CTE)]:
        print(table.name)

[barakalon]

Scope is a good way to do this:

from sqlglot import expressions as exp
from sqlglot.optimizer.scope import traverse_scope

[
    source
    for scope in traverse_scope(expression)
    for source in scope.sources.values()
    if isinstance(source, exp.Table)
]

We should probably add these as little utility functions in sqlglot. I copied this code from my own application.