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

Blog park home page blog flash memory new essay subscription

Manage ORM: (in django, automatically generating database tables based on classes in the code is also called-- code first)

ORM:Object Relational Mapping (Relational object Mapping)

The classes we write represent tables in the database

The object we create from this class is a row of data in the database table.

Obj.id obj.name. Is a part of a row of data in the database

ORM--First:

When we learn orm in django, we can divide one-to-many and many-to-many into forward search and reverse search.

1 2 3 4 56 7class UserType (models.Model): caption = models.CharField (max_length=32) class UserInfo (models.Model): username = models.CharField (max_length=32) age = models.IntegerField () user_type = models.ForeignKey ('UserType') # Foreign key

Forward lookup: ForeignKey in the UserInfo table, if you start from the UserInfo table to query other tables, this is a forward operation, whereas if you query other tables from the UserType table, this is a reverse operation.

We are about to start our orm query tour!

Create the table + write the corresponding function in the configuration url+views

Models.py (only and only database-related classes can be written here in django)

1 2 3 4 56 7class UserType (models.Model): caption = models.CharField (max_length=32) class UserInfo (models.Model): username = models.CharField (max_length=32) age = models.IntegerField () user_type = models.ForeignKey ('UserType')

Here we use the sqlite3 database, just use the default settings in settings

Configuration in url.py

1 2 3 4 5 6 7 8 9from django.conf.urls import url from django.contrib import admin from app01 import views urlpatterns = [url (r'^ admin/', admin.site.urls), url (r'^ user_type',views.user_type), url (r'^ user_info',views.user_info),]

Views.py does not take any action first. Let's first ensure normal access to the url that has been set up.

1 2 3 4 5 6 7from django.shortcuts import render,HttpResponse def user_type (req): return HttpResponse ("ok") def user_info (req): return HttpResponse ("ok")

First insert a few pieces of data into the table for testing:

Usertype table

Userinfo data insertion:

So there are two ways to create UserType data: the first is to add data directly based on this field! Add'_ id' to user_type

1 2 3 4def user_info (request): dic = {'username':'mosson','age':18,'user_type_id':1} models.UserInfo.objects.create (* * dic) return HttpResponse (' OK')

Or add through an object

1 2 3 4 5 6 "get the object of the group first usertype = models.UserType.objects.fiter (id=2) # when you add the object directly, you can models.UserInfo.objects.create (username='seven',age=18,user_type=usertype) # write one line or models.UserInfo.objects.create (username='lile',age=18,user_type=models.UserType.objects.filter (id=1))

Django's get method takes a matching result from the database and returns an object that reports an error if the record does not exist.

Django's filter method takes matching results from the database, returns a list of objects, and returns [] if the record does not exist.

One-to-many of ORM:

When do we use one-to-many when designing table structures?

For example, when we set up users, we have a menu for us to select user types, using one-to-many!

1. One-to-many forward search:

Positive lookup: ForeignKey is in the UserInfo table. If you query the combined contents of the two associated tables according to the UserInfo table, it is a positive lookup.

Reverse lookup: ForeignKey is not in UserType. If you query the combined contents of the two associated tables according to the table UserType, it is reverse lookup.

Forward lookup-demo1-- queries all users whose users are COO

In django, a foreign key is equivalent to simply using a _ _ join table, encapsulating all the fields in the user_ type table in the foreign key object.

We want to query all users whose users are CEO. Do we check according to the UserType table? if it is a cross-table query, use "double underscore" + attribute.

1 2 3 4 5 6 7from app01 import models def index (req): ret = models.UserInfo.objects.filter (user_type__caption='COO') print (ret) for item in ret: print (item,item.username,item.age,item.user_type.caption) return HttpResponse ("OK")

Query results:

Reverse query:--

We can take out a user group according to the following command, so how many users does he have for this user group?

1models.UserType.objects.get (id=1) 1 2 3 4 5 6 7 8 9 10 11obj=models.UserType.objects.get (id=1) obj.caption==== get the caption obj.id= corresponding to id 1 in UserType table and id as 1 obj.userinfo_set # in UserType table is understood as a capability You can get all current users of this user type / or multiple users of this user type obj.userinfo_set.all () # get all users of user type COO obj.userinfo_set.filter (username='tim') # get users of user type COO and user tim''this. Userinfo_set. Equivalent to what? It is equivalent to models.UserInfo.objects.filter (user_type=obj)''

Reverse query instance: query all user names under the COO user

12 3ret = models.UserType.objects.filter (caption='COO'). Values ('userinfo__username') for item in ret: print (item,type (item))

Method 2.

