Incorrect SQL Alchemy Sort Order

Question

Hi, I am trying to achieve ascending sort order for certain columns in sqlite database using sql alchemy, im problem is that the column I want to sort has upper and lower case data and thus sort order don't work right.

Then I found out about func.lower and tried to include this in the query, but it either errors, or just doesn’t work, can someone give me a working example of how to do case insensitive taking into account the increasing sort order using sql alchemy.

below is what I still (gives an error): -

session.query(ResultsDBHistory).order_by(func.lower(asc(history_sort_order_column))).all() 

python 2.6.6 sql alchemy 0.7.10

Answer 1

You need to reverse the order of your functions:

 session.query(ResultsDBHistory).order_by(asc(func.lower(history_sort_order_column))).all() 

so below and then declare an ascending order.

Alternatively, change the mapping to NOCASE :

 from sqlalchemy.sql import collate session.query(ResultsDBHistory).order_by(asc(collate(history_sort_order_column, 'NOCASE'))).all() 

which is perhaps the best idea.

I don’t think that ASC is required, as a result of which this simplifies the code:

 from sqlalchemy.sql import collate session.query(ResultsDBHistory).order_by(collate(history_sort_order_column, 'NOCASE')).all() 

Answer 2

Have you thought about sorting after the request.

 res = session.query(ResultsDBHistory).all() res.sort() #sort as the way you like