CODEWITHSUNDEEP
pythonsqlalchemy
Niel
Niel

Reputation: 2006

SQLAlchemy: how can I order a table by a column permanently?

I'm not sure if this has been answered before, I didn't get anything on a quick search.

My table is built in a random order, but thereafter it is modified very rarely. I do frequent selects from the table and in each select I need to order the query by the same column. Now is there a way to sort a table permanently by a column so that it does not need to be done again for each select?

Upvotes: 2

Views: 766

Answers (2)

knitti
knitti

Reputation: 7033

You can have only one place where you define it, and re-use that for every query:

def base_query(session, what_for):
    return session.query(what_for).order_by(what_for.rank_or_whatever)

Expand that as needed, then for all but very complex queries you can use that like so:

some_query = base_query(session(), Employee).filter(Employee.feet > 3)

The resulting query will be ordered by Employee.rank_or_whatever. If you are always querying for the same, You won't habve to use it as argument, of course.

EDIT: If you could somehow define a "permanent" order on your table which is observed by the engine without being issued an ORDER BY I'd think this would be an implementation feature specific to which RDBMS you use, and just convenience. Internally it makes for a DBMS no sense to being coerced how to store the data, since retrieving this data in a specific order is easily and efficiently accomplished by using an INDEX - forcing a specific order would probably decrease overall performance.

Upvotes: 1

Tasos Vogiatzoglou
Tasos Vogiatzoglou

Reputation: 2453

You can add an index sorted by the column you want. The data will be presorted according to that index.

Upvotes: 3

Related Questions