為django撰寫一個有效的FOR回圈查詢？

我需要從這些模型中獲取每個月購買的保單數量

class Insurance(models.Model):
  #other fields..
  date_purchased_on = models.DateField(auto_now_add=True)    
  customer_region = models.CharField(
    max_length=10, choices=RegionType.choices)

所以我寫了一個意見以獲得回應

@api_view(['GET'])
def chart_data(request):
    # structure of the op should be all the months from 1 to 12
    #  [{"month": 1,"count": 100,}, { "month:2", "count":20}]

    filtered_data = []
    for i in range(1, 13):
        query = Insurance.objects.filter(date_purchased_on__month=i).count()
        data = {
            "month": i,
            "count": query
        }
        filtered_data.append(data)
        
    return Response(filtered_data)

現在的問題是這個查詢每次訪問資料庫12 次。有沒有辦法處理這個問題以進入單個查詢或減少它

和我的記錄器結果

(0.000) SELECT COUNT(*) AS "__count" FROM "insurance_insurance" WHERE EXTRACT('month' FROM "insurance_insurance"."date_purchased_on") = 1; args=(1,); alias=default
(0.000) SELECT COUNT(*) AS "__count" FROM "insurance_insurance" WHERE EXTRACT('month' FROM "insurance_insurance"."date_purchased_on") = 2; args=(2,); alias=default
(0.000) SELECT COUNT(*) AS "__count" FROM "insurance_insurance" WHERE EXTRACT('month' FROM "insurance_insurance"."date_purchased_on") = 3; args=(3,); alias=default
(0.000) SELECT COUNT(*) AS "__count" FROM "insurance_insurance" WHERE EXTRACT('month' FROM "insurance_insurance"."date_purchased_on") = 4; args=(4,); alias=default
(0.000) SELECT COUNT(*) AS "__count" FROM "insurance_insurance" WHERE EXTRACT('month' FROM "insurance_insurance"."date_purchased_on") = 5; args=(5,); alias=default
(0.000) SELECT COUNT(*) AS "__count" FROM "insurance_insurance" WHERE EXTRACT('month' FROM "insurance_insurance"."date_purchased_on") = 6; args=(6,); alias=default
(0.000) SELECT COUNT(*) AS "__count" FROM "insurance_insurance" WHERE EXTRACT('month' FROM "insurance_insurance"."date_purchased_on") = 7; args=(7,); alias=default
(0.000) SELECT COUNT(*) AS "__count" FROM "insurance_insurance" WHERE EXTRACT('month' FROM "insurance_insurance"."date_purchased_on") = 8; args=(8,); alias=default
(0.000) SELECT COUNT(*) AS "__count" FROM "insurance_insurance" WHERE EXTRACT('month' FROM "insurance_insurance"."date_purchased_on") = 9; args=(9,); alias=default
(0.000) SELECT COUNT(*) AS "__count" FROM "insurance_insurance" WHERE EXTRACT('month' FROM "insurance_insurance"."date_purchased_on") = 10; args=(10,); alias=default
(0.000) SELECT COUNT(*) AS "__count" FROM "insurance_insurance" WHERE EXTRACT('month' FROM "insurance_insurance"."date_purchased_on") = 11; args=(11,); alias=default
(0.000) SELECT COUNT(*) AS "__count" FROM "insurance_insurance" WHERE EXTRACT('month' FROM "insurance_insurance"."date_purchased_on") = 12; args=(12,); alias=default

uj5u.com熱心網友回復：

您可以使用ExtractMonth運算式 [Django-doc]提取月份并按該月份排序：

from django.db.models import Count
from django.db.models.functions import ExtractMonth

Insurance.objects.values(month=ExtractMonth('date_purchased_on')).annotate(
    count=Count('pk')
).order_by('month')

這將產生一個QuerySet看起來像這樣的字典：

<QuerySet [
    {'month': 6, 'count': 100 },
    {'month': 7, 'count': 20 }
]>

沒有Insurance物件的月份將不包括在QuerySet. 因此，您可能必須對其進行后處理并包含'count': 0這些記錄。

標籤：Python 姜戈 数据库 PostgreSQL的

上一篇：DRL中的奇怪曲線

下一篇：反轉'entrypage'，沒有找到任何引數。嘗試了1個模式：['wiki/(?P<title>[^/] )$']