SQLAlchemy 一些常用查询语句

Equals:

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

NOT Equals:

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

LIKE:

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

IN:

query.filter(User.name.in_(['Ann', 'Cheryl', 'Robert']))

# works with query objects too:

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

NOT IN:

query.filter(~User.name.in_(['Ann', 'Robert', 'Cheryl']))

IS NULL:

filter(User.name == None)

IS NOT NULL:

filter(User.name != None)

AND:

from sqlalchemy import and_
filter(and_(User.name == 'Ann', User.fullname == 'Ann Garza'))

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

filter(User.name == 'Ann', User.fullname == 'Ann Garza')

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

filter(User.name == 'Ann').filter(User.fullname == 'Ann Garza')

OR:

from sqlalchemy import or_
filter(or_(User.name == 'Ann', User.name == 'Robert'))

Match:

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