Shulou(Shulou.com)06/02 Report--

This article mainly shows you "how to use emp and dept in Oracle to learn Django ORM", which is easy to understand and clear, hoping to help you solve your doubts. Let me lead you to study and learn "how to use emp and dept in Oracle to learn Django ORM".

When learning Django, I always feel that there is a certain difference or distance between this part of the content and the actual application. On the one hand, the ORM that comes with Django is a highly adaptable component for the underlying database, which can not be strongly dependent on a certain database, sqlite,MySQL,Oracle,PG, etc., and requires a certain cycle to learn. On the other hand, because this approach is a general API, all of a sudden there is no SQL statement, to understand and accept this idea, it takes a little time, for many DBA need to adapt. The third point is that there is no comprehension. I seem to have understood it, but when I actually wrote it, I found that it was still dark, and I didn't know where to start.

So I'm going to change my mind and reverse parse how Django implements our common data requirements from a database point of view. Do subtraction first, focusing on the part of the query. Common data requirements, this demand is a bit large, how to make it more general? I think of emp,dept in Oracle. Since learning database, many test cases are inseparable from these two tables, so we will analyze it step by step from this entry point.

Some students may back down at the beginning, Oracle has to convert statements, as well as data types, and the database used is MySQL, is it a bit of trouble? in fact, these are not things, it will be difficult to gain without a little effort.

We configure the structure of emp,dept by configuring it in the models.py file of Django.

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 ('hiredate' 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)

In fact, the content is not difficult, the type is universal.

Use python manage.py makemigrations to get the changed structure and data

Migrations for 'scott': 0001_initial.py:-Create model dept-Create model emp

The resulting SQL is as follows:

> 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

With a simple confirmation, we can generate and create these two tables, using python manage.py migrate.

The table structure of emp is as follows:

The table structure of dept is as follows:

Let's initialize the data, or we can use SQL directly at this time.

The initialization statement for the dept table is as follows:

Insert into dept values (10 new YORK'); insert into dept values (20 minutes); insert into dept values (30 minutes); insert into dept values (40 minutes).

The initialization statement of the emp table is as follows. It is important to note that the field is not deptno, but deptno_id

Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO_ID) values (7369) values (7902, 1980-12-17); insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO_ID) values (7499) (7499) Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO_ID) values (7521 Magnum WARDIMENTS) values 1250.00); insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO_ID) values (7566) MANAGER (7839) Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO_ID) values (7654 MARTING) values (7698 Magna, 1981-9-28), insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO_ID) values (7698, Blakee, MANAGERM, 7839, 1981-1, 2850.00.30) Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO_ID) values (7782CLARK) values (7839pr. 1981-6-9); insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO_ID) values (7788) values (7788) Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO_ID) values (7839 and insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO_ID) values) (7839) and insert into emp (September) values (7839) Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO_ID) values (7876 values); insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO_ID) values (7900) Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO_ID) values (7902) insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO_ID) values (7902) (7902) (7902) (7902) (7902) (7902) (7902) (7902) (7902) Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO_ID) values (9999, ShUNPING, 7782, 1988-5-5, 1988-5-5, 2456.34, 10)

The rest is practice. Let's choose some of the common SQL of emp,dept to see if ORM can accomplish this task.

1. Display all names, types of work, wages and bonuses, in descending order of types of work, or in ascending order of wages if the types of jobs are the same.

If you use MySQL, the statement and data results are as follows:

Mysql > select ename,job,sal from emp order by job desc,sal asc; +-+ | ename | job | sal | +-+ | WARD | SALESMAN | 1250 | | MARTIN | SALESMAN | 1250 |

Using the order_by approach, you can see that there is a clue, but the requirements are still not implemented.

> emp.objects.all (). Order_by ('job') [,....

So we focus on sorting. ORM itself has an order_by function and you can adjust DESC,ASC, so a way that basically meets the requirements is as follows:

Emp.objects.all (). Order_by (('- job'), ('sal')) [,

The second topic is similar.

2. Inquire the employee's name and entry date, and arrange the entry date from first to last.

The SQL statement is as follows:

Select ename,hiredate from emp order by hiredate asc

The statement is now as follows:

Emp.objects.all () .order_by (('hiredate'))

3. Employees who calculate their wages.

This requirement takes full account of the aggregate function, and we can use aggregate to do this.

> emp.objects.all () .aggregate (Max ('sal')) {' sal__max': 5000}

4. Query the department information of at least one employee.

This section deals with the table association relationship. If it is through SQL, the statement is as follows:

Select * from dept where deptno in (select distinct deptno from emp where mgr is not null)

The results of the execution are as follows. You can see that * can produce results, but there are still duplicate values, which need to be passed with distinct.

> dept.objects.filter (emp__mgr__isnull=False) [,] > > dept.objects.filter (emp__mgr__isnull=False). Distinct () [,] > above is all the content of this article "how to use emp and dept in Oracle to learn Django ORM". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.