SQLAlchemy query with common filters

2 min readApr 28, 2021

Usage filter

Some of the most common operators used in filter() method SQLAlchemy Assume class model name Post as field below

class Post(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    title = db.Column(db.String(100))
    content = db.Column(db.Text)
    author = db.Column(db.Text)

all

Post.query.all()

equal

Post.query.filter(Post.author == 'Robert')

find exist record

Post.query.filter(Post.author == 'James').first()

not equals

Post.query.filter(Post.author != 'James')

LIKE

Post.query.filter(Post.title.like('%Getting started%'))

iLIKE

The query that selects all posts, case insensitive, that have titles that match '%' + some_phrase + '%'. That is, select all rows that have titles that contain some phrase, case insensitive.

Post.query.filter(Post.title.ilike('%Lesson%'))

IN

Post.query.filter(Post.author.in_(['John', 'James', 'Robert']))

NOT IN:

Post.query.filter(~Post.author.in_(['Lee', 'James', 'Robert']))

IS NULL:

Post.query.filter(Post.author == None)

IS NOT NULL:

Post.query.filter(Post.author != None)

AND:

from sqlalchemy import and_Post.query.filter(and_(Post.author == 'James', Post.id == 1))

You can write different style with comma separated

Post.query.filter(Post.author == 'James', Post.id == 1)

or call filter() multiple time

Post.query.filter(Post.author == 'James').\
           filter(User.id.in_([1,2,3])

OR:

from sqlalchemy import or_
Post.query.filter(
      or_(Post.author == 'James', Post.author == 'Robert')
  )

match:

Post.query.filter(Post.title.match('python lesson'))

I’m sure, the information above will be useful for you. But there are hundreds of other creative people whom it might be useful for as well. So Clap! Clap! :)👏🏼

I usually post on Twitter before publishing on Medium, so if you want to stay updated, follow me on

Twitter/X at https://x.com/rimsovankiry

if you interested for free money (airdrop reward), passive income