One-to-many ORM operations for the Django starter database

The Django database is implemented via ORM. What is ORM? To put it simply, the statement that we query all the data of a table through an SQL statement follows select * from Test. The tables used in Django are defined in the models.py file. So we're going to check the data in the test table by

Test_obj = Models.test.objects.all () to get the object of all data in the test table. Again through

The Test_obj.values () method reads the fields of each row of data in the form of a dictionary of key and value. This is called an ORM operation.

Since the operation of the database is involved, it is necessary to use the table operation. The ORM will simply divide the table operation into one-to-many and many-to-many operations.

What do you call a one-to-many? Is the abstract argument is that the database of foreign key operations is a typical one-to-many. A field in a database can correspond to multiple values in another table. The simple example is that an administrator can manage multiple hosts in the host table. However, each host can only have one administrator. This situation is called a one-to-many

1) Forward operation

Let's start by creating 2 tables. One is the host table inside the host IP and port information. One is the Administrator's table, which contains the administrator name and managed host information. So the code in our models.py is as follows:

# coding:utf-8from __future__ Import unicode_literalsfrom django.db import models# Create your models Here.class host (mode Ls. Model): IP = models. Charfield (max_length=32) port = models. Integerfield () class Hostadmin (models. Model): Username = models. Charfield (max_length=32) host = models. ForeignKey (' host ')

After creating the table, we fill in several rows of test data.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/7F/2E/wKiom1cV2liDhldpAAAVMjSj31Y531.png "title=" 1.png " alt= "Wkiom1cv2lidhldpaaavmjsj31y531.png"/>

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M01/7F/2E/wKiom1cV2vbxooPVAAAZZYzCflM056.png "title=" 1.png " alt= "Wkiom1cv2vbxoopvaaazzyzcflm056.png"/>

Querying the data in the associated table from a table with a foreign key is called a forward operation. From our example can see the foreign key definition in the Hostadmin table, then we query from the Hostadmin table user Tom manages all host IP, this demand is forward query. Our forward query code in views.py is as follows:

# coding:utf-8from django.shortcuts import render,httpresponsefrom app01 import  models# create your views here.def onetomany (Request):     #   By using the filter method to match the condition, the returned object is stored in the ret_obj variable     ret_obj =  Models.hostadmin.objects.filter (username= ' Tom ')     #  because Ret_obj is an object, this object is made up of multiple rows of data in the database     #  so every time the item is looped, this item represents a row of database data that contains all the fields     for item  In ret_obj:        # orm to get data in a cross-table operation with '. ' To connect         #  Item.host.ip represents the IP field of the corresponding host table by the value of the Hostadmin table's host foreign key field         #  Note the table structure of the database the foreign key field of the host is automatically written by Django host_id         #  However, you should not use host_id when using ORM cross-table operations, or you should use the name of the host defined in models         print  item.host.ip&nbsP;   return httpresponse (' OK ') 

Operation results, you can correctly query the name of all the host IP in Tom

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/7F/2C/wKioL1cV5RDzkPoJAAADKlO3tqk238.png "title=" 1.png " alt= "Wkiol1cv5rdzkpojaaadklo3tqk238.png"/>

Then query all administrator names that have administrative permissions on the IP-1.1.1.1 host, and the code is as follows

# coding:utf-8from django.shortcuts Import render,httpresponsefrom app01 import models# Create your views here.def onetoma NY (Request): # Querying data with ' __ ' double underscore for cross-table query Operations Ret_obj = Models.hostadmin.objects.filter (host__ip= ' 1.1.1.1 ') for item In Ret_obj:print item.username return HttpResponse (' OK ')

The operation results are as follows

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M02/7F/2E/wKioL1cV803xknrEAAAOxn7EF6I360.png "title=" 1.png " alt= "Wkiol1cv803xknreaaaoxn7ef6i360.png"/>

The above two kinds of queries, we are through the operation of the Hostadmin table to implement the query function. At this point, the query is finished.

2) Reverse Operation

