How to implement efficient filtering logic in Python?

Question

I am trying to create a program that stores

1. Fruit Name
2. Fruit Type
3. Fruit Color
4. Fruit Size

and show them back to the user upon request. The user will be given pre-defined choices to select from. Something like this:

My database table will be like this:

Now, I am trying to implement a filter function that lets the user select

• Fruit Type
• Fruit Color
• Fruit Size

And it will give out all the Fruit Names that have the above properties. But now, I have an additional option, "All".

Assuming that I have already queried out the data for all the fruits and stored them in dictionary like this:

myfruits = { 'apple':('fleshy','red','medium'),
            'orange':('fleshy','orange','medium'),
             'peanut':('dry','red','small'),...}

How do I get the list of fruit names that has the three properties that the user selected? (For example, if the user selected 'fleshy' type , 'All' color, 'All' size -- it should return ['apple','orange'].)

I have thought of using if statement, but as the number of properties grow, I would have to write so many lines of if and else which I don't think is feasible.

I am using Python 2.7 with PyQt 4 and the SQLite 3 database on Windows XP SP3 32-bit.

Answer 1

I'd use SQLAlchemy here to handle the database layer, either using it as an ORM or just to generate the SQL expressions.

This lets you generate the filters dynamically; you can loop through the filters and a specific filter hasn't been set to 'All', limit the query by it.

Using SQLAlchemy to just generate SQL expressions would look something like:

from sqlalchemy.sql import select

fruit_query = select([fruits])
for filtername in ui_filters:
    filtervalue = obtain_filter_value_for(filtername)
    if filtervalue != 'All':
        fruit_query = fruit_query.where(filtername == filtervalue)

The alternative would be to generate the WHERE clause manually:

query = 'SELECT * FROM fruits'
params = []
whereclauses = []

for filtername in ui_filters:
    filtervalue = obtain_filter_value_for(filtername)
    if filtervalue != 'All':
        whereclauses.append('{} = ?'.format(filtername))
        params.append(filtervalue)

if whereclauses:
    query = '{} WHERE {}'.format(query, ' AND '.join(whereclauses))

cursor.execute(query, params)

but note that the SQLAlchemy expression engine is a lot more flexible and less prone to errors. Pretty soon you'll want to add more complex filters (searches in ranges, text searches, combining filters in 'OR' expressions) and SQLAlchemy can make generating the expressions a lot simpler, really.

Answer 2

If you already query out all the data, an easy way is to simply use the filter function :

def predicate(fruit_type, fruit_color, fruit_size):
    def _predicate(fruit):
        if not fruit_type == 'All' and not fruit_type == fruit[1][0]:
            return False
        if not fruit_color == 'All' and not fruit_color == fruit[1][1]:
            return False
        if not fruit_size == 'All' and not fruit_size == fruit[1][2]:
            return False
        return True
    return _predicate

query_type = 'All'
query_color = 'All'
query_size = 'All'
myfruits = {}
my_filtered_fruit = list(filter(predicate(query_type, query_color, query_size), myfruits.items()))

An other way is to defined an object Predicate which has a view (the name of the filter) and the filter function associated :

class Predicate:
    def __init__(self, predicate, view):
        self.predicate = predicate
        self.view = view

# Creation of the predicates :
all_color = Predicate(lambda fruit: True, 'All colors')
red_color = Predicate(lambda fruit: fruit[2] == 'red')
# ...

# Then you have to generate your select form. I don't remember exactly the PyQt4 doc but it's not the harder part.

predicates = getAllSelected() # I guess you know how to get this kind of function

myfruits = {}
my_filtered_fruits = myfruits.items()
for pred in predicates:
    my_filtered_fruit = filter(lambda x: pred(x[1]), my_filtered_fruit)
my_filtered_fruit = list(my_filtered_fruit)