Dynamically generate sqlalchemy filter according to the conditions

Overview

I saw the following implementation using sqlalchemy. It is a method that only makes the difference between narrowing down from the passed user list or narrowing down from the entire table. (Other conditions are exactly the same) I thought that it would be better to make it common when considering the correction, so I investigated the solution.

def get_specified_age_users_from_user_list(user_name_list={}, age):
    """
Get users of a specific age among the specified users from the User table
    """
    users = session.query(User).\
        filter(users.age==age).\
        filter(users.name.in_(user_name_list)).\
        all()
    return users

def get_specified_age_users(age):
    """
Get users of a specific age from the User table
    """
    users = session.query(User).\
        filter(users.age==age).\
        all()
    return users

solution

Pass the condition in tuple format to the ʻand_` method.

from sqlalchemy import and_

def get_specified_age_users(user_name_list={}, age):
    filters = []
    #Add condition only if arguments are passed
    if user_name_list:
        filters.append(User.name.in_(user_name_list))
    # and_The argument of is tuple because it needs to be tuple
    users = session.query(User).\
        filter(and_(*filters)).\
        all()
    return users

#SQL to be executed
##If specified
SELECT omitted
FROM users 
WHERE users.name IN (%s)

##If not specified
SELECT omitted
FROM users 

Recommended Posts

Dynamically generate sqlalchemy filter according to the conditions
Dot according to the image
Python constants like None (according to the reference)
To dynamically replace the next method in python
How to read dynamically generated table definitions using SQLAlchemy
[Blender] How to dynamically set the selection of EnumProperty
Display / update the graph according to the input with PySimpleGui
Coloring points according to the distance from the regression curve
Automatically select BGM according to the content of the conversation