python - Chained comparisons in SQLAlchemy

Question

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 = session.query(Couple).filter(10 < Couple.NumOfResults < 20).all()

The results I got were not as expected. I've turned the engine's echo=True keyword, and indeed - the generated SQL query only included one of the two comparisons.

I can't find any documentation that explicitly says this is forbidden. I assumed that if this type of expression is supported in Python, it should be supported in SQLAlchemy as well.

Why doesn't this work? I have one possible solution in mind (shared in answers), but will be glad to hear other opinions.

Answer 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