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.
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' )
Base.metadata.create_all(engine)
Session = sessionmaker(bind = engine)
session = Session()
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" )])
session.add_all([parent1,parent2,parent3])
session.commit()
|
Parent & Child Tables
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
Python3
query = session.query(Parent). filter (
Parent.children. any (Child.name.in_([ "Ravi" , "Pinki" ])))
result = query(). all ()
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 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
Python3
query = session.query(Parent). filter (
Parent.children. any (Child.name.like( "Ra%" )))
result = likeQuery. all ()
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 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”
Python3
requried_names = [ "venkat" , "likitha" , "manasa" ]
parent_with_reqried_children = (
session.query(Parent)
.join(Child)
. filter (Child.name.in_(requried_names))
.distinct()
. 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 ()
result = set ()
for parent in parentData:
for child in parent.children:
if (child.name in requried_names):
result.add(parent)
for parent in result:
print (f "Parent: {parent.name}" )
session.close()
|
Output
Share your thoughts in the comments
Please Login to comment...