Related comparisons in SQLAlchemy

Question

Python supports coordinated comparisons : 1 < 2 < 3 translates to (1 < 2) and (2 < 3) .

I am trying to make a SQL query using SQLAlchemy, which looks like this:

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

The results obtained by me were not so expected. I turned on the echo=True keyword for the engine, and indeed - the generated SQL query included only one of two comparisons.

I cannot find documentation that explicitly states that this is prohibited. I suggested that if this type of expression is supported in Python, it should also be supported in SQLAlchemy.

Why is this not working? I have one possible solution (common in the answers), but we will be glad to hear other opinions.

Answer 1

This is probably the reason Python really evaluates this expression:

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

The and operator is not supported in SQLAlchemy (use and_ ). And thus, SQLAlchemy does not allow string matching.

In the original example, you need to write this code instead:

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

Answer 2

SQLAlchemy does not support Python mappings. Here is the official reason why author Michael Bayer:

Unfortunately, this is hardly possible from a python point of view. The x <y <z mechanism is based on the return value of two separate expressions. an SQLA expression such as "column <5" returns a BinaryExpression object that evaluates to True, so the second expression is never called, and we are never given the opportunity to detect a chain of expressions. In addition, the expression chain must be detected and converted to BETWEEN, because SQL does not support chaining comparison operators. Apart from detecting the chain-> BETWEEN part, to perform this work, you will need to manipulate the value of the BinaryExpression __nonzero__() object based on the direction of the comparison operator to force both comparisons. Adding the base expression __nonzero__() to BinaryExpression, which returns False, shows that it is rather poorly tolerated by the current code base, and at least many dozens of types of “if x:” should be converted to “if x is None”: " , but there may be additional problems that are more difficult to solve. For the outside world, this can be detrimental. Given that the corresponding SQL statement is BETWEEN, which is easily accessible due to the statement, I don’t think the level of bending backwards and entangling people is worth so that is "wontfix".

For more details see: https://bitbucket.org/zzzeek/sqlalchemy/issues/1394/sql-expressions-dont-support-x-col-y