如何在Flask-SQLAlchemy中查询ScalarListType的列表指定索引元素

嘿，我明白你的需求——用ScalarListType存了整数列表后，想根据列表指定索引的元素来筛选记录对吧？其实ScalarListType默认是把列表以逗号分隔的字符串存在数据库里的（比如[0,0,1,0]会存成"0,0,1,0"），所以要查询指定索引的元素，得借助数据库的字符串/数组处理函数来实现，不同数据库的写法略有不同，我给你分情况说明：

情况1：使用PostgreSQL数据库 #

PostgreSQL有专门的字符串转数组函数string_to_array，可以轻松提取指定索引的元素：

from sqlalchemy import func
from your_app import db, Foo

# 查询list中第3个索引（也就是第4个元素）等于1的Foo记录
target_records = Foo.query.filter(
    # 把字符串转成数组，取索引3的元素，转成整数后比较
    func.string_to_array(Foo.list, ',')[3].cast(db.Integer) == 1
).all()

如果你想更省心，其实可以直接改用PostgreSQL的原生ARRAY类型（比ScalarListType的字符串存储更适合查询）：

class Foo():
    id = db.Column(db.Integer, primary_key=True)
    # 直接用PostgreSQL的整数数组类型
    list = db.Column(db.ARRAY(db.Integer))

# 此时查询逻辑就和你预想的一样简单了！
target_records = Foo.query.filter(Foo.list[3] == 1).all()

情况2：使用SQLite数据库 #

SQLite没有直接的字符串拆分函数，得用substr和instr组合来提取指定位置的元素，不过这种写法要注意边界情况（比如第一个/最后一个元素）：

from sqlalchemy import func, text
from your_app import db, Foo

# 查询索引3的元素等于1的记录（假设元素都是无逗号的整数）
target_records = Foo.query.filter(
    func.cast(
        func.substr(
            Foo.list,
            # 找到第3个逗号的位置，加2跳过逗号和引号（如果有的话）
            func.instr(Foo.list, text("','"), 1, 3) + 2,
            # 计算当前元素的长度：第4个逗号位置减去第3个逗号位置再减2
            func.instr(Foo.list, text("','"), 1, 4) - (func.instr(Foo.list, text("','"), 1, 3) + 2)
        ),
        db.Integer
    ) == 1
).all()

不过这种写法比较繁琐，更推荐你改用SQLite支持的JSON类型来存储列表，这样查询会简单很多：

class Foo():
    id = db.Column(db.Integer, primary_key=True)
    # 用JSON类型存储整数列表
    list = db.Column(db.JSON)

# 直接用索引访问查询，和你想要的逻辑完全一致！
target_records = Foo.query.filter(Foo.list[3] == 1).all()

小总结 #

• 如果你用PostgreSQL，优先考虑原生ARRAY类型，查询最直接；
• 如果是SQLite，改用JSON类型比用ScalarListType的字符串存储更便于查询；
• 坚持用ScalarListType的话，就根据数据库类型用对应的字符串处理函数来提取索引元素。

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