SQLAlchemy - Search entire DB model columns for a pattern

Question

I have a database which I need to return any value that matches the query or looks similar.

Using flask-sqlalchemy I can filter manually, but I'm having trouble getting a list of objects using list comprehensions or any other more pythonic method.

I have already tried to create a dict with all model columns (which I have a list ) and the search value passed to the filter_by query

column_dict_and_value = {column_name:'value' for column_name in columns}    
a = model.query.filter_by(**column_dict_and_value).all()

...but doesn't even return an existing object, although

a = model.query.filter_by(column_name='value').all()

actually returns the object.

I tried filter(.like('%')) and it sort of works

a = model.query.filter(model.column_name.like('valu%')).all()

returns me a list of all the objects that matches that pattern but for that column, and I want to iterate over all columns. Basically a full blown search since I'm not sure what the user is going to look for and I want to show the object if the query exist on any column or a list of objects if the query is incomplete, like the * in any search.

I tried to use list comprehensions to iterate through the model attributes but I'm not allowed to do that apparently:

a = [model.query.filter(model.column.like('valu%')) for column in columns]

...this complains that column is not an attribute of the model, which makes sense due to the syntax, but I had to try anyway, innit?

I'm a newbie regarding databases and class objects so please be gentle. I tried to look for similar answers but I can't find the one that suits me. the filter_by(one_column, second_column, etc) doesn't look to me very pythonic and if for any reason I change the model i need to change the query too where as creating a dict comprehension of the model seems to me more foolproof.

SOLUTION

Based on calestini proposed answer I wrote this code that seems to do the trick. It also cleans the list because if all() doesn't have any result (since it looks in every field) it returns None and adds up to the list. I'd rather prefer it clean.

Note: All my fields are text. Please check the third proposed solution from calestini if yours differ. Not tested it though.

columns = [
"foo",
"bar",
"foobar"
]


def list_everything(search):
    d = {column: search for column in columns}
    raw = [
        model.query.filter(getattr(model, col).ilike(f"{val}%")).all()
        for col, val in d.items()
    ]
    return [item for item in raw if item]

I'll keep optimizing and update this code if I come with a better solution. Thanks a lot

Answer 1

Probably the reason why you are not getting any result from

a = model.query.filter_by(**column_dict_and_value).all()

is because filter_by is testing for direct equality. In your case you are looking for a pattern, so you will need to loop and use filter as opposed to filter_by.

Assuming all your columns are String or Text types, you can try the following:

a = model.query

for col, val in column_dict_and_value.items():
   a = a.filter(getattr(model, col).ilike(f'{val}%')) #ilike for case insensitive

a = a.all()

Or vs And

The issue can also be that in your case you could be testing for intersection but expecting union. In other words, you are returning something only if the pattern matches in all columns. If instead you want a result in case any column matches, then you need to tweak the code a bit:

condition = or_(*[getattr(model, col).ilike(f'{val}%') for col, val in column_dict_and_value.items()])

a = model.query.filter(codition).all()

Now if you actually have other data types among the columns, then you can try to first verify if the column type and then pass the same logic:

for col, val in column_dict_and_value.items():
    ## check if field python equivalent is string
    if isinstance(class_.__table__.c[col].type.python_type, str):
       ...