or using just two tables, the reverse operation is, by definition, a table in which a non-existent foreign key is used to query the in-table data containing the foreign key. In our case, the data in the Hostadmin table is queried in turn using the fields in the host table.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/7F/2E/wKiom1cV2liDhldpAAAVMjSj31Y531.png "title=" 1.png " alt= "Wkiom1cv2lidhldpaaavmjsj31y531.png"/>

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M01/7F/2E/wKiom1cV2vbxooPVAAAZZYzCflM056.png "title=" 1.png " alt= "Wkiom1cv2vbxoopvaaazzyzcflm056.png"/>

First of all we know that the host table and Hostadmin table is through the hostadmin inside the host foreign key to establish a connection, do a positive query by the host this foreign key can be queried to the host table data. In fact, when the two tables create a connection, Django is in the host table

A hidden hostadmin field is also created to associate with the Hostadmin table. Then if we want to view all the IP of Tom's user in the host table, we can use the hidden field in the host table to associate the user name. The code is as follows:

# coding:utf-8from django.shortcuts Import render,httpresponsefrom app01 import models# Create your views here.def onetoma NY (Request): # using the Hostadmin field hidden in the host table to query the user name in the Hostadmin table # This way and the forward query is much like Ret_obj = Models.host.objects.filter (host Admin__username= ' Tom ') # Iterate through all the rows of the query for lines in Ret_obj: # Print the IP field of each line print Line.ip return HTTPRESP Onse (' OK ')

Run the results, as you can find out the corresponding IP

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/7F/2C/wKioL1cV5RDzkPoJAAADKlO3tqk238.png "title=" 1.png " alt= "Wkiol1cv5rdzkpojaaadklo3tqk238.png"/>

If we want to use the IP in the host table as the 1.1.1.1 field, reverse the lookup to the name of the corresponding administrator. The difference between this and the forward lookup is quite large. Here are 2 points to note

1, in the host table must pass the Get () method to specify the exact row of data (without using the Get () method, you cannot call Hostadmin_set reverse fetch data)

2. Obtain the collection of data rows for all hostadmin tables that meet the criteria by Hostadmin_set.all ()

The code for the feature implementation is as follows:

# coding:utf-8from django.shortcuts import render, httpresponsefrom app01 import models# create your views here.def  Onetomany (Request):     # get () method is to get only one row of data, if not get the error     #  Therefore, the Get method must be used to ensure that the available conditions     host_obj = models.host.objects.get (ip= ' 1.1.1.1 ')     #  Reverse cross-table through Hostadmin_set.all () to get all data row objects for the associated table     admin_obj  = host_obj.hostadmin_set.all ()     #  Print acquired objects     print  admin_obj    #  Traversal Object     for line in admin_obj:          #打印对象里的字段         print  line.username    return httpresponse (' OK ') 

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/7F/30/wKioL1cWJtzBBH0AAAAeT8wv_tE826.png "title=" 1.png " alt= "Wkiol1cwjtzbbh0aaaaet8wv_te826.png"/>

At this point the reverse query operation is also finished.

One-to-many optimizations (select_related () method)

We still use the first forward query as an example to explain how the code

# coding:utf-8from django.shortcuts Import render,httpresponsefrom app01 import models# Create your views here.def onetoma    NY (Request): # Find all qualifying data rows in the hostadmin table ret_obj = Models.hostadmin.objects.filter (username= ' Tom ') # Print the original SQL statement Print Ret_obj.query for item in Ret_obj:print ITEM.HOST.IP return HttpResponse (' OK ')

Get the original SQL code as follows:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/7F/30/wKioL1cWLYnjf-qNAAAcSJaoK7c824.png "title=" 1.png " alt= "Wkiol1cwlynjf-qnaaacsjaok7c824.png"/> The execution flow of the above code should look like this:

Find data rows for all username= ' Tom ' in the Hostadmin table

Get the value of the host_id in these data rows after querying it out

--based on the value of host_id to find the corresponding IP in the host table

This process needs to read two databases to get the data in host and Hostadmin.

Let's refine this by adding the select_related () method behind Models.hostadmin.objects.filter (username= ' Tom ').

