Python Learning---Object-based forward/reverse query for ORM queries

the ORM Query based on object forward query vs. reverse query

queries in the form of objects

# Forward Query

    Ret1=models. Book.objects.first () print (    ret1.title) print (    ret1.price) print (    ret1.publisher)    print ( Ret1.publisher.name)  # Ret1.publisher is the book property, is a publish object, not a Queryset collection

# Reverse Lookup

Ret2=models. Publish.objects.last ()    print (ret2.name)    print (ret2.city)

#如何拿到与它绑定的Book对象呢?

    Print (Ret2.book_set.all ())  # Ret2.book_set is a Queryset collection, and all () can omit    print (Ret2.book_set.all (). VALUES (' Title '). Distinct ())  # Go Heavy

the ORM Query based on conditions forward query vs. reverse query

Single-table conditional query [double underline to implement table Association] : Use commas to separate conditional queries that implement and

#    models. Book.objects.filter (id__lt=10, id__gt=1)      # Gets the value of ID greater than 1 and less than 10 #    models. Book.objects.filter (id__in=[11, [+])      # Gets the data with ID equal to 11, 22, 33 #    models. Book.objects.exclude (id__notin=[11, [+])  # not in#    models. Book.objects.filter (title__contains= "Python") # fuzzy Match #    models. Book.objects.filter (name__icontains= "ven")    # icontains case insensitive #    models. Book.objects.filter (id__range=[1,12])         # range Bettwen and#    startswith,istartswith, EndsWith, Iendswith,        # start/xxx End with XXX

Multi-table conditional correlation query [double underline to implement table Association]

One-to-many [forward query]: use commas to separate conditional queries for and, double underline in filter and values

#  models. Book.objects.filter (title= ' Python ', id=5). VALUES (' Publish__name '). Distinct ()      # [{' publisher__city ': ' Beijing '}]  Publish__name The Publish attribute # models in this book  . Book.objects.filter (publish__id=5). VALUES (' title '). Distinct ()      # Book finds the name of a publishing house id=5 of Books #  models. Book.objects.filter (publish__id=5). VALUES (' Publish_name '). Distinct ()      # Book Find publishing house Id=5 's name

One-to-many [reverse query]: Use commas to separate conditional queries for and, double underline in filter and values

#  models. Publish.objects.filter (book__title= ' Python ', book_id=5). VALUES (' name '). Distinct ()      # [{' Name ': ' H press '}] Find the contents of the book related to publish, and the book in Book__title is the associated table name of publish

Many-to-many [forward query]: use commas to separate conditional queries for and

#  models. Book.objects.filter (title= ' Python '). VALUES (' Author__name '). Distinct ()  #  models. Book.objects.filter (author__name= ' FTL '). VALUES (' title '). Distinct ()  

Many-to-many [reverse query]: Use commas to separate conditional queries for and

#  

Attention:

1. The book in the Book__title of the forward lookup is the table name book

2. The publish__city of a forward lookup or the Publish,author in Author__name are the fields in the Book table that are bound in "book Properties"

3, one-to-many and many-to-many use no difference here

Orm Aggregation Query and group query

ORM Aggregation Query and group query

Aggregate query: Aggregate (*args,**kwargs): can be filtered by filter/values after polymerization

Returns a dictionary of aggregated values by calculating the queryset. Each parameter in aggregate () specifies a return value contained in the dictionary [the name of the key is an identifier for the aggregated value, and the value is the computed aggregate value. The name of the key is automatically generated by the name of the field and aggregate function], that is, the aggregation is generated on the query set.

From django.db.models import avg,min,sum,max>>> Book.objects.all (). Aggregate (AVG (' price ')) {' Price__avg ': 34.35}     # System definition name >>> Book.objects.all (). Aggregate (Average_price=avg (' price ')) {' Average_price ': 34.35}     # Custom Name >>> Book.objects.aggregate (AVG (' price '), Max (' price '), Min (' price '))  # Other queries

Group query: Annotate (*args,**kwargs): can be grouped by filter/values filter

You can generate aggregates for each item in the query set by calculating the collection of objects associated with each object in the query result , which can be either an average or a sum.

From django.db.models import avg,min,sum,max>>> Book.objects.values (' author_name '). Annotate (Sum (' price ')) # Each author's price is calculated according to the author's name

ORM's F Query and Q query

F query: Only applicable to integerfiled "using the F query, the default value is 0"

From Django.db.models import Fmodels.Book.objects.update (num=f (' num ') + 100)  

Q Query:

from django.db.models import q#1 Q object (DJANGO.DB.MODELS.Q) can encapsulate the keyword parameters to better apply multiple query q1=models.    Book.objects.filter (Q (title__startswith= ' P ')). All () # 2, can be combined with the &,| operator, when an operator is used for two Q object, it produces a new Q object. Q2=models. Book.objects.filter (Q (name= ' HHH ') | Q (name= ' FTL ')). All () # 3, Q object can be put in front of the ~ operator to indicate negation, can also allow the combination of negative and non-negative form Q (title__startswith= ' P ') | ~q (pub_date__year=2005) # 4, Application: Book.objects.get (q (title__startswith= ' P ') and (Q (Pub_date=date (2005, 5, 2)) |    Q (Pub_date=date (2005, 5, 6))) # 5, Q objects can be used with the keyword parameter query, but be sure to put the Q object in front of the keyword parameter query. # correct: Book.objects.get (Q (Pub_date=date (2005, 5, 2)) |         Q (Pub_date=date (2005, 5, 6)), title__startswith= ' P ') # Error: Book.objects.get (question__startswith= ' P ', Q (Pub_date=date (2005, 5, 2)) | Q (Pub_date=date (2005, 5, 6))) 

Python Learning---Object-based forward/reverse query for ORM queries