6

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.

Improve this question

1 Answer 1

Reset to default
5

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()
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Okay, I might just go with using select() then. I was just wondering if there was a better way to go about it using Query() since that is what seems to be the preferred method of querying by the writers of sqlalchemy. I kind of assumed something would be available to cover this use case.
You could actually use the same method with Query as well, with some slight modifications. A Query expects entities to select as positional arguments, where select() takes a list.
The Session.query() method expects pos. args., to be exact. The Query constructor also takes a sequence of entities as first argument, but usually the method on a session is used.
can you elaborate on how to do this using query()? Since I can't pass in the entities as args to query() as a list, how else would I pass them in? Reason being, I need to pass in X number of columns and not a set amount.
You can unpack an iterable as positional arguments to a function with the asterisk: db.session.query(*[table.c[c] for c in columns]) for example.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.