SQL alchemy case insensitive sort order

Question

Hi Im trying to achieve a ascending sort order for particular columns in a sqlite database using sql alchemy, the issue im having is that the column I want to sort on has upper and lower case data and thus the sort order doesn't work correctly.

I then found out about func.lower and tried to incorporate this into the query but it either errors or just doesn't work, can somebody give me a working example of how to do a case insensitive ascending sort order using sql alchemy.

below is what I have so far (throws 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 ordering of your functions:

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

so lower first, then declare the ascending order.

Alternatively, change the collation to NOCASE:

from sqlalchemy.sql import collate

session.query(ResultsDBHistory).order_by(asc(collate(history_sort_order_column, 'NOCASE'))).all()

which arguably is a better idea anyway.

I don't think the ASC is required, leaving that off simplifies your code somewhat:

from sqlalchemy.sql import collate

session.query(ResultsDBHistory).order_by(collate(history_sort_order_column, 'NOCASE')).all()

Answer 2

Michael Bayer (author of SQLAlchemy) addressed this topic in a post on the sqlalchemy mailing list:

several ways to approach that, without it being built in as an expression. as a string:

order_by=["name COLLATE NOCASE"]

or just using the lower() function (to me this would be more obvious)

order_by=[func.lower(table.c.name)]

or you could use sql's _CompoundClause:

from sqlalchemy.sql import _CompoundClause

order_by = [_CompoundClause(None, table.c.name, "COLLATE NOCASE")]

Id go with func.lower() probably...

There is also an example in the documentation for desc(column):

from sqlalchemy import desc

stmt = select(users_table).order_by(desc(users_table.c.name))