Dynamic ordering by sqlAlchemy 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'm trying to accomplish is to arrange desc results in a column and only in the result set.

I tried using the following:

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

I even tried creating a Column object and arranging it like this:

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

All to no avail. Results are returned as if there was no order_by clause.

Any help would be appreciated.

Answer 1

order_by() method in SQLAlchemy does not change the query to which it is applied, but returns a new query. Columns are also accessed by name, not index. Use something like the following:

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

Answer 2

 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 response to dynamically sorting / arranging SQL results.