Python ORM之SQLAlchemy查询笔记

查询

过滤

query.filter()

根据关键字过滤

query.filter_by()

返回列表

query.all()

返回第一个元素

query.first()

有且只有一个元素时才正确返回

query.one()

类似one，但如果没有找到结果，则不会引发错误

query.one_or_none()

调用one方法，并在成功时返回行的第一列

query.scalar()

计数

query.count()

排序

query.order_by()

连接查询

query.join()

以为字段名(列)设置别名

query(column.label())

查询常用筛选器运算符

# 等于
query.filter(User.name == 'zzy')
 
# 不等于
query.filter(User.name != 'zzy')
 
# like和ilike
query.filter(User.name.like('%zzy%'))
query.filter(User.name.ilike('%zzy%')) # 不区分大小写
 
# 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)