[Python] SQLAlchemy query with filters

程式語言：Python

Package：

sqlalchemy

官網

功能：各種 filter() 可使用的條件

使用範例

query.filter(User.name == 'zWind')
query.filter(User.name != 'zWind')
query.filter(User.name.like('%zWind%'))
query.filter(User.name.ilike('%zWind%'))

Filter Method

• has for to one relationship
  

  query.filter(User.mother.has(User.name == 'Alice'))

• any for to many relationship
  

  query.filter(User.phones.any(Phone.country == 'Taiwan'))
  

• extract
  

  from sqlalchemy import extract
  query.filter(extract("year", User.birthday) == 1996)

• equals
  

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

• not equals
  

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

• bigger than
  

  query.filter(User.age > 50)

  query.filter(User.age >= 50)

• less than
  

  query.filter(User.age < 20)

  query.filter(User.age <= 20)

• between
  

  query.filter(User.age.between(10,30))

• like
  

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

• not like
  

  # query.filter(~User.name.like('%zWind%'))
  query.filter(User.name.notlike('%zWind%'))
  

• ilike (case-insensitive like)
  

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

• not ilike
  

  # query.filter(~User.name.ilike('%zWind%'))
  query.filter(User.name.notilike('%zWind%'))
  

• contains
  

  # 等同 query.filter(User.name.like('%zWind%'))
  query.filter(User.name.contains('zWind'))
  

• startswith
  

  query.filter(User.name.startswith('zW'))

• endswith
  

  query.filter(User.name.endswith('Wind'))

• in
  

  query.filter(User.name.in_(['zWind', 'Alice', 'Fred']))
  

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

  from sqlalchemy import tuple_
  query.filter(tuple_(User.name, User.nickname).in_([('zWindr', 'z'), ('Alice', 'A')]))
  

• not in
  

  # query.filter(~User.name.in_(['zWind', 'Alice', 'Fred']))
  query.filter(User.name.notin_(['zWind', 'Alice', 'Fred']))
  

• is
  

  # query.filter(User.name == None)
  query.filter(User.name.is_(None))

• is not
  

  # query.filter(User.name != None)
  query.filter(User.name.isnot(None))

• and
  

  from sqlalchemy import and_
  query.filter(and_(User.name == 'zWind', User.fullname == 'zz zWind'))
  

  query.filter(User.name == 'zWind', User.fullname == 'zz zWind')
  

  query.filter(User.name == 'zWind').filter(User.fullname == 'zz zWind')
  

• or
  

  from sqlalchemy import or_
  query.filter(or_(User.name == 'zWind', User.name == 'Alice'))
  

• match (不是每個資料庫都支援，例：SQLite 不支援)
  

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

• all (資料庫支援 arraytype or subquery，例：postgresql, mysql)
  

  # postgresql
  query.filter(User.addresses.all_() == 'Taiwan')
  

  # mysql
  query.filter(User.addresses.as_scalar().all_() == 'Taiwan')
  

• any (資料庫支援 arraytype or subquery，例：postgresql, mysql)
  

  # postgresql
  query.filter(User.addresses.any_() == 'Taiwan')
  

  # mysql
  query.filter(User.addresses.as_scalar().any_() == 'Taiwan')
  

• operator
  

  query.filter(User.age.op("*")(10) == 100)

Order Method

• asc 遞增
  

  query.order_by(User.age.asc())

  query.order_by(User.age.asc(), User.height.asc())

• desc 遞減
  

  query.order_by(User.age.desc())

  query.order_by(User.age.desc(), User.height.desc())

• NULLS LAST for ORDER (資料庫不見得支援)
  

  query.order_by(User.age.desc().nullslast())

• NULLS FIRST for ORDER (資料庫不見得支援)
  

  query.order_by(User.age.desc().nullsfirst())

參考

SQLAlchemy query with OR/AND/like common filters
Common Filter Operators
Column Elements and Expressions
列元素和表達式