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

This article introduces the relevant knowledge of "the query operations commonly used in Django and the introduction of aggregate query and grouping query". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Common operation all (): query all results filter (* * kwargs): it contains objects that match the given filter criteria get (* * kwargs): returns objects that match the given filter criteria, and returns one and only one result. If more than one object meets the filter criteria or none, an error will be thrown. Exclude (* * kwargs): it contains objects values (* field) that do not match the given filter criteria: returns a ValueQuerySet-- and a special QuerySet. Instead of a series of model instantiated objects, the result is an iterative dictionary sequence values_list (* field): very similar to values (), which returns a tuple sequence Values returns a dictionary sequence order_by (* field): sort the query results reverse (): sort the query results in reverse, and note that reverse () can usually only be called on QuerySet with a defined order (specify ordering in the Meta of the model class or call the order_by () method). Distinct (): removes duplicate records from the returned results (if your query spans multiple tables, you may get duplicate results when calculating QuerySet. You can use distinct () at this point, and note that de-duplicating by field is supported only in PostgreSQL.) Count (): returns the number of objects in the database that match the query (QuerySet). First (): returns the first record last (): returns the last record exists (): returns True if the QuerySet contains data Otherwise return False return QuerySet object method all () filter () exclude () order_by () reverse () distinct () special QuerySetvalues () returns an iterative dictionary sequence values_list () returns an iterative meta-ancestor sequence returns a specific object get () first () last () returns a Boolean method exists () returns a number method count () magical double underscore of a single table query Line models.Tb1.objects.filter (id__lt=10 Id__gt=1) # get values with id greater than 1 and less than 10 models.Tb1.objects.filter (id__in= [11,22,33]) # get data models.Tb1.objects.exclude with id equal to 11,22,33 (id__in= [11,22) 33]) # not in models.Tb1.objects.filter (name__contains= "ven") # get models.Tb1.objects.filter with "ven" in the name field (name__icontains= "ven") # icontains case insensitive models.Tb1.objects.filter (id__range= [1,3]) # id range is 1 to 3 Bettwen and # equivalent to SQL is also similar: startswith,istartswith, endswith, iendswith # date fields can also: models.Class.objects.filter (first_day__year=2017) ForeignKey operation forward lookup object lookup (across tables)

Syntax: object. Associated field. Field

Example:

Book_obj = models.Book.objects.first () # first book object print (book_obj.publisher) # get the publishing house object associated with this book print (book_obj.publisher.name) # get the name field lookup of the publishing house object (across tables)

Syntax: associated field _ _ field

Example:

Print (models.Book.objects.values_list ("publisher__name")) reverse operation object lookup

Grammar: obj. Table name _ set

Example:

Publisher_obj = models.Publisher.objects.first () # find the first publishing house object books = publisher_obj.book_set.all () # find all the books published by the first publishing house titles = books.values_list ("title") # find the title field of all the books published by the first publishing house

Syntax: table name _ _ field

Example:

Titles = models.Publisher.objects.values_list ("book__title") ManyToManyFieldclass RelatedManager

The Association Manager is a manager that is used in the context of one-to-many or many-to-many associations.

It exists in the following two situations:

Reverse query of foreign key relations

Many-to-many relationship

To put it simply, when there may be multiple objects after the point, you can use the following methods

Method

Create (): create a new object, save it, and add it to the associated object set, returning the newly created object.

Import datetimemodels.Author.objects.first () .book_set.create (title= "Tale of Tomato", publish_date=datetime.date.today ())

Add (): adds the specified model object to the associated object set.

Add object author_objs = models.Author.objects.filter (id__lt=3) models.Book.objects.first () .authors.add (* author_objs) add id models.Book.objects.first () .authors.add (* [1,2])

Set (): updates the associated object of the model object.

Book_obj = models.Book.objects.first () book_obj.authors.set ([2,3])

Remove (): removes the executed model object from the associated object set

Book_obj = models.Book.objects.first () book_obj.authors.remove (3)

Clear (): removes all objects from the associated object set.

Book_obj = models.Book.objects.first () book_obj.authors.clear ()

Note: for ForeignKey objects, the clear () and remove () methods exist only when null=True.

For example:

When null=True is not set in the ForeignKey field

Class Book (models.Model): title = models.CharField (max_length=32) publisher = models.ForeignKey (to=Publisher)

There are no clear () and remove () methods:

Models.Publisher.objects.first (). Book_set.clear () Traceback (most recent call last): File "", line 1, in AttributeError: 'RelatedManager' object has no attribute' clear'

When the ForeignKey field sets null=True

Class Book (models.Model): name = models.CharField (max_length=32) publisher = models.ForeignKey (to=Class, null=True)

There are clear () and remove () methods:

Models.Publisher.objects.first (). Book_set.clear () aggregate query and grouping query aggregation

Aggregate () is a termination clause of QuerySet, meaning that it returns a dictionary that contains some key-value pairs.

The name of the key is the identifier of the aggregate value, and the value is the calculated aggregate value. The name of the key is automatically generated based on the name of the field and the aggregate function.

Built-in functions used:

From django.db.models import Avg, Sum, Max, Min, Count

Example:

From django.db.models import Avg, Sum, Max, Min, Countmodels.Book.objects.all (). Aggregate (Avg ("price")) {'price__avg': 13.233333}

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

Models.Book.objects.aggregate (average_price=Avg ('price')) {' average_price': 13.233333}

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

Models.Book.objects.all () .aggregate (Avg ("price"), Max ("price"), Min ("price") {'price__avg': 13.233333,' price__max': Decimal ('19.90'),' price__min': Decimal ('9.90')} grouping

Suppose you now have a company staff table:

We use the native SQL statement to calculate the average salary by partial grouping:

Select dept,AVG (salary) from employee group by dept

ORM query:

From django.db.models import AvgEmployee.objects.values ("dept") .annotate (avg=Avg ("salary")) .values (dept, "avg")

Grouping of connected table queries:

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.