Python full stack Road--django orm detailed

ORM: (in django, The table that automatically generates the database according to the class in the code is also Called--code First)

ORM:Object Relational Mapping (relational object Mapping)

We write a class that represents a table in the database

The object that we create based on this class is a row of data in the database table

Obj.id Obj.name ..... is part of the data in the database row

Orm--first:

When we learn about the ORM in django, we can put one-to-many, many-to-many, into two ways: forward and Reverse.

Class 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 ') #外键

Forward Lookup: ForeignKey in the UserInfo table, if you start from the UserInfo table to the other table query, this is a positive operation, or vice versa if you query from the Usertype table other tables This is the reverse Operation.

Get started on our ORM search tour!!!

Build Table + Config Url+views write the corresponding function

models.py (only relevant classes in Django that only write databases Here)

Class 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 and use the default settings in Settings.

Configuration in the url.py

From django.conf.urls import urlfrom django.contrib import adminfrom app01 import viewsurlpatterns = [    url (r ' ^admin/') , admin.site.urls),    url (r ' ^user_type ', views.user_type),    url (r ' ^user_info ', views.user_info),]

views.py do not do anything first, we guarantee the normal access to the URL already set

From django.shortcuts import render,httpresponsedef user_type (req):    return httpresponse ("ok") def user_info (req):    return HttpResponse ("ok")

Insert a few data into the table first for testing:

Usertype table

UserInfo table Data insertion:

So there are two ways to create usertype Data: the first method is to add data directly to this field! and give User_type ' _id '

def user_info (request):    dic = {' username ': ' Mosson ', ' age ':, ' user_type_id ': 1}    models. UserInfo.objects.create (**dic)    return httpresponse (' OK ')

or add by object

#先获取组的对象usertype = Models. UserType.objects.fiter (id=2) #添加的时候直接添加对象就可以models. UserInfo.objects.create (username= ' Seven ', Age=18,user_type=usertype) #写成一行也行models. UserInfo.objects.create (username= ' Lile ', age=18,user_type=models. UserType.objects.filter (id=1))

The Django get method is to get a matching result from the database, return an object, and if the record does not exist, it will Error.
The Django Filter method returns a list of objects from the result of a database match, and returns [] if the record does not exist.

One-to-many orm:

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

For example, when we set up a user, we have a menu that allows us to select a user type when using a one -to-many !!

1, A pair of more forward lookup:

Positive Check: ForeignKey in UserInfo table, if according to userinfo this table to query the two linked tables together the content is positive

reverse Check : ForeignKey is not in the usertype, if according to usertype this table to query the two linked tables together the content is reverse check

Forward to check-demo1-- querying all users who are the COO

In the Django foreign key is equivalent to the simple use of __ table, the outer key that object encapsulates all the fields in the User_type table

We want to query all users for the CEO of the user, we are not based on usertype this table to check, if it is a cross-table query using "double underscore" + property

From APP01 import modelsdef 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 Result:

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?

Models. UserType.objects.get (id=1)

Obj=models. UserType.objects.get (id=1)    obj.caption==== Gets the caption obj.id====== corresponding to the ID 1 in the usertype table to    get the ID 1 in the usertype table    obj.userinfo_set  #理解为一种能力, You can get all users of the current user type/or multiple users of this user type    obj.userinfo_set.all () #获取所有用户类型为COO的用户    obj.userinfo_set.filter (username= ' Tim ') #获取用户类型为COO的并且用户为tim的用户    "    this. userinfo_set. What's The equivalent?  Models. UserInfo.objects.filter (user_type=obj)    "

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

RET = Models. UserType.objects.filter (caption= ' COO '). values (' userinfo__username ') for    item in ret:        print (item,type (item ))

Method two,

RET = Models. UserType.objects.filter (caption= ' COO '). first () for    item in  ret.userinfo_set.all ():        Print ( Item.username)

Summarize:

Forward Lookup:

Filter (when cross-table, It should be the field of the object __ Cross-table)

When I get this value, I get a line of Data. object. fields across tables

Reverse Lookup:

The filter (associated with this table indicates) automatically creates and indicates the same object, through which the fields of the table are __

Line. automatically create and indicate the same object _set. method

ORM Many-to-many Systems generate a third table:

Many-to-many and One-to-many don't have any relationship

models.py

Class 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 in the host table and in the Hostadmin table, and the third chapter has nothing to do with it, when we set up the table, the columns in the third table are fixed, respectively, The IDs of the two Tables.

To add data to the host Table:

def index (req):    #主机数据    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)    #用户数据    models. HostAdmin.objects.create (username= ' Alex ', email= ' [email protected] ')    models. HostAdmin.objects.create (username= ' Mosson ', email= ' [email protected] ')    models. HostAdmin.objects.create (username= ' aliven ', email= ' [email protected] ')    models. HostAdmin.objects.create (username= ' wusir ', email= ' [email protected] ')

