postgresql - 使用 sqlalchemy orm 过滤数据库列中的一系列值

Question

我有一个 postgresql 数据库，在一个特定的表中，有很多行。此表中的一列称为数据，是一个 float 组 REAL[]，并填充了约 4500 个元素的数组。我想通过 SQLAlchemy 和 ORM 的一些查询来访问这个表。

如何选择表中此列的子集满足某些条件(例如包含一系列值)的所有行？就像我想选择数据包含值 >= 10 或值在 >=10 和 <=20 之间的所有行。

我可以用像这样的直接 session 查询来做到这一点吗

rows = session.query(Table).filter(Table.data.(some conditional)).all()

我的条件是“VALUES >= 10 and VALUES <= 20”？

或者当我定义我的 SQLAlchemy 表类时，我是否需要定义一些特殊的方法或设置。例如，我将表格设置为

class Table(Base):
    __tablename__ = 'table'
    __table_args__ = {'autoload' : True, 'schema' : 'testdb', 'extend_existing':True}

    data = deferred(Column(ARRAY(Float)))

    def __repr__(self):
        return '<Table (pk={0})>'.format(self.pk)       

理想情况下，我想对其进行设置，这样我就可以在我的 session.query 调用中进行简单的过滤。这可能吗？我对 ORM 不是很熟悉，也许是这样？

我看过 ARRAY Comparator sqlalchemy 文档，但这些文档似乎只适用于精确值。我的数据精确到 6 个 sigfigs，我不知道提前的确切值。

执行此操作的最佳方法是什么？谢谢。

编辑:

根据以下评论，这是我在尝试选择数据(来自 1 列)>= 1.0 的所有行(1000 行中)时使用的代码。应该有 537 行。

rows =  session.query(datadb.Table).filter(datadb.Table.data.any(1.0,operator=operators.le)).all()

这给出了正确的子集编号。 len(rows) = 537. 但是，我不明白with this operator 的逻辑，在哪里选择数据 >=1.0 ，我使用 le 运算符？此外，按照同样的思路，应该有 234 行的数据介于值 >=1.0 和 <1.0 之间，但此语句未能给出正确的子集。

rows = session.query(datadb.Table).filter(datadb.Table.data.any(1.0,operator=operators.le)).filter(datadb.Table.data.any(1.2,operator=operators.ge)).all()

* 编辑 2 *

这是我的数据库表的一个示例，其中包含几行。 pk为整数，data为实数[]。

db datadb
schema Table 
pk      data
0       [0.0,0.0,0.5,0.3,1.3,1.9,0.3,0.0,0.0]
1       [0.1,0.0,1.0,0.7,1.1,1.5,1.2,0.3,1.4]
2       [0.0,0.6,0.4,0.3,1.6,1.7,0.4,1.3,0.0]
3       [0.0,0.1,0.2,0.4,1.0,1.1,1.2,0.9,0.0]
4       [0.0,0.0,0.5,0.3,0.2,0.1,0.7,0.3,0.1]

我有 5 行，其中 4 行的数据值 >= 1.0，而只有 2 行的值在 >= 1.0 和 <= 1.2 的范围内。在第一种情况下，我要获取行的查询是

rows = session.query(datadb.Table).filter(datadb.Table.data.any(1.0,operator=operators.le)).all()

这应该返回 4 行，位于 pk=0,1,2,3。此查询符合我的预期。第二种情况

rows = session.query(datadb.Table).filter(datadb.Table.data.any(1.0,operator=operators.le)).filter(datadb.Table.data.any(1.2,operator=operators.ge)).all()

并且应该返回 pk=1,3 处的 2 行。但是，此查询仅返回第一个查询的 4 行。对于第二个查询，我也试过了

rows = session.query(datadb.Table).filter(datadb.Table.data.any(1.0,operator=operators.le),datadb.Table.data.any(1.2,operator=operators.ge)).all()

这也没有用。

Answer 1

请阅读有关 ARRAY.Comparator 的文档，根据它您应该能够执行以下操作:

rows = (session.query(Table)
        .filter(Table.data.any(10, operator=operators.le))
        .filter(Table.data.any(20, operator=operators.ge)
).all()

编辑:

# combined filter does not work,
# but applying one or the other is still useful as it reduces the result set
q = (session.query(MyTable)
     .filter(MyTable.data.any(1.0, operator=operators.le))
     # .filter(MyTable.data.any(1.2, operator=operators.ge))
     )

# filter in memory
items = [_row for _row in q.all()
         if any(1.0 <= item <= 1.2 for item in _row.data)]

for item in items:
    print(item)