Creating Dynamic Select Query in SqlAlchemy

Question

I have researched this topic and have decided just to ask here since I can't seem to find anything. I'll explain below:

Context: Flask Application with a form the client fills out and posts to the server. The form inputs are used to create a query and return data.

I am using SQLalchemy currently to construct the query from scratch. At this point, I have successfully connected to my existing Redshift database and can query properly but I cannot figure out how to dynamically construct a simple Select x, y, z statement based on the user's form inputs.

The main problem being that Query() can't take in a python list of columns. It seems you must specify each column like table.c.column1 which doesn't work well with a dynamic query since I don't know what columns I want until the user submits the form.

My 2 ideas so far:

1. Loop through all column names and use Query.add_columns(table.c['colname'])
2. Use select([col1, col2, ...]) instead of Query()
3. Use load_columns() to load only specific columns in a table to query. Unfortunately seems to only work with model objects and not reflected tables unless I am mistaken

Both of these seem backwards to me as they do not really accomplish my goal effectively.

Answer 1

SQLAlchemy is quite flexible, so both 1 and 2 get the job done. If you've no need for ORM functionality, then perhaps #2 is more natural. If the user were to pass a list of column names such as

columns = request.args.getlist('columns')

you could then create your select() quite easily with a bunch of column() constructs:

stmt = select([column(c) for c in columns]).\
    select_from(some_table)

or if you have the table at hand, like you hint in the question:

stmt = select([table.c[c] for c in columns])

and then all that is left is to execute your statement:

results = db.session.execute(stmt).fetchall()