11

I've been trying to figure out how to pass the request.args to sqlalchemy filter.

I thought this should work:

model.query.filter(**request.args).all()

But it's throwing the error:

TypeError: <lambda>() got an unexpected keyword argument 'userid'

When userid or any other get arg is present.

According to this post - https://stackoverflow.com/questions/19506105/flask-sqlalchemy-query-with-keyword-as-variable - you can pass a dict to the filter function.

Any ideas what I'm doing wrong?

Many thanks :)

UPDATE: Many thanks to the poster below, however now it's throwing the following error: 

ProgrammingError: (ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ORDER BY tblclients.clientname' at line 3") 'SELECT favourites.id AS favourites_id, favourites.userid AS favourites_userid, favourites.clientid AS favourites_clientid, favourites.last_visit AS favourites_last_visit \nFROM favourites INNER JOIN tblclients ON tblclients.clientid = favourites.clientid \nWHERE favourites.userid = %s ORDER BY tblclients.clientname' ([u'41'],)

Any ideas?

Community
  • 1
  • 1
Ben Kilah
  • 3,445
  • 9
  • 44
  • 56

4 Answers4

19

First, you have to use filter_by, not filter.

Second, Flask request.args uses a MultiDict, a dict with the values inside a list, allowing more than one value for the same key, because the same field can appear more than once in a querystring. You got the error because the SQL query got the [u'41'] when it expected only '41'. You can use request.args.to_dict() to fix that:

model.query.filter_by(**request.args.to_dict()).all()
Pedro Werneck
  • 40,902
  • 7
  • 64
  • 85
  • 4
    This is the correct answer. And to add to it: using `request.args.to_dict()` is the correct while `dict(request.args)` is not because it will produce an dictionary whose values consist of lists. – Red-Tune-84 May 18 '17 at 18:26
4

Use filter_by:

model.query.filter_by(**request.args).all()

filter is used like this: query.filter(Class.property == value) while filter_by is used like this: query.filter_by(property=value) (the first one being an expression and the latter one being a keyword argument).

javex
  • 7,198
  • 7
  • 41
  • 60
2

filter_by(**request.args) doesn't work well if you have non-model query parameters, like page for pagination, otherwise you get errors like these:

InvalidRequestError: Entity '<class 'flask_sqlalchemy.JobSerializable'>' has no property 'page'

I use something like this which ignores query parameters not in the model:

    builder = MyModel.query
    for key in request.args:
        if hasattr(MyModel, key):
            vals = request.args.getlist(key) # one or many
            builder = builder.filter(getattr(MyModel, key).in_(vals))
    if not 'page' in request.args:
        resources = builder.all()
    else:
        resources = builder.paginate(
            int(request.args['page'])).items

Considering a model with a column called valid, something like this will work:

curl -XGET "http://0.0.0.0/mymodel_endpoint?page=1&valid=2&invalid=whatever&valid=1"

invalid will be ignored, and page is available for pagination and best of all, the following SQL will be generated: WHERE mymodel.valid in (1,2)

(get the above snippet for free if you use this boilerplate-saving module)

opyate
  • 5,388
  • 1
  • 37
  • 64
1

You can:

http://localhost:5000/filter-test?var=test

query_dict = request.args.to_dict()

print(query_dict)
{'var': 'test'}

print(query_dict['var'])
var
eike
  • 21
  • 2
  • While this code may solve the question, [including an explanation](//meta.stackexchange.com/q/114762) of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please [edit] your answer to add explanations and give an indication of what limitations and assumptions apply. – Yunnosch Oct 03 '21 at 14:36