SQLAlchemy Filter in List
Last Updated :
24 Apr, 2025
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.
Python3
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="Manasa")])
#adding data to DB
session.add_all([parent1,parent2,parent3])
#commiting chagnges
session.commit()
Parent & Child TablesFiltering 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
Python3
# query to fecth parand details having child Child1 or Child6
query = session.query(Parent).filter(
Parent.children.any(Child.name.in_(["Ravi", "Pinki"])))
# 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
Parents with child Ravi or PinkiExample 2: In the following Example we are retrieving data from parent table where child name start with Ra and having even id number
Python3
# query to get parent details where parent having child name start with Ra
query = session.query(Parent).filter(
Parent.children.any(Child.name.like("Ra%")))
result = likeQuery.all()
# printing the data of parent
for parent in result:
print(parent.id, parent.name)
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.
Python3
result = (
session.query(Parent)
.filter(
~exists()
.where(and_(
Child.parent_id == Parent.id,
~Child.name.like('%a')
))
)
.all()
)
print("parent with all children names ends with a")
for parent in result:
print({parent.name, parent.id})
session.close()
Output
Parents with all children names ends with aUsing 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"
Python3
requried_names = ["venkat", "likitha", "manasa"]
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
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.
Python3
requried_name={"Likitha","Manasa","Venkat"}
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"Parent: {parent.name}")
session.close()
Output
Similar Reads
SQLAlchemy Core - Joins
SQLAlchemy Core is a Python toolkit that enables developers to create complex database applications. It provides several features, one of which is the ability to join tables. Joining tables allows developers to retrieve data from multiple tables simultaneously, which is useful when the data is rela
3 min read
SQLAlchemy filter by json field
In this article, we will be discussing the SQLAlchemy filter by JSON field using Python. Before we begin the introduction, to the topic we will be discussing the basics of how to filter by JSON fields using a few examples and a brief introduction to SQLAlchemy, JSON, and JSON fields. Required Packag
5 min read
SQLAlchemy - Label
SQLAlchemy is a Python library used for working with relational databases. It provides an intuitive way to interact with databases and allows us to write database-independent code. In this article, we'll explore one of the powerful features of SQLAlchemy called label(), which is used to add labels t
4 min read
SQLAlchemy - Introduction
SQLAlchemy is basically referred to as the toolkit of Python SQL that provides developers with the flexibility of using the SQL database. The benefit of using this particular library is to allow Python developers to work with the language's own objects, and not write separate SQL queries. They can b
3 min read
Inserting NULL as default in SQLAlchemy
In this article, we will see how to insert NULL as default in SQLAlchemy in Python. The default value can be provided using the default parameter while defining the table (creating the table). We will be using mySQL local database for the examples in this article. Feel free to use any database you m
3 min read
SQLAlchemy Core - Set Operations
SQLAlchemy Core is a powerful tool for working with databases in Python. One of the key features of SQLAlchemy Core is its support for set operations, which allow you to perform complex queries on your data. In this article, we will explore the basics of set operations in SQLAlchemy Core and provide
7 min read
Python SQLAlchemy - func.count with filter
In this article, we are going to see how to perform filter operation with count function in SQLAlchemy against a PostgreSQL database in python Count with filter operations is performed in different methods using different functions. Such kinds of mathematical operations are database-dependent. In Po
3 min read
Python Falcon - SQLAlchemy Models
Python Falcon is an up-to-date web framework with an adjustable applicative architecture that is oriented to creating high-speed application processes and APIs. Another often implemented pattern is CRUD and interaction with the database can be facilitated with the help of SQLAlchemy for Python. When
5 min read
SQLAlchemy Core - Functions
SQLAlchemy provides a rich set of functions that can be used in SQL expressions to perform various operations and calculations on the data. SQLAlchemy provides the Function API to work with the SQL functions in a more flexible manner. The Function API is used to construct SQL expressions representin
7 min read
Floor division in SQLAlchemy
In this article, we will see how to perform floor division in SQLAlchemy against a PostgreSQL database in python. Floor division is performed in different methods using different functions. Such kinds of mathematical operations are database-dependent. In PostgreSQL, floor division is performed using
2 min read