How to implement efficient filtering logic in Python?

Question

I am trying to create a program that stores

• Fruit name
• Fruit type
• Fruit color
• Fruit size

and return them to the user upon request. The user will be provided with predefined selection options. Something like that:

My database table will look like this:

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

• Fruit type
• Fruit color
• Fruit size

And it will return all Fruit Names that have the above properties. But now I have an additional option "Everything".

Assuming I already requested data for all fruits and saved them in a dictionary as follows:

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

How to get a list of fruit names that has three properties that the user has selected? (For example, if the user selected the "fleshy" type, "All", "All" - he should return ['apple','orange'] .)

I thought about using an if , but as the number of properties grows, I would have to write so many if and else lines that I think are unthinkable.

I am using Python 2.7 with PyQt 4 and SQLite 3 on a 32-bit version of Windows XP XP SP3.

Answer 1

If you have already requested 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())) 

Another way is to define a Predicate object, which has the form (filter name) and the associated filter function:

 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 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) 

Answer 2

I would use SQLAlchemy here to process the database layer, using either as ORM or just generate SQL expressions .

This allows you to generate filters dynamically; you can scroll through the filters, but a certain filter has not been set to "All", restricts its request.

Using SQLAlchemy to generate SQL statements will look something like this:

 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) 

An alternative would be to generate a WHERE 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 much more flexible and less error prone. Pretty soon you will want to add more complex filters (search in ranges, search for text, combine filters in "OR" expressions), and SQLAlchemy can make expression generation a lot easier, really.