django-aggregation, grouping, f query and q query, summary

Prepare test Data Aggregation Query group query f query Q query Summary

Preparing test Data

From django.db import models # Create your models here. Class Publisher (models. Model): id = models. Autofield (primary_key=true) name = models. Charfield (max_length=32) addr = models. Charfield (max_length=32) phone = models. Integerfield def __str__ (self): return Self.name # Author of the book, designed to the author of Class Author (models. Model): id = models. Autofield (primary_key=true) name = models. Charfield (max_length=16) Author_detail = models. Onetoonefield ("Authordetail") # Multiple books = models. Manytomanyfield (to= "book") def __str__ (self): Return Self.name class book (Models. Model): id = models. Autofield (primary_key=true) title = models. Charfield (max_length=6) Price = models. Decimalfield (max_digits=5, decimal_places=2) Publish_day = models. Datefield (auto_now_add=true) # book-Publishing house multiple to one associate publisher = models. ForeignKey (to= "Publisher", to_field= "id") def __str__ (self): Return Self.title class Authordetail (models.Model): id = models. Autofield (primary_key=true) city = models. Charfield (max_length=32) email = models. Emailfield () def __str__ (self): return self.city

Aggregate Queries

Aggregate () is a terminating clause of queryset, meaning that it returns a dictionary containing some key-value pairs. The name of the key is the identifier of the aggregate value, which is the computed aggregate value. The name of the key is automatically generated according to the name of the field and the aggregate function.

From django.db.models import max,avg,f,q
models. Book.objects.all (). Aggregate (AVG ("price"))
{' Price__avg ': 192.593333}

If you want to specify a name for the aggregate value, you can supply it to the aggregation clause.

Models. Book.objects.all (). Aggregate (Avg_price=avg ("price"))
{' Avg_price ': 192.593333}

If you want to generate more than one aggregation, you can add another parameter to the aggregate () clause. So, if you also want to know the maximum and minimum value of all book prices, you can query this way:

From django.db.models import max,avg,f,q,min,count,sum
models. Book.objects.all (). Aggregate (AVG ("Price"), Max ("Price"), Min ("Price"), Sum ("price"))
{' Price__avg ': 192.593333 , ' Price__max ': decimal (' 233.33 '), ' price__min ': decimal (' 111.12 '), ' price__sum ': decimal (' 577.78 ')}

grouped Queries

Generates a separate statistic for each object in the called Queryset
count the number of authors in each book

Models. Book.objects.all (). Annotate (Authornum=count ("author"))
<queryset [<book: Book A, <book: Book Two, < Book: Books III >]>
book_list = models. Book.objects.all (). Annotate (Authornum=count ("author")) for
i in Book_list:
    print (I.authornum)
2
2
1

count the price of the cheapest book each publisher buys
Method One

Pulisher_list = models. Publisher.objects.all (). Annotate (Min_price=min ("Book__price")) for
i in Pulisher_list:
    print (I.min_price )
111.12
12.00
456.00
785.00

Method Two

TMP = models. Book.objects.all (). VALUES ("Author__name"). Annotate (Min_price=min ("price")) to
I in TMP:
    print (I.min_ Price)
Traceback (most recent called last):
  File "<input>", line 2, in <module>
attributeerror: ' Dict ' object has no attribute ' Min_price '
for i in TMP:
    print (i)

