Dynamically generating queries with SQLAlchemy

I have a table containing Posts, with a number of columns. id, title, author, tags, timestamp

I'm trying to use SQLAlchemy to sort/filter these posts by each of the columns. For example, a user can decide they want to see all posts from author, with tags a, b, c, ordered by timestamp descending.

Is there a way to dynamically build a query starting from a simple sqlalchemy.select()?

For example:

import sqlalchemy as sa

query = sa.select(models.Post)
if userProvidedAuthor:
    query.filter(models.Post.author == userProvidedAuthor)
if userProvidedOrder:
    if userProvidedDirection == 'asc':
        query.order_by(userProvidedOrder.asc())
    else:
        query.order_by(userProvidedOrder.desc())

results = session.execute(query)

Obviously this example won't run, but I hope it illustrates what I'm trying to do.

Create a Select
'append' some filters to it
'append' some order_bys to it
run the query

Some things I've considered:

Running raw SQL that I generate on the fly based on user input. Not ideal for obvious reasons.
A massive, ugly chain of if statements for every possible combination of filters/order_by's/directions
Creating a list of separate queries based on the filter inputs, run them all, somehow combine the results and prune duplicates if that isn't done already, then display those
Scrapping this entire project and starting from scratch

Any ideas/help would be appreciated. For context, I'm trying to build a blog site in Flask as a learning project.

Solution

Some chaining will work. I don't think it makes much sense sometimes but you just have to keep re-assigning the result.

import sqlalchemy as sa

query = sa.select(models.Post)
if userProvidedAuthor:
    query = query.where(models.Post.author == userProvidedAuthor)
#...

The docs demonstrate how repeated calls to where() produce an AND here: the-where-clause

Usually it is better to just build up the major things yourself like conditions into a list then put them in all at once.

Like (untested but should work):

import sqlalchemy as sa
where_args = []
if userProvidedAuthor:
    where_args.append(models.Post.author == userProvidedAuthor)
order_by_args = []
if userProvidedOrder == 'timestamp':
    order_by_args.append(models.Post.timestamp)
#...

query = sa.select(models.Post).where(*where_args).order_by(*order_by_args)

#...

The SQLAlchemy 2.0 docs have a lot of great examples. The creator and maintainers put a lot of effort into them. Maybe try going through the tutorials while simultaneously working on the test project.