SQLAlchemy `order_by` gotcha

by Zack Marvel
in programming

tagged Python, SQLAlchemy, SQL

Today I encountered a surprising SQLAlchemy gotcha. In short, if you have a Query object and call its order_by method on the same column more than once, the first ORDER BY is not superseded. Here's an illustrative example. I'll include the boilerplate code (schema, etc.) at the end of the article.

In [1]: session.add(Person(age=19))
In [2]: session.add(Person(age=21))
In [3]: session.add(Person(age=25))

In [4]: session.query(Person).all()
2020-09-08 17:31:58,237 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2020-09-08 17:31:58,237 INFO sqlalchemy.engine.base.Engine SELECT person.id AS person_id, person.age AS person_age
FROM person
2020-09-08 17:31:58,237 INFO sqlalchemy.engine.base.Engine ()
Out[3]: [Person<age=19>, Person<age=21>, Person<age=25>]

In [5]: session.query(Person).order_by(asc('age')).all()
2020-09-08 17:32:11,742 INFO sqlalchemy.engine.base.Engine SELECT person.id AS person_id, person.age AS person_age
FROM person ORDER BY person.age ASC
2020-09-08 17:32:11,742 INFO sqlalchemy.engine.base.Engine ()
Out[4]: [Person<age=19>, Person<age=21>, Person<age=25>]

In [6]: session.query(Person).order_by(asc('age')).order_by(desc('age')).all()
2020-09-08 17:32:37,082 INFO sqlalchemy.engine.base.Engine SELECT person.id AS person_id, person.age AS person_age
FROM person ORDER BY person.age ASC, person.age DESC
2020-09-08 17:32:37,082 INFO sqlalchemy.engine.base.Engine ()
Out[5]: [Person<age=19>, Person<age=21>, Person<age=25>]

Can you see why this would be confusing? Even reading the generated SQL, I might have thought the result would be sorted twice, or that the first ORDER BY would be ignored.

So what's the solution? Turns out, it's easy:

In [7]: session.query(Person).order_by(asc('age')).order_by(None).order_by(desc('age')).all()
2020-09-08 17:35:39,602 INFO sqlalchemy.engine.base.Engine SELECT person.id AS person_id, person.age AS person_age
FROM person ORDER BY person.age DESC
2020-09-08 17:35:39,602 INFO sqlalchemy.engine.base.Engine ()
Out[7]: [Person<age=25>, Person<age=21>, Person<age=19>]

This is something to watch out for if you're mutating a Query object as I was!

Here's the supporting code:

from sqlalchemy import create_engine, Column, Integer, desc, asc
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker


engine = create_engine('sqlite:///:memory:', echo=True)

Base = declarative_base()


class Person(Base):
    __tablename__ = 'person'

    id = Column(Integer, primary_key=True)
    age = Column(Integer)

    def __repr__(self):
        return f'Person<age={self.age}>'


Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()