{' author__name ': ' Small One ', ' Min_price ': decimal ( ' 111.12 '}
{' Author__name ': ' Little Two ', ' Min_price ': Decimal (' 233.33 ')}
{' author__name ': ' Small ', ' min_price ': Decimal (' 111.12 ')}
{' Author__name ': None, ' Min_price ': decimal (' 12.00 ')}

a book that counts more than one author

TMP = models. Book.objects.all (). Annotate (Au=count ("author")). Filter (au__gt=1)
tmp
<queryset [<book: Book I., <book: Book Two >]>

sort the query set Queryset according to the number of authors of a book

TMP = models. Book.objects.all (). Annotate (Au=count ("author")). Order_by ("au")
tmp
<queryset [<book: Book Four, < Book: Books Five, <book: Six, <book: Book Three, <book: Book A, <book: Book Two >]>

inquire about the total price of each author's book

TMP = models. Author.objects.all (). Annotate (Total=sum ("Books__price")). VALUES ("name", "Total")
tmp
<queryset [{] Name ': ' Small One ', ' Total ': decimal (' 344.45 ')}, {' name ': ' Little Two ', ' Total ': decimal (' 689.33 ')}, {' name ': ' Small ', ' total ': ' Decimal ' (' 11 1.12 ')}, {' name ': ' Small Four ', ' Total ': none}]>

F Query

In all of the above examples, the filters we construct simply compare the field values to a constant. If we're going to compare the values of two fields, how do we do that?

Django provides an F () to do this comparison. An instance of F () can refer to a field in a query to compare the values of two different fields in the same model instance.

Book with a query ID greater than \ Less than the price

Models. Book.objects.filter (Id__gt=f ("price"))
<queryset []>

models. Book.objects.filter (Id__lt=f ("price"))
<queryset [<book: Book, <book: Book Two, <book: Book III, < Book: Books Four, <book: Book Five, <book: six >]>

Django supports the operation of subtraction and modulo between f () objects and between f () objects and constants.

Models. Book.objects.filter (Id__lt=f ("Price")/2)
<queryset [<book: Book A, <book: Book Two, <book: Book III, < Book: Four, <book: five >]>

Modify operations can also use the F function, such as the price of each book increased by 30 yuan

Models. Book.objects.all (). Update (Price=f ("price") +30)

After the revised screenshot is as follows:

Concat Links

From django.db.models.functions import Concat from
django.db.models import Value
models. Book.objects.update (Title=concat ("title"), Value ("("), Value ("First"), Value (")"))

The modified table is as follows:
q Query

Keyword parameter queries in methods such as filter () are all "and" together. If you need to execute a more complex query (such as an OR statement), you can use the Q object

Query author name is a little fairy or little witch.

Models. Author.objects.filter (Q (name= "small One") | Q (name= "small")
<queryset [<author: Xiao Yi, <author: minor >]>

You can mix & and | Operators and use parentheses to group any complex Q objects. At the same time, the Q object can be reversed using the ~ operator, which allows the combination of normal queries and retrieval (not) queries

The name of the query author is the little Fairy and not the title of the book published in 2018.

Models. Book.objects.filter (Q (author__name= "Small One") & ~q (publish_day__year=2018)). Values_list ("title")
<queryset [(' Book One ',]]>

Query functions can be mixed with Q objects and keyword parameters. All parameters that are provided to the query function (keyword parameters or Q objects) are "and" together. However, if the Q object appears, it must be in front of all keyword parameters

Models. Book.objects.filter (Q (publish_day__year=2018) | Q (publish_day__year=2017), title__icontains= "book")
<queryset [<book: Book A, <book: Book Two, <book: Book Three (part ", <book: Book Four (part), <book: Book Five (part), <book: Book Six (part) >]>

Summary

ORM Query class book (models) across tables. Model): title = models. Charfield (max_length=32) publish=models. ForeignKey (to= "Publish", to_field= "id") authors=models. Manytomanyfield (to= ' Author ', related_name= ' Booklist ') class Publish (models. Model): Name=models. Charfield (MAX_LENGTH=32) class Author (models. Model): Name=models. Charfield (max_length=32) ad=models. Onetoonefield ("Authordetail") class Authordetail (models. Model): Telephone=models.

                  Bigintegerfield () based on an object query (sql: subquery) one-to-many Relationship (Publish--book) forward query, by field: The name of the publisher that queries the Python book is book_obj=book.objects.filter (title= "Python"). #print (Book _obj.publish.name) reverse query, by table name lowercase _set: All book titles published by the publishing house Publish_obj=pu Blish.objects.filter (name= "Publishing House").----------(Publish_obj.book_set.all ()) for O BJ in PUBlish_obj.book_set.all (): Print (Obj.title) Many-to-many relationship forward query, by field:
                        Python This book is the name of all authors Book_obj=book.objects.filter (title= "Python"). First () Book_obj.authors.all () reverse query, the name of all books published by the table name lowercase _set:alex alex=a Uthor.objects.filter (name= "Alex"). Alex.bookList.all () One-to-one relationship forward query, press
                    Field: Query Alex's mobile number Alex=author.objects.filter (name= "Alex"). Alex.ad.telephone Reverse query: The name of the author of the cell phone number starting with 151 in the table name Ad=authordetail.ob

          Jects.get (telephone__startswith= "151") Ad.author.name based on Queryset and __ (sql:join statement):
                  Forward query, reverse query by field, by table name lowercase one-to-many relationship (publish--book) query the name of the publisher of the Python book Book.objects.filter(title= "Python"). VALUES ("Publish__name") for obj in Book.objects.filter (title= "Python"):         
                  temp={} temp["Publish__name"]=obj.publish.name all book titles published by the publishing house Publish.objects.filter (name= "Publication of the publishing house"). VALUES ("Book__title") many-to-many relationship Pyth On this book the names of all authors Book.objects.filter (title= "Python"). VALUES ("Authors__name") ale

                    X all published book Names Author.objects.filter (name= "Alex"). VALUES ("Book__title") one-to-one relationship

                    Check Alex's cell phone number Author.objects.filter (name= "Alex"). VALUES ("Ad__telephone")

         The name of the AuthorDetail.objects.filter (telephone__startswith= "151") is the author of the cell phone number starting with 151. VALUES ("Author__name") Extension: Exercise 1: Query the name of the publisher of the Python book Book.objects.filter (title= "Python") . VALUES ("Publish__name")          
                Publish.objects.filter (book__title= "Python"). VALUES ("name") Exercise 2: The name of all books published by the author of the cell phone number with 151 and the Publisher name Book.objects.filter (authors__ad__telephone__startswith= "151"). VALUES ("Tit
            Le "," publish__name ") Group query: Query the number of books published by each publisher Publish.objects.annotate (Count (" book__id "))  Select COUNT (*) from publish GROUP by ID