SQLAlchemy：查找数组列之间的差异 [英] SQLAlchemy: Find difference between array columns

问题描述

我有一个名为 UnitOfWork 的表，其中有3列 cases_identified ， cases_completed 和 cases_double_check ，它们都是Postgresql整数数组。

I have a table called UnitOfWork which has 3 columns cases_identified, cases_completed and cases_double_check, all of which are Postgresql arrays of integers.

是否可以写一个查询（或混合属性），该查询找到已确定但不在完成或再次检查列中的案例？

Is it possible to write a query (or a hybrid property) that finds the cases that have been identified, but are not in the completed or double-check columns?

这就是我的想法与，但SQL表达式不起作用：

This is what I came up with, but the SQL expression doesn't work:

@hybrid_property
def todo(self):
    return [x for x in self.cases_identified
            if x not in self.cases_completed and
            x not in self.cases_double_check]

@todo.expression
def todo(cls):
    return [x for x in cls.cases_identified
            if x not in  cls.cases_completed and
            x not in cls.cases_double_check]

我在测试查询中遇到的错误是：

The error I get on a test query is:

test = Session.query(UnitOfWork.todo).first()
NotImplementedError: Operator 'contains' is not supported on this expression

推荐答案

对于这个答案，我假设 cls.cases_identified ， cls。 cases_completed 和 cls.cases_double_check 在Python中的类型为 postgresql.ARRAY（Integer）

For this answer I'll assume that cls.cases_identified, cls.cases_completed, and cls.cases_double_check are of postgresql.ARRAY(Integer) type in Python side.

您的 @ todo.expression 应该只返回：SQL表达式。目前，它正在尝试返回python列表。由于 postgresql.ARRAY 不支持 in 运算符，尽管它具有方法 包含 ，它映射到Postgresql中的 @> 运算符，并测试如果元素是参数数组表达式的元素的超集。另一方面，这不是您想要的。您很幸运，如果x不位于... 中，那么就像普通

Your @todo.expression should return just that: an SQL expression. Currently it is trying to return a python list. The exception is raised since a postgresql.ARRAY does not support the in operator, though it does have a method contains, which maps to the @> operator in Postgresql and tests "if elements are a superset of the elements of the argument array expression". This on the other hand is not what you want. You're lucky that you had the if x not in ... in there, as plain

[x for x in cls.cases_identified]

似乎导致无限循环而不是异常。

seems to result in an infinite loop instead of an exception.

在Postgresql中获取数组之间的差异已在，但是这是使用SQLAlchemy的方法，首先使用数组构造器：

Getting the difference between arrays in Postgresql has been covered here extensively, but here's how you would apply that using SQLAlchemy, first using an array constructor:

from sqlalchemy import func

...

class UnitOfWork(...):

    @todo.expression
    def todo(cls):
        # Force correlation of `cls` from outer FROM objects. Use
        # the `select()` shorthand method of `FunctionElement`.
        identified = func.unnest(cls.cases_identified).select().correlate(cls)
        completed = func.unnest(cls.cases_completed).select().correlate(cls)
        double_check = func.unnest(cls.cases_double_check).select().correlate(cls)
        # Create the subquery statement
        stmt = identified.except_(completed.union(double_check))
        # Uses the `func` generic for ARRAY constructor from
        # subquery syntax. The subquery statement has to be
        # converted to a "scalar subquery" in the eyes of SQLA
        # (really just a subquery returning 1 col) in order
        # to remove it from the FROM clause hierarchy.
        return func.array(stmt.as_scalar())

这有一个缺点提供任何FROM对象（因为它与封闭查询中的所有内容相关），因此您必须发出如下原始查询：

This has a downside of not providing any FROM objects (as it correlates everything from the enclosing query), so you'd have to issue the original query like this:

test = Session.query(UnitOfWork.todo).select_from(UnitOfWork).first()

您也可以使用提供特殊功能和运算符的Postgresql intarray模块对于无空的整数数组：

You could also use the Postgresql intarray module that provides special functions and operators for null-free arrays of integers:

class UnitOfWork(...):

    @todo.expression
    def todo(cls):
        return (cls.cases_identified - cls.cases_completed - 
                cls.cases_double_check)

请注意，您必须先在Postgresql中安装扩展程序：

Note that you have to install the extension first in Postgresql:

CREATE EXTENSION intarray;

这篇关于SQLAlchemy：查找数组列之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！