flask之 sqlalchemy 高级查询
1.flask 之创建和运行2.flask之配置文件3.flask 路由、钩子函数4.flask之蓝图和前后端变量传递5.python之任务调度（APScheduler和schedule）6.flask之cbv、模版7.flask之请求对象、响应对象、cookies和session8.falsk之闪现、wtforms表单9.django、flask自定义命令10.flask 之信号(事件)、信号量(计数器、锁)11.flask之 sqlalchemy 原生操作和增删改查12.flask之sqlalchemy ORM 一对多、多对多13.flask之 scoped实现线程安全.py
14.flask之 sqlalchemy 高级查询
1、filter_by写条件
1 | session.query(Users).filter_by(name='lqz').all() |
2、between 、in_
1 2 3 | session.query(Users).filter(Users.id.between(1, 10)).all()ret = session.query(Users).filter(Users.id.in_([1, 4, 5])).all() |
3、~非，除...外
1 | session.query(Users).filter(~Users.id.in_([1, 3, 5])).all() |
4、二次筛选
1 | session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='lqz'))).all() |
5、and_、or_ 条件
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | 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
1 2 3 4 | # # 通配符，以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、限制、分页
1 | ret = session.query(Users)[1:2] |
8、升序、降序
1 2 3 4 5 6 | # 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、分组
1 2 3 4 5 6 7 8 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筛选
1 2 3 4 5 6 7 | # 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关联）
1 2 | # 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
1 2 3 4 5 6 7 | # 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 会去重
1 2 3 4 5 6 7 | 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() |
