python - 指定枚举字段类型的排序顺序

Question

我有一个使用 Enum 字段类型的模型:

from sqlalchemy import Column, Enum, Integer, String

class Task(Base):
    __tablename__ = 'tasks'

    id = Column(Integer, primary_key=True)
    summary = Column(String)
    priority = Column(Enum('low', 'medium', 'high'))

如果我按优先级(枚举)对记录进行排序，它会按字母顺序排序:

high
low
medium

如何指定该字段的排序顺序？

Answer 1

可能存在另一种方法，但我所知道的最简单(并且与数据库无关)的解决方案是使用 case statement在 order by 子句中。

首先，通过获取用于创建列的Enum 的元组，为您的案例语句设置whens 字典:

enums = Task.priority.type.enums  # finds the Enum tuple (with the same order you originally gave it)
whens = {priority: index for index, priority in enumerate(enums)}  # dynamically creates whens dict based on the order of the Enum tuple

或者，如果你必须，硬编码你的 whens 字典(绝对不理想，因为你现在必须在两个地方更改代码):

whens = {'low': 0, 'medium': 1, 'high': 2}

其次，在 case 语句中设置排序逻辑:

sort_logic = case(value=Task.priority, whens=whens).label("priority")

第三，根据您的案例语句运行您的查询和订单:

q = session.query(Task).order_by(sort_logic)  # see the sql at the end of this answer

您现在有一个按原始 Enum 元组排序的查询:

>>> for item in q:
        print item.id, item.priority
#  now in the order you were looking for
4 low
7 low
3 medium
8 medium
2 high
6 high

仅供引用，上述查询生成以下 SQL:

SELECT        tasks.id AS tasks_id,
              tasks.summary AS tasks_summary,
              tasks.priority AS tasks_priority 
FROM          tasks
ORDER BY CASE tasks.priority
             WHEN :param_1 THEN :param_2
             WHEN :param_3 THEN :param_4
             WHEN :param_5 THEN :param_6
         END