python - 如何按字母在前的字符串列排序？

Question

比如说，我想按名称对记录进行排序，而名称也可以从数字字母开始。

如果我这样做

Something.query.order_by(Something.name)

我有以下顺序:

• 连续第一名
• 33颗 dentry
• 测试版
• 热情

如何将查询更改为将数字放在字母后面:

• 测试版
• 热情
• 连续第一名
• 33颗 dentry

Answer 1

添加一个初始排序子句以将前导数字与前导非数字区分开来似乎有效:

engine = sa.create_engine(connection_url)
metadata = sa.MetaData()

mytable = sa.Table('mytable', metadata, autoload=True, autoload_with=engine)

# 1. default sort
stmt = sa.sql.select([mytable.c.thing])\
    .select_from(mytable)\
    .order_by(mytable.c.thing)
with engine.begin() as conn:
    print(conn.execute(stmt).fetchall())
    # [('1st in a row',), ('99 beers',), ('Beta version',), ('Zesty',)]

# 2. non-numeric first
sort_boundary = '9' * mytable.c.thing.type.length
print(sort_boundary)
# 99999999999999999999999999999999999999999999999999
#
stmt = sa.sql.select([mytable.c.thing])\
    .select_from(mytable)\
    .order_by(mytable.c.thing <= sort_boundary, mytable.c.thing)
with engine.begin() as conn:
    print(conn.execute(stmt).fetchall())
    # [('Beta version',), ('Zesty',), ('1st in a row',), ('99 beers',)]