Use of filter function in SQLAlchemy

Table of Contents

filter filter data

Methods and usage examples

Basic filtering

Filtering by multiple conditions

fuzzy query

IN query

Null and non-null values

code demo

Code analysis

filter filter data

In SQLAlchemy, the filter method is used to filter data in queries to obtain records that meet specific conditions. This method allows you to construct WHERE clauses in SQL queries and combine multiple conditions to filter data.

Methods and usage examples

The following explains some commonly used filtering conditions, and these filtering conditions can only be achieved through the filter method:

Basic Filtering

Use the filter method to get records that meet specified criteria.

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine('mysql://user:password@localhost/dbname')
Session = sessionmaker(bind=engine)
session = Session()

# Query the user named 'John'
results = session.query(User).filter(User.name == 'John').all()

Filtering by Multiple Conditions

You can combine multiple conditions to filter data, using and_ and or_ to build complex conditional expressions.

from sqlalchemy import and_, or_

# Query users whose username is 'John' and whose age is greater than or equal to 30
results = session.query(User).filter(and_(User.name == 'John', User.age >= 30)).all()

# Query users whose username is 'John' or whose age is greater than or equal to 30
results = session.query(User).filter(or_(User.name == 'John', User.age >= 30)).all()

Fuzzy Query

Use the like method for fuzzy queries.

# Query users whose username starts with 'J'
results = session.query(User).filter(User.name.like('J%')).all()

IN Query

Use the in_ method to query whether a field is included in a set of values.

values = ['John', 'Alice', 'Bob']
results = session.query(User).filter(User.name.in_(values)).all()

Null and non-null values

Use the is_ method to query for null or non-null values.

# Query users without email addresses
results = session.query(User).filter(User.email.is_(None)).all()

# Query users with email addresses
results = session.query(User).filter(User.email.isnot(None)).all()

Code Demonstration

from random import randint
from uuid import uuid4


from sqlalchemy import Column,Integer,String,Float,Text,and_,or_


from db_util import Base,Session


class Article(Base):
  __tablename__ = 't_article'
  id = Column(Integer,primary_key=True,autoincrement=True)
  title = Column(String(50),nullable=False)
  price = Column(Float,nullable=False)
  content = Column(Text)
  
  def __repr__(self):
    return f"<Article(title:{self.title} price:{self.price} content:{self.content})>"


def create_data():
  with Session() as ses:
    for i in range(10):
      if i%2 == 0:
        art = Article(title = f'title{i + 1}',price=randint(1,100),content = uuid4())
      else:
        art = Article(title = f'TITLE{i + 1}',price=randint(1,100))
      ses.add(art)
    ses.commit()


def query_data():
  with Session() as ses:
    # rs = ses.query(Article).filter_by(id=1).first()
    rs = ses.query(Article).filter(Article.id == 1).first()
    print(rs)


def query_data_equal():
  with Session() as ses:
    rs = ses.query(Article).filter(Article.title == 'title2').first()
    print(rs)




def query_data_not_equal():
  with Session() as ses:
    rs = ses.query(Article).filter(Article.title != 'title2').all()
    print(rs)


def query_data_like():
  with Session() as ses:
    # select * from t_article where title like 'title%';
    rs = ses.query(Article).filter(Article.title.like('title%')).all()
    for r in rs:
      print(r)


def query_data_in():
  with Session() as ses:
    rs = ses.query(Article).filter(Article.title.in_(['title1','title3','title6'])).all()
    for r in rs:
      print(r)


def query_data_not_in():
  with Session() as ses:
    rs = ses.query(Article).filter(~ Article.title.in_(['title1','title3','title6'])).all()
    for r in rs:
      print(r)


def query_data_null():
  with Session() as ses:
    rs = ses.query(Article).filter(Article.content == None).all()
    for r in rs:
      print(r)


def query_data_not_null():
  with Session() as ses:
    rs = ses.query(Article).filter(Article.content != None).all()
    for r in rs:
      print(r)


def query_data_and():
  with Session() as ses:
    # rs = ses.query(Article).filter(Article.title !='title4' and Article.price >8 ).all()
    # rs = ses.query(Article).filter(Article.title !='title4',Article.price >50 ).all()
    rs = ses.query(Article).filter(and_(Article.title !='title4',Article.price >50) ).all()
    for r in rs:
      print(r)


def query_data_or():
  with Session() as ses:
    rs = ses.query(Article).filter(or_(Article.title =='title4',Article.price >50) ).all()
    for r in rs:
      print(r)


if __name__ == '__main__':
  # Base.metadata.create_all()
  #create_data()
  # query_data()
  # query_data_equal()
  # query_data_not_equal()
  # query_data_like()
  # query_data_in()
  # query_data_not_in()
  # query_data_null()
  # query_data_not_null()
  # query_data_and()
  query_data_or()

Code Analysis

Article model class definition:

You define a model class named Article, which maps to the t_article table in the database, including id, title, price and content four fields.

create_data function:

This function inserts some sample data into the database, including title, price, and content. Among them, the naming rule of the title is title{i + 1} or TITLE{i + 1}, the price is a random integer, and the content uses uuid4( ) generated.

query_data function:

This function queries for records with id being 1.

query_data_equal function:

This function queries the first record with a title equal to 'title2'.

query_data_not_equal function:

This function queries all records whose title is not equal to 'title2'.

query_data_like function:

This function queries all records whose title begins with 'title'.

query_data_in function:

This function queries all records with titles in ['title1','title3','title6'].

query_data_not_in function:

This function queries all records whose titles are not in ['title1','title3','title6'].

query_data_null function:

This function queries all records with content None.

query_data_not_null function:

This function queries all records whose content is not None.

query_data_and function:

This function queries all records whose title is not equal to 'title4' and whose price is greater than 50.

query_data_or function:

This function queries all records with a title equal to 'title4' or a price greater than 50.