Menu
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)) 
634
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 ). You can use | operator instead of or_ , like this - (User. firstname. like(searchVar)) | (User.
The select() method of table object enables us to construct SELECT expression. The resultant variable is an equivalent of cursor in DBAPI. We can now fetch records using fetchone() method. Here, we have to note that select object can also be obtained by select() function in sqlalchemy.
Normally, IS NOT is generated automatically when comparing to a value of None , which resolves to NULL . However, explicit usage of IS NOT may be desirable if comparing to boolean values on certain platforms. The method was formerly named isnot() and was renamed in SQLAlchemy 1.4.
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) ) ) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
