使用Oracle中的emp、dept來學習Django ORM
作者：r14筆記第75天
學習Django的時候，總是覺得這部分內容和實際的應用有一定的差別或者距離。一方面Django自帶的ORM對于底層數據庫來說是一種適配性很強的組件，可以不強依賴于某一種數據庫，sqlite,MySQL,Oracle,PG等等都可以，學習起來需要一定的周期。
學習Django的時候，總是覺得這部分內容和實際的應用有一定的差別或者距離。一方面Django自帶的ORM對于底層數據庫來說是一種適配性很強的組件，可以不強依賴于某一種數據庫，sqlite,MySQL,Oracle,PG等等都可以，學習起來需要一定的周期。另外一方面是因為這種方式是通用的API,一下子沒有了SQL語句，要理解并接受這種思想，需要一點時間，對很多DBA來說需要適應。第三點就是沒有融會貫通，好像看明白了，但是實際寫的時候發現還是摸黑，不知道從何入手。
所以我就換個思路，從數據庫的角度來反向解析Django怎么實現我們常見的數據需求。先做減法，側重于說查詢的部分。常見的數據需求，這個需求有些大，怎么讓他更通用呢，我想到了Oracle里面的emp,dept,自打學習數據庫，很多的測試案例就和這兩個表分不開，所以我們就從這個為切入點來逐步分析。
有的同學可能開始就打了退堂鼓，Oracle的還要轉換語句，還有數據類型，而使用的數據庫是MySQL，是不是有些麻煩啊，其實這些都不是事兒，不花一點功夫肯定難有收獲。
我們配置下emp,dept的結構，是在Django的models.py的文件中配置即可。
- from django.db import models
- import django.utils.timezone as timezone
- class dept(models.Model):
- deptno = models.AutoField(primary_key=True)
- dname = models.CharField(max_length=30)
- loc = models.CharField(max_length=30, default=' ')
- class Meta:
- db_table = 'dept'
- verbose_name = 'DEPT'
- verbose_name_plural = 'DEPT'
- ordering = ['deptno']
- def __unicode__(self):
- return '%s %s' % (self.deptno, self.dname)
- class dept(models.Model):
- deptno = models.AutoField(primary_key=True)
- dname = models.CharField(max_length=30)
- loc = models.CharField(max_length=30, default=' ')
- class Meta:
- db_table = 'dept'
- verbose_name = 'DEPT'
- verbose_name_plural = 'DEPT'
- ordering = ['deptno']
- def __unicode__(self):
- return '%s %s' % (self.deptno, self.dname)
- class emp(models.Model):
- empno = models.AutoField(primary_key=True)
- ename = models.CharField(max_length=30)
- job = models.CharField(max_length=30)
- mgr = models.IntegerField()
- hiredate = models.DateTimeField('hire date', default=timezone.now)
- sal = models.IntegerField()
- comm = models.IntegerField
- deptno = models.ForeignKey('dept')
- class Meta:
- db_table = 'emp'
- verbose_name = 'EMP'
- verbose_name_plural = 'EMP'
- verbose_name_plural = 'EMP'
- ordering = ['empno', 'ename']
- def __unicode__(self):
- return '%s %s' % (self.empno, self.ename)
其實內容來看倒也不難，類型是通用的。
使用python manage.py makemigrations得到變化的結構和數據
- Migrations for 'scott':
- 0001_initial.py:
- - Create model dept
- - Create model emp
得到的SQL如下：
- >python manage.py sqlmigrate scott 0001
- BEGIN;
- CREATE TABLE "dept" ("deptno" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "dname" varchar(30) NOT NULL, "loc" varchar(30) NOT NULL);
- CREATE TABLE "emp" ("empno" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "ename" varchar(30) NOT NULL, "job" varchar(30) NOT NULL, "mgr" integer NOT NULL
- , "hiredate" datetime NOT NULL, "sal" integer NOT NULL, "deptno_id" integer NOT NULL REFERENCES "dept" ("deptno"));
- CREATE INDEX "emp_d6b13549" ON "emp" ("deptno_id");
- COMMIT;
簡單確認下，我們就可以生成創建出來這兩個表了，使用python manage.py migrate即可。
emp的表結構如下：
dept的表結構如下：
我們初始化一下數據，這個時候直接使用SQL也可以.
dept表的初始化語句如下：
- insert into dept values(10,'ACCOUNTING','NEW YORK');
- insert into dept values(20,'RESEARCH','DALLAS');
- insert into dept values(30,'SALES','CHICAGO');
- insert into dept values(40,'OPERATIONS','BOSTON');
emp表的初始化語句如下，特別需要注意的就是字段不是deptno，而是deptno_id
- insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO_ID) values(7369,'SMITH','CLERK',7902,'1980-12-17',800.00,20);
- insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO_ID) values(7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600.00,30);
- insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO_ID) values(7521,'WARD','SALESMAN',7698,'1981-2-22',1250.00,30);
- insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO_ID) values(7566,'JONES','MANAGER',7839,'1981-4-2',2975.00,20);
- insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO_ID) values(7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250.00,30);
- insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO_ID) values(7698,'BLAKE','MANAGER',7839,'1981-5-1',2850.00,30);
- insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO_ID) values(7782,'CLARK','MANAGER',7839,'1981-6-9',2450.00,10);
- insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO_ID) values(7788,'SCOTT','ANALYST',7566,'1987--4-19',3000.00,20);
- insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO_ID) values(7839,'KING','PRESIDENT',0,'1981-11-17',5000.00,10);
- insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO_ID) values(7844,'TURNER','SALESMAN',7698,'1981-9-8',1500.00,30);
- insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO_ID) values(7876,'ADAMS','CLERK',7788,'1987-5-23',1100.00,20);
- insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO_ID) values(7900,'JAMES','CLERK',7698,'1981-12-3',950,30);
- insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO_ID) values(7902,'FORD','ANALYST',7566,'1981-12-3',3000,20);
- insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO_ID) values(7934,'MILLER','CLERK',7782,'1982-1-23',1300,10);
- insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO_ID) values(9999,'SHUNPING','CLERK',7782,'1988-5-5',2456.34,10);
剩下的事情就是實踐了。我們就選擇emp,dept常見的一些SQL來看看ORM能否完成這個任務。
1、顯示所有的姓名、工種、工資和獎金,按工種降序排列,若工種相同則按工資升序排列。
如果使用MySQL，語句和數據結果如下：
- mysql> select ename,job,sal from emp order by job desc,sal asc;
- +----------+-----------+------+
- | ename | job | sal |
- +----------+-----------+------+
- | WARD | SALESMAN | 1250 |
- | MARTIN | SALESMAN | 1250 |
使用order_by的方式來處理，可以看到有了一點頭緒，但是還是沒有實現需求。
- >>> emp.objects.all().order_by('job')
- [<emp: 7788 SCOTT>, <emp: 7902 FORD>, <emp: 7369 SMITH>, ....
所以我們的重點就是排序了，ORM本身有order_by函數，還可以調整DESC,ASC,所以一個基本符合要求的方式如下：
- >>> emp.objects.all().order_by(('-job'),('sal'))
- [<emp: 7521 WARD>, <emp: 7654 MARTIN>, <emp: 7844 TURNER>
第二個題目也是類似的。
2、查詢員工的姓名和入職日期，并按入職日期從先到后進行排列。
SQL語句如下:
- select ename,hiredate from emp order by hiredate asc；
現在的語句如下：
- emp.objects.all().order_by(('hiredate'))
3. 計算工資***的員工
這個需求充分考慮到聚合函數的部分，我們可以使用aggregate來完成這個工作。
- >>> emp.objects.all().aggregate(Max('sal'))
- {'sal__max': 5000}
4.查詢至少有一個員工的部門信息。
這個部分會涉及到表關聯關系，如果是通過SQL的方式，語句如下:
- select * from dept where deptno in (select distinct deptno from emp where mgr is not null);
執行的結果如下,可以看到***種方式能出結果，但是還是存在重復值，需要用distinct過啦一下。
- >>> dept.objects.filter(emp__mgr__isnull=False)
- [<dept: 10 ACCOUNTING>, <dept: 10 ACCOUNTING>, <dept: 10 ACCOUNTING>, <dept: 10 ACCOUNTING>, <dept: 20 RESEARCH>, <dept: 20 RESEARCH>, <dept: 20 RESEARCH>, <dept: 20 RESEARCH>, <dept: 20 RESEARCH>, <dept: 30 SALES>, <dept: 30 SALES>, <dept: 30 SALES>, <dept: 30 SALES>, <dept: 30 SALES>, <dept: 30 SALES>]
- >>> dept.objects.filter(emp__mgr__isnull=False).distinct()
- [<dept: 10 ACCOUNTING>, <dept: 20 RESEARCH>, <dept: 30 SALES>]
- >>>
后續繼續補充ORM的內容。
責任編輯：龐桂玉
來源：
楊建榮的學習筆記
