Django Advanced (ii) Many-to-many ORM operations of the database

Many-to-many relationships are simply two tables of data, and each row can correspond to multiple rows of data in another table. Use the example we used in a couple of long. Join us to release the restrictions, the Host table hosts table data can have more than one administrator. In turn, each administrator in the Hostadmin table also manages multiple hosts at the same time. The code 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 () # Configure many-to-many fields in the Administrator table, and establish the administrator tables associated with the Host table Class Hostadmin (models. Model): Username = models. Charfield (max_length=32) # Create many-to-many fields host = models. Manytomanyfield (Host)

Populate test data after creation

Host table records hosts and port information

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M01/7F/42/wKiom1cXkmOh4xBcAAAk3lXh0vE496.png "title=" 1.png " alt= "Wkiom1cxkmoh4xbcaaak3lxh0ve496.png"/>

The host field in the Hostadmin table that is defined as a many-to-many relationship is not displayed in the tables

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/7F/42/wKiom1cXk1Ki6TBlAAAhgrX8ZIk140.png "title=" 1.png " alt= "Wkiom1cxk1ki6tblaaahgrx8zik140.png"/>

since many-to-many relationships are not fully displayed with only 2 tables, Django automatically creates a many-to-many relationship table . All associated information for the host and administrator tables is recorded in this table. The name of this table is a concatenation of two linked tables.

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/7F/3F/wKioL1cXkhbyrnnpAAAj3jZRFWQ159.png "title=" 1.png " alt= "Wkiol1cxkhbyrnnpaaaj3jzrfwq159.png"/>

Let's add the data to the relational table.

Forward add

Because our many-to-many relationship fields are defined in the Hostadmin table, we add relationships by hostadmin tables as positive add operations. In short, this is the case where we add managed hosts to the administrator. Many-to-many associations use the Add () method to associate a host field with a set of objects to be associated with a table.

The code implemented by views.py is as follows:

# coding:utf-8from django.shortcuts Import render,httpresponsefrom app01 import models# Create your views here.def (req uest): # object host_obj = models that finds all data rows corresponding to the host table. Host.objects.all () # Gets the only row in the Hostadmin table Tom User's data Object admin_obj = models. HostAdmin.objects.get (username= ' Tom ') # Associating the Host field in the Hostadmin with the Host_obj field isolated in the host table by using a cross-list operation # because the host_obj contains multiple rows of data objects, So use (*host_obj) to represent multiple objects admin_obj.host.add (*host_obj) return HttpResponse (' OK ')

From which we know that Tom User ID number is 2, just we added to the user for Tom to all the host. The expected results are now visible in the auto-generated relationships table.

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

Forward Lookup

Now that the positive add has been completed, the forward lookup operation is actually the same as a pair of many. The code is as follows

# coding:utf-8from django.shortcuts Import render,httpresponsefrom app01 import models# Create your views here.def (req uest): # Gets the object admin_obj = models for all the rows that the user Tom corresponds to. HostAdmin.objects.get (username= ' Tom ') # finds an object of all data rows across the table through the host field in this row host_obj = Admin_obj.host.all () # Traverse Ho St Table gets the object for Host_line in Host_obj: # Prints the IP field in each line object print Host_line.ip return HttpResponse (' OK ')

So we've got the data in the host table through the hostadmin.

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/7F/40/wKioL1cXm3rR90i3AAAJnSHudwI783.png "title=" 1.png " alt= "Wkiol1cxm3rr90i3aaajnshudwi783.png"/>

Reverse Add

In contrast to the forward operation, adding an administrator to the host by manipulating the hosts table is a reverse addition. Same as a one-to-many operation. When we get the data in reverse, we need to use the Xxx_set method to get the data of the associated table across the table. Now we add Jack as administrator to ip4.4.4.4 's host, the code is as follows:

# coding:utf-8from django.shortcuts Import render,httpresponsefrom app01 import models# Create your views here.def (req uest): # Gets a unique row of data Objects Host_obj = models. Host.objects.get (ip= ' 4.4.4.4 ') # Gets the object of Jack's line # id__lt=3 equivalent to id<3 admin_obj = models. HostAdmin.objects.filter (id__lt=2) # Use the Host table to hide Fields Hostadmin # use Hostadmin_set Inverse association hostadmin table Host_obj.hostadmin_set . Add (*admin_obj) return HttpResponse (' OK ')

By looking at the relational table, we have associated the ID of Jack's row with the ID of the row where 4.4.4.4 is located.

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/7F/42/wKiom1cXonLRmqX4AAAxPIuO6lE914.png "title=" 1.png " alt= "Wkiom1cxonlrmqx4aaaxpiuo6le914.png"/>

Reverse Lookup

Finding an administrator through the host IP is a reverse lookup. Reverse lookup is also the same as a pair of multiple reverse lookups

The code is as follows:

# coding:utf-8from django.shortcuts Import render,httpresponsefrom app01 import models# Create your views here.def (req uest): # Gets a unique row of data Objects Host_obj = models.    Host.objects.get (ip= ' 4.4.4.4 ') # Gets the set of objects in the hostadmin table admin_obj = Host_obj.hostadmin_set.all () through the hidden fields in the host table +_set # Traverse each object for Admin_line in Admin_obj: # Prints the user name in the object print Admin_line.username return HttpResponse (' OK ')

The user name of the query is as follows

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/7F/42/wKiom1cXplKQ698NAAALnZ3ujBs603.png "title=" 1.png " alt= "Wkiom1cxplkq698naaalnz3ujbs603.png"/>

