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 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() |
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_([ "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
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( "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.
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
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 = [ "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.
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