如何使用SQLAlchemy会话确定一个PGArray是否包含在另一个PGArray中？

Question

我有一个SqlAlchemy表，如下所示：

table = sql.Table('treeItems', META,
    sql.Column('id', sql.Integer(), primary_key=True),
    sql.Column('type', sql.String, nullable=False),
    sql.Column('parentId', sql.Integer, sql.ForeignKey('treeItems.id')),
    sql.Column('lineage', PGArray(sql.Integer)),
    sql.Column('depth', sql.Integer),
)

映射到如下对象：

^{pr2}$

我想选择给定节点的任何子节点，所以我要寻找的是如下所示的SQL（对于pk=2的父节点）：

SELECT *
FROM "treeItems"
WHERE ARRAY[2] <@ "treeItems".lineage AND "treeItems".id != 2
ORDER BY "treeItems".lineage

下面是我使用的SqlAlchemy/Python代码来尝试不太幸运地访问上面的SQL：

arrayStr = 'ARRAY[%s]' % ','.join([str(i) for i in self.lineage])
lineageFilter = expr.text('%s <@ %s' % (arrayStr, TreeItem.table.c.lineage))
query = SESSION.query(TreeItem).filter(expr.and_(lineageFilter, TreeItem.table.c.id!=self.id))

但下面是我最后使用的SQL（注意where子句中treeItems表名周围缺少引号）：

SELECT "treeItems".id AS "treeItems_id", "treeItems".type AS "treeItems_type", "treeItems"."parentId" AS "treeItems_parentId", "treeItems".lineage AS "treeItems_lineage", "treeItems".depth AS "treeItems_depth"
FROM "treeItems"
WHERE ARRAY[2] <@ treeItems.lineage AND "treeItems".id != %(id_1)s

所以，现在我们来回答问题：

是否有比使用text（）表达式更好的方法/SqlAlchemy中是否有运算符或表达式可以对PGArray执行<；@？

如果必须使用text（）表达式，如何让引号出现在表名的周围？

谢谢大家！在

Answer 1

SQLAlchemy的子句元素有一个用于自定义运算符的.op（）方法。不能使用的是数组文本的特殊子句。可以使用literal_列指定数组文本：

print sql.literal_column('ARRAY[2]').op('<@')(table.c.lineage)
# ARRAY[2] <@ "treeItems".lineage

如果您想要一个更好的数组文本API，那么可以使用sqlalchemy0.5.4中添加的sqlalchemy.ext.compiler模块来创建它。在

Answer 2

在这个特殊的例子中，我注意到SQL中的引号是由于我使用的是混合大小写的表名。将表名从“treeItems”转换为“tree\u items”解决了引用问题，并且我能够使我的文本表达式正常工作：

expr.text('%s <@ %s' % (arrayStr, TreeItem.table.c.lineage))

这是一个修复，很高兴知道混合大小写的表名需要被引用，但是Ants的答案仍然是解决这个问题的正确方法。在