Summarize:

1, many-to-many operation of the association table, it must be like a couple of times. Select a unique row of data in the main operation to correlate multiple rows of data from another table. In a nutshell, for example, you first use get to find a single row of administrators, and then associate multiple host objects. Multiple administrators cannot be associated with multiple hosts at one time.

2. The Add () method for adding an operation only applies if Django automatically creates a many-to-many relationship table. If a self-built relational table is not available, the Add () method

3. Many-to-many fields defined in the table are not displayed when looking at the structure of the tables.

###########################################################################################

Customizing a Many-to-many relationship table

The above-mentioned forward query reverse query is used by Django to automatically generate a third-party relational table for us. Although it's easy to build automatically, it doesn't work if we want to customize the table name or add another field to this table. Given these requirements, I can specify the name of the relational table in the CREATE table by using the through parameter, the models.py code 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 () # Configure many-to-many fields in the Administrator table, and establish the administrator tables associated with the Host table Class Hostadmin (models. Model): Username = models. Charfield (max_length=32) # Specifies a many-to-many relationship table host = models through the through method. Manytomanyfield (Host, through= ' hostrelation ') class Hostrelation (models. Model): # Associated Host table and administrator table Hostadmin = models via foreign key. ForeignKey (hostadmin) host = models. ForeignKey (Host)

The structure of the Hostrelation table created manually is the same as the structure created automatically

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

Now let's add data to the relational table. There are two ways to add data (because it is a manually created relational table, so the Add () method used above adds no data)

The first way to add a data row object

This is the way to add data to the database in the Python layer as object objects, assuming that we now assign the user Jack the host administrative right to the ID number >2 in the host table. Then the code is as follows

# coding:utf-8from django.shortcuts import render, HTTPRESPONSEFROM APP01 IMPORT MODELS# CREATE YOUR VIEWS HERE.DEF M2M ( Request):    #  get hostadmin table Jack's only data object     admin_obj =  Models. HostAdmin.objects.get (username= ' Jack ')     #  get collection of objects id>2 the host table      host_obj = models. Host.objects.filter (id__gt=2)     #  traverse each row of objects obtained in the host table     for  host_line in host_obj:        # because the value of each foreign key is the corresponding row of data          #   therefore assigns a row of objects as objects to the values of the relational table fields          models. HostRelation.objects.create (Hostadmin=admin_obj, host=host_line)     return  HttpResponse (' OK ') 

Implementation results can be achieved by our request, which is not well understood. And this method 4 operations on the database, two queries two writes.

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M02/7F/40/wKioL1cXsl6ADewcAAAjJpnzAZQ529.png "title=" 1.png " alt= "Wkiol1cxsl6adewcaaajjpnzazq529.png"/>

The second method writes the add data at the database level. (This method is more intuitive)

We know that when creating a foreign key, Django automatically appends a _id to the field name we define as the last field name. If we now want to give the user Tom Add administrative rights to the host 1.1.1.1. All we need to know is the row ID and the row ID number of the user in the Hostadmin table in the host table. You can then add the field values in a dictionary by following the fields specified in the Hostrelation table. The code is as follows

# coding:utf-8from django.shortcuts Import render,httpresponsefrom app01 import models# Create your views here.def (req     uest): insert_dic={# The ID number of each row must be an integer, not the string ' hostadmin_id ': 2, ' host_id ': 1} # Pass the dictionary as a parameter through the * * method Models. HostRelation.objects.create (**insert_dic) return HttpResponse (' OK ')

The operation results are as follows

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

This method minimizes the number of operations on the data. This is especially true if the drop-down menu is generated from the previous segment based on the value in models. The drop-down menu allows you to know directly the ID value of the row that the user chooses, and the incoming backend is immediately ready to operate on the ID.

As for the data query, we can directly query the associated table in the Operation Hostrelation table, greatly reducing the complexity of many-to-many queries. For example, to view the host IP managed by Jack, the code is as follows

# coding:utf-8from django.shortcuts Import render,httpresponsefrom app01 import models# Create your views here.def (req uest): # Querying the username host_obj=models of the Hostadmin table through a double-glide line to the cross-table. HostRelation.objects.filter (hostadmin__username= ' Jack '). select_related () for Host_line in Host_obj: # through '. ' Connector cross table gets data from the host table print Host_line.host.ip return HttpResponse (' OK ')

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M02/7F/42/wKiom1cXuDegoR3cAAAINkjdMZg950.png "title=" 1.png " alt= "Wkiom1cxudegor3caaainkjdmzg950.png"/>

In turn, the administrator who queries the host 1.1.1.1 is a similar approach

# coding:utf-8from django.shortcuts Import render,httpresponsefrom app01 import models# Create your views here.def (req uest): Admin_obj=models. HostRelation.objects.filter (host__ip= ' 1.1.1.1 '). select_related () for admin_line in Admin_obj:print admin_line.h Ostadmin.username return HttpResponse (' OK ')

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/7F/42/wKiom1cXuTWiwXjkAAAFjBsmpEU775.png "title=" 1.png " alt= "Wkiom1cxutwiwxjkaaafjbsmpeu775.png"/>

Through the Hostrelation table, how to query is a forward query. is very convenient, so it is recommended that you create a relational table when dealing with many-to-many relationships.

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

Django Advanced (ii) Many-to-many ORM operations of the database