sql: select list of columns

user_16976676 0
Viewed 189

I want to pass an str or list argument and want that sql knows how to treat it. Example of list_col='date1, date2, date3, date4' and at the end i want to have dataframe date1, date2, date3, id

query = """
    SELECT {list_col} AT TIME ZONE 'Europe/Paris' as {list_col}, {table}.{id}
    FROM {table} 
    ORDER BY {table}.{id}
"""

def fun_query(table_name, list_col, id):
    return query.format(table=table_name, list_col=list_col, id=id)

Does anyone knows how to do it please?

pandas sql python
1 Answers

As already noted this is not doable in a way you suggested because both AT TIME ZONE and AS clauses should appear along with each column. I would suggest doing something like this.

query = """
    SELECT {date_cols_as_tz}, {table}.{id}
    FROM {table} 
    ORDER BY {table}.{id}
"""


def fun_query(table_name, list_col, id, tz="'Europe/Paris'"):
    date_cols_as_tz = ",".join((f"{c} AT TIME ZONE {tz} as {c}" for c in list_col))
    return query.format(date_cols_as_tz=date_cols_as_tz, table=table_name, list_col=list_col, id=id)

When you call e.g. fun_query("my_table", ["date1", "date2"], "table_id") and print it you get following query:

SELECT date1 AT TIME ZONE 'Europe/Paris' as date1,date2 AT TIME ZONE 'Europe/Paris' as date2, my_table.table_id
FROM my_table 
ORDER BY my_table.table_id

The major changes are:

  • create date_cols_as_tz inside the fun_query
  • use real list for list_col parameter (not string like "date1,date2" but list like ["date1", "date2"])
  • added optional tz parameter to the function

The advantage of this solution is that you can easily change the timezone by using different value for tz instead of hard coded value.

Also note that this function expects that all columns in list_col are dates (but that's probably what you expect if I understood your question correctly).

user_79640980
Related