1 2 3 4ret = models.UserType.objects.filter (caption='COO'). First () for item in ret.userinfo_set.all (): print (item.username)

Summary:

Forward lookup:

Filter (when crossing a table, it should be an object _ _ cross-table field)

When you get this value, when you get a row of data, line. Object. Fields across tables

Reverse lookup:

Filter (the indication of associating this table) automatically creates and indicates the same object, through this object _ _ fields across the table

Line. Automatically create and indicate the same object _ set. Method

The ORM many-to-many system generates the third table:

Many-to-many has nothing to do with one-to-many

Models.py

1 2 3 4 5 6 7 8class Host (models.Model): hostname = models.CharField (max_length=32) port = models.IntegerField () class HostAdmin (models.Model): username = models.CharField (max_length=32) email = models.CharField (max_length=32) host = models.ManyToManyField ('Host')

When we add data to the host table and the hostadmin table, it has nothing to do with the third chapter relational table. When we create the table in this way, the columns in the third table are already fixed, which are the id of the two tables.

Add data to the host table:

1 2 3 4 5 6 7 8 9 10 11def index (req): # Host data models.Host.objects.create (hostname='host1.test.com', port=80) models.Host.objects.create (hostname='host2.test.com', port=80) models.Host.objects.create (hostname='host3.test.com', port=80) models.Host.objects.create (hostname='host4.test.com', port=80) # user data models.HostAdmin.objects.create (username='alex' Email='alex@qq.com') models.HostAdmin.objects.create (username='mosson', email='mosson@qq.com') models.HostAdmin.objects.create (username='aliven', email='aliven@qq.com') models.HostAdmin.objects.create (username='wusir', email='wusir@qq.com')

The effect in the data:

Empty relation table

Many-to-many forward and reverse add data

Add data forward:

12 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18def index (request): # add data # find the user dali this admin_obj = models.HostAdmin.objects.get (username='dali') # find the host host_list = models.Host.objects.filter (id__lt=3) # add to add data admin_obj.host.add (* host_list)''admin_obj through the found dali object Vigorously this object. Add to the host to operate The powerful ID is 2 and the host ID is: (1) then a table like this is generated: # 21 # 22 'return HttpResponse (' OK')

Add data in reverse:

12 3 4 5 6 7 8 9 10 11 12 13 14def index (request): # add data in reverse # get host host_obj = models.Host.objects.get (id=3) # get user list admin_list = models.HostAdmin.objects.filter (id__gt=1) # host_obj.hostadmin_set.add (* admin_list) # host_obj = 3 administrator ID = 2 3 4 # 3 2 # 3 3 # 3 4 return HttpResponse ('OK') ORM many-to-many customization third table 12 3 4 5 6 7 8 9 10 11 12 13 14 15 16 18 19 20 21class HostInfo (models.Model): hostname = models.CharField (max_length=32) port = models.IntegerField () class UserMap (models.Model): username = models.CharField (max_length=32) email = models.CharField (max_length=32) # through tells Django to associate host = models.ManyToManyField (HostInfo) with that table Through='HostRelation') class HostRelation (models.Model): host = models.ForeignKey ('HostInfo') user = models.ForeignKey (' UserMap') 'and here we can add multiple relationships, such as adding a field usertype = models.ForeignKey (' UserType') or adding a normal field status = models.CharField (max_length=32)''

Now we have created the third table ourselves. Now we have two ways to create the third table, when we use the custom created third table, when we add data!

You can't add objects in the first way!

Now that we have the third table for this object, we don't need to worry about the other two tables, just add them! 0 0!

Add hosts and users--

1 2 3 4 5 6 7 8 9 10 11def index (req): models.HostInfo.objects.create (hostname='alex.test.com', port=80) models.HostInfo.objects.create (hostname='seven.test.com', port=80) models.HostInfo.objects.create (hostname='mosson.test.com', port=80) models.UserMap.objects.create (username='alex', email='alex@qq.com') models.UserMap.objects.create (username='seven', email='seven@qq.com') models.UserMap.objects.create (username='mosson' Email='mosson@qq.com') return HttpResponse ('ok')

Insert the data into the third table-method 1:

Insert a single piece of data

1 2 3 4 5 6def index (request): models.HostRelation.objects.create (user = models.UserMap.objects.get (id=1), host = models.HostInfo.objects.get (id=1)) return HttpResponse ('OK')

Many-to-many comparison and query

Query-method 1:

The first way is to find the third table based on the objects in the table! Find the handle to this table indirectly!

