flake8 complains on boolean comparison "==" in filter clause

Question

I have a boolean field in the mysql db table.

# table model
class TestCase(Base):
    __tablename__ = 'test_cases'
    ...
    obsoleted = Column('obsoleted',  Boolean)

To get the count of all the non-obsoleted test cases, that can be done simply like this:

caseNum = session.query(TestCase).filter(TestCase.obsoleted == False).count()
print(caseNum)

That works fine, but the flake8 report the following warning:

E712: Comparison to False should be "if cond is False:" or "if not cond:"

Okay, I think that make sense. So change my code to this:

caseNum = session.query(TestCase).filter(TestCase.obsoleted is False).count()

or

caseNum = session.query(TestCase).filter(not TestCase.obsoleted).count()

But neither of them can work. The result is always 0. I think the filter clause doesn't support the operator "is" or "is not". Will someone can tell me how to handle this situation. I don't want to disable the flake.

Nil

PEP 8 specifically advises against "if cond is False". I'm surprised that the pep8 tool does the opposite. – Oft

Answer 1

That's because SQLAlchemy filters are one of the few places where == False actually makes sense. Everywhere else you should not use it.

Add a # noqa comment to the line and be done with it.

Or you can use sqlalchemy.sql.expression.false:

from sqlalchemy.sql.expression import false

TestCase.obsoleted == false()

where false() returns the right value for your session SQL dialect. There is a matching sqlalchemy.expression.true.

Regress

Answer 2

SQL Alchemy also has is_ and isnot functions you can use. An example would be

Model.filter(Model.deleted.is_(False))

More on those here

Expurgate

In Python is and == are different but I'm pretty sure the SQL being generated here will be the same. – Expurgate

is and == are different in SQLAlchemy because you cannot override the identity operator (is) in python. An expression like Model.column is False is always evaluated as False because the comparison always happens immediately in python and not the database. The result of comparing the identity of a column object and a boolean is always False. This inserts statements like WHERE FALSE or AND FALSE into your query, which in most cases will cause it to return 0 rows no matter what. – Countertype

Not working with Oracle – Troyes

Answer 3

I have a look what exact query is generated for using SQLAlchemy when == and is_ when the database dialect is Postgresql for boolean field:

• for == we get:

  1. field == False is converted to field = false
  2. field == True is converted to field = true
  3. field == None is converted to field IS NULL

• for is_() we get:

  1. field.is_(False) is converted to field IS false
  2. field.is_(True) is converted to field IS true
  3. field.is_(None) is converted to field IS NULL

NOTE: is_(not None) will be evaluated to is_(bool(not None) what gives is_(True) giving field = true so you rather go for isnot(None) producing field IS NOT NULL

Faline

And also note that when you uses is rather than = in PostgreSQL, you will never get NULL as an answer, even if an operand is NULL. = will return NULL if either operand is NULL. – Vasculum

Answer 4

Why don't you use .filter_by(field=True) / .filter_by(field=False) ?

Mockery

One thing to watch for if you've joined table filter_by can be ambiguous. – Margalit

Answer 5

caseNum = session.query(TestCase).filter(~TestCase.obsoleted).count()
print(caseNum)

works.try it in your sqlalchemy versions

Litt

Answer 6

@Jruv Use # noqa in front of statement, it'll ignore the warning.

Bathrobe

# noqa will disable flake8 / pep / other-linters for the entire string, so other linting-warnings will be ignored too. Other answers offer better solutions, for example, .is_-method. – Gobbledegook