115

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.

Jruv
  • 1,438
  • 2
  • 9
  • 10
  • [PEP 8](http://www.python.org/dev/peps/pep-0008/#programming-recommendations) specifically advises *against* "if cond is False". I'm surprised that the [pep8 tool](http://pep8.readthedocs.org/en/latest/intro.html) does the opposite. – Janne Karila Sep 25 '13 at 06:59

6 Answers6

145

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.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
117

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

avoliva
  • 3,181
  • 5
  • 23
  • 37
  • 1
    In Python `is` and `==` are different but I'm pretty sure the SQL being generated here will be the same. – avoliva Sep 19 '16 at 18:00
  • 3
    `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. – Josh Jun 20 '17 at 14:36
  • Not working with Oracle – Alpensin Apr 06 '22 at 09:15
24

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

andilabs
  • 22,159
  • 14
  • 114
  • 151
  • 2
    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. – Jim Hunziker Jun 25 '20 at 13:56
3

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

Dominik Janků
  • 31
  • 1
1

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

user3016020
  • 827
  • 1
  • 6
  • 7
  • 5
    `# 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. – maxkoryukov Oct 07 '18 at 06:26
1
caseNum = session.query(TestCase).filter(~TestCase.obsoleted).count()
print(caseNum)

works.try it in your sqlalchemy versions

Tearf001
  • 95
  • 7