CRUD (add, delete, modify, query)

1 increase

There are two ways to add data.
1)save
By creating a model class object, execute the save() method of the object and save it to the database.

>>> from datetime import date
>>> book = BookInfo(
    btitle='Journey to the West',
    bpub_date=date(1988,1,1),
    bread=10,
    bcomment=10
)
>>> book.save()
>>> hero = HeroInfo(
    hname='Sun WuKong',
    hgender=0,
    hbook=book
)
>>> hero.save()
>>> hero2 = HeroInfo(
    hname='Zhu Bajie',
    hgender=0,
    hbook_id=book.id
)
>>> hero2.save()

2)create
Through the model class objects. Save with create().

>>> HeroInfo.objects.create(
    hname='Sha Wujing',
    hgender=0,
    hbook=book
)
<HeroInfo: Sha Wujing>

2 query

2.1 basic query

get queries a single result and throws a model class if it does not exist DoesNotExist exception.
all query multiple results.
count the number of query results.

>>> BookInfo.objects.all()
<QuerySet [<BookInfo: Legend of Shooting Heroes>, <BookInfo: Tianlong Babu>, <BookInfo: Xiaoao Jianghu>, <BookInfo: Snow mountain flying fox>, <BookInfo: Journey to the West>]>
>>> book = BookInfo.objects.get(btitle='Journey to the West')
>>> book.id
5

>>> BookInfo.objects.get(id=3)
<BookInfo: Xiaoao Jianghu>
>>> BookInfo.objects.get(pk=3)
<BookInfo: Xiaoao Jianghu>
>>> BookInfo.objects.get(id=100)
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/Users/delron/.virtualenv/dj/lib/python3.6/site-packages/django/db/models/manager.py", line 85, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/Users/delron/.virtualenv/dj/lib/python3.6/site-packages/django/db/models/query.py", line 380, in get
    self.model._meta.object_name
db.models.DoesNotExist: BookInfo matching query does not exist.

>>> BookInfo.objects.count()
6

2.2 filtering query

Implement the where function in SQL, including

• filter filters out multiple results
• exclude excludes the remaining results that meet the conditions
• get filter single results
  For the use of filter conditions, the above three methods are the same, so only filter is used to explain.
  The expression syntax of filter conditions is as follows:

Attribute name__Comparison operator=value
# Two underscores are used between the property name and the comparison operator, so the property name cannot include multiple underscores

1) Equal
exact: indicates judgment, etc.
Example: query the book with number 1

BookInfo.objects.filter(id__exact=1)
It can be abbreviated as:
BookInfo.objects.filter(id=1)

2) Fuzzy query
contains: whether to include.
Note: if you want to include% without escape, you can write it directly.
Example: query the books whose title contains' biography '.

	BookInfo.objects.filter(btitle__contains='pass')

Startswitch, endswitch: start or end with the specified value
Example: query books whose titles end with 'department'

BookInfo.objects.filter(btitle__endswith='Department')

The above operators are case sensitive. Adding i before these operators means that they are case insensitive, such as iexact, icontains, istartswitch, and friendswith

3) Empty query
isnull: whether it is null.
Example: query books whose book name is not empty.

BookInfo.objects.filter(btitle__isnull=False)

1. Multiple queries
   In: whether it is included in the specified item.
   Example: query books numbered 1 or 3 or 5

BookInfo.objects.filter(id__in=[1, 3, 5]) 

5) Comparison query

• gt greater than (greater then)
• gte greater then equal
• lt less than (less then)
• lte less than or equal
  Example: query books with number greater than 3

BookInfo.objects.filter(id__gt=3)

Operators that are not equal to, use the exclude() filter.
Example: query books whose number is not equal to 3

BookInfo.objects.exclude(id=3)

1. Date query
   year,month,day,week_day, hour, minute, second: calculate the attributes of date time type
   Example: query the books published in 1980.

BookInfo.objects.filter(bpub_date__year=1980)

For example: query books published after January 1, 1980.

BookInfo.objects.filter(bpub_date__gt=date(1990, 1, 1))

F object

Previous queries used to compare object attributes with constant values. How do you compare the two attributes? A: Using F object, it is defined in Django db. Models.
The syntax is as follows:

F(Attribute name)

