Discussion on the models&orm of 13.Django database (i.)

First, use the Django ORM to prepare the operation.
Django supports Sqlite,mysql, Oracle,postgresql database by default.
The SQLite database is used by default in Django projects, with the following settings in open settings:

When we want to change to MySQL database, we need to make the following changes in settings.py.
DATABASES = {
' Default ': {
' ENGINE ': ' Django.db.backends.mysql ',
' NAME ': ' test_db ', #数据库名称
' USER ': ' Root ', #连接mysql时使用的用户名
' PASSWORD ': ' 123456 ', #连接mysql使用的密码
' HOST ': ' 127.0.0.1 ', #mysql的ip
' PORT ': ' 3306 ', #连接mysql的端口.
}
}

When we complete the configuration settings for the connection data, restart the Django project and the error will be made!
No module named MySQLdb
This is because Django defaults to the module that you import to operate MySQL is MYSQLDB, but MySQLdb is a big problem for py3, so the module we need is pymysql.

So how to solve it?
First, locate the init. py file under the project directory.
Then write two lines of code in this file:
Import Pymysql
Pymysql.install_as_mysqldb ()
Then, this error will be resolved.
(If the error is still ...) Please check if your pymsql is installed ... ）

Second, before the table (model) is created, you need to understand the concept.
First, let's assume the following concepts, fields, and relationships.

(1) A pair of relations:
If you say that a database has two tables, one of which is the author's name, and the other is the author's details.
The first table holds the author's name, the second table is saved, the author's details, gender, age, email, etc.
In this case, the author's name and the author's details are a one-to-a-kind relationship, so there is no need to split the one-to-two relationship into separate tables.

(2) Many-to-many relationships:
Take the publisher for example, if the publisher's data in a table, where the bread contains the name, address, city, province, country and website.
Book information contains the title and date of publication, but a book may have multiple authors, but an author can write more than one book.
So, many-to-many relationships are like the relationship between the author and the book.

(3) A one-to-many relationship:
For example, a book can only be published by a publisher, but a publisher may publish a lot of books, so that books for publishers, is a one-to-many relationship.
This one-to-many relationship is also known as a foreign key.

Third, about the creation of the table.
models.py
Class UserGroup (models. Model):
UID = models. Autofield (Primary_key=true)
Caption = models. Charfield (Max_length=32,unique=true)
CTime = models. Datetimefield (Auto_now_add=true, Null=true)
Uptime = models. Datetimefield (Auto_now=true, Null=true)

Class UserInfo (models. Model):

ID column, self-increment, primary key

# 用户名列，字符串类型，指定长度# 字符串、数字、时间、二进制username = models.CharField(max_length=32,blank=True,verbose_name=‘用户名‘)password = models.CharField(max_length=60, help_text=‘pwd‘)email = models.CharField(max_length=60)test = models.EmailField(max_length=19,null=True,error_messages={‘invalid‘: ‘请输入密码‘})#user_group_id 数字user_group = models.ForeignKey("UserGroup",to_field=‘uid‘) # (uid,catption,ctime,uptimew)user_type_choices = (    (1, ‘超级用户‘),    (2, ‘普通用户‘),    (3, ‘访客‘),)user_type_id = models.IntegerField(choices=user_type_choices,default=1)

The above code, each data model is a subclass of Django.db.models.Model, and its parent model contains all the necessary methods for interacting with the database.
Next, each model is equivalent to a single database table (many-to-many relationship exceptions, which generate one more relational table), and each attribute is also a field in the table. The property name is the field name, and its type (for example, Charfield) corresponds to the field type of the database (for example, varchar). You can be aware of the other types that correspond to what fields are in the database.

And then, under careful thought, the three relationships that were mentioned above:
They are one-to-many, many-to-many, respectively.
One: To achieve the essence of a single, that is, the foreign key, (author_id is foreign key) on the basis of the relationship to the external key added a unique=true attribute;

One-to-many: is the primary foreign key relationship; (foreign key)

