SQLAlchemy: unexpected results when using `and` and `or`

by

The problem is this:

News.label == None and f(News.title) == 'good'
#                  ^^^ here

Python does not allow overriding the behaviour of boolean operations and and or. You can influence them to some extent with __bool__ in Python 3 and __nonzero__ in Python 2, but all that does is that it defines the truth value of your object.

If the objects in question had not implemented __bool__ and thrown the error, or the implementation had not thrown, you would’ve gotten possibly rather cryptic errors due to the short-circuiting nature of and and or:

In [19]: (News.label == 'asdf') and True
Out[19]: <sqlalchemy.sql.elements.BinaryExpression object at 0x7f62c416fa58>

In [24]: (News.label == 'asdf') or True
Out[24]: True

because

In [26]: bool(News.label == 'asdf')
Out[26]: False

This could and would lead to hair pulling in the form of incorrect SQL expressions:

In [28]: print(News.label == 'asdf' or News.author == 'NOT WHAT YOU EXPECTED')
news.author = :author_1

To produce boolean SQL expressions either use the and_(), or_(), and not_() sql expression functions, or the binary &, |, and ~ operator overloads:

# Parentheses required due to operator precedence
filter((News.label == None) & (f(News.title) == 'good'))

or

filter(and_(News.label == None, f(News.title) == 'good'))

or pass multiple criterion to a call to Query.filter():

filter(News.label == None, f(News.title) == 'good')

or combine multiple calls to filter():

filter(News.label == None).filter(f(News.title) == 'good')

More Related Contents:

  1. How to serialize SqlAlchemy result to JSON?
  2. Parsing boolean values with argparse
  3. SQLAlchemy: print the actual query
  4. SQLAlchemy create_all() does not create tables
  5. Comparing boolean and int using isinstance
  6. Python’s in (__contains__) operator returns a bool whose value is neither True nor False
  7. When do I need to use sqlalchemy back_populates?
  8. Python call sql-server stored procedure with table valued parameter
  9. Generate sql with subquery as a column in select statement using SQLAlchemy
  10. Bulk insert with SQLAlchemy ORM
  11. Can SQLAlchemy be used with Google Cloud SQL?
  12. How can I verify Column data types in the SQLAlchemy ORM?
  13. overriding bool() for custom class [duplicate]
  14. Boolean value of objects in Python
  15. Complex foreign key constraint in SQLAlchemy
  16. Connection problems with SQLAlchemy and multiple processes
  17. Python: False vs 0
  18. Why does “a == x or y or z” always evaluate to True? How can I compare “a” to all of those?
  19. How to upsert pandas DataFrame to Microsoft SQL Server table?
  20. SQLAlchemy: engine, connection and session difference
  21. Relations on composite keys using sqlalchemy
  22. flake8 complains on boolean comparison “==” in filter clause
  23. SQLAlchemy – Mapping self-referential relationship as one to many (declarative form)
  24. flask-sqlalchemy or sqlalchemy
  25. How do I use a Boolean in Python?
  26. How to set connection timeout in SQLAlchemy
  27. Reverse engineer SQLAlchemy declarative class definition from existing MySQL database?
  28. SQLAlchemy classes across files
  29. How can I achieve a self-referencing many-to-many relationship on the SQLAlchemy ORM back referencing to the same attribute?
  30. flask sqlalchemy query with keyword as variable

Leave a Comment