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(User.id == a, User.group == 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(User.group == b, User.key == c).all()

That is, given a string with all the possible User.id.
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
Solution
from sqlalchemy import or_

query_result = User.query.filter(or_(a == None, User.id == a)
 or_(b == None, User.group == 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
SELECT *
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 User.id == 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_(User.id == 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(User.id == None, User.group == 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(User.id == a)
if b is not None:
 query_result = query_result.filter(User.group == 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