SQLAlchemy query with OR/AND/like common filters

equals

query.filter(User.name == 'leela')

not equals

query.filter(User.name != 'leela')

like

query.filter(User.name.like('%leela%'))

in

query.filter(User.name.in_(['leela', 'akshay', 'santanu']))

# works with query objects too:

query.filter(User.name.in_(session.query(User.name).filter(User.name.like('%santanu%'))))

not in

query.filter(~User.name.in_(['lee', 'sonal', 'akshay']))

is null

filter(User.name == None)

is not null

filter(User.name != None)

and

from sqlalchemy import and_
filter(and_(User.name == 'leela', User.fullname == 'leela dharan'))

#or, default without and_ method comma separated list of conditions are AND

filter(User.name == 'leela', User.fullname == 'leela dharan')

# or call filter()/filter_by() multiple times

filter(User.name == 'leela').filter(User.fullname == 'leela dharan')

or

from sqlalchemy import or_
filter(or_(User.name == 'leela', User.name == 'akshay'))

match

query.filter(User.name.match('leela'))

between

时间范围查询 方法一：

User.query.filter(User.column.between(start_dt, stop_dt)).all()

方法二：

User.query.filter(and_(User.column> start_dt, User.column <= stop_dt)).all()