1. Set addition
a = {1,2,3}
b = {3,4,5}
print(type(a))
print(a|b)
2. queryset eligible sieve sequence
projects = Project_models.(user=).order_by('id')
projects = (bad_numbers__gt=0).order_by('bad_numbers')
Additional knowledge:The use of aggregationaggregate and annotate GROUP BY in django
Contact django has been a long time, but the use of QuerySet query set has been relatively low-end, will only use filter/Q function/exclude and other ways to query, the amount of data is relatively small when it can be, but if the amount of data is very large, and the query is more complex, then if you are still using more than one filter for querying efficiency will be very low. Just take advantage of the time of the Qingming vacation, run to the company to do some private work. Output into this document, one is to deepen the impression, improve proficiency; two is to share it, to benefit everyone~
There are two options for improving the efficiency of querying the database:
The first, is to use the native SQL statement to query, which has the advantage of being able to execute exactly as the developer intends, and will be very efficient, but the disadvantage is also obvious:
1. Developers need to be very familiar with SQL statements, increasing the workload of developers, at the same time, mixed with the SQL project is not conducive to the maintenance of the program in the future, increasing the coupling of the program.
2. If the query condition is dynamically changing, it will make the development more difficult.
django in order to solve this dilemma, provides aggregate (aggregation function) and annotate (GROUP BY operation on the basis of aggregate).
Below, the second method is described.
I. The use of aggregate
Today, under the guidance of a colleague, a closer look at the use of annotate in django, will be based on the query conditions to dynamically generate SQL statements to improve the efficiency of the combination of queries.
The key to understanding aggregate is to understand the aggregate function in SQL: the following is taken from Baidu Encyclopedia: SQL basic function, aggregate function performs calculations on a set of values and returns a single value. Except for COUNT, the aggregate function ignores null values. Common aggregation functions are AVG / COUNT / MAX / MIN /SUM and so on.
aggregate is in django in the implementation of the aggregation function . First look at the use of aggregate scenario: in the project sometimes you want to take out an aggregated collection from the database. We use django official example:
from import models class Author(): name = (max_length=100) age = () class Publisher(): name = (max_length=300) num_awards = () class Book(): name = (max_length=300) pages = () price = (max_digits=10, decimal_places=2) rating = () authors = (Author) publisher = (Publisher) pubdate = () class Store(): name = (max_length=300) books = (Book) registered_users = ()
If we use aggregate for counting:
>>> from import Count
>>> pubs = (num_books=Count('book'))
>>> pubs
{'num_books': 27}
And aggregate can not only be summed, but also the average Avg, maximum and minimum, etc..
>>> from import Avg
>>> ().aggregate(Avg('price'))
{'price__avg': 34.35}
# Cost per page The name of the output can also be specified, e.g. price_per_page
>>> from import F, FloatField, Sum
>>> ().aggregate(
... price_per_page=Sum(F('price')/F('pages'), output_field=FloatField()))
{'price_per_page': 0.4470664529184653}
Through the above introduction, we can know that the logic of aggregate is relatively simple, the application scenario is relatively narrow, if you want to group the data (GROUP BY) and then aggregated operations, you need to use annotate to achieve.
II. Usage of annotate
First, let's assume that there are mods like this.
# python:2.7.9
# django:1.7.8
class MessageTab():
msg_sn = (max_lenth=20, verbose_name=u'Number')
msg_name = (max_length=50, verbose_name=u'Message name')
message_time = (verbose_name=u'When the message appeared')
msg_status = (max_length=50, default='Not processed', verbose_name=u'Message Status')
class Meta:
db_table = 'message_tab'
If during development, there is such a requirement: query the number of individual message states. Then we often use filter(...).count(...) to do the query. Now we can use:
msgS = .values_list('msg_status').annotate(Count('id'))
Where id is a self-incrementing field automatically generated by the database. values_list usage Google it yourself, or print it out and take a look.
At this point, the database actually executes the code that can be passed:
Print it out. Visible:
SELECT `message_tab`.`msg_status`, COUNT(`message_tab`.`id`) AS `id__count` FROM `message_tab` GROUP BY `message_tab`.`msg_status` ORDER BY NULL
It's very intuitive. Group by is done via msg_status, if you want to customize id__count, e.g. specify msg_num, you can use: annotate(msg_num=Count('id'))
The Q function can be used when there are multiple query conditions, such as querying for messages within the last 7 days where message_name belongs to a certain group:
date_end = now().date() + timedelta(days=1)
date_start = date_end - timedelta(days=7)
messageTimeRange = (date_start, date_end)
GroupList = getGroupIdLis() # Returns a list of groups that the current user can query. For reference only.
qQueryList = [Q(message_time__range=messageTimeRange), Q(message_name__in=GroupList)] # There can be more than one Q function query condition
msgS = (reduce(operator.and_, qQueryList)).values_list('msg_status').annotate(msg_num=Count('id'))
Call print again to see the SQL statement:
SELECT `message_tab`.`msg_status`, COUNT(`message_tab`.`id`) AS `msg_num` FROM `message_tab` WHERE (`message_tab`.`message_time` BETWEEN 2017-03-27 00:00:00 AND 2017-04-03 00:00:00 AND `message_tab`.`message_name` IN (1785785, 78757, 285889, 2727333, 7272957, 786767)) GROUP BY `message_tab`.`msg_status` ORDER BY NULL
Isn't it perfect!
This django queryset summing and filtering tutorial above is all I have to share with you, I hope it can give you a reference, and I hope you will support me more.