Adding introspection to the PyPika query builder

[Ovid]

I'm used to doing a lot of work with metaprogramming. For a project I'm working on, I have classes which receive query builder objects. It would be extremely useful to have introspection available on those objects. For example, for an outer join, I need to know which tables have been joined against and which fields have been selected.

from pypika import Query, Table

employees   = Table('Employees')
departments = Table('Departments')

# Build the query
query = Query.from_(employees) \
    .left_join(departments).on(employees.department_id == departments.id) \
    .select(employees.name, departments.department_name)

# Attempt to extract tables
# Note: This is a workaround and depends on PyPika's internal implementation
tables = [table.get_sql(quote_char=None) for table in query._from]

print(tables)

Unfortunately, that only prints Employees. Departments is omitted. I could try to write code to walk through the query instance, but that's fraught with error, especially since I'm forced to use internal methods such as query._from.

It would be lovely to have something like this:

tables = query.metatada.all_tables() # [ "Employees", "Departments" ]
fields = query.metadata.all_fields() # { "Employees": ["name" ], "Departments": ["department_name"] }

Obviously, this would not be all that is needed, but it would be enough for my particular use case.

[wd60622]

I think this sounds very useful. Do you think returning Table objects and Field objects instead of string would still be useful?

[Ovid]

@wd60622 I'd be happy getting objects back instead of strings, so long as I can extract the extra information. However, I suspect we'd want metadata objects and not just Table and Field objects. For example, if we later want to extend its capabilities, we might not want to change the underlying Table and Field objects.

[wd60622]

yeah, I think returning strings would not be ideal for general usage.

What do you imagine the result would store if it was not the Table and Field instances? Those objects hold a little surround information but not much by design.

For instance,

from pypika import Table, Field

table = Table("table")
field_from_table = table.field
field = Field("field")

table alone doesn't store that much information field_from_table has table information in table attribute field is standalone which could be from any table without query context