python - sqlalchemy filter multiple columns

97
votes

How do I combine two columns and apply filter? For example, I want to search in both the "firstname" and "lastname" columns at the same time. Here is how I have been doing it if searching only one column:

query = meta.Session.query(User).filter(User.firstname.like(searchVar))
4
pythonsqldatabasesqlalchemy
I thought that this question matched the issue that I have, but the answers don't apply to my particular scenario. If firstname is "joe" and lastname is "smith", I'm looking for a filter statement that will match when the supplied searchVar is "joe smith". I.e., the fields need to be concatenated (with space added) before the test is made. Seems like a very real-world scenario. - Groovee60
@Groovee60 This is exactly what I am looking for. I would appreciate it if you'd be able to share your solution, if you found one. - Lilylakshi

4 Answers

109
votes

There are number of ways to do it:

Using filter() (and operator)

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

Using filter_by() (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 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)
    )
)
80
votes

You can simply call filter multiple times:

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

You can use SQLAlchemy's or_ function to search in more than one column (the underscore is necessary 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)))
2
votes

A generic piece of code that will work for multiple columns. This can also be used if there is a need to conditionally implement search functionality in the application.

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.