Django Rest框架创建查询集分组依据

Question

您好，我将编写此行sql查询：

select rating, date, count(rating) as "Count" from analyse
where wkn='x' and date between 'x' and 'y' group by rating

我的示范班是这个

^{pr2}$

序列化程序和视图是这样的

class RatingSerializer(serializers.Serializer):
rating = serializers.ReadOnlyField()
total = serializers.ReadOnlyField()
date = serializers.DateField()#format='%Y'

class CompaniesChartsView(generics.ListAPIView):
http_method_names = ['get']
permission_classes = (AllowAny,)
serializer_class = RatingSerializer

def get(self, request, wkn, format=None):
    self.query = self.request.GET.get('chart')

    end = datetime.date.today()
    #start = datetime.date(datetime.date.today().year-1, 1, 1)
    start = datetime.date(datetime.date.today().year-1, 
    datetime.date.today().month, datetime.date.today().day)
    try:
        if self.query == "ratings":
            queryset = Analyse.objects.values('date', 'rating') 
                         .filter(
                         Q(wkn=wkn) &
                         Q(date__range=([start, end]))) 
                         .annotate(total=Count('rating'))

            serializer = RatingSerializer(queryset, many=True)

            print(serializer.data)
            print(connection.queries)
            return Response(serializer.data)
        else:
            return Response("Bad request", 
              status=status.HTTP_400_BAD_REQUEST)
    except:
        pass

Django生成但是这个查询。这不是我要的。在

'SELECT "statistic_app_analyse"."date", 
 "statistic_app_analyse"."rating", 
 COUNT("statistic_app_analyse"."rating") AS "total" FROM 
 "statistic_app_analyse" WHERE ("statistic_app_analyse"."wkn" = 
  \'840400\' AND "statistic_app_analyse"."date" BETWEEN \'2017-12-20\' 
  AND \'2018-12-20\') GROUP BY "statistic_app_analyse"."date", 
  "statistic_app_analyse"."rating"'

Django将日期字段添加到groupby。在

当我将查询更改为：

                      queryset = Analyse.objects.values('rating') 
                         .filter(
                         Q(wkn=wkn) &
                         Q(date__range=([start, end]))) 
                         .annotate(total=Count('rating'))

然后就可以了，但是日期不见了。在

当我将Django查询更改为行sql查询时

          queryset = Analyse.objects.raw("SELECT db.wkn, 
          db.rating, db.date, COUNT (*) as 'Count' FROM 
          analyse as db WHERE db.wkn='578580' and db.date like 
          '2018%'")

          queryset = list(queryset)

         serializer = RatingSerializer(queryset, many=True)
         return Response(serializer.data)

然后我得到一个错误

Internal Server Error: /api/companies/840400/charts/
Traceback (most recent call last):
File "/home/hendrik/PycharmProjects/automatic-information-extraction- 
from-stock-analysis/venv/lib/python3.5/site- 
packages/django/core/handlers/exception.py", line 34, in inner
response = get_response(request)
File "/home/hendrik/PycharmProjects/automatic-information-extraction- 
from-stock-analysis/venv/lib/python3.5/site- 
packages/django/core/handlers/base.py", line 126, in _get_response
response = self.process_exception_by_middleware(e, request)
File "/home/hendrik/PycharmProjects/automatic-information-extraction- 
from-stock-analysis/venv/lib/python3.5/site- 
packages/django/core/handlers/base.py", line 124, in _get_response
response = wrapped_callback(request, *callback_args, 
**callback_kwargs)
File "/home/hendrik/PycharmProjects/automatic-information-extraction- 
from-stock-analysis/venv/lib/python3.5/site- 
packages/django/views/decorators/csrf.py", line 54, in wrapped_view
return view_func(*args, **kwargs)
File "/home/hendrik/PycharmProjects/automatic-information-extraction- 
from-stock-analysis/venv/lib/python3.5/site- 
packages/django/views/generic/base.py", line 68, in view
return self.dispatch(request, *args, **kwargs)
File "/home/hendrik/PycharmProjects/automatic-information-extraction- 
from-stock-analysis/venv/lib/python3.5/site- 
packages/rest_framework/views.py", line 497, in dispatch
self.response = self.finalize_response(request, response, *args, 
**kwargs)
File "/home/hendrik/PycharmProjects/automatic-information-extraction- 
from-stock-analysis/venv/lib/python3.5/site- 
packages/rest_framework/views.py", line 412, in finalize_response
 % type(response)
 AssertionError: Expected a `Response`, `HttpResponse` or 
 HttpStreamingResponse` to be returned from the view, but received a 
 <class 'NoneType'>`
 [20/Dec/2018 12:05:18] "GET /api/companies/840400/charts/? 
 chart=ratings 
 HTTP/1.1" 500 85144

谢谢你的帮助

Answer 1

直接的问题是视图从get()返回{}。这可以通过修改try/except来解决。在

def get(self, request, wkn, format=None):
    self.query = self.request.GET.get('chart')

    end = datetime.date.today()
    #start = datetime.date(datetime.date.today().year-1, 1, 1)
    start = datetime.date(datetime.date.today().year-1, 
    datetime.date.today().month, datetime.date.today().day)
    try:
        if self.query == "ratings":
            queryset = Analyse.objects.values('date', 'rating') 
                         .filter(
                         Q(wkn=wkn) &
                         Q(date__range=([start, end]))) 
                         .annotate(total=Count('rating'))

            serializer = RatingSerializer(queryset, many=True)

            print(serializer.data)
            print(connection.queries)
            return Response(serializer.data)
    except:
        pass
    return Response("Bad request", 
        status=status.HTTP_400_BAD_REQUEST)

然而，捕捉空白异常并将其传递是一种糟糕的做法。如果您要传递一个异常，您应该知道将引发哪些可能的异常，此时您应该捕获这些特定的异常，而不是简单地Exception。另一方面，它隐藏了代码中的错误。所以我建议您：

^{pr2}$

为了使日期从GROUP BY子句中删除，请尝试将其从参数列表中删除到.values()：

queryset = Analyse.objects.values('rating') 
    .filter(
        Q(wkn=wkn) &
        Q(date__range=([start, end]))) 
    .annotate(total=Count('rating'))