Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQLAlchemy filter query "column LIKE ANY (array)"

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

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?

like image 842
user1599438 Avatar asked Feb 02 '15 03:02

user1599438


People also ask

What is difference between filter and filter by in SQLAlchemy?

The second one, filter_by(), may be used only for filtering by something specifically stated - a string or some number value. So it's usable only for category filtering, not for expression filtering. On the other hand filter() allows using comparison expressions (==, <, >, etc.)

What is all () in SQLAlchemy?

method sqlalchemy.orm.Query. all() Return the results represented by this Query as a list. This results in an execution of the underlying SQL statement. The Query object, when asked to return either a sequence or iterator that consists of full ORM-mapped entities, will deduplicate entries based on primary key.

What is lazy dynamic SQLAlchemy?

lazy = 'dynamic': When querying with lazy = 'dynamic', however, a separate query gets generated for the related object. If you use the same query as 'select', it will return: You can see that it returns a sqlalchemy object instead of the city objects.

What does SQLAlchemy all () return?

It does return an empty list.


2 Answers

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 condition WHERE my_column LIKE 'a%' OR my_column LIKE 'b%' would be generated from above code.

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.

like image 92
Paul Lo Avatar answered Sep 19 '22 19:09

Paul Lo


You can try to use any_()

In your case it would look something like this:

from sqlalchemy import any_

foo = ['a%', 'b%']
DBSession().query(MyTable).filter(MyTable.my_column.like(any_(foo)))
like image 23
S. Blue Avatar answered Sep 21 '22 19:09

S. Blue