flask之 sqlalchemy 高级查询

1、filter_by写条件 

session.query(Users).filter_by(name='lqz').all()

2、between 、in_

session.query(Users).filter(Users.id.between(1, 10)).all()

ret = session.query(Users).filter(Users.id.in_([1, 4, 5])).all()

3、~非，除...外

session.query(Users).filter(~Users.id.in_([1, 3, 5])).all()

4、二次筛选

session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='lqz'))).all()

5、and_、or_ 条件

from sqlalchemy import and_, or_

# ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'zhangsan')).all()
ret = session.query(Users).filter(Users.id > 3, Users.name == 'lqz').all()  # 根上面一样

# select * from users where id<=2 or name =lqz;
ret = session.query(Users).filter(or_(Users.id <= 2, Users.name == 'zhangsan')).all()

# select * from users where id <2 or (name=lqz and id>3) or extra !='';
ret = session.query(Users).filter(
    or_(
        Users.id < 2,
        and_(Users.name == 'zhangsan', Users.id > 3),
        Users.extra != ""
    )).all()　

6、like

# # 通配符，以e开头，不以e开头
# select * from users where name like l%;
ret = session.query(Users).filter(Users.name.like('z%')).all()
ret = session.query(Users).filter(~Users.name.like('l%')).all()

7、限制、分页

ret = session.query(Users)[1:2] 

8、升序、降序

# ret = session.query(Users).order_by(Users.id.desc()).all()  # 降序
# ret = session.query(Users).order_by(Users.id.asc()).all()  # 升序
ret = session.query(Users).order_by(Users.id).all()  # order_by 默认就是升序排序

# 第一个条件重复后，再按第二个条件升序排
ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all()

9、分组

from sqlalchemy.sql import func
# select name from users group by name;  一旦分组,只能查询 分组字段和聚合函数的字段
ret = session.query(Users.name).group_by(Users.name).all()

# #分组之后取最大id，id之和，最小id 和名字
ret = session.query(
    func.max(Users.id),
    func.sum(Users.id),
    func.min(Users.id), func.count(Users.id), Users.name).group_by(Users.name).all()

10、haviing筛选

# select max(id),sum(id),min(id),count(id),name from users group by name where id >5 having min(id)>2;
ret = session.query(
    func.max(Users.id),
    func.sum(Users.id),
    func.min(Users.id),
    func.count(Users.id),
    Users.name).filter(Users.id > 2).group_by(Users.name).having(func.min(Users.id) > 5).all()

11、连表（默认用forinkey关联）

# select * from person,hobby where user.hobby_id=hobby.id;
ret = session.query(Person, Hobby).filter(Person.hobby_id == Hobby.id).all()　

12、join表，默认是inner join

# select * from person inner join hobby on person.hobby_id=hobby.id;
ret = session.query(Person).join(Hobby).all()

# # isouter=True 外连，表示Person left join Hobby，没有右连接，反过来即可
# select * from person left join hobby on person.hobby_id=hobby.id;
ret = session.query(Person).join(Hobby, isouter=True).all()
ret = session.query(Hobby).join(Person, isouter=True).all()  # 右链接

13、union和union all

union 会去重

q1 = session.query(Boy.name).filter(Boy.id > 1)
q2 = session.query(Girl.name).filter(Girl.id < 10)
ret = q1.union(q2).all()

q1 = session.query(Boy.name).filter(Boy.id > 1)
q2 = session.query(Girl.name).filter(Girl.id < 10)
ret = q1.union_all(q2).all()