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

Martijn Pieters · Accepted Answer · 2013-07-10T11:12:39.030

24

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()

edited Jul 10 '13 at 11:12

answered Jul 10 '13 at 09:50

Martijn Pieters

1,048,767
296
4,058
3,343

hmm i cant get this to work, as soon as i use collate or func.lower it stops the sort order completely and just defaults to sorting by id. the only way i can get this to work is by setting the collation for the column i have an issue with to NOCASE directly on the database and then ignore trying to do a case insensitive query, any thoughts on why this might not be working? – binhex Jul 10 '13 at 11:10
Probably you need to create an index for the collation order: `CREATE INDEX ResultsDBHistory ON tablename (history_sort_order_column COLLATE NOCASE)`. – Martijn Pieters Jul 10 '13 at 11:14
No, the index will help speed things up but the collation should still work, see http://www.sqlite.org/datatype3.html#collation – Martijn Pieters Jul 10 '13 at 11:18
ive now created an index for the table i wanted and the column that ive been having issues with and im still getting the same result, it stops sorting as soon as i specify nocase or func.lower – binhex Jul 10 '13 at 11:28
Try to recreate the situation in the sqlite command line *only*. Does it work there? Then perhaps we have a SQLAlchemy or python `sqlite3` version issue here. But if it *doesn't* work in the command line either.. – Martijn Pieters Jul 10 '13 at 11:29
You can retrieve the SQL that SQLAlchemy generates by printing the `str()` result: `print str(session.query(ResultsDBHistory).order_by(collate(history_sort_order_column, 'NOCASE')))` (note that you do *not* call `.all()` on it). – Martijn Pieters Jul 10 '13 at 11:47
just for anybody else who reads this, it helped to have "echo=True" for create_engine in sql alchemy to help diagnose the issue. – binhex Jul 16 '13 at 13:17
Stick to `func.lower()`. The NOCASE collation doesn't appear to be portable. On PostgresQL, I get: `collation "NOCASE" for encoding "UTF8" does not exist`. – Sam Bull Jul 22 '22 at 20:13
@SamBull the question is tagged with `sqlite`; I used a database specific colation in my answer. See [this SO post for PG options](https://stackoverflow.com/q/18807276/100297). – Martijn Pieters Aug 13 '22 at 11:15
Fair enough, but I wasn't looking for a Postgres answer, I was looking for something portable. Just highlighting this to anybody else who wants to keep their code portable (which I'd imagine is a decent amount of sqlalchemy users, given that is half the reason to use sqlalchemy in the first place). – Sam Bull Aug 13 '22 at 15:15
@SamBull SQLA is powerful enough that we can create hooks that’ll switch out collations depending on the target database, but collations themselves are just way too tied to a specific database environment for SQLA, or the limited scope of a SO answer, to make a generic solution feasible. Sorry. – Martijn Pieters Aug 13 '22 at 16:35
Yeah, but `func.lower()` is fine (it's also the method that the author of SQLA says he would choose, linked in the below answer). – Sam Bull Aug 13 '22 at 16:51

score 1 · Answer 2 · answered Mar 17 '22 at 20:11

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))

score 0 · Answer 3 · answered Jul 10 '13 at 09:42

0

Have you thought of doing a sorting after the query.

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

answered Jul 10 '13 at 09:42

Friedmannn

138
1
1
9

SQL alchemy case insensitive sort order

3 Answers3

Linked