SQLAlchemy 过滤查询“column LIKE ANY (array)" [英] SQLAlchemy filter query "column LIKE ANY (array)"

问题描述

您好 SQLAlchemy 专家，这里有一个棘手的问题:

Hi SQLAlchemy experts out there, here's a tricky one for you:

我正在尝试编写一个解析为以下内容的查询:

I'm trying to write a query that resolves into something like:

SELECT * FROM MyTable where my_column LIKE ANY (array['a%', 'b%'])

使用 SQLAlchemy:

using SQLAlchemy:

foo = ['a%', 'b%']

# this works, but is dirty and silly
DBSession().query(MyTable).filter("my_column LIKE ANY (array[" + ", ".join(["'" + f + "'" for f in token.tree_filters]) + "])")

# something like this should work (according to documentation), but doesn't (throws "AttributeError: Neither 'AnnotatedColumn' object nor 'Comparator' object has an attribute 'any'"
DBSession().query(MyTable).filter(MyTable.my_column.any(foo, operator=operators.like)

有什么解决办法吗?

推荐答案

使用 or_() 和 like()，下面的代码应该可以很好地满足你的需求:

Use or_() and like(), the following code should satisfy your need well:

from sqlalchemy import or_

foo = ['a%', 'b%']
DBSession().query(MyTable).filter(or_(*[MyTable.my_column.like(name) for name in foo]))

一个 where 条件 WHERE my_column LIKE 'a%' OR my_column LIKE 'b%' 将从上面的代码生成.

A where condition WHERE my_column LIKE 'a%' OR my_column LIKE 'b%' would be generated from above code.

至于为什么你的 any() 不起作用，我认为是因为它要求 my_column 是一个列表(见 此处)，例如，query(MyTable).filter(MyTable.my_list_column.any(name='abc')) 将返回 MyTable 行，如果该行的 my_list_column 列(列表)中的任何元素命名为'abc'，所以它实际上与您的需求大不相同.

As for why your any() didn't work, I think it's because it requires my_column to be a list (see here), and, for instance, query(MyTable).filter(MyTable.my_list_column.any(name='abc')) is to return MyTable rows if any element in my_list_column column (a list) of that row is named with 'abc', so it's actually quite different from your need.

这篇关于SQLAlchemy 过滤查询“column LIKE ANY (array)"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！