Sqlalchemy how to filter column which contains both Null and integer values

Question

I have table with a nullable column which contains both types of values Null (as default) and integer:

class Node(db.Model):     __tablename__ = "node"     maintenance = db.Column(db.Integer, nullable=True) 

The request is like this one:

maintenance = 1 node_list = Node.query.filter(Node.maintenance != maintenance).all() 

I need to select all cells which contains Null or 0 values.

Thank you in advance!

Answer 1

I need to select all cells which contains 'Null' or '0' values.

Using | as the logical OR

Node.query.filter((Node.maintenance == None) | (Node.maintenance == 0)) 

Using is_(None)

Or, to avoid using == None (because of linters)

Node.query.filter((Node.maintenance.is_(None)) | (Node.maintenance == 0)) 

Using or_

Or, this form

from sqlalchemy import or_ Node.query.filter(or_(Node.maintenance == None, Node.maintenance == 0)) 

Using in_

If are wondering if you can query using something akin to the in operator in SQL and Python, you're right you can do it in SQLAlchemy too, but unfortunately it doesn't work for None/NULL values, but to illustrate we can see that

Node.query.filter(Node.maintenance.in_([0, 1])) 

is equivalent to

Node.query.filter((Node.maintenance == 0) | (Node.maintenance == 1))