I’m writing a code to expand a SELECT query that uses * or alias.* into SELECT query with defined column names.

The idea is to supply the query, table list and associated column list and return a query with all those columns defined within the query.

Example

table_names = ["my_table", "other_table"]
column_names = {
    "my_table": ["id", "column1", "column2"],
    "other_table": ["id", "column3", "column4"]
}
sql_query = """SELECT * FROM (SELECT * FROM my_table as m join other_table as o on o.id=m.id) AS subquery"""

result_query = """SELECT subquery.id,subquery.column1,subquery.column2,subquery.id,subquery.column3,subquery.column4 FROM (SELECT my_table.id,my_table.column1,my_table.column2,other_table.id,other_table.column3,other_table.column4 FROM my_table as m join other_table as o on o.id=m.id) AS subquery

I have tried getting the column list vie sql_metadata, but I was wondering if there’s a library that can already do it.

If not, do we just get a list of tables and append the column list side by side?

Thanks in advance.

NOTE: I cannot directly connect to the DB and need to do this outside of the system with python