How to specify multiple "where" or "order_by" conditions?

It's not straight forward to find information on this so wondering if there are some docs I can look at but basically I want to achieve passing multiple conditions to either .where() or .order_by() that is safe from SQL injection.

Here's how I am currently doing this: Two tables: Archive and Backup, and I am trying to filter by archive.city, archive.zip, and backup.serial and then I am ordering by all of those fields. The values are coming from the user via URL parameters so I need to make sure these are safe from SQL injection and sanitized.

filters = []
sorts = []

    if 'city' in query:
            city = query['city']
            filters.append(text(f'archive.city = {city}')) 
            sorts.append(text(f'archive.city = {city}')) 

    if 'zip' in query:
            zip = query['zip']
            filters.append(text(f'archive.zip > {zip}'))    
            sorts.append(text(f'archive.zip DESC'))  

    if 'serial' in query:
            serial = query['serial']
            filters.append(text(f'backup.serial IN {serial}'))    
            sorts.append(text(f'backup.serial ASC')) 

    with Session(engine) as session:
        results = session.exec(select(Archive, Backup)
                               .join(Backup)
                               .where(and_(*filters))
                               .order_by(*sorts).all()

as I understand the text() is not safe from sql injection, so how do I transform this so that it does what I want and is safe from sql injection?

Solution

You can invoke .where() and .order_by() on a select() multiple times and SQLAlchemy will logically "and" them for you:

qry = select(Task)
qry = qry.where(Task.description == "foo")
qry = qry.where(Task.priority < 2)
qry = qry.order_by(Task.priority)
qry = qry.order_by(Task.description)

print(qry)
"""
SELECT task.id, task.description, task.priority 
FROM task 
WHERE task.description = :description_1 AND task.priority < :priority_1 
ORDER BY task.priority, task.description
"""