SQLAlchemy Filter in List

Last Updated : 17 Oct, 2023

SQLAlchemy is a Python's powerful Object-Relational Mapper that, provides flexibility to work with Relational Databases. SQLAlchemy provides a rich set of functions that can be used in SQL expressions to perform various operations and calculations on the data using Python. In SQLAlchemy, we can filter on a list attribute using any, exists, joins, and loops.

Prerequisites:

Table Creation in Database

In this section, we are making a connection with the database, we are creating the structure of two tables with parent-child relationships. Also, we are inserting data in both tables.

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Parent(Base):
    __tablename__ = 'parent'

    id = Column(Integer, primary_key=True)
    name = Column(String(length=30))
    
    children = relationship('Child')

class Child(Base):
    __tablename__ = 'children'

    id = Column(Integer, primary_key=True)
    name = Column(String(length=30))
    parent_id = Column(Integer, ForeignKey('parent.id'))

    parent = relationship('Parent')

#create engine for MySql DB
engine = create_engine('mysql+pymysql://user_name:password@host:port/db_name')
Base.metadata.create_all(engine)

# Create a session
Session = sessionmaker(bind=engine)
session = Session()

#Create Parent objects and child objects
parent1 = Parent(name='Ramesh', children=[Child(name='Pinki'), Child(name='Venkat')])
parent2 = Parent(name='Sharadha', children=[Child(name='Rama'),Child(name='Nikitha')])
parent3=Parent(name='Parent3',children=[Child(name='Ravi'),Child(name='Likitha'),Child(name=&quot;Manasa&quot;)])

#adding data to DB
session.add_all([parent1,parent2,parent3])

#commiting chagnges
session.commit()

Filtering a list attribute using SQLAlchemy

Using any()

The any() method in SQLAlchemy is used to check if any element in a collection or list of elements matches a certain condition or not. It is used as part of a query expression to filter records based on conditions.

Syntax:  list_object.any(conditions)

Example: In the following example we are retrieving the data of parents having the child Ravi or Pinki

# query to fecth parand details having child Child1 or Child6
query = session.query(Parent).filter(
    Parent.children.any(Child.name.in_([&quot;Ravi&quot;, &quot;Pinki&quot;])))

# fetching the data from DB
result = query().all()

# printing the data
for data in query:
    print(data.id, data.name)
    for child in data.children:
        print(child.id, child.name)
    print()

Output

Ravi&Piniki — Parents with child Ravi or Pinki

Example 2: In the following Example we are retrieving data from parent table where child name start with Ra and having even id number

# query to get parent details where parent having child name start with Ra
query = session.query(Parent).filter(
    Parent.children.any(Child.name.like(&quot;Ra%&quot;)))

result = likeQuery.all()

# printing the data of parent
for parent in result:
    print(parent.id, parent.name)

FilterLike

Using exists()

The exists() method helps us to check if a subquery produces any results or not. It gives us a Boolean value, either True or False depending on whether the subquery has any rows, in its result set. In the following example we are retrieving the parent details whose all children names ends with "a".

Example: In the above code, we have used the filter method to apply a filter condition to the parent selection. Inside the filter, there is a subquery created using exists(). This subquery checks if there exists at least one child associated with a parent whose name does not end with 'a'. The subquery uses and_() to combine two conditions:

Child.parent_id == Parent.id ensures that the child is associated with the parent being considered.
~Child.name.like('%a') checks if the child's name does not end with 'a'. The ~ operator is used for negation here.

result = (
    session.query(Parent)
    .filter(
        ~exists()
        .where(and_(
            Child.parent_id == Parent.id,
            ~Child.name.like('%a')
        ))
    )
    .all()
)
print(&quot;parent with all children names ends with a&quot;)

for parent in result:
    print({parent.name, parent.id})

session.close()

Output

ExistsParent — Parents with all children names ends with a

Using Joins

We can apply filters on list attributes using joins. Please refer SQLAlchemy Core Join for knowing more about Joins in Sqlalchemy.

Example: In the following example we are retrieving the parent details where parent with child names "Venkat","likitha" or "manasa"

requried_names = [&quot;venkat&quot;, &quot;likitha&quot;, &quot;manasa&quot;]

parent_with_reqried_children = (
    session.query(Parent)
    .join(Child)  # Join to access child names
    .filter(Child.name.in_(requried_names))  # Filter by desired child names
    .distinct()  # Ensure distinct parent records
    .all()
)

for parent in parent_with_reqried_children:
    print(parent.name,parent.id)
    
session.close()

Output

JoinFilter

Using Loop

LOOP statement could be used to run a block of code or set of statements, again and again, depending on the condition.We can filter list attributes using loops also.

Example: In the following example we first retrieve the all parent details from using query() and then we iterate over the parent data and check if parent children names parent is in required names or not.

requried_name={&quot;Likitha&quot;,&quot;Manasa&quot;,&quot;Venkat&quot;}

parentData = session.query(Parent).all()

# Iterate through parents and check if any child has the desired names
result = set()
for parent in parentData:
    for child in parent.children:
        if(child.name in requried_names):
            result.add(parent)
# Print the details of parents with children having the desired names
for parent in result:
    print(f&quot;Parent: {parent.name}&quot;)

session.close()