The default sorting criteria of sqlalchemy?

Question

Now I sort the data in the database by its attribute 1. If there is a tie of different items with same value of attribute 1, the data seems to be sorted by its id. However, I would like to break the tie by sorting by desc(id). How could I change the default sorting criteria of the database if there is a tie?

Thanks!

Answer 1

Update

Since version 1.1 the order_by parameter in the mapper configuration has been deprecated. Instead Query.order_by must be used.

db.query(User).order_by(User.fullname)

# or in desc order
db.query(User).order_by(User.fullname.desc())

I left here the original answer for historial purposes:

This is possible by means of the mapper configuration.

If you have a user table and want to retrieve the records always ordered by fullname something like this should works:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    password = Column(String)

    __mapper_args__ = {
        "order_by": fullname,
    }
    
    def __repr__(self):
       return f"<User(id='{self.id}', name='{self.name}', fullname='{self.fullname}')>"

Default order_by is ascending, if you want to reverse the order this can be used:

__mapper_args__ = {
    "order_by": fullname.desc(),
}

Answer 2

The order is entirely determined by the database, not SQLAlchemy. With plain SQL you just add additional ORDER BY clauses, in SQLAlchemy ORM you do the same by chaining order_by methods. For example:

for eq in session.query(Equipment).order_by(Equipment.model_id).order_by(Equipment.name).all():
    print (eq)

Whichever is left-most is the primary sort.