查詢
過(guò)濾
query.filter()
根據(jù)關(guān)鍵字過(guò)濾
query.filter_by()
返回列表
query.all()
返回第一個(gè)元素
query.first()
有且只有一個(gè)元素時(shí)才正確返回
query.one()
類似one透葛，但如果沒(méi)有找到結(jié)果粹胯，則不會(huì)引發(fā)錯(cuò)誤
query.one_or_none()
調(diào)用one方法烁巫，并在成功時(shí)返回行的第一列
query.scalar()
計(jì)數(shù)
query.count()
排序
query.order_by()
連接查詢
query.join()
以為字段名（列）設(shè)置別名
query(column.label())
查詢常用篩選器運(yùn)算符
# 等于
query.filter(User.name == 'zzy')
# 不等于
query.filter(User.name != 'zzy')
# like和ilike
query.filter(User.name.like('%zzy%'))
query.filter(User.name.ilike('%zzy%')) # 不區(qū)分大小寫
# in
query.filter(User.name.in_(['zzy', 'Judy', 'jack']))
query.filter(User.name.in_(
session.query(User.name).filter(User.name.like('%zzy%'))
))
# not in
query.filter(~User.name.in_(['zzy', 'Judy', 'jack']))
# is
query.filter(User.name == None)
query.filter(User.name.is_(None))
# is not
query.filter(User.name != None)
query.filter(User.name.is_not(None))
# and
from sqlalchemy import and_
query.filter(and_(User.name == 'zzy', User.fullname == 'ionc'))
query.filter(User.name == 'zzy', User.fullname == 'ionc')
query.filter(User.name == 'zzy').filter(User.fullname == 'ionc')
# or
from sqlalchemy import or_
query.filter(or_(User.name == 'zzy', User.name == 'Judy'))
# match
query.filter(User.name.match('Judy'))
使用文本SQL
from sqlalchemy import text
for user in session.query(User).\
filter(text("id<224")).\
order_by(text("id")).all():
print(user.name)