Effects in the Data:

An empty relational table

Many-to-many forward, Reverse Add data

To add Data directly:

def index (request):    #正向添加数据    #找到用户dali这个    admin_obj = models. HostAdmin.objects.get (username= ' Dali ')    #找到主机    host_list = models. Host.objects.filter (id__lt=3)    #通过找到的dali的对象. Add to add data    admin_obj.host.add (*host_list)    "    admin_ Obj by vigorously this object. add to operate the host,    vigorously the ID 2 host ID is: (+)    that will generate such a table:    #2 1    #2 2 ""    return HttpResponse (' OK ')

To add data in reverse:

def index (request):    #反向添加数据    #获取主机    host_obj = models. Host.objects.get (id=3)    #获取用户列表    admin_list = models. HostAdmin.objects.filter (id__gt=1)    #和一对多一样的道理    host_obj.hostadmin_set.add (*admin_list)    #host_obj = 3   Admin id = 2 3 4    #3 2    #3 3     #3 4        return httpresponse (' OK ')

ORM Multi-to-many Custom Third table

Class 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告诉Django用那张表做关联    host = Models. Manytomanyfield (hostinfo, 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 add 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 go to add data!

You cannot use the first method to add an object!

Now we have the third table of this object, we do not need to tube two other tables, directly added on the line! 0 0!

Add hosts and users--

def 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= ' [email protected] ')    models. UserMap.objects.create (username= ' seven ', email= ' [email protected] ')    models. UserMap.objects.create (username= ' Mosson ', email= ' [email protected] ')    return httpresponse (' OK ')

Insert data into the third Table---method 1:

Inserting a single piece of data

def 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 two ways to compare and query

Query-method one:

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

    #正向查    admin_obj = models. HostAdmin.objects.get (id=1)    admin_obj.host.all ()    #反相差    host_obj = models. Host.objects.get (id=1)    Host_obj.hostadmin_set.all ()

Query--method two:

With the second method there is no forward and reverse such a said, direct check can!

    Relation_list = Models. HostRelation.objects.all () for    item in relation_list:  #每一个item就是一个关系        print (item.user.username)        Print (item.host.hostname)

Relation_list = Models. HostRelation.objects.filter (user__username= ' Mosson ') for    item in relation_list:  #每一个item就是一个关系        Print (item.user.username)        print (item.host.hostname)

The second way you can find all the relationships, the first way you can find all the relationship tables?

The first way can only find a person to manage the machine, you can not find the corresponding relationship!

The role of Select_related:

Class Usertype (models. Model):    caption = Models. Charfield (max_length=32) class UserInfo (models. Model):    user_type = models. ForeignKey (' usertype ') #这个user_type是一个对象, The object encapsulates the ID and caption    username = models. Charfield (max_length=32) Age    = Models. Integerfield ()

Select_related role, He is used to optimize the query, no he can also, using it is mainly used to optimize the ForeignKey

def index (request):    ret = models. UserInfo.objects.all ()    #咱们看下他执行的什么SQL语句    print (ret.query) "select" app01_userinfo "." ID "," app01_userinfo "." user_type_id "," App01_userinfo "." Username "," app01_userinfo "." Age "from" App01_userinfo "'"

plus, What's select_related like?

def user_info (request):    ret = models. UserInfo.objects.all (). select_related ()    #咱们看下他执行的什么SQL语句    print Ret.queryselect "app01_userinfo". " ID ",        " app01_userinfo "." user_type_id ",        " app01_userinfo "." Username ",        " App01_userinfo "." Age ",        " App01_usertype "." ID ",        " App01_usertype "." Caption "        from       " app01_userinfo "INNER joins" app01_usertype "on (" app01_userinfo "." user_type_id "=" App01_usertype "." ID ")

so, select_related is the optimization of the query!

The ORM even table operation Combing:

One or one to multiple creation

1. Create data

Creating with objects

Or create from the Object field _id

2. find

Forward Lookup

Use double underline ' __ ' on cross-table when passing filter

At the time of getting it worth passing. cross-table

Reverse Lookup

Django automatically generates table name _set

Other operations are the same as forward lookups

two, many pairs of

1. Automatically generate relational tables

An indirect way to get a relational table, if it is forward: an object of one row of Data. The Manytomany dictionary is reversed: an object of one row of Data. table name _set

2, Custom Relationship Table (recommended) whether to add, modify only the relationship table action on the line

third, select_related

Used to optimize queries, one time to load the table of the query and the Foriegnkey associated tables into memory at Once.

The F and Q in Django

F: used to bulk modify data (using the values of the query Criteria)