# coding:utf-8from django.shortcuts Import render,httpresponsefrom app01 import models# Create your views here.def onetoma    NY (Request): # Use the select_related () method to refine the query ret_obj = models.hostadmin.objects.filter (username= ' Tom '). select_related () # Print original SQL statement print Ret_obj.query for item in Ret_obj:print ITEM.HOST.IP return HttpResponse (' OK ')

Let's see how the original SQL statements are different.

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M02/7F/32/wKiom1cWL7-AfT6NAAA4fivCaEM739.png "title=" 1.png " alt= "Wkiom1cwl7-aft6naaa4fivcaem739.png"/> This query directly through the join on the Statement hostadmin table query results corresponding to the host table data is also queried out. In this way, the item.ip need to use the data in the host table to obtain the required data directly in memory, reducing the access to the database at a time.

Note: the Select_related () method provides optimizations only for one-to-many such foreign-key access methods. Many-to-many operations have no effect.

A simple summary of one-to-many

1, the query data is through Models.xxx.objects.filter () fill in the query conditions. This time the result is a set of data row objects, not a specific data. Object name __ Field name (double underscore) for cross-table queries

2. Getting data is also specific to getting data for a field. Cross-table operation through '. ' To connect individual tables

3, reverse lookup, the lookup table will create a hidden field, the field name is the name of the table that created the foreign key

4, the reverse acquisition of data, through the Xxx_set.all () to get to xxx all the matching objects

5, try to use positive operation, the reverse look at the trouble.

Common operations for Double underlines

#  increased     #    # models. Tb1.objects.create (c1= ' xx ',  c2= ' oo ')    add a data to accept dictionary type data  **kwargs    #  obj = models. TB1 (c1= ' xx ',  c2= ' oo ')     # obj.save ()     #       #    # models. Tb1.objects.get (id=123)          #  get single data, no error (not recommended)      # models. Tb1.objects.all ()                #   Get all     # models. Tb1.objects.filter (Name= ' seven ')  #  get data for specified conditions     #  Delete      #    # models. Tb1.objects.filter (Name= ' seven '). Delete ()  #  delete data for specified criteria     #  change      # models. Tb1.objects.filter (Name= ' seven '). Update (gender= ' 0 ')  #  update the data for the specified conditions, all support  **kwargs        # obj  = models. Tb1.objects.get (id=1)     #  Modify single Data     # obj.c1 =  ' 111 '     # obj.save ()                                                             #  Get number     #     # models. Tb1.objects.filter (Name= ' seven '). Count ()     #  greater than, less than     #     # models. Tb1.objects.filter (id__gt=1)                #  gets the value of ID greater than 1     # models.Tb1.objects.filter (id__lt=10)              #   Gets the value of ID less than 10     # models. Tb1.objects.filter (id__lt=10, id__gt=1)    #  get the value with ID greater than 1  and   less than 10      # in    #    # models. Tb1.objects.filter (id__in=[11, 22, 33])    #  get ID equals 11, 22, 33 data      # models. Tb1.objects.exclude (id__in=[11, 22, 33])   # not in    #  contains    #    # models. Tb1.objects.filter (name__contains= "ven")     # models. Tb1.objects.filter (name__icontains= "ven")  # icontains case-insensitive     # models. Tb1.objects.exclude (name__icontains= "ven")     # range    #     # models. Tb1.objectS.filter (Id__range=[1, 2])    #  range bettwen and    #  other similar     #    # startswith,istartswith, endswith, iendswith,     # order by    #    # models. Tb1.objects.filter (Name= ' seven '). order_by (' id ')     # asc    #  models. Tb1.objects.filter (Name= ' seven '). Order_by ('-id ')    # desc    #  limit , Offset    #    # models. Tb1.objects.all () [10:20]    # group by    from  Django.db.models import count, min, max, sum    # models. Tb1.objects.filter (c1=1). VALUES (' ID '). Annotate (C=count (' num '))     # SELECT  " App01_tb1 "." ID ",  count (" App01_tb1 "." Num ")  as  "C"  FROM  "App01_tb1"  WHERE  "app01_tb1". " C1 " = 1 GROUP BY " APP01_TB1 "." Id

This article from "Thunderbolt Tofu" blog, declined reprint!

One-to-many ORM operations for the Django starter database