0

I'm stuck on this database search problem: I have a PyQt5 widget with different checkBoxes. Each of them represents a column from my database. For example, 'worker_name' and 'department'. checkBoxes are placed near lineEdits, so user inserts some text into this lineEdit, than click on checkBox and 'Find' button.

Let's say, user insert 'John' in lineEdit, than press 'worker_name' checkBox and gets all docs of this worker via 

session.query(Doc).filter_by('worker_name'=text_from_lineEdit)

If user insert text in both lineEdits and pressed both checkBoxes ('worker_name' and 'department') query will look like this: 

session.query(Doc).filter_by('worker_name'=text_from_lineEdit, 'department'=text_from_lineEdit_2)

But what if I have many checkBoxes (as many as columns in DB table) and i don't know which of them will be pressed and which are not. How should I form the query in that case ? I mean I will have 'wrk_name','department' and 'date' and user could search only by 'worker_name' or by 'worker_name' + 'date' or with all of the checkBoxes.

Is there any way to form database query dynamically relying on which arguments are given and which are not to include them in 'filter_by'/'filter or just ignore.

Please, share your ideas about possible implementation of such functionality. Thx for your time.

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
V.Nosov
  • 93
  • 3
  • 10
  • please, provide a [mcve] – eyllanesc Oct 04 '18 at 20:46
  • Also https://stackoverflow.com/questions/41305129/sqlalchemy-dynamic-filtering, https://stackoverflow.com/questions/29885879/sqlalchemy-dynamic-filter-by, https://stackoverflow.com/questions/19506105/flask-sqlalchemy-query-with-keyword-as-variable, and https://stackoverflow.com/questions/39137911/build-dynamic-filters-in-sqlalchemy-python – Ilja Everilä Oct 05 '18 at 08:57

1 Answers1

5

Just remember that everything is an object in python, so you can dynamically build a list of filters and pass that to the filter() (or any other) method. Some pseudocode to illustrate the concept:

flist = []
for inp in input:
    flist.append(Table.column == inp.property)

db.query(Table).filter(*flist)
musbur
  • 567
  • 4
  • 16
  • for those unaware (as me) of the star operator: https://docs.python.org/3/tutorial/controlflow.html#unpacking-argument-lists and https://stackoverflow.com/questions/2921847/what-does-the-star-and-doublestar-operator-mean-in-a-function-call – Greg Holst May 19 '21 at 13:35