SQLAlchemy在使用SQL索引时的自定义排序算法

Question

在SQLAlchemy中，能否写自定义的排序函数并与索引一起使用呢？比如，SQLite允许在C语言层面指定排序函数，方法是使用sqlite3_create_collation()。

James Tauber提供了一些Unicode排序算法的实现，可以在这里找到。这个算法的一个例子是，它会把所有的"a"字母排在一起，无论它们有没有重音符号。

还有其他一些例子说明了这为什么可能有用，比如处理不同的字母顺序（非英语的语言）和排序数字（比如把10排在9后面，而不是按字符顺序排）。

在SQLAlchemy中可以做到这一点吗？如果不行，那pysqlite3或MySQLdb模块支持吗？或者其他任何Python支持的SQL数据库模块呢？

任何信息都将非常感谢。

Answer 1

我稍微修改了一下Denis Otkidach的回答，所以我把我的改动作为社区共享放在这里，以便其他人有兴趣时可以参考：

# -*- coding: utf-8 -*-
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import types
from pyuca import Collator

class MyUnicode(types.TypeDecorator):
    impl = types.Unicode
    def get_col_spec(self):
        # Return a new Unicode type sorted with 
        # the `mycollation` function
        return 'Unicode COLLATE mycollation'

# Create the collator (sorting) function/instance
collator = Collator('allkeys.txt')
def mycollation(value1, value2):
    if False:
        # Use pyuca for sorting
        return cmp(collator.sort_key(value1), 
                   collator.sort_key(value2))
    else:
        # Normalize to lowercased combining characters for sorting
        import unicodedata
        return cmp(unicodedata.normalize('NFD', unicode(value1)).lower(),
                   unicodedata.normalize('NFD', unicode(value2)).lower())

# Create a new metadata/base/table
metadata = MetaData()
Base = declarative_base(metadata=metadata)
class Item(Base):
    __tablename__ = 'CollatedTable'
    id = Column(Integer, primary_key=True)
    # (Note the `unique=True` in the next line so that an index 
    #  is created, therefore stored in collated order for faster SELECTs)
    value = Column(MyUnicode(), nullable=False, unique=True)

# Create a new database connection
engine = create_engine('sqlite://')
engine.echo = True # Print the SQL
engine.raw_connection().create_collation('mycollation', mycollation)
metadata.create_all(engine)
session = sessionmaker(engine)()

# Add some test data
for word in [u"ĉambr", u"ĉar", u"car'", u"carin'", u"ĉe", u"ĉef'",
             u"centjar'", u"centr'", u"cerb'", u"cert'", u"ĉes'", u"ceter'",

             u"zimble", u'bumble', 
             u'apple', u'ápple', u'ãpple',
             u'đjango', u'django']:
    item = Item(value=word)
    session.add(item)
session.commit()

for item in session.query(Item).order_by(Item.value): # collate(Item.value, 'mycollation')
    print item.value

Answer 2

下面是一个示例，展示了sqlite的unicode排序算法：

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from pyuca import Collator

metadata = MetaData()
Base = declarative_base(metadata=metadata)

class Item(Base):
    __tablename__ = 'Item'
    id = Column(Integer, primary_key=True)
    value = Column(String, nullable=False)

collator = Collator('allkeys.txt')

def collate_unicode(value1, value2):
    return cmp(collator.sort_key(value1), collator.sort_key(value2))

engine = create_engine('sqlite://')
engine.raw_connection().create_collation('unicode', collate_unicode)
metadata.create_all(engine)
session = sessionmaker(engine)()

for word in [u"ĉambr", u"ĉar", u"car'", u"carin'", u"ĉe", u"ĉef'",
             u"centjar'", u"centr'", u"cerb'", u"cert'", u"ĉes'", u"ceter'"]:
    item = Item(value=word)
    session.add(item)
    session.commit()

for item in session.query(Item).order_by(collate(Item.value, 'unicode')):
    print item.value