SQLAlchemy多列元组比较分页查询SQLite下第二条件失效问题

问题根因 #

SQLite 3.15.0及以上版本本身原生支持多列元组（行值）比较语法，你遇到的问题是SQLAlchemy 1.x版本的SQLite方言对隐式元组比较的解析存在缺陷：你写的多列比较语句会被错误截断，仅保留第一个字段的比较逻辑，第二个item_id的判断条件直接被丢弃。

解决方案 #

这里提供两种可行的实现方式，均可以满足shipment_time相同时用item_id作为决胜字段的需求：

方案1：手动展开比较逻辑（全版本兼容）

多列元组(a,b) < (c,d)的语义等价于a < c OR (a = c AND b < d)，你可以直接把条件显式写出来，完全避开ORM的解析问题：

from sqlalchemy import or_, and_

def get_paginated_items(
        self,
        limit: int,
        last_shipment_time: int = MAX_INT,
        last_item_id: int = MAX_INT,
):
    s = (
        select([self.items])
        .where(
            or_(
                self.items.c.shipment_time < last_shipment_time,
                and_(
                    self.items.c.shipment_time == last_shipment_time,
                    self.items.c.item_id < last_item_id
                )
            )
        )
        .order_by(
            self.items.c.shipment_time.desc(),
            self.items.c.item_id.desc(),
        )
        .limit(limit)
    )
    with self.get_connection() as connection:
        result = connection.execute(s)
        return result.fetchall()

方案2：使用tuple_显式构造行值（SQLAlchemy 1.4+支持）

如果你的SQLAlchemy版本在1.4以上，可以用内置的tuple_构造器显式声明多列比较，避免解析错误：

from sqlalchemy import tuple_

def get_paginated_items(
        self,
        limit: int,
        last_shipment_time: int = MAX_INT,
        last_item_id: int = MAX_INT,
):
    s = (
        select([self.items])
        .where(
            tuple_(
                self.items.c.shipment_time,
                self.items.c.item_id,
            ) < tuple_(
                last_shipment_time,
                last_item_id,
            )
        )
        .order_by(
            self.items.c.shipment_time.desc(),
            self.items.c.item_id.desc(),
        )
        .limit(limit)
    )
    with self.get_connection() as connection:
        result = connection.execute(s)
        return result.fetchall()

修改后你可以开启SQLAlchemy的SQL打印日志，确认生成的执行语句中同时包含两个字段的比较逻辑即可。

内容的提问来源于stack exchange，提问作者yzernik