Aggregate query

Aggregate query refers to the statistical query of part or all of the data of a field in a data table. Aggregate query is required to query the average price of all books in the book data table and the total number of all books.

1. Whole table aggregation
   Import method: from django.db.models import*
   Aggregate function: Sum, Avg, Count, Max, Min
   Syntax: mymodel.objects.aggregate (result variable name = aggregate function ("column"))
   Return result: Dictionary formed by result variable name and value, {'result variable name': value}

>>> from django.db.models import *
>>> Book.objects.aggregate(Summary = Sum('price') )
{'Summary': Decimal('170.00')}
>>> Book.objects.aggregate(Summary = Avg('price') )
{'Summary': Decimal('56.666667')}

1. Grouping aggregation
   Group aggregation refers to the aggregation generated for each item of the query set by calculating the object set associated with each object in the query result to obtain the total value
   Syntax: queryset.annotate (result variable name = aggregate function ('column '))
   Return value: QuerySet
   Steps:
   • By first using the query result MyModel.objects.values to find the columns to be grouped in the query, MyModel.objects.values('column 1 ',' column 2 ') to obtain a QuerySet
   • The grouping results are obtained by grouping and aggregating through the QuerySet.annotate method that returns the results

>>> books = Book.objects.values('pub','is_active')
>>> books
<QuerySet [{'pub': 'tsinghua university press ', 'is_active': True}, {'pub': 'Machinery Industry Press', 'is_a
ctive': True}, {'pub': 'tsinghua university press ', 'is_active': True}]>

>>> books.annotate(myCount = Count('pub'))
<QuerySet [{'pub': 'tsinghua university press ', 'is_active': True, 'myCount': 2}, {'pub': 'Machinery industry
 press', 'is_active': True, 'myCount': 1}]>

>>> books.annotate(active_count = Count('is_active'))
<QuerySet [{'pub': 'tsinghua university press ', 'is_active': True, 'active_count': 2}, {'pub': 'machine
 Machinery Industry Press', 'is_active': True, 'active_count': 1}]>
>>> books.annotate(myCount = Count('pub')).filter(myCount__gt=1)
<QuerySet [{'pub': 'tsinghua university press ', 'is_active': True, 'myCount': 2}]>

Native database operations

Django can also support the direct communication of databases with sql statements
Query: use MyModel.objects.raw() to query the database
Syntax: MyModel.objects.raw(sql statement, splicing parameters)
Return value: RawQuerySet set object [only basic operations, such as loop, are supported]

>>> books = Book.objects.raw('select * from book')
>>> books
<RawQuerySet: select * from book>
>>> for book in books:
...     print(book)
...
Django_tsinghua university press _70.00_75.00
Linux_Machinery Industry Press_80.00_65.00
Python_tsinghua university press _20.00_25.00

Operate the database completely across model classes - query / update / delete

1. Import the package of cursor
   from django.db import connection
2. Use the constructor that creates the cursor class to create the cursor object, and then use the cursor object. In order to ensure that the cursor resource can be released when an exception occurs, you usually use the with statement to create it
   Syntax:
   from django.db import connection
   with connection.cursor() as cur:
   cur.execute('execute sql statement ',' splice parameter ')

admin administration background

django provides a relatively perfect interface for background management database, which can be used for debugging and testing in the development process
django will collect all registered model classes and provide a data management interface for these model classes for developers to use
Create background management account python manage.py createsuperuser

Registering custom model classes

Registration steps:
1. Import and register the model models class to be managed in admin.py in the application app
2. Call admin.site.register method to register, such as admin.site.register (user-defined model class)

Model manager class

Function: add new functions convenient for operation to the background management interface
The model manager class needs to inherit the ModelAdmin class in django.contrib.admin
usage method:

1. Define the model manager class in < app > / admin.py
   class xxxManage(admin.ModelAdmin):

2. Binding registry manager and model classes
   from django.contrib import admin
   from .models import *
   Admin.site.register (YYYY, XXXXManager) # binding YYYY model class and XXXXManager manager class
   Before binding: in the Book model class__ str__ Output format defined in
   
   After binding:
   

from django.contrib import admin
from .models import *


class BookManager(admin.ModelAdmin):
    # The list page displays columns for which fields
    list_display = ['id', 'title', 'pub', 'price', 'market_price', 'info']
    # Control list_ Fields in display, which can be linked to the modification page
    # Click the title to link to the modification page
    list_display_links = ['title']
    # Add filter
    list_filter = ['pub']
    # Add a search box [fuzzy query]
    search_fields = ['title']
    # Add fields that you can edit on the list page
    list_editable = ['price']

class AuthorManager(admin.ModelAdmin):
    list_display = ['id', 'name', 'age', 'email']

# Register your models here.
admin.site.register(Book,BookManager)
admin.site.register(Author, AuthorManager)

1. list_display controls which fields are displayed in the Admin modification list page
2. list_display_links can control lists_ Should fields in display be linked to the modify page of the object
3. list_filter setting activates the filter in the right column of the Admin modification list page
4. search_ The fields setting enables the search box on the Admin change list page
5. list_editable is set as the list of field names on the model, which will allow editing on the change list page

Meta class

class Book(models.Model):
    title = models.CharField("title", max_length=50, default='', unique=True)
    pub = models.CharField("press", max_length=100, default='')
    price = models.DecimalField("Book pricing", max_digits=7, decimal_places=2, default=0.0)
    market_price = models.DecimalField("Book retail price", max_digits=7, decimal_places=2, default=0.0)
    info = models.CharField("Book information", max_length=100, default='', blank=True)
    is_active = models.BooleanField("active",default=True)

    def __str__(self):
        return '%s_%s_%s_%s'%(self.title,self.pub,self.price,self.market_price)

    class Meta:
        db_table = 'book'
        # Give the model object an easy to understand name (singular) for display in the / admin administration interface
        verbose_name = 'books'
        # Give the model object an easy to understand name (plural) for display in the / admin administration interface
        verbose_name_plural = verbose_name

Relational mapping

One to one mapping

One to one refers to the one-to-one correspondence between real transactions
Syntax: OneToOneField (class name, on_delete= xxx) cascade deletion
class A(model.Model):
...
class B(model.Model):
Attribute = models.OneToOneField(A, on_delete = xxx)

on_delete cascade delete option

1. Cascade deletion of models.CASCADE. Django simulates the behavior of SQL constraint ON DELETE CASCADE and deletes objects containing ForeignKey
2. models.PROTECT throws ProtectError to prevent the deletion of the referenced object; [equivalent to mysql default RESTRICT]
3. SET_NULL set ForeignKey null; null = True needs to be specified
4. SET_DEFAULT sets ForeignKey as its default value. The default value of ForeignKey must be set

// Create data
>>> from oto.models import *
>>> author = Author.objects.create(name = 'Wang')
// The author is a foreign key class and must correspond to the same class object
>>> wife = Wife.objects.create(name = 'Wife Wang',author = author)
>>> author = Author.objects.create(name = 'Guo')
// author_id is the class attribute of the foreign key and must correspond to the correct field value
>>> wife = Wife.objects.create(name = 'Wife Guo',author_id = author.id)

Query:
① Forward query: query directly through foreign key attributes, which is called forward query

>>> wife = Wife.objects.get(name = 'Wife Guo')
>>> print(wife.name,'My husband is',wife.author.name)
Wife Guo My husband is Guo

② Reverse query: the party without foreign key can query the associated party by calling the reverse attribute
The reverse association attribute is "instance object. Reference class name (lower case)". When the reverse reference does not exist, an exception will be triggered. If it does exist, the object of the reference class will be returned

>>> author = Author.objects.get(id = 1)
>>> author
<Author: Author object (1)>
>>> author.wife
<Wife: Wife object (1)>
>>> print(author.wife.name)
Wife Wang

One to many mapping

One to many refers to the one to many correspondence between the fifteen realms.
One to many needs to specify specific roles and set foreign keys on multiple tables.
establish:
class A(models.Model):
...
class B(models.Model):
Attribute = models.ForeignKey("..." model class, on_delete = xx)
On must be specified_ Delete mode

from django.db import models
# Create your models here.
class Publisher(models.Model):
    name = models.CharField('name', max_length=11, unique=True)
class Book(models.Model):
    title = models.CharField('title', max_length=11)
    price = models.DecimalField('Price', max_digits=7, decimal_places=2)
    pub = models.ForeignKey('Publisher', on_delete=models.CASCADE)

// establish
>>> from otm.models import *
>>> publisher = Publisher.objects.create(name = 'tsinghua university press ')
>>> publisher = Publisher.objects.create(name = 'Peking University Press')
>>> book = Book.objects.create(title = 'HTML & CSS',price = 79.80,pub_id = 1)
>>> book = Book.objects.create(title = 'data structure',price = 35,pub_id = 1)

Query:
① Forward query [query Publisher through Book]

>>> from otm.models import *
>>> book = Book.objects.get(id = 1)
>>> book.pub.name
'tsinghua university press '

② Reverse query [query Book through Publisher]
Reverse queries have a reverse attribute of 'multiple tables'_ set [lowercase] to obtain multiple data objects corresponding to multiple tables

>>> pub1 = Publisher.objects.get(id =1)
>>> books = pub1.book_set.all() // 
>>> for book in books:
...     print(book.title)
...
HTML & CSS
 data structure
>>> books2 = Book.objects.filter(pub = pub1)
>>> for book in books2:
...     print(book.title)
...
HTML & CSS
 data structure

Many to many mapping

Many to many complex relationships between many to many expression objects, such as: everyone has different schools (primary school, junior middle school, senior high school), and each school has different students
Creating many to many relationships in mysql depends on the third table
There is no need to manually create the third table in Django, and Django completes it automatically

from django.db import models
# Create your models here.
class Author(models.Model):
    name = models.CharField('full name', max_length=11)
class Book(models.Model):
    title = models.CharField('title', max_length=11)
    authors = models.ManyToManyField(Author)

// establish
>>> author1 = Author.objects.get(id =1)
// Reverse creation
>>> book11 = author1.book_set.create(title = "Python")
>>> author2 = Author.objects.get(id =2)
>>> author2.book_set.add(book11)
//  Forward creation
>>> book = Book.objects.create(title = 'Java')
>>> book.authors.add(author1)
>>> author3 = book.authors.create(name = 'Season')

// Forward query
>>> book.authors.all()
<QuerySet [<Author: Author object (1)>, <Author: Author object (3)>]>
>>> for author in book.authors.all():
...     print(author.name)
...
Ma Zhikai
Season

// inverse query
>>> author = Author.objects.get(name = 'Ma Zhikai')
>>> books = author.book_set.all()
>>> for book in books:
...     print(book.title)
...
Python
Java