python: flask-sqlalchemy combine filter_by and _in

Question

Following the example in this question I have a little flask view that filters data based on querystring arguments:

@app.route('/regs')
def regs_data():

    #check what data is available
    #REGS is a sqlalchemy model
    cols = REGS.__table__.columns.keys()

    kwargs = {}
    for k, v in request.args.lists():
        if k not in cols:
            abort(400, "helpful err msg")

        #takes only first occurrence
        kwargs[k]=v[0]

    my_query = REGS.query.filter_by(**kwargs).all()

    #....modify the query data for return

    return the_data_as_json

This works great for when there is only 1 occurence of each key in request.args. How can I extend this to cope with multiple values for each keyword? e.g /regs?foo=1&foo=2

Is there a way to apply both filter_by and in_()

Thanks

@RichArt using v instead of v[0] raises sqlalchemy.exc.InterfaceError — jprockbelly, Nov 18 '16 at 11:13
Python dictionaries don't support duplicate keys. May be the answer of NPE helps you: http://stackoverflow.com/questions/10664856/make-dictionary-with-duplicate-keys-in-python — RichArt, Nov 18 '16 at 11:32

score 0 · Answer 1 · answered Nov 20 '16 at 23:29

The best solution I could come up with is to iterate over each of the arguments and chain the filters together.

Not quite as neat but it works.

def regs_data():

    #get allowed data names
    cols = REGS.__table__.columns.keys()

    #make base query object
    my_query = REGS.query

    for k, v in request.args.lists():
        if k not in cols:
            abort(400, "helpful err msg")

        #chain together filters for each arg 
        my_query  = my_query.filter(getattr(REGS, k).in_(tuple(v)))

     #fire the query
     data = my_query.all()


    #....modify the query data for return

    return the_data_as_json

python: flask-sqlalchemy combine filter_by and _in

1 Answers1