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

This article mainly introduces "how to use aggregate function in Django". In daily operation, I believe many people have doubts about how to use aggregate function in Django. Xiaobian consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "how to use aggregate function in Django". Next, please follow the editor to study!

Preface

The aggregate function in orm model is consistent with that in MySQL, and there are also some such as Sum, Avg, Count, Max, Min. Let's introduce them one by one.

Aggregate function

All aggregate functions are placed under django.db.models. And the aggregate function can not be executed alone, the aggregate function is implemented through the aggregate method. When explaining the use of aggregate functions, they are all based on the following model objects.

Class Author (models.Model): "author Model"name = models.CharField (max_length=100) age = models.IntegerField () email = models.EmailField () class Meta: db_table = 'author' class Publisher (models.Model):" Publishing House Model "" name = models.CharField (max_length=300) class Meta: db_table =' publisher' class Book ( Models.Model): "" Book Model "" name = models.CharField (max_length=300) pages = models.IntegerField () price = models.FloatField () rating = models.FloatField () author = models.ForeignKey (Author On_delete=models.CASCADE) publisher = models.ForeignKey (Publisher, on_delete=models.CASCADE) class Meta: db_table = 'book' class BookOrder (models.Model): "Book order Model"book = models.ForeignKey (" Book ", on_delete=models.CASCADE) price = models.FloatField () class Meta: db_table =' book_order'Avg

Avg: find the average. For example, you want to get the average price of all books. Then you can use the following code to implement it.

From django.db.models import Avg result = Book.objects.aggregate (Avg ('price')) print (result)

The above print results are as follows:

{"price__avg": 23.0}

The structure of price__avg is based on field__avg rules. If you want to change the default name, you can assign Avg to a keyword parameter. The sample code is as follows:

From django.db.models import Avg result = Book.objects.aggregate (my_avg=Avg ('price')) print (result)

The above print result is

{"my_avg": 23}

Count

Count: gets the number of specified objects. The sample code is as follows:

From django.db.models import Count result = Book.objects.aggregate (book_num=Count ('id'))

The above result returns the total number of books in the Book table.

In the Count class, there is another parameter called distinct, which is equal to False by default. If it is equal to True, then the duplicate values will be removed. For example, to get the total number of non-duplicated mailboxes in the author's table, you can do this through the following code:

From djang.db.models import Countresult = Author.objects.aggregate (count=Count ('email',distinct=True)) Max and Min

Max and Min: gets the maximum and minimum values of the specified object. For example, you want to get the maximum age and the minimum age in the Author table. Then you can do this through the following code:

From django.db.models import Max,Minresult = Author.objects.aggregate (Max ('age'), Min (' age'))

If the maximum age is 90, the youngest age is 10. Then the above result will be:

{"age__max": 90, "age__min": 10}

Sum

Sum: find the sum of specified objects. For example, the total sales of books are required. Then you can implement it using the following code:

From djang.db.models import Sumresult = Book.objects.annotate (total=Sum ("bookorder__price"))

The above code annotate means to add a field called total to the Book table when querying, and the data for this field is derived from the sum of the price of the BookOrder model.

The difference between aggregate and annotate

What you have in common: both methods can execute aggregate functions.

Differences:

Aggregate returns a dictionary in which the results of the execution of the aggregate function are stored. Annotate, on the other hand, returns a QuerySet object and adds an aggregate function property to the lookup model.

Aggregate does not group, while annotate uses the group by clause for grouping, and only when the group by clause is called can the aggregate function value be calculated for each piece of data.

F expression:

F expression: dynamically gets the value on a field. And this F expression, will not really go to the database to query data, it is equivalent to just play the role of a logo. For example, if you want to increase the price of each book by 10 yuan on the original basis, you can use the following code:

From django.db.models import FBook.objects.update (price=F ("price") + 10) Q expression

Q expressions: use Q expressions to wrap query conditions, and you can perform a variety of operations between conditions. And / or not, etc., so as to implement some complex query operations. Examples are as follows:

Find books with a price greater than 100 and a score of 4.85 or above:

# books = Book.objects.filter (price__gte=100,rating__gte=4.85) without Q expression # use books = Book.objects.filter of Q expression (Q (price__gte=100) & Q (rating__gte=4.85))

Find books with a price of less than 100 yuan or a score of less than 4:

Books = Book.objects.filter (Q (price__gte=100) & Q (rating__gte=4.85))

Get a book whose price is greater than 100 and the name of the book does not contain the word "biography":

Books = Book.objects.filter (Q (price__gte=100) & Q (name__icontains='')) at this point, the study on "how Django uses aggregate functions" is over, hoping to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.