1 2 3 4 5 forward check admin_obj = models.HostAdmin.objects.get (id=1) admin_obj.host.all () # inverse difference host_obj = models.Host.objects.get (id=1) host_obj.hostadmin_set.all ()

Query-method 2:

There is no such thing as forward and reverse with the second method, just check it directly!

1 2 3 4relation_list = models.HostRelation.objects.all () for item in relation_list: # each item is a relational print (item.user.username) print (item.host.hostname) 1 23 4relation_list = models.HostRelation.objects.filter (user__username='mosson') for item in relation_list: # each item is a relational print (item.user.username) print (item.host.hostname)

All the relationships can be found in the second way, and can all the relationship tables be found in the first way?

The first way can only find the machine managed by a certain person, not the corresponding relationship!

The function of select_related: 1 2 3 4 56 7class UserType (models.Model): caption = models.CharField (max_length=32) class UserInfo (models.Model): user_type = models.ForeignKey ('UserType') # this user_type is an object, which encapsulates ID and caption username = models.CharField (max_length=32) age = models.IntegerField ()

The function of select_related, which is used to optimize queries, can be done without him. It is mainly used to optimize ForeignKey.

1 2 3 456 7def index (request): ret = models.UserInfo.objects.all () # Let's take a look at what SQL statement he executed: print (ret.query)''SELECT "app01_userinfo". "id", "app01_userinfo". "user_type_id", "app01_userinfo". "username", "app01_userinfo". "age" FROM "app01_userinfo"''

Plus what select_related looks like.

12 3 4 5 6 7 8 9 10 11 12 13 14def user_info (request): ret = models.UserInfo.objects.all (). Select_related () # Let's take a look at the SQL statement print ret.query SELECT "app01_userinfo". "id", "app01_userinfo". "user_type_id", "app01_userinfo". "username", "app01_userinfo". "age", "app01_usertype". "id" "app01_usertype". "caption" FROM "app01_userinfo" INNER JOIN "app01_usertype" ON ("app01_userinfo". "user_type_id" = "app01_usertype". "id")

So select_related is to optimize the query!

Combing the operation of ORM connected tables:

One-to-many creation

1. Create data

Create from object

Or created through the object field _ id

2. Find

Forward search

Use double underscore'_ _ 'across tables when passing through filter

Pass when it is worth it. Cross tabl

Reverse search

Django automatically generates table name _ set

Other operations are the same as forward lookup

Second, many pairs

1. Automatic generation of relational tables

Get the relational table indirectly, if it is positive: the object of a row of data. ManyToMany dictionary is reversed: the object of a row of data. Table name _ set

2. Customize the relational table (recommended) whether it is adding or modifying, only operate on the relational table.

III. Select_related

It is used to optimize the query and load the query table and the table associated with ForiegnKey into memory at once.

F and Q in Django

F: used to modify data in batches (using values of query criteria)

Demo: for example, I have a price column, and I want price to increase by 10 or something by 10.

1 "from django.db.models import F # models.Tb1.objects.update (num=F ('num') + 10)

Q: used for conditional query

The default django query is only and operates as follows:

1models.UserInfo.objects.filter (username='mosson',age='18')

Find the data with user name: mosson and age=18

Is there a situation where username=mosson or username=wusir or username=alex and age=18 are required? Native queries are not supported! So I used Q~.

12 34 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 26 27 28 30 31 32 33 34 35 36 first step: # generate a search object search_q = Q () # generate two search objects search2 = Q () search3 = Q () step 2: # Mark the search condition in search2 as'or 'query search2.connector =' OR' # add the search condition to Search2.children.append (('field name') in search2 ) search2.children.append (('field name', 'field content')) search2.children.append (('field name', 'field content')) search2.children.append (('field name', 'field content')) # the search condition in the tag search3 is'or 'query search3.connector =' OR' # add the search condition to the search3 search3.children.append (('field name') ) search3.children.append (('field name', 'field content')) search3.children.append (('field name', 'field content')) search3.children.append (('field name', 'field content')) step 3: # merge multiple search criteria search_q.add (search2,'AND') search_q.add (search3 Step 4: # perform a search for models.HostInfo.objects.filter (search_q) instance:

When I get the search criteria at the front end, I write the same type of search in the form of a dictionary {field name: [condition combination list]}, so that when I query, I can divide the search criteria into different sub-conditions directly through the circular dictionary!

