Dynamic SQLAlchemy filter [duplicate]

Question

I am trying to write a dynamic filter for an API that uses SQLAlchemy (python). The front end will pass in optional arguments via query string to the backend and then the python backend should query the database with the filter values. Zero filters should return all rows of users in this case.

Generally: the front end will call my API endpoint with something like this

http://mybackend.com/api?first_name=John&last_name=Doe

The desired backend looks like this in my mind, but maybe this is not the best way to handle things.

def filter_results():
    filter_obj = {
        'first_name' : request.args.get('first_name', null, type=str),
        'last_name' : request.args.get('last_name', null, type=str),
        'city' : request.args.get('city', null, type=str),
        'country' : request.args.get('country', null, type=str),
        'phone' : request.args.get('phone', null, type=int)
    }
    query_string = ''
    for k, v in filter_obj.items():
        if v != null:
            query_string += f'{k} = {v}, '
    query_string.rstrip(', ')
    user_profile = Users.query.filter_by(query_string).all()
    response = users_schema.dump(user_profile)
    return jsonify(response)

I cannot figure out how to do this correctly and now I'm here.

I have tried playing with hardcoding a filter string like the following

filter_string = 'first_name = "John"'
Users.query.filter_by(filter_string).all()

and

from sqlalchemy import text
filter_string = 'first_name = "John"'
Users.query.filter_by(text(filter_string)).all()

But these do not work.

Any ideas on how to implement this would be appreciated.

Yes! That's even better than what I came up with. I just couldn't figure out how to google my problem! Much appreciated. — acarlseen
– acarlseen, Commented Jun 2, 2024 at 21:48

L Tyrone · Accepted Answer · 2024-06-02 23:33:22Z

0

I figured it out, wWat worked for me:

def filter_results():
    first_name = request.args.get('first_name', '%%', type=str),
    last_name = request.args.get('last_name', '%%', type=str),
    city = request.args.get('city', '%%', type=str),
    country = request.args.get('country', '%%', type=str),
    phone = request.args.get('phone', '%%', type=int)
    # the '%%' is a wildcard and will turn up all results in the column

    # using .ilike() ignores case, so 'foo' == 'Foo' == 'fOo', etc...
    filtered_users = Users.query.filter(Users.first_name.ilike(first_name),
        Users.last_name.ilike(last_name),
        Users.city.ilike(city),
        Users.country.ilike(country),
        Users.phone.ilike(phone)).all()
    response = users_schema.dump(filtered_users)
    return jsonify(response)

edited Jun 2, 2024 at 23:33

L Tyrone

8,36123 gold badges34 silver badges47 bronze badges

answered Jun 2, 2024 at 21:41

acarlseen

11 bronze badge

Sign up to request clarification or add additional context in comments.

1 Comment

ViAchKoN Over a year ago

You can try to use pypi.org/project/dataclass-sqlalchemy-mixins. You can just create a dict and apply to a query.

Collectives™ on Stack Overflow

Dynamic SQLAlchemy filter [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related