Chained Comparisons In Sqlalchemy

Python supports chained comparisons: 1 < 2 < 3 translates to (1 < 2) and (2 < 3). I am trying to make an SQL query using SQLAlchemy which looks like this: results = ses

Solution 1:

SQLAlchemy won't support Python's chained comparisons. Here is the official reason why from author Michael Bayer:

unfortunately this is likely impossible from a python perspective. The mechanism of "x < y < z" relies upon the return value of the two individual expressions. a SQLA expression such as "column < 5" returns a BinaryExpression object, which evaluates as True - therefore the second expression is never called and we are never given a chance to detect the chain of expressions. Furthermore, the chain of expressions would need to be detected and converted to BETWEEN, since SQL doesn't support the chained comparison operators. Not including the detection of chains->BETWEEN part, to make this work would require manipulation of the BinaryExpression object's __nonzero__() value based on the direction of the comparison operator, so as to force both comparisons. Adding a basic __nonzero__() to BinaryExpression that returns False illustrates that it's tolerated pretty poorly by the current codebase, and at the very least many dozens of "if x:" kinds of checks would need to be converted to "if x is None:", but there might be further issues that are more difficult to resolve. For the outside world it might wreak havoc. Given that the appropriate SQL operator here is BETWEEN which is easily accessible from the between operator, I don't think the level of bending over backwards and confusing people is worth it so this a "wontfix".

See details at: https://bitbucket.org/zzzeek/sqlalchemy/issues/1394/sql-expressions-dont-support-x-col-y

Solution 2:

The reason is that Python actually evaluates something akin to this:

_tmp = Couple.NumOfResults
(10 < _tmp and _tmp < 20)

The and operator is unsupported in SQLAlchemy (one should use and_ instead). And thus - chained comparisons are not allowed in SQLAlchemy.

In the original example, one should write this code instead:

results = session.query(Couple).filter(and_(10 < Couple.NumOfResults, 
                                            Couple.NumOfResults < 20)).all()