12 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 25 26 27 28 29 30function SearchSubmit () {/ / clear the current list $('# table-body'). Children (). Remove (); / set an empty dictionary SEARCH_DIC = {}; / / find all search boxes var search_data = $('.inputs). Find ("input [condition='true']") / / the content found in the loop $.each (search_data,function (index,data) {/ / get the type of search / * Note here: key: this can be coupled with the Q of Django, and when we define the type of search, it can be written directly as the 'fields or conditions in the library we want to search!!' As follows: * / var search_name = $(this) .attr ('name'); / / get the value of the search var search_value = $(this). Val (); if (SEARCH_DIC.hasOwnProperty (search_name)) {/ / determine whether this KEY SEARCH_ DIC [search _ name]. Push (search_value)} else {SEARCH_ DIC [search _ name] = [search_value]}})) / / ajax request ends $.get ("{% url 'search_info'%}", {' search_list':JSON.stringify (SEARCH_DIC)}, function (callback) {$('# table-body') .append (callback)}); / / search button ends

Key points:

When we define the name attribute of input at the front end, we can directly define it as the "field name" in the database, and the cross-table operation "double underscore" is supported in the Q of Django, so we can directly define the double underscore operation when defining name.

1search2.children.append (('field name' _ _ 'cross-table field name') 'cross-table field content') "+% s'% i.id +" + "" + "+ i.hostname +" + i.hostip + "" + i.hostbusiness.hostbusiness + "+ i.hoststatus.hoststatus +"+") html = mark_safe ("" .join (html)) return HttpResponse (html) 12 34 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 26 27 28 29 30 33 33 36 37 38 39 40@login_auth def search_info (request): # get the data requested by the user user_post = json.loads (request.GET ['search_list']) print user_post # generate the search object Serach_Q = Q () # Loop Dictionary and generate the search criteria set for k V in user_post.items (): # generate a search combination Q = Q () # the search condition in the life set is'or 'condition q.connector =' OR' # value,value in the loop dictionary is the condition set passed from the front end for i in v: # add a condition to the search condition set in the form of tuple, k is the key in the dictionary! Key is a field name or cross-table field name or supports # I, such as _ gt, to be an element in the vlaue in the dictionary. After the condition # q.children.append ((kmemi)) # does not cycle once, it is added to the overall search condition Serach_Q.add (Q). 'AND') # query data = models.HostInfo.objects.filter (Serach_Q) # concatenated string and return html = [] for i in data: html.append ("

Classification: Django should pay more attention to my collection of good articles.

Want to be a scholar of bandits

Follow-12

Fans-21 00? Previous article: masturbation skills season 2

? The next article: Python threads

Posted on 2017-03-23 20:47 A scholar who wants to be a bandit reads comments (0) Edit collection refresh comment refresh page return top [recommendation] 500000 lines of VC++ source code: large configuration industrial control, power simulation CAD and GIS source code library

[free] learn programming from scratch, developers' own experimental platform for free practice!

[recommended] now register and shoot the cloud, you can enjoy 200G CDN traffic in the first month, and you can apply for a SSL certificate for free

Nickname: a scholar who wants to be a bandit

Garden age: 1 year and 1 month

Fans: 21

Attention: 12234 5625262728293012345678910111213141516171819202232425272930112345 search essay classification Django (5) Flask full series (5) Git (1) Interviews (1) Linux series (5) mysql&&sqlalchemy (8) python sporadic knowledge (5) py fundamentals (2) key points of py memory (2) rabbitmq (3) frame-Python module decryption (2) Kit obscene complete book (4) front-end html/css/jqurey (5) Design pattern (1) calculation Law (1) Network Foundation (1) Bug scapy (1) essay Archives July 2017 (1) June 2017 (6) May 2017 May (1) March 2017 February 2017 (2) January 2017 (14) December 2016 December 2016 (17) October 2016 September 2016 (8) August 2016 (2) June 2016 (2) blog Daniel python_ standard style latest comment @ GuoYouLI this is what I saw from a front-end website shang in the background whether it is so comprehensive.-- boss, please forget which bug2. Re: use JS to export ecxel tables and achieve file renaming @ Boss Please forget which bug I tested, method two is compatible with the ie browser processing part of the last piece of ie6+ optimization method 5, in ie9 10 and the latest do not respond but normal in ie7 8.-- GuoYouLI3. Re: use JS to export the ecxel table and implement the file rename @ GuoYouLI brtton button for an a-tag id number.-- boss, please forget which bug4. Re: what is document.getElementById ("dlink") dlink that uses JS to export ecxel tables and rename files? -- GuoYouLI5. Re:datatables plug-in application example @ tomorrow OoO Hello you are ugly, there is no way! Boss, please forget which bug reading list, comment list, recommended list. Python full stack-5.1 Murray-decorator (1) 2. Centos installation Python2.7 (1) 3. Django routing, template and model system (1)

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.