Variable filter for SQLAlchemy Query

Question

I'm adding a search feature to my application (created using PyQt5) that will allow the user to search an archive table in the database. I've provided applicable fields for the user to choose to match rows with. I'm having some trouble with the query filter use only what was provided by the user, given that the other fields would be empty strings.

Here's what I have so far:

 def search_for_order(pierre):
    fields = {'archive.pat.firstname': pierre.search_firstname.text(),
              'archive.pat.lastname': pierre.search_lastname.text(),
              'archive.pat.address': pierre.search_address.text(),
              'archive.pat.phone': pierre.search_phone.text(),
              'archive.compound.compname': pierre.search_compname.text(),
              'archive.compound.compstrength': pierre.search_compstrength.text(),
              'archive.compound.compform': pierre.search_compform.currentText(),
              'archive.doc.lastname': pierre.search_doctor.text(),
              'archive.clinic.clinicname': pierre.search_clinic.text()
             }

    filters = {}

    for field, value in fields.items():
        if value is not '':
            filters[field] = value

     query = session.query(Archive).join(Patient, Prescribers, Clinic, Compound)\
             .filter(and_(field == value for field, value in filters.items())).all()

The fields dictionary collects the values of all the fields in the search form. Some of them will be blank, resulting in empty strings. filters is intended to be a dictionary of the object names and the value to match that.

le_affan · Accepted Answer · 2018-02-15 08:06:55Z

3

The problem lies in your definition of the expressions within your and_ conjunction. As of now you're comparing each field with the corresponding value which of course returns false for each comparison.

To properly populate the and_ conjunction you have to create a list of what sqlalchemy calls BinaryExpression objects.

In order to do so I'd change your code like this:

1) First use actual references to your table classes in your definition of fields:

fields = {
    (Patient, 'firstname'): pierre.search_firstname.text(),
    (Patient, 'lastname'): pierre.search_lastname.text(),
    (Patient, 'address'): pierre.search_address.text(),
    (Patient, 'phone'): pierre.search_phone.text(),
    (Compound, 'compname'): pierre.search_compname.text(),
    (Compound, 'compstrength'): pierre.search_compstrength.text(),
    (Compound, 'compform'): pierre.search_compform.currentText(),
    (Prescribers, 'lastname'): pierre.search_doctor.text(),
    (Clinic, 'clinicname'): pierre.search_clinic.text()
}

2) Define filters as a list instead of a dictionary:

filters = list()

3) To populate the filters list explode the tuple of table and fieldname used as key in the fields dictionary and add the value to again create tuples but now with three elements. Append each of the newly created tuples to the list of filters:

for table_field, value in fields.items():
    table, field = table_field
    if value:
        filters.append((table, field, value))

4) Now transform the created list of filter definitions to a list of BinaryExpression objects usable by sqlalchemy:

binary_expressions = [getattr(table, attribute) == value for table, attribute, value in filters]

5) Finally apply the binary expressions to your query, make sure it's presented to the and_ conjunction in a consumable form:

query = session.query(Archive).join(Patient, Prescribers, Clinic, Compound)\
         .filter(and_(*binary_expressions)).all()

I'm not able to test that solution within your configuration, but a similar test using my environment was successful.

edited Feb 15, 2018 at 8:06

answered Feb 14, 2018 at 2:11

le_affan

3142 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

smallpants Over a year ago

Both answers are producing WHERE false = 1 inside the statement, resulting in an empty list.

smallpants Over a year ago

Just kidding, syntax mistake. This seems to work well, but I don't fully understand why.

le_affan Over a year ago

Could you specify what part you don't understand? I tried to explain what is happening, yet obviously that was not enough yet.

jsbueno · Accepted Answer · 2018-02-14 02:11:32Z

0

Once you get a query object bound to a table in SqlAlquemy - that is, what is returned by session.query(Archive) in the code above -, calling some methods on that object will return a new, modified query, where that filter is already applied.

So, my preferred way of combining several and filters is to start from the bare query, iterate over the filters to be used, and for each, add a new .filter call and reassign the query:

query = session.query(Archive).join(Patient, Prescribers, Clinic, Compound)
for field, value in filters.items():
    query = query.filter(field == value)
results = query.all()

Using and_ or or_ as you intend can also work - in the case of your example, the only thing missing was an *. Without an * preceeding the generator expression, it is passed as the first (and sole) parameter to and_. With a prefixed *, all elements in the iterator are unpacked in place, each one passed as an argument:

...
.filter(and_(*(field == value for field, value in filters.items()))).all()

edited Feb 14, 2018 at 2:11

answered Feb 14, 2018 at 2:03

jsbueno

113k11 gold badges159 silver badges239 bronze badges

2 Comments

smallpants Over a year ago

This produces a WHERE clause of false =1

jsbueno Over a year ago

I did not wrote about your filters dictionary having to be comprised of real field objects, picked from the model classes - the other answer deals into this. I supposed you knew it before I wrote this answer.

Collectives™ on Stack Overflow

Variable filter for SQLAlchemy Query

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related