How to build a SQLAlchemy query, so if None of the filter is given all rows of the table?

There are several variables.
a = 23
b = 45
c = 67

Do here such request:
query_result = User.query.filter( == a, == b, User.key == c).all()

Question: it is Necessary that in the absence of the variable a or when a = None the query was executed like this:
query_result = User.query.filter( == b, User.key == c).all()

That is, given a string with all the possible
Is it possible to dispense with design if ... elif ... else and several different queries?
July 9th 19 at 10:25
3 answers
July 9th 19 at 10:27
from sqlalchemy import or_

query_result = User.query.filter(or_(a == None, == a)
 or_(b == None, == b)
 or_(c == None, User.key == c))
Don't quite understand the logic. Can you please describe? - Lloyd commented on July 9th 19 at 10:30
: function or_ simulates the behavior of the OR operator in SQL. The first argument is one condition, the second argument is the second condition.
That is, for example, the code
FROM Users
WHERE user_id = 1 OR user_id = 2

In ORM will look like:
Users.query.filter(or_(User.user_id ==1, User.user_id ==2))

Now our case. We substitute in the function of the condition of the first == a and the condition of the second: a == None. The condition turns to True if the user id equals the value of a variable, or if variable a has the value None. You know? If a == None, this condition is always True, that is, it is as if ignored, exactly what you need:
User.query.filter(or_( == a, a == None) <...>

and then the same for the rest of the fields. - tanya commented on July 9th 19 at 10:33
: slightly corrected the answer - tanya commented on July 9th 19 at 10:36
Thank you! I understand your thinking. - Lloyd commented on July 9th 19 at 10:39
July 9th 19 at 10:29
If it is function code, then add the a option=None. Something like
def func(a=None):
I do not understand what does it. - Lloyd commented on July 9th 19 at 10:32
: the query
query_result = User.query.filter( == None, == b, User.key == c).all()
will work as needed? - tanya commented on July 9th 19 at 10:35
No. Will be an empty result. - tanya commented on July 9th 19 at 10:38
July 9th 19 at 10:31
Have done so:
1. Created the request object.
query_result = User.query
2. Using design if adding filters and overrides the request to apply a chain of filters.
if a is not None:
 query_result = query_result.filter( == a)
if b is not None:
 query_result = query_result.filter( == b)
if c is not None:
 query_result = query_result.filter(User.key == c)

3. Get data from the database.
query_result = query_result.all()

Need some constructive criticism of this approach.

Find more questions by tags Python