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:

Loop through all column names and use Query.add_columns(table.c['colname'])
Use select([col1, col2, ...]) instead of Query()
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.

Ilja Everilä · Accepted Answer · 2017-10-06 07:14:57Z

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()

answered Oct 6, 2017 at 7:14

Ilja Everilä

53.4k9 gold badges137 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user3002486 Over a year ago

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.

Ilja Everilä Over a year ago

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.

Ilja Everilä Over a year ago

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.

user3002486 Over a year ago

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.

Ilja Everilä Over a year ago

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.

|

Collectives™ on Stack Overflow

Creating Dynamic Select Query in SqlAlchemy

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related