CODEWITHSUNDEEP
python-3.xsqlalchemyormunion
user3557405
user3557405

Reputation: 626

Union many tables SQLalchemy

I have a list of table names with many tables (a few dozen) in a Postgres DB mapped to SQLalchemy ORM objects. All of them have common columns and I would like to union a sub-set of columns from all of the tables into one CTE or subquery to use in further queries etc.

I know that using table1.union(table2) will union 2 tables. However, I do not want to copy paste dozens of time the same code.

Is there an elegant way of doing this?

Thank you.

Solution:

Actually it is simple as pointed out by Ian.

all_tables = []
for table in list_of_tables:
    this_table = select(.....)
    all_tables.append(this_table)

# union
unioned_tables = union(*all_tables)

Upvotes: 0

Views: 937

Answers (1)

Ian Wilson
Ian Wilson

Reputation: 9059

I think you can keep chaining that union like

a = table1.union(table2)
a = a.union(table3)

But also there are functions that take a dynamic number of tables to union as positional args:

union or union_all

q = union(table1, table2, table3)

Upvotes: 1

Related Questions