SQLAlchemy filter query "column LIKE ANY (array)"

Question

Hi SQLAlchemy experts, here's a daunting task:

I am trying to write a query that resolves something like:

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

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) 

Any solutions?

Answer 1

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])) 

A, where the WHERE my_column LIKE 'a%' OR my_column LIKE 'b%' will be generated from the code above.

As for why your any() did not work, I think because it requires my_column list (see here ), and for instance query(MyTable).filter(MyTable.my_list_column.any(name='abc')) should return MyTable rows if any element in the my_list_column column (list) of this row has a name with 'abc', so it really is very different from your need.

Answer 2

You can try using any_ ()

In your case, it will look something like this:

 from sqlalchemy import any_ foo = ['a%', 'b%'] DBSession().query(MyTable).filter(MyTable.my_column.like(any_(foo)))