Many-to-many: automatically create a third table (and of course we can create a third table ourselves: two foreign key.

Four, the basic ORM additions and deletions to investigate.
(1) Increase:
From app01.models Import *

#create方式一: Author.objects.create (name= ' Kodakumi ')

#create方式二: Author.objects.create (**{"name": "Hamasakiayumi"})

！！ Attention! When using the features of MySQL InnoDB, it is necessary to use the Save method to insert the record.!!

#save方式一: Author=author (name= "Amuro")
Author.save ()

#save方式二: Author=author ()
Author.name= "Amuro"
Author.save ()

(2) by deleting:
Models. UserInfo.objects.filter (username= "Amuro"). Delete ()

(3) Change:
Models. UserInfo.objects.filter (id=3). Update (password= "130")

---------------the Save Method---------
Obj=models. UserInfo.objects.filter (id=3) [0]
Obj.password = ' 130 '
Obj.save ()

(4) Check:

#result = models.UserInfo.objects.all() #列出表所有记录，返回一个QuerySet类型。

！！ Here is a brief introduction to what is the Queryset type!
Here, we can think of Queryset as a list, which has many objects, each of which is the data in the database!!

#result = models.UserInfo.objects.filter(username=‘root‘,password=‘123‘) #按条件查询    查询相关API如下：    【1】filter(**kwargs):      它包含了与所给筛选条件相匹配的对象。    filter方法的参数补充：    #在这以age列做例子：    age_gt = 1 #age大于1的。    age_lt = 1 #age小于1的。    age_lte = 1 #age小于等于1    age_gte = 1 #age大于等于1    age_in = [1,2,3] # 找出age为1或2或3的（只要列表中有的就可以）    age_range = [1,2]#age为这个范围的。    age_startwith = ‘xxx‘ #age列以xxx开头的    age_contains = ‘xxx‘ #包含xxx的。    【2】all():                 查询所有结果。    【3】get(**kwargs):         返回与所给筛选条件相匹配的对象，返回结果有且只有一个，如果符合筛选条件的对象超过一个或者没有都会抛出错误。#===============================#下面是对查询结果进行处理的API【1】values(field):        返回一个ValueQuerySet——一个特殊的QuerySet，运行后得到的并不是一系列 model的实例化对象，而是一个可迭代的字典序列.需要特别注意！！values这个方法可以用来分组！！

Values can take data from one or more columns in all records in a table.
For example:
V1 = models. UserInfo.objects.values (' name ') #select Userinfo.name from UserInfo
Print (v1.query) #获取到生成的sql语句.
Select App01_userinfo.name from UserInfo

！！ Combine by the values method and then through the. Annotate!
From Django.db.models import Count
V2 = models. UserInfo.objects.values (' name '). Annotate (Count_num=count (' id '))
Print (V2.query)
#select ' app01_userinfo '. ' Id ', COUNT (' app01_userinfo '. id) as "count_num" from "App01_userinfo" group By ' App01_userinfo '. Id '

！！ Combined query results can also be filtered by the filter Method!

From Django.db.models import Count
V2=models. UserInfo.objects.values (' name '). Annotate (Count_num=count (' id ')). Filter (Count_num = 2)
! #筛选出count_num = record for > 2.
Print (V2.query)
#select ' app01_userinfo '. ' Id ', COUNT (' App01_userinfo '. id) as "count_num" from "App01_userinfo" group By ' App01_userinfo '. Id ' have COUNT (' app01_ UserInfo '. Id ') > 2
#! that is, the filter is placed in the back, and the having!! in the SQL statement appears.

【2】order_by(*field):      对查询结果排序。例：user_list = models.UserInfo.objects.all().order_by(‘id‘) #以id列为基准从小到大排序。user_list = models.UserInfo.objects.all().order_by(‘-id‘) #以id列为基准从大到小逆向排序。    user_list = models.UserInfo.objects.all().order_by(‘-id‘，‘name‘) #以id列为基准从大到小逆向排序，当id列有值重复时，按照name从小到大排序。

"3" reverse (): Reverses the results of the query.
"4" distinct (): Removes duplicate records from the result.
"5" Values_list (*field): It is very similar to values (), it returns a tuple sequence, and values returns a dictionary sequence.
"6" Count (): Returns the number of objects in the database that match the query (QuerySet).
"7" First (): Return to record one
"8" Last (): Returns the final record
"9" exists (): Returns True if Queryset contains data, otherwise false.

Single-table conditional query:
Models. Tb1.objects.filter (id=10,name= ' test ') #找出id = 10 and the Name field is a record of test.
！ ####### #下面是神奇的双(underline)! ########
Models. Tb1.objects.filter (IDlt=10, idgt=1) # Gets a value with an ID greater than 1 and less than 10
Models. Tb1.objects.filter (IDin=[11, 22, 33]) # Get data with ID equal to 11, 22, 33
Models. Tb1.objects.exclude (id__in=[11, [+]) # in

is empty:
Entry.objects.filter (Pub_date__isnull=true)

Five, through the foreign key to achieve a one-to-many example:
models.py
Class business (models. Model):
#id
Caption = models. Charfield (MAX_LENGTH=32)

Class Host (models. Model):
Nid = Models. Autofield (Primary_key=true)
hostname = models. Charfield (Max_length=32,db_index=true)
IP = models. Genericipaddressfield (protocol= ' IPv4 ', db_index=true)
Port = models. Integerfield ()
Busi = models. ForeignKey (to= ' business ', to_field= ' id ') #创建外键, this foreign key is subject to the ID field of the business of the table.
However, it is important to note that the content of this busi variable is not a certain value, but rather a special object that can be used to fetch records of the corresponding IDs in the business table.!

When a foreign key relationship is created, three data is inserted into the business table:
+----+-----------+
| ID | Caption |
+----+-----------+
| 1 | Operation and Maintenance Department |
| 2 | Development Department |
| 3 | Testing Department |
+----+-----------+

Then insert four host information in the host table:
+-----+----------+---------------+------+---------+
| Nid | hostname | IP | Port | busi_id |
+-----+----------+---------------+------+---------+
| 1 | host1 | 192.168.100.1 | 80 | 1 |
| 2 | Host2 | 192.168.100.5 | 80 | 1 |
| 3 | Host3 | 192.168.100.6 | 80 | 2 |
| 4 | Host4 | 192.168.100.7 | 80 | 2 |
+-----+----------+---------------+------+---------+

At this point, use the Django Orm to query the tables:
views.py:

DEF host (Request):
H1 = models. Host.objects.all ()
For row in H1:
Print (row.nid,row.hostname,row.ip,row.busi.caption)
Return HttpResponse (' host ')
#busi with just one row of data from another table, that's the nature of the foreign key!
The results are as follows:
1 host1 192.168.100.1 operation and Maintenance department
2 host2 192.168.100.5 operation and Maintenance department
3 Host3 192.168.100.6 Development Dept.
4 Host4 192.168.100.7 Development Dept.

Vi. about F,Q
F: When you want to make changes to the database records, you can quickly get to the previous value, to do the operation.
Like what:
Models. UserInfo.objects.all (). Update (Age=f (' age ') +1)

Q: For complex conditional queries, the Q object (DJANGO.DB.MODELS.Q) can encapsulate the keyword parameters to better apply multiple queries. You can combine the & (and), | (or), ~ (not) operator, when an operator is used for two Q objects, which produces a new Q object.
For example:
Usage 1:
Models. UserInfo.objects.filter (Q (id=1) | Q (id__gt=2))
# #查找出id为1或者2的记录.

Usage 2: Create a Q object directly.
Q1 = Q () #首先创建一个Q对象.
Q1.connector = ' OR ' #Q对象中的查询条件的连接方式.
Q1.children.append (' id ', 1)
The Q1.children.append ((' id ', 2)) #在Q1这个对象里添加两个查询条件.

More complex conditions are nested.
Q1 = Q ()
Q1.connector = ' OR '
Q1.children.append (' id ', 1)
Q1.children.append (' id ', 2)

Q2 = Q ()
Q2.connector = ' OR '
Q2.children.append (' name ', ' Ayu ')
Q2.children.append (' name ', ' Hamasaki ')

con = Q ()
Con.add (Q1, ' and ')
Con.add (Q2, ' and ')
#最后生成的结果是 (id=1 or id=2) and (Name=ayu or Name=hamasaki)

Discussion on the models&orm of 13.Django database (i.)