python - 如何使用 SQLAlchemy 选择 PostgreSQL 数组中某个位置的值？

Question

对于嵌套评论系统，我在 Comment 模型中使用以下定义将树结构存储在 PostgreSQL 表中:

path = sa.Column(ARRAY(sa.Integer))

这会将所有评论的 ID 存储在当前评论的路径中。因此，如果 id 为 15 的评论是 11 和 13 的子评论，则其路径将为 [11,13,15]。

现在我想找到并统计 child 的评论。因此，为了找到评论 11 的所有子项，包括它自己，我想生成如下所示的 SQL:

SELECT id, path FROM comments WHERE path[1] = 11;

在我的数据库中，这很高兴地返回了一组不错的评论 11 及其子评论。

在上面的例子中，如果我想抓取评论的 child ，我可以使用以下 SQL:

SELECT id, path FROM comments WHERE path[1] = 11 AND path[2] = 13;

应该如何使用 SQLAlchemy 来生成此 SQL？

我已经尝试使用 filter_by() 和 filter() 但都没有成功......这些都不起作用:

q = Comment.query.filter(path[1] == 11)
> NameError: name 'path' is not defined

q = Comment.query.filter_by(path[1] = 11)
> SyntaxError: keyword can't be an expression

我不想为此查询编写自定义 SQL，但如果这是唯一的方法，那么请就如何最好地构造它给出一些指示。

编辑 1

确认我使用的是 SQLAlchemy 0.7 - 因此适用于此版本的答案会格外有用。

我意识到对于上面的评论实现，没有必要在确切的位置找到 id 号，在任何位置找到它们也可以。因此，任何关于如何创建 SQLAlchemy 以生成类似这样的东西的输入都是好的:

SELECT id, path FROM comments WHERE 11 = ANY (path);

这可能吗？

Answer 1

这仅适用于从 SQLAlchemy 0.8(撰写本文时为 beta2)开始:

from sqlalchemy.dialects import postgresql

class Comment(Base):
    __tablename__ = 'comments'
    id = Column(Integer, primary_key=True)
    path = Column(ARRAY(Integer))

q = Comment.query.filter(Comment.path[1] == 11)
# This is just to demonstrate how compiled query would look like.
print q.statement.compile(dialect=postgresql.dialect())

输出:

SELECT comments.id, comments.path 
FROM comments 
WHERE comments.path[%(path_1)s] = %(param_1)s

---

更新

最近有一个discussion在 SA 邮件列表中关于如何为 Postgres 数组创建 = ANY(和相关的)查询。即使那里的示例是为 0.8 编写的，它也应该在 0.7 中工作。这是 0.7 的 = ANY 的有效实现(已测试):

from sqlalchemy.sql import literal, tuple_

# There's already built-in any() function in Python, we don't want to
# shadow that.
def any_(value, col):
    return literal(value).op('= ANY')(tuple_(col))

q = Comment.query.filter(any_(11, Comment.path))