Can I pass a dict to where() in SQLAlchemy?

Question

Rather than selecting rows by a static set of columns, e.g.

stmt = select(User).where(and_(User.first_name == 'foo', User.last_name == 'bar'))

I would like to pass a dict with a variable set of columns, e.g.

cols = {'User.first_name': 'foo', 
        'User.last_name': 'bar',
        'User.other_col': 'blah'}
stmt = select(User).where(cols)

Is something like this possible?

Answer 1

As I put in the comment, you can add to a statement after first defining it.

I feel a good example would be Flask, where you need to build a query based on inputs.

@app.route('/users', methods=['GET'])
def users_get():
    """Get matching users."""
    stmt = select(User)

    if request.args.get('username'):
        stmt = stmt.where(User.username == request.args['username'])

    if request.args.get('parent'):
        stmt = stmt.where(User.parent.has(User.username == request.args['parent']))

    if request.args.get('children'):
        children = request.args['children'].split(',')
        stmt = stmt.where(User.children.any(User.username.in_(children)))

    matching_users = session.query(stmt).scalars()

    return map(user_to_dict, matching_user)

Alternatively if you need to build up an OR statement, just build up a list and add it all at once:

stmt = select(User)

exprs = []
if a:
    exprs.append(User.username == a)
if b:
    exprs.append(User.username == b)
if where:
    stmt = stmt.where(or_(*exprs))

You said how you don't know the table structure until execution, depending on how you get the columns, perhaps something like this would work if you did getattrs?

stmt = select(User)

for k, v in get_column_values(User).items():
    stmt = stmt.where(getattr(User, k) == v)