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

This article mainly explains "how to understand Django ORM operation". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to understand Django ORM operation".

Query

Aggregation operation

Aggregation operation, do not be frightened by the name, usually used to filter some data, to find the average, and so on.

For example, seek the total price and average price of all books

Native sql

SELECT SUM (price) AS "total price of all books", avg (price) AS "average price of all books" FROM web_book

T SUM (price) AS "total price of all books", avg (price) AS "average price of all books" FROMweb_book

Execution result

ORM

Price = models.Book.objects.all () .aggregate (Sum ("price"), Avg ("price"),) print (price)

Execution result

You can find that it is the same as above, but you will find that the column name is the field _ _ aggregate function name by default.

Native sql can specify the column names that are displayed, and so can ORM.

Code

# package to be imported from django.db.models import Avg,Sum price = models.Book.objects.all () .aggregate (total price of all books = Sum ("price"), average price of all books = Avg ("price"),) print (price)

Execution result

Note: the type of price is directly dict, so you can't view the native sql here.

But the native SQL for the above ORM is indeed the same, so that's fine.

Grouping operation

The grouping operation is to compress a column with the same value, and then you can get the number of compressed values.

If you are compressing a foreign key, you can also extract the details of the foreign key.

Example: query the number of publications published by each publisher.

Through the study of the table structure, it is found that each published book is recorded in the book table, and each book will be a foreign key for a publisher id.

If we can compress the publishing house id, and then find out the corresponding quantity in the compressed publishing house id.

Tut-tut, isn't this coming out?

Code

From django.db.models import Count ret = models.Book.objects.values ("publish_id") .annotate (publish_count=Count ("publish_id")) print (ret)

Execution result

Native sql

SELECT `web_ book`.`publish _ id`, COUNT (`web_ book`.`publish _ id`) AS `publish_ count` FROM `web_ book`GROUP BY `web_ book`.`publish _ id`

ORM grouping and native SQL mapping

I remember that I was confused for a while, mainly because I didn't know how to correspond to the native SQL. According to the experience of many tests, the corresponding figure is as follows.

Group to get foreign key field information

The above functions can indeed be achieved through grouping.

But the above can only get the id of the publisher, not the name of the publishing house, but how to get the details of the compressed foreign key field?

Code

Ret = models.Book.objects.values ("publish_id") .annotate (publish_count=Count ("publish_id")) .values ("publish__title", "publish__phone", "publish_count") print (ret)

Execution result

Note: values followed by grouping (annotate).

It can only write the columns of the foreign key field and the columns in the annotate, but not anything else.

If the grouping is not a foreign key field, then you can no longer follow values!

Group re-screening

The essence of grouping re-screening is the group by of native sql. Having, to judge the condition of the compressed data.

But the compressed data can only be judged by having.

Example: query the data of more than 2 books published by the publishing house.

Code

Ret = models.Book.objects.values ("publish_id")\ .annotate (publish_count=Count ("publish_id"))\ .filter (publish_count__gt=2) print (ret)

Execution result

F query

Sometimes we may have the need to compare between two columns.

For example, the classic question, a commodity, finding a collection of goods that are larger than sales, and so on, two columns of demand for comparison.

Example: query the book table for data with fewer comments than favorites.

Code

From django.db.models import F book = models.Book.objects.filter (comment_num__lt=F ("collect_num")) print (book)

Actual result

Execution result

F object also supports comparison after addition, subtraction, multiplication and division.

Example: the number of comments is less than twice the number of collections.

Code

But *, it can also be -, +, /

From django.db.models import F book = models.Book.objects.filter (comment_num__lt=F ("collect_num") * 2) print (book)

Execution result

The F object is also suitable for updating

Code

Models.Book.objects.all () .update (price=F ("price") + 30)

Q query

Typically, the filter we use (condition 1, condition 2, etc.) executes and queries.

But there are usually times when we need to execute an or query.

For example, book table, query title= or title=.

At this point, if you use Django ORM, you can only use Q queries to build conditions.

Code

From django.db.models import Q books = models.Book.objects.filter (Q (title= "") | Q (title= "")) print (books)

Execution result

Note: | it means or, & it means and.

So, if you replace the above | with &, filter (condition 1, condition 2.) The same meaning, or and.

Q query ~

~ equivalent to not.

Example: query title = "" or title! = "".

Code

From django.db.models import Q books = models.Book.objects.filter (Q (title= "") | ~ Q (title= "")) print (books)

Execution result

Q query and and mixed query

Q query and and query appear at the same time, Q query must precede other queries.

Example: query title = "" or title! = "and publish_id=1.

Code

From django.db.models import Q books = models.Book.objects.filter (Q (title= "") | ~ Q (title= ""), publish_id=1) print (books)

Execution result

Dynamically construct Q query

Sometimes, we may not be sure what the conditions are.

It may be transmitted dynamically, and it will be spliced as much as it comes.

Q query, can do this, when doing dynamic Q query, dynamic Q not only supports or, but also supports and.

Example: query the books of publish_id=1 or title Fuzzy = Daming.

Code

Q = Q () # query method, or is still and q.connector = "or" # or,and # publish_id=1 q.children.append (("publish_id", "1")) # title__contains= "Daming" q.children.append (("title__contains", "Daming")) books = models.Book.objects.filter (Q) print (books)

Execution result

Increase

After all that has been said above, we are almost done. Let's take a brief look at how to add a piece of data.

Example: add a book

Code

Way one, through objects.create.

This method is used the most.

Models.Book.objects.create (title= "", price=66.66, PublishDate= "2020-01-02", comment_num=23, collect_num=12, # foreign key field django models corresponding to mysql is field _ id publish_id=1, # publish field needs to be a Publish object # publish=models.Publish.objects.filter (id=1))

Second, through the model object. Save ().

Book_obj = models.Book (title= "", price=66.66, PublishDate= "2020-01-02", the mysql corresponding to the foreign key field django models of comment_num=23, collect_num=12, # is field _ id publish_id=1,) book_obj.save ()

The third way is through the dictionary.

There may be times when we construct a dictionary of the passed parameters, which is great, so we don't have to take them one by one.

C_dict = {"title": "," price ": 88.1," PublishDate ":" 2020-01-03 "," comment_num ": 13," collect_num ": 78," publish_id ": 1,} models.Book.objects.create (* * c_dict)

Update

Note: update can only follow filter.

Example: change the data of title= "" to title= "".

Code

Models.Book.objects.filter (title= "") .update (title= "")

Filter may filter multiple values, so be careful.

Delete

Delete can only follow filter.

Example: delete data for title=.

Models.Book.objects.filter (title= "). Delete () Thank you for your reading, the above is the content of" how to understand Django ORM Operation ". After the study of this article, I believe you have a deeper understanding of how to understand Django ORM operation, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.