Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQLAlchemy substring is in string

I am trying to query a database for records that have a string field inside an input search string.

Something like this:

User.query.filter(User.name in search_string)

Of course that does not work since it is not a valid SQLAlchemy statement. This seems a simple problem but I really can't find the right column operator.

I tried the in_ operator:

User.query.filter(User.name.in_(search_string)

But that of course is not working. What I need is a is_substring operator.

like image 841
blueSurfer Avatar asked Oct 28 '15 10:10

blueSurfer


2 Answers

In SQL it would be

SELECT * FROM user 
WHERE 'search string' LIKE CONCAT('%', name, '%')

SQLAlchemy contains(other) produces the clause LIKE '%<other>%'

from sqlalchemy import literal

User.query.filter(literal(search_string).contains(User.name))
like image 83
r-m-n Avatar answered Oct 05 '22 12:10

r-m-n


Try this:

User.query.filter(User.name.match(search_string)) or

User.query.filter(User.name.contains(search_string))

For use in_ you have to use a list, like that:

search_string = ['ed', 'wendy', 'jack']

User.query.filter(User.name.in_(search_string))
like image 41
Andre Araujo Avatar answered Oct 05 '22 11:10

Andre Araujo