SQL炼金术不区分大小写的排序顺序 [英] SQL alchemy case insensitive sort order
问题描述
我正在尝试使用 sql alchemy 对 sqlite 数据库中的特定列实现升序排序,我遇到的问题是我要排序的列具有大写和小写数据,因此排序顺序没有正常工作.
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.
然后我发现了 func.lower 并尝试将其合并到查询中,但它要么出错要么就是不起作用,有人可以给我一个工作示例,说明如何使用 sql alchemy 进行不区分大小写的升序排序.
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()
蟒蛇 2.6.6sql 炼金术 0.7.10
python 2.6.6 sql alchemy 0.7.10
推荐答案
您需要反转函数的顺序:
session.query(ResultsDBHistory).order_by(asc(func.lower(history_sort_order_column))).all()
所以先,然后声明升序.
或者,将排序规则更改为 NOCASE
:
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.
我不认为ASC
是必需的,不使用它会在一定程度上简化您的代码:
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()
这篇关于SQL炼金术不区分大小写的排序顺序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!