python - SQLAlchemy Core select where condition contains boolean expression `is False`

Question

How to use SQLAlchemy expression language to select columns with where condition to check boolean expression. example:

select([table]).
    where(and_(table.c.col1 == 'abc',
               table.c.is_num is False 
    ))

This doesn't give syntax error, but evaluates the condition wrong. I cannot use == False which gives error. SQLAlchemy Core v.1.0.8

Answer 1

The identity comparison operator is cannot be overloaded in Python, so

table.c.is_num is False

compares the identities of the Column object and False, and since they're clearly not the same object, evaluates to False. By

I cannot use == False which gives error

you probably mean that some Python linter adhering to PEP-8 gives you a warning. Checking equality against True or False is still valid Python, though unpythonic in the general sense – but it does make sense in SQLAlchemy filters and it is used in the docs. For example:

In [5]: t.c.bar == False
Out[5]: <sqlalchemy.sql.elements.BinaryExpression object at 0x7fdc355a1da0>

In [6]: print(_)
foo.bar = false

But: instead of comparing a boolean to a boolean you could use the value itself:

select([table]).
    where(and_(table.c.col1 == 'abc',
               ~table.c.is_num
    ))

which would translate to (approximately):

SELECT ... FROM table WHERE col1 = 'abc' AND NOT is_num

since SQLAlchemy ColumnOperators overload the __invert__ to not_(). Some backends may not support a boolean type, but SQLAlchemy handles the conversion:

In [6]: print((~t.c.bar).compile(dialect=sqlite.dialect()))
foo.bar = 0