On 07/27/2016 05:29 PM, T Johnson wrote:
I am generating select queries that include alias tables, literal columns,
renamed columns via label, multiple joins etc.
The queries can be somewhat complex and as a debugging feature, I'd like be
able to know which columns came from which tables...but I don't want to store
this information static and would like it to be derived from the query itself.
If the column is a literal column, constructed, or similar, then there is no
original table. If the table is used as part of an == join key, then the column
should be associated to each of the tables. So if doing three joins, a column
could have three original parent tables, etc.
So I'd like a mapping from columns to a list of tables. Example:
select a as aa, b, c, now() as d
from t1 inner join (select a, b, c from t3) t2
on t1.a == t2.a
This should say:
{aa : [t1, t3]
b : [t3]
c : [t3]
d : []}
But with proper sqlalchemy Column, Label, Table objects etc. Seems like this
should be possible, but I'm not familiar enough with traversing the trees.
the join thing plus being able to reduce below the subquery is awkward,
but perhaps this will illustrate some ideas:
from sqlalchemy import func, table, column, select
t1 = table('t1', column('a'))
t3 = table('t3', column('a'), column('b'), column('c'))
t2 = select([t3]).alias('t2')
stmt = select([t1.c.a.label('aa'), t2.c.b, t3.c.c, func.now().label('d')]).\
select_from(
t1.join(t2, t1.c.a == t2.c.a)
)
print stmt
from sqlalchemy.sql import expression
from sqlalchemy.sql import visitors
import collections
import operator
result = collections.defaultdict(list)
stack = []
all_columns = set()
all_comparisons = []
def search_for_comparisons(binary):
if binary.operator is operator.eq and \
isinstance(binary.left, expression.ColumnClause) and \
isinstance(binary.right, expression.ColumnClause):
all_comparisons.append((binary.left, binary.right))
visitors.traverse(
stmt, {}, {
"column": lambda col: all_columns.add(col),
"binary": search_for_comparisons
})
def _root_col_table(col):
return list(col.base_columns)[0].table
for col in stmt.inner_columns:
list_ = result[col.name]
if isinstance(col, expression.Label):
col = col.element
if hasattr(col, 'table'):
list_.append(_root_col_table(col).name)
for pair in all_comparisons:
if col is pair[0]:
list_.append(_root_col_table(pair[1]).name)
elif col is pair[1]:
list_.append(_root_col_table(pair[2]).name)
print result
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
