SQL Alchemy filter和filter_by

参考：https://docs.sqlalchemy.org/en/14/orm/tutorial.html#common-filter-operators

filter() 常用操作

 

Here’s a rundown of some of the most common operators used in filter():

• • ColumnOperators.__eq__():

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

  • ColumnOperators.__ne__():

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

  • ColumnOperators.like():

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

Note

ColumnOperators.like() renders the LIKE operator, which is case insensitive on some backends, and case sensitive on others. For guaranteed case-insensitive comparisons, use ColumnOperators.ilike().

• • ColumnOperators.ilike() (case-insensitive LIKE):

    query.filter(User.name.ilike('%ed%'))

Note

most backends don’t support ILIKE directly. For those, the ColumnOperators.ilike() operator renders an expression combining LIKE with the LOWER SQL function applied to each operand.

• • ColumnOperators.in_():

    query.filter(User.name.in_(['ed', 'wendy', 'jack']))
    
    # works with query objects too:
    query.filter(User.name.in_(
        session.query(User.name).filter(User.name.like('%ed%'))
    ))
    
    # use tuple_() for composite (multi-column) queries
    from sqlalchemy import tuple_
    query.filter(
        tuple_(User.name, User.nickname).\
        in_([('ed', 'edsnickname'), ('wendy', 'windy')])
    )

  • ColumnOperators.not_in():

    query.filter(~User.name.in_(['ed', 'wendy', 'jack']))

  • ColumnOperators.is_():

    query.filter(User.name == None)
    
    # alternatively, if pep8/linters are a concern
    query.filter(User.name.is_(None))

  • ColumnOperators.is_not():

    query.filter(User.name != None)
    
    # alternatively, if pep8/linters are a concern
    query.filter(User.name.is_not(None))

  • AND:

    # use and_()
    from sqlalchemy import and_
    query.filter(and_(User.name == 'ed', User.fullname == 'Ed Jones'))
    
    # or send multiple expressions to .filter()
    query.filter(User.name == 'ed', User.fullname == 'Ed Jones')
    
    # or chain multiple filter()/filter_by() calls
    query.filter(User.name == 'ed').filter(User.fullname == 'Ed Jones')

Note

Make sure you use and_() and not the Python and operator!

• • OR:

    from sqlalchemy import or_
    query.filter(or_(User.name == 'ed', User.name == 'wendy'))

Note

Make sure you use or_() and not the Python or operator!

filter_by() 相对用法较少

 query.filter_by(name='jack', age='18')