时间戳

首页 > 解决方案 > sqlalchemy 按 mysql python 排序

python - sqlalchemy 按 mysql python 排序

问题描述

我正在尝试运行以下查询：

SELECT order, user, email, date
RANK() OVER (PARTITION BY order ORDER BY date DESC) as ranked
FROM orders

蟒蛇代码：

engine.execute(table('orders'), func.rank().over(partition_by='order', order_by='date').desc().label('ranked'))

错误：

sqlalchemy.exc.ObjectNotExecutableError: Not an executable object: <sqlalchemy.sql.selectable.TableClause at 0x2c528354340; tracking>

标签： pythonsqlalchemyflask-sqlalchemy

解决方案

from sqlalchemy import select, func
from sqlalchemy.sql import table, literal_column, column


query = select(
    [
        column('order'),
        # ...other columns. or you can select all: column('*')
        literal_column(
            str(func.rank().over(partition_by='order', order_by='date DESC'))
        ).label('ranked'),
        # or:
        # literal_column('RANK() OVER (PARTITION BY order ORDER BY date DESC)').label('ranked')
    ]
).select_from(table('orders'))

print(query)

推荐阅读