Tutorial :sqlalchemy filter multiple columns



Question:

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


Solution:1

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


Solution:2

You can simply call filter multiple times:

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


Solution:3

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

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

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

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »