CODEWITHSUNDEEP
pythonmysqlflasksqlalchemy
Ling He
Ling He

Reputation: 167

SQLAlchemy and multi-column case-insensitive query

Assuming we have a table consisting of column_1, column_2, ... , column_n and all of them are string fields. The conditions we are going to do case-insensitive query are stored in a dictionary d like d[column_1] = "Hello", which may or may not contains all columns. How can we do the query?

I checked the question Case Insensitive Flask-SQLAlchemy Query. It contains a lot of awesome answers, but none of them works if we do not know what conditions we have until runtime.

Upvotes: 0

Views: 1537

Answers (1)

AArias
AArias

Reputation: 2568

You would need to build the query looping through each key of the dictionary.

As you didn't give any code sample, I'm going to call the table model class TableModel and each column will be column_1, column_2, etc.

Something like this should work:

d = {'column_1': 'some_string', 'column_3': 'another_string'}
# skipping 'column_2' just to exemplify how every column is optional in the dictionary

my_query = TableModel.query

for k in d:
    my_query = my_query.filter(getattr(TableModel, k).ilike(d[k]))

And that's about it. Afterwards you can use my_query as any other query, e.g., my_query.count() or my_query.all()

Upvotes: 1

Related Questions