TOC
数据查询
之前对于SqlAlchemy的数据新增、数据删除、数据修改已经做了一个基本的介绍，那么接下来就是数据查询了，查询也是一个关系型数据库最为复杂的一项SQL类型，因为表表之间，字段和字段之间关系极为复杂，我们的需求也是根据业务的变化而变化，因此查询的语法也就变得变化多端；
创建测试数据
那么下面为了实现基本的查询，我们就创建了一个名为users的表，作为查询示例表，并向该表提交五条数据，如下；
from sqlalchemy import Column, String, Integer, Enum, DATE, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
import datetime
engine = create_engine("mysql+pymysql://cce:caichangen@47.104.197.46/cce?charset=utf8", encoding='utf-8', echo=False)
Session = sessionmaker(bind=engine)
session = Session()
BaseModel = declarative_base()
class Users(BaseModel):
__tablename__ = "users"
id = Column(Integer, primary_key=True)
name = Column(String(14), nullable=False)
gender = Column(Enum("M", "F"), nullable=False)
age = Column(String(12), nullable=False)
create_time = Column(DATE, nullable=False, default=datetime.datetime.now().date())
def __repr__(self):
return "<%s %s %s>"%(__class__.__name__,self.id,self.name)
BaseModel.metadata.drop_all(engine)
BaseModel.metadata.create_all(engine)
# 创建测试数据
session.add_all([Users(name="cce", gender="M", age=26),
Users(name="csw", gender="M", age=49),
Users(name="cfj", gender="F", age=14),
Users(name="dxf", gender="F", age=48),
Users(name="yml", gender="F", age=22)])
session.commit()
session.close()
消费者方法
起初，我们在使用session的query方法时，返回的是一个惰性对象，我们想要拿到里面数据，还必须得迭代才行，那么SqlAlchemy也提供了相关方法，直接能够取得这个惰性对象里面的数据；
all():从惰性对象里面取得所有数据；
get():从惰性对象里面获取表里面指定主键的数据；
count():求这个惰性对象里面包含数据的长度，它内部的机制就是count(*)；
first():从这个惰性对象里面获取第一个，其实它内部的机制就是first()；
one()：从惰性对象里面拿到数据，它要求这个惰性对象只有一条数据，多一行少一行都会抛出异常；
scalar()：scalar方法其实就是one方法的一个升级，它仅在使用sqlalchemy.func时才有用，因为one返回了一个列表对象，列表里面的元素是一个元祖，而scalar则是直接取出了这个元素，等价于ont()[0]；
- 注意：
虽然说，query()方法返回的是一个惰性对象，但是我们需要知道的是，其实数据已经返回了，并不是我们理解的，query()方法并不会去数据库中真正的执行这条SQL；
基本查询
基本查询代表的是单表查询，在一个表内进行简单的过滤、聚合、分组等操作，单表查询还是比较简单的，他没有太过于复杂的关系查询，更多的是一些判断性的操作，如大于小雨，包含不包含，模糊匹配等；
全表查询
可以看到，我们使用query查询时，将我们的映射类直接传递进去，就默认查询的是所有数据，可以看到我们的打印结果，默认是name，这说明是说明呢，说明调用了我们映射类的__repr__()方法，这说明什么呢，说明每条迭代出来的对象都是映射类的实例；
这就足以说明对象关系映射了，SqlAlchemy将数据库返回的每条数据，都封装成了一个一个的实例对象，这就是对象和关系映射起来了；
# 查询指定表的全部数据
print(session.query(Users).all()) # [<Users 1 cce>, <Users 2 csw>, <Users 3 cfj>, <Users 4 dxf>, <Users 5 yml>]
基本过滤查询
基本的过滤查询就是单表查询，它不涉及复杂的多表查询，也不涉及到外键查询等，相对于还是比较简单的一些查询语法，它和我们的MySQL查询语法差不多，大差不差，SqlAlchemy给我们包装成Python所支持的语法，使用起来也比较简单，如下；
等价判断
等价判断，就是我们常用的一些大于、小于、等于和不等于判断，这种判断可以说是在日常开发中使用最为广泛的判断语法，使用也比较简单，和我们的编程所使用的判断是一样的；
# 大于
print(session.query(Users).filter(Users.id > 3).all()) # [<Users 4 dxf>, <Users 5 yml>]
# 小于
print(session.query(Users).filter(Users.id < 3).all()) # [<Users 1 cce>, <Users 2 csw>]
# 等于
print(session.query(Users).filter(Users.id == 3).all()) # [<Users 3 cfj>]
# 不等于
print(session.query(Users).filter(Users.id != 3).all()) # [<Users 1 cce>, <Users 2 csw>, <Users 4 dxf>, <Users 5 yml>]
与或非判断
与或非判断，这里的与或非判断的实现方式就有很多种了，甚至连官方都没有收集全，主要看个人编码偏好，任选其一即可，没有什么好与不好之说；
# 导入函数from sqlalchemy import and_,or_,not_
# and一
print(session.query(Users).filter(Users.id > 3,Users.id < 5).all()) # [<Users 4 dxf>]
# and二
print(session.query(Users).filter(Users.id > 3).filter(Users.id < 5).all()) # [<Users 4 dxf>]
# and三
print(session.query(Users).filter(and_(Users.id > 3,Users.id < 5)).all()) # [<Users 4 dxf>]
# and四
print(session.query(Users).filter((Users.id > 3) & (Users.id < 5)).all()) # [<Users 4 dxf>]
# or一
print(session.query(Users).filter(or_(Users.id == 3,Users.id == 5)).all()) # [<Users 3 cfj>, <Users 5 yml>]
# or二
print(session.query(Users).filter((Users.id == 3) | (Users.id == 5)).all()) # [<Users 3 cfj>, <Users 5 yml>]
# not一
print(session.query(Users).filter(~(Users.id == 5)).all()) # [<Users 1 cce>, <Users 2 csw>, <Users 3 cfj>, <Users 4 dxf>]
# not二
print(session.query(Users).filter(not_(Users.id == 5)).all()) # [<Users 1 cce>, <Users 2 csw>, <Users 3 cfj>, <Users 4 dxf>]
成员判断
成员判断，它实际上就是判断指定字段是否在我们提供的可迭代对象里面，它对应的就是我们MySQL中的in和not in，使用也较为广泛；
# in
print(session.query(Users).filter(Users.id.in_([1,2,3])).all()) # [<Users 1 cce>, <Users 2 csw>, <Users 3 cfj>]
# notin
print(session.query(Users).filter(Users.id.notin_([1,2,3])).all()) # [<Users 4 dxf>, <Users 5 yml>]
模糊匹配
模糊匹配，对应MySQL中的like和not like，一般不推荐全模糊匹配，因为它不会走索引，尽量做左前方匹配，模糊匹配使用的特殊字符和MySQL中的一样，使用"%"代表任意值；
# like
print(session.query(Users).filter(Users.name.like("c%")).all()) # [<Users 1 cce>, <Users 2 csw>, <Users 3 cfj>]
# notlike
print(session.query(Users).filter(Users.name.notlike("c%")).all()) # [<Users 4 dxf>, <Users 5 yml>]
排序
排序，和MySQL中的排序没什么不一样，数据想要进行倒序首先得排序，所以排序的第一阶段就是order_by，默认升序；
# 使用order_by(排序字段)实现排序，默认升序
print(session.query(Users).filter(Users.id < 3).order_by(Users.id).all()) # [<Users 2 csw>, <Users 1 cce>]
# 倒序
print(session.query(Users).filter(Users.id < 3).order_by(Users.id.desc()).all()) # [<Users 2 csw>, <Users 1 cce>]
# 升序
print(session.query(Users).filter(Users.id < 3).order_by(Users.id.asc()).all()) # [<Users 2 csw>, <Users 1 cce>]
限制和偏移
限制和偏移就是我们常在分页里面使用的limit和offset，limit就是限制只获取几条数据，而offset偏移就是向右移动多少条数据开始，所以这也是实现分页的最好方式之一；
# limit
print(session.query(Users).limit(2).all()) # [<Users 1 cce>, <Users 2 csw>]
# offset
print(session.query(Users).offset(2).all()) # [<Users 3 cfj>, <Users 4 dxf>, <Users 5 yml>]
# 实现分页，每页条数据
print(session.query(Users).limit(2).offset(0).all()) # 第一页，偏移0个限制2个
print(session.query(Users).limit(2).offset(2).all()) # 第二页，偏移2个限制2个
print(session.query(Users).limit(2).offset(4).all()) # 第三页，偏移4个限制2个
聚合查询
聚合查询，对于聚合查询来讲，sqlalchemy为我们提供了一个func方法，它专用于聚合查询，我们常用的count、sum、max、min、avg等聚合函数，func都提供了；
对于count方法，虽然我们的session对象给我们提供了一个可用的count方法，但是这个count方法存在子查询，性能可想而知是非常低的；
from sqlalchemy import func
# 求count
print(session.query(func.count(Users.id)).first()) # (5,)
# 求sum
print(session.query(func.sum(Users.id)).first()) # (Decimal('15'),)
# 求max
print(session.query(func.max(Users.id)).first()) # (5,)
# 求min
print(session.query(func.min(Users.id)).first()) # (1,)
# 求avg
print(session.query(func.avg(Users.age)).first()) # (31.8,)
分组查询
分组查询，顾名思义，对指定的字段进行分组，相同的为一组，使用SqlAlchemy进行分组查询也非常简单，直接使用惰性对象的group_by方法即可；
# 结果是两组
print(session.query(Users.gender).group_by(Users.gender).all()) # [('M',), ('F',)]
# 借助having对分组进行条件过滤
print(session.query(Users,Users.gender).group_by(Users.gender).having(Users.id > 1).all()) # [(<Users 3 cfj>, 'F')]
排序扩展
对于排序来讲，我们也可以在定义模型时，使用__mapper_args__来指定一个默认的排序字段，如下，指定了__mapper_args__之后，默认使用id字段的倒序来排序；
from sqlalchemy import Column, String, Integer, Enum, DATE, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
import datetime
engine = create_engine("mysql+pymysql://cce:caichangen@192.168.1.134/cce?charset=utf8", encoding='utf-8', echo=False)
Session = sessionmaker(bind=engine)
session = Session()
BaseModel = declarative_base()
class Users(BaseModel):
__tablename__ = "users"
id = Column(Integer, primary_key=True)
name = Column(String(14), nullable=False)
gender = Column(Enum("M", "F"), nullable=False)
age = Column(String(12), nullable=False)
create_time = Column(DATE, nullable=False, default=datetime.datetime.now().date())
__mapper_args__ = {
"order_by": id.desc() # numbers:升序 numbers.desc()：降序
}
def __repr__(self):
return "<%s %s %s>"%(__class__.__name__,self.id,self.name)
BaseModel.metadata.drop_all(engine)
BaseModel.metadata.create_all(engine)
# 创建测试数据
session.add_all([Users(name="cce", gender="M", age=26),
Users(name="csw", gender="M", age=49),
Users(name="cfj", gender="F", age=14),
Users(name="dxf", gender="F", age=48),
Users(name="yml", gender="F", age=22)])
session.commit()
# 测试排序
print(session.query(Users).all()) # [<Users 5 yml>, <Users 4 dxf>, <Users 3 cfj>, <Users 2 csw>, <Users 1 cce>]
# 关闭session
session.close()
