Sqlalchemy: Unexpected Results When Using 'And' and 'Or'

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

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')

SQLAlchemy returns unexpected results with limit/offset

That's just how SQL works. This article about the order of operations in an SQL statement might be of interest. You could offset and limit in a subquery and join against that. Query.from_self() is handy in this particular situation, especially since it does automatic aliasing:

result = session.query(A)\
    .order_by(A.id)\
    .offset(0).limit(100)\
    .from_self()\
    .outerjoin(B)\
    .outerjoin(C)\
    .all()

type command in python gives unexpected results

Your s is actually an instance of a subclass of sqlalchemy.orm.session.Session. The subclass is confusingly also named sqlalchemy.orm.session.Session.

Looking at the SQLAlchemy source, it seems likely that this object was produced by a sqlalchemy.orm.session.sessionmaker, which creates a subclass of Session for reasons I'm not entirely clear on. I don't know if this behavior is documented; I didn't find any mention of it in the docs on a quick look.

SqlAlchemy Case with multiple conditions

You need to use sqlalchemy.and_ instead of the and operator:

and_(Jobs.interview_type == 'PHONE_SCREEN',
     Jobs.interview_type == 'INCLINED')

---

Related Topics