5

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

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
user956424
  • 1,611
  • 2
  • 37
  • 67

2 Answers2

10

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
Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
5

According to the documentation, the way you should handle this is by using the true() or false() constants that you can import from SqlAlchemy. It would look like this:

from sqlalchemy import false

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

Hope this helps!

Rohan Varma
  • 1,145
  • 6
  • 11
  • I am specifically talking of sqlalchemy Core 1.0.8, what you have answered is for orm – user956424 Jan 16 '18 at 05:34
  • Ah, sorry i missed that. It looks like instead of comparing to the boolean False, try comparing it to the True constant created by SqlAlchemy which you can read about here: http://docs.sqlalchemy.org/en/rel_1_0/core/sqlelement.html?highlight=true#sqlalchemy.sql.expression.true (I edited my answer to reflect this answer) – Rohan Varma Jan 16 '18 at 05:45
  • can you set the expression in the above code and show how exactly it is incorporated? – user956424 Jan 16 '18 at 06:23
  • 1
    For sure. I used the false() method in the answer above (edited) check it out and try it to see if it works – Rohan Varma Jan 16 '18 at 06:34