Dynamic Order By Clause using sqlAlchemy's SQL Expression Language

Question

I am using sqlAlchemy SQL Expression Language as follows:

col_name = 'variable_col'
metadata = MetaData(db)
myTable = Table('table_name', metadata,
              Column(col_name, Integer))
s = myTable.select()
for rec in conn.execute(s):

What I am trying to accomplish is to order the results desc by the one and only column in the results set.

I tried using the following:

s.order_by(desc(myTable.c))
s.order_by(desc(myTable.c[0]))

I even tried to create a Column object and order by that:

temp_col = Column(col_name, Integer)
s.order_by(desc(temp_col))

All to no avail. The results are returned as if there is no order_by clause.

Any help would be appreciated.

Answer 1

from sqlalchemy import create_engine, desc, asc
from sqlalchemy.orm import sessionmaker, Query
# setup the session here...
sort_column = "tables_column_name"
sort_dir = "desc"  # or "asc"
sort = asc(sort_column) if sort_dir == "desc" else desc(sort_column)
query = Query([...]).filter(...)
query = query.order_by(sort)
results = session.execute(query).fetchall()

I think this is a more complete answer to dynamically sorting/ordering SQL results.

Answer 2

order_by() method in SQLAlchemy doesn't modify query it's applied to, but returns a new query. Also columns are accessed by name, not index. Use something like the following:

s = s.order_by(myTable.c[col_name].desc())