Example: query books with reading volume greater than or equal to comments

from django.db.models import F

BookInfo.objects.filter(bread__gte=F('bcomment'))

You can use arithmetic operations on F objects.
Example: query books with more than twice the amount of comments.

BookInfo.objects.filter(bread__gt=F('bcomment') * 2)

Q object

Multiple filters call one by one to represent the logic and relationship, which is the same as the and keyword in the where part of the sql statement.
Example: query books with reading volume greater than 20 and number less than 3.

BookInfo.objects.filter(bread__gt=20,id__lt=3)
or
BookInfo.objects.filter(bread__gt=20).filter(id__lt=3)

If you need to implement the query of logic or or, you need to use the Q() object combined with the | operator. The Q object is defined in Django db. Models.
The syntax is as follows:

Q(Attribute name__operator=value)

Example: query books with reading volume greater than 20 and rewrite it as Q. the object is as follows.

from django.db.models import Q

BookInfo.objects.filter(Q(bread__gt=20))

Q objects can be connected by &, |, & represents logical and, | represents logical or.

For example, only Q objects can be used to query books with reading volume greater than 20 or number less than 3

BookInfo.objects.filter(Q(bread__gt=20) | Q(pk__lt=3))

The ~ operator can be used before the Q object to represent non not.
Example: query books whose number is not equal to 3.

BookInfo.objects.filter(~Q(pk=3))

Aggregate function

The aggregate function is called using the aggregate() filter. Aggregation functions include Avg average, Count quantity, Max Max max, Min Min, Sum, which are defined in Django db. Models.
Example: query the total reading volume of books.

from django.db.models import Sum

BookInfo.objects.aggregate(Sum('bread'))

Note that the return value of aggregate is a dictionary type. The format is as follows:

{'Attribute name__Aggregate class lowercase':value}
  as:{'bread__sum':3}

The aggregate() filter is generally not used when count is used.
Example: query the total number of books.

BookInfo.objects.count()

Note that the return value of the count function is a number.

2.3 sorting

Using order_ Sort results by

BookInfo.objects.all().order_by('bread')  # Ascending order
BookInfo.objects.all().order_by('-bread')  # Descending order

2.4 Association query

One to many access syntax:
A corresponding model class object Multiple corresponding model class names are lowercase_ set
Example:

b = BookInfo.objects.get(id=1)
b.heroinfo_set.all()

Multiple to one access syntax:
Multiple corresponding model class objects Attribute name of relation class in multiple corresponding model classes
Example:

h = HeroInfo.objects.get(id=1)
h.hbook

id syntax for accessing a corresponding model class associated object:
Multiple corresponding model class objects Association class properties_ id
Example:

h = HeroInfo.objects.get(id=1)
h.hbook_id

Association filtering query

Query the first mock exam data from a multi model class.
The syntax is as follows:

Association model class name lowercase__Attribute name__Conditional operator=value

Note: if there is no "_ operator" part, it means equal to.
Example:
Query books and ask the book hero to be "Monkey King"

BookInfo.objects.filter(heroinfo__hname='Sun WuKong')

Query books and require the description of heroes in books to include "eight"

BookInfo.objects.filter(heroinfo__hcomment__contains='eight')

Query the first mock exam data from multiple model classes:
The syntax is as follows:

The first mock exam class associated attribute name__The first mock exam attribute name__Conditional operator=value

Note: if there is no "_ operator" part, it means equal to.
Example:
Check all heroes in the book entitled "Tianlong Babu".

HeroInfo.objects.filter(hbook__btitle='Tianlong Babu')

Query all heroes who read more than 30 books

HeroInfo.objects.filter(hbook__bread__gt=30)

3 modification

There are two ways to modify and update

1)save
Modify the properties of the model class object, and then execute the save() method

hero = HeroInfo.objects.get(hname='Zhu Bajie')
hero.hname = 'Pig Wuneng'
hero.save()

2)update
Use model classes objects.filter().update() returns the number of rows affected

HeroInfo.objects.filter(hname='Sha Wujing').update(hname='Monk Sha')

4 delete

There are two ways to delete
1) Model class object delete

hero = HeroInfo.objects.get(id=13)
hero.delete()

2) Model class objects.filter().delete()

HeroInfo.objects.filter(id=14).delete()