cleaning up my SQLAlchemy operations (reducing repetition)

Question

I have some server side processing of some data (client-side library = jQuery DataTables)

I am using POST as my ajax method. In my Flask webapp, I can access the POST data with request.values

The data type / structure of request.values is werkzeug.datastructures.CombinedMultiDict

If the user wants to sort a column, the request contains a key called action with a value of filter (note the below printouts are obtained with for v in request.values: print v, request.values[v])

...
columns[7][data] role
columns[8][search][regex] false
action filter
columns[10][name] 
columns[3][search][value] 
...

all the column names are also contained in the request as keys. The columns that have search terms will have the search string as a value for the column name key (as opposed to empty for columns with no search term entered. So, If I want to search for firstname containing bill, I would see the following in my request

columns[7][searchable] true
...
columns[6][name] 
firstname bill
columns[0][search][value] 
columns[2][searchable] true
...
columns[5][data] phone
role 
columns[10][data] registered_on
...
columns[0][searchable] true
email 
columns[7][orderable] true
...
columns[2][search][value]

Notice how role and email are empty. So my code below is very non-DRY

rv = request.values
if rv.get('action') == 'filter':    
    if len(rv.get('firstname')):
        q = q.filter(User.firstname.ilike('%{0}%'.format(rv.get('firstname'))))
    if len(rv.get('lastname')):
        q = q.filter(User.lastname.ilike('%{0}%'.format(rv.get('lastname'))))
    if len(rv.get('username')):
        q = q.filter(User.username.ilike('%{0}%'.format(rv.get('username'))))
    if len(rv.get('email')):
        q = q.filter(User.email.ilike('%{0}%'.format(rv.get('email'))))
    if len(rv.get('phone')):
        q = q.filter(User.phone.ilike('%{0}%'.format(rv.get('phone'))))
    if len(rv.get('region')):
        q = q.filter(User.region.name.ilike('%{0}%'.format(rv.get('region'))))
    if len(rv.get('role')):
        q = q.filter(User.role.name.ilike('%{0}%'.format(rv.get('role'))))
    if len(rv.get('is_active')):
        q = q.filter(User.is_active_ == '{0}'.format(rv.get('is_active')))
    if len(rv.get('is_confirmed')):
        q = q.filter(User.is_confirmed == '{0}'.format(rv.get('is_confirmed')))
    if len(rv.get('registered_on_from')):
        fdate = datetime.strptime(rv.get('registered_on_from'), '%Y-%m-%d')
        q = q.filter(User.registered_on > fdate)
    if len(rv.get('registered_on_to')):
        tdate = datetime.strptime(rv.get('registered_on_to'), '%Y-%m-%d')
        q = q.filter(User.registered_on < tdate)

I was building the sorting functionality, and I found the following statement that greatly simplified my life (see this answer)

q = q.order_by('{name} {dir}'.format(name=sort_col_name, dir=sort_dir))

I was wondering if there was a way to simplify this set of filtering queries like the above sorting code since I will have to do this for many other models.

GG_Python · Accepted Answer · 2015-04-16T15:34:35.517

This should help:

from sqlalchemy import inspect
from sqlalchemy.sql.sqltypes import String,Boolean

def filter_model_by_request(qry,model,rv):
    if rv.get('action') == 'filter':
        mapper = inspect(model).attrs # model mapper
        col_names = list(set([c.key for c in mapper]) & set(rv.keys()))
        # col_names is a list generated by intersecting the request values and model column names
        for col_name in col_names:
            col = mapper[col_name].columns[0]
            col_type = type(col.type)
            if col_type == String: # filter for String
                qry = qry.filter(col.ilike('%{0}%'.format(rv.get(col_name))))
            elif col_type == Boolean: # filter for Boolean
                qry = qry.filter(col == '{0}'.format(rv.get(col_name)))
    return qry

Example call (I used it with a @app.before_request and a cURL call to verify):

qry = db.session.query(User)
print filter_model_by_request(qry,User,request.values).count()

The date range filtering is not included in the function, add this feature if you wish, your code is fine for that purpose.

side note: be careful with the bigger/smaller operators for the dates. You're excluding the actual requested dates. Use <= or >= to include dates in filtering action. It's always a pitfall for me..

cleaning up my SQLAlchemy operations (reducing repetition)

1 Answers1

Linked