Using sqlalchemy, you may filter several columns.

Problem

What is the best way to merge two columns and apply a filter? I want to search both the “firstname” and “lastname” columns at the same time, for example. If you only want to search one column, here’s how I’ve been doing it:

query = meta.Session.query(User).filter(User.firstname.like(searchVar))

Asked by teggy

Solution #1

It can be done in a variety of ways:

Using the filter() method (and operator)

query = meta.Session.query(User).filter(
    User.firstname.like(search_var1),
    User.lastname.like(search_var2)
    )

Using the filter by() method (and operator)

query = meta.Session.query(User).filter_by(
    firstname.like(search_var1),
    lastname.like(search_var2)
    )

Chaining filter() or filter_by() (and operator)

query = meta.Session.query(User).\
    filter_by(firstname.like(search_var1)).\
    filter_by(lastname.like(search_var2))

Using the functions or_(), and_(), and not_() ()

from sqlalchemy import and_, or_, not_

query = meta.Session.query(User).filter(
    and_(
        User.firstname.like(search_var1),
        User.lastname.like(search_var2)
    )
)

Answered by Vlad Bezden

Solution #2

Filter can be called numerous times:

query = meta.Session.query(User).filter(User.firstname.like(searchVar1)). \
                                 filter(User.lastname.like(searchVar2))

Answered by David Johnstone

Solution #3

The or_ function in SQLAlchemy can be used to search over many columns (the underscore is required to distinguish it from Python’s own or).

Here’s an example:

from sqlalchemy import or_
query = meta.Session.query(User).filter(or_(User.firstname.like(searchVar),
                                            User.lastname.like(searchVar)))

Answered by gclj5

Solution #4

A piece of code that can be used on several columns. This can also be used if the application needs to integrate search functionality on a conditional basis.

search_key = "abc"
search_args = [col.ilike('%%%s%%' % search_key) for col in ['col1', 'col2', 'col3']]
query = Query(table).filter(or_(*search_args))
session.execute(query).fetchall()

Note: the %% are important to skip % formatting the query.

Answered by Azharullah Shariff

Post is based on https://stackoverflow.com/questions/3332991/sqlalchemy-filter-multiple-columns