SQLAlchemy passing null to or

Question

In SQL if I provide a v_category="fiction" then it would return all fiction books, if we provide v_category=null then it would provide books from all categories:

select * from book where category = :v_category or :v_category is null

How do I do this in SQLAlchemy:

books = Book.query.filter(or_(category = v_category, v_category is None).all()

However since or_ only accepts arguments and not conditions, it throws an error:

".filter(or_(workscope = v_workscope, v_workscope is None))                                        ^
SyntaxError: positional argument follows keyword argument"

[`column == None`](https://stackoverflow.com/a/5632224/6560549) or [`column.is_(None)`](https://stackoverflow.com/a/44679356/6560549). Linters prefer the latter. — SuperShoot, Oct 03 '19 at 22:15

score 1 · Answer 1 · answered Oct 04 '19 at 04:58

I think if your v_category is null, you should not filter by category.is_(None). By doing so, you would be return all the books with Category as NULL. As per your question, if v_category is None, you want to return all the books of all the categories.

You can try the following code:

def get_book_by_category(v_category):
    query = Book.query
    if v_category:
         query = query.filter(category == v_category)
    return query

SQLAlchemy passing null to or

1 Answers1