Demo: for example, I have a price column, I want to increase the price by 10 or some self-increase 10

# from Django.db.models import F # Models. Tb1.objects.update (num=f (' num ') +10)

Q: used to make conditional queries.

The default Django query is only and operates as Follows:

Models. UserInfo.objects.filter (username= ' Mosson ', age= ' 18 ')

Find data with user Name: Mosson and age=18

Is there such a situation: Username=mosson or Username=wusir or Username=alex and age=18 demand? Native queries are not supported! so I used the q~.

First step: #生成一个搜索对象search_q = q () #在生成两个搜索对象search1 = q () search2 = q () second step: #标记search1中的搜索条件为  ' or '  query Search1.connector = ' OR ' #把搜索条件加入到search1中search1. children.append ((' field name ', ' field content ')) search1.children.append ((' field name ', ' field contents ')) Search1.children.append (' Field name ', ' field content ')) search1.children.append ((' field name ', ' field content ')) #标记search2中的搜索条件为  ' or '  Query search2.connector = ' OR ' #把搜索条件加入到search2中search2. children.append ((' field name ', ' field contents ') search2.children.append ((' field name ') , ' field contents ')) search2.children.append ((' field name ', ' field content ')) search2.children.append ((' field name ', ' field contents ')) step three: #把多个搜索条件进行合并search_ Q.add (search1, ' and ') search_q.add (search2, ' and ') fourth step: #执行搜索models. HostInfo.objects.filter (search_q)

Instance:

When I get the search condition on the front end I write the same type of search as a dictionary {field name: [conditional Union list]}, so that I can use the loop dictionary to divide the search criteria into different sub-conditions when querying.

 function Searchsubmit () {//empties The current list $ (' #table-body '). children (). Remove ();            Set an empty dictionary Search_dic = {};            Find all the search box body var search_data = $ ('. Inputs '). Find ("input[is-condition= ' true ']");                Loop found content $.each (search_data,function (index,data) {//get Search type/*                Here's a note: focus:: here's what you can do with Django q, where we define the type of search we're going to be able to write directly to the "fields or conditions in the library we want to search for!!!" As Follows: <input is-condition= "true" type= "text" placeholder= "comma split multi-condition" class= "form-control no-radius" name= "ho                Stname "/> */var search_name = $ (this). attr (' name ');                Gets the value of the search var Search_value = $ (this). val ();                If (search_dic.hasownproperty (search_name)) {//determine if there is a 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 (c Allback) {$ (' #table-body '). append (callback)});//search button End

Focus:

In the front-end we define the Name property of input, We can directly define the "field name" in the database, and in Django Q support the cross-table operation "double underscore", So we can define the name when the double underline operation is defined directly

Search1.children.append (' field name ' __ ' cross-table field name ', ' cross-table field contents ')

@login_authdef search_info (request): #获取用户请求的数据 user_post = json.loads (request.        get[' search_list ') Print user_post #生成搜索对象 serach_q = Q () #循环字典并生成搜索条件集合 for k,v in User_post.items ():        #生成一个搜索结合 q = q () #生命集合中的搜索条件为 ' or ' condition q.connector = ' or ' #循环字典中的value, value is the set of conditions passed by the front end. For I in V: #在搜索条件集合中增加条件, the condition is tuple form, K is the key! in the dictionary Key is a field name or a cross-table column name, or a #i为字典中的vlaue中的元素 such as support _gt, for the condition # q.children.append ((K,I)) #没循环一次后后, He Add to the total search condition Serach_q.add (Q, ' and ') #使用总的搜索条件进行查询 data = Models.                 HostInfo.objects.filter (serach_q) #拼接字符串并返回 html = [] for I in Data:html.append ("<tr>" + "<td>" + "<input type= ' checkbox ' >" + "</td>" + "<td name= ' host_id ' >                "+ '%s '%i.id +" </td> "+" <td name= ' host_name ' edit= ' true ' > "+ i.hostname +" </td> "+ "&LT;TD name= ' host_ip ' edit= ' true ' >"+ i.hostip +" </td> "+" <td name= ' host_port ' edit= ' true ' > ' + '%s '%i.hostport + ' </td> ' + "&LT;TD name= ' host_business ' edit= ' true ' edit-type= ' select ' global-key= ' business ' select-val= '" + '%s '%i. hostbusiness_id + ">" + i.hostbusiness.hostbusiness + "</td>" + "<td name= ' host_status ' edit= ' t Rue ' edit-type= ' select ' global-key= ' STATUS ' select-val= ' "+ '%s '%i.hoststatus_id +" ' > "+ i.hoststatus.hoststatus +" & lt;/td> "+" </tr> ") html = mark_safe (" ". join (html)) return HttpResponse (HTML)

　　

Python full stack Road--django orm detailed