SQLAlchemy教程-第三章-SQL常用查询的ORM写法进阶

今天是2018-07-04. 今天睡觉前一定要更新一篇教程. 写在这里占个坑先.

简称声明

sqlalchemy, 简称为SA.

SA生成的sql语句如何查看呢? 怎么知道SA是否生成了自己预期的sql?

回到 db_util.py里, 给create_engine增加一个参数. echo=True
如下:
engine = create_engine('mysql+pymysql://root:PWD@localhost:3306/sqlalchemy_demo?charset=utf8', echo=True)

可以看到
emps = sess.query(Emp.empno, Emp.ename, Emp.job, Emp.hiredate).order_by(Emp.hiredate.asc()).all()
生成了
SELECT emp.empno AS emp_empno, emp.ename AS emp_ename, emp.job AS emp_job, emp.hiredate AS emp_hiredate FROM emp ORDER BY emp.hiredate ASC

表联合查询.

表联合查询的情况在sql中特别常见, 为了高效的存储数据, 减少不必要的重复, 我们会设计表和表之间的关系, 多数情况下指定1对多关系, 还有1对1, 多对多.
我们一开始建立了部门表dept和员工表模型, 我们也知道他们的对应关系. 即: 一个部门有多个员工, 一个员工只能属于一个部门.
如果没有指定外键关系, 单纯靠约定表的字段之间的关系( 为什么这么假设? 因为外键不是必须的. 我们知道是外键就可以了, 只要查询的时候指定两列对应即可. 要不要设置外键? 据说在高并发的情况下, 设置外键不合适, 关于这个问题, 我也了解不多, 需要以后深入探究, 了解的同学可以给我科普一下). 我们如何写SA的查询呢?

场景1:
查询部门所在地为纽约的所有员工.
使用sql:
select emp.* from emp join dept on emp.deptno = dept.deptno where dept.loc = 'NEW YORK';
转换成SA的写法为
sess.query(Emp).join(Dept, Emp.deptno = Dept.deptno).filter(Dept.loc == 'NEW YORK').all()
相信写到这里, 就很清楚了.

话外, 说一种不合理的写法. select emp.* from emp, dept where emp.detpno = dept.deptno and dept.loc = 'NEW YORK'.
这么写也没错啊? 不用写join了对不对. 而且看上去很帅吧. 我使用这种写法写了很久. 但是慢慢的我发现问题所在. 这种sql的可读性不如第一种. 第一种通过 join语句 指明了连接条件, 一目了然. 而第二种却把连接条件写在where里. 语法上这样写没错. 但是容易把连接条件和过滤条件, 也就是针对某个字段的过滤条件 顺序打乱, 可读性变差.
第二种也可以转换为SA 写法, 但是我不推荐, 所以我不写出来. 我强烈建议使用第一种写法, 使用 join 指明连接条件.

场景2:
查询部门所在地为纽约的经理. 经理指job='MANAGER'的员工.
使用sql怎么写呢?
select emp.* from emp join dept on emp.deptno = dept.deptno where dept.loc = 'NEW YORK' and emp.job = 'MANAGER';
那么怎么转换为SA写法呢?
sess.query(Emp).join(Dept, Emp.deptno = Dept.deptno).filter(Dept.loc == 'NEW YORK', Emp.job == 'MANAGER').all()
注意了, filter的写法很灵活. 上面这一行可以写成如下格式:
sess.query(Emp).join(Dept, Emp.deptno = Dept.deptno).filter(Dept.loc == 'NEW YORK').fitler(Emp.job == 'MANAGER').all()
这种写法是链式写法.

提示: 写在filter里面, 用逗号(",")隔开, 表示各个条件之间是 and关系. 也就是所谓的隐式and, 如果想显式声明and关系. 使用 and_, 我们把刚才的句子重写一遍:
from sqlalchemy import and_
sess.query(Emp).join(Dept, Emp.deptno = Dept.deptno).filter(and_(Dept.loc == 'NEW YORK', Emp.job == 'MANAGER')).all()

场景3:
这次, 我们想找到职务是经理或者销售的所有员工.
select emp.* from emp where job='MANAGER' or job='SALESMAN'.
使用SA如何写呢?
这次的筛选条件是或的关系.
如下:
sess.query(Emp).filter(or_(Emp.job == 'MANAGER', Emp.job == 'SALESMAN')).all()
所有在or_包裹下的条件都是并行的, 而所有在and_条件下包裹的都是与的关系.

举一反三, 有人就说, 与或非. 与和或都有了, 那么非呢? 是用not吗? 不. 不用not. 用一个特殊的符号 ~.
比如我要查询所有不是经理的员工.
sess.query(Emp).filter(~(Emp.job == 'MANAGER')).all()
或者
sess.query(Emp).filter(Emp.job != 'MANAGER').all()
第二种比较常见, 直接用不等号. 但是我们也可以通过执行第一句证明它是对的. 使用~ , 也就是非的时候, 要把一个或者多个条件包裹起来, 或者用在 and_, or_的前面. 表示相反的条件.

再补充一点: 如果两个表的连接条件不是一个, 而是多个, 那么就应该按照如下格式写: sess.query(SchemaA).join(SchemaB, and_(SchemaA.col1 == SchemaB.colx, SchemaA.col2 == SchemaB.coly) ).filter(XXXX).all() ( 如果可能的话, 我后面会设计这种例子来说明)

今天就写这么多, 磨磨唧唧写了1个多小时. 下次说一下 分页的写法, exists的用法. 以及列别名, 和函数的用法.

---

SQLAlchemy官方网站
http://docs.sqlalchemy.org/en/latest/contents.html
教程全部代码github地址
https://github.com/notfresh/sqlalchemy_demo

转载于:https://www.cnblogs.com/notfresh/p/9263389.html