Django Rest Framework- how to put complex SQL query in views.py

Question

I have some problem with put sql query in Django Rest views.py.

views.py

from rest_framework import generics
from ..models import Stat
from .serializers import StatSerializer

class StatListView(generics.ListAPIView):
    queryset = Stat.objects.raw("SELECT parameter1, COUNT(*) FROM 
               statistic_stat GROUP BY parameter1 order by count(*) desc 
               LIMIT 10")
    serializer_class = StatSerializer

serializers.py

from rest_framework import serializers
from ..models import Stat

class StatSerializer(serializers.ModelSerializer):
    class Meta:
        model = Stat
        fields = ('parameter1', 'parameter2')

When i run this code i get an error like: "get() returned more than one Stat -- it returned 122!"

The same query works in another script (with the same database) and returns the result like this:

[('something1', 56), ('something2', 32), ('something3', 21), ('something4', 
19), ('something5', 10), ('something6', 8), ('something7', 4), ('something8', 
3), ('something9', 2), ('something10', 1)]

Traceback

Environment:


Request Method: GET
Request URL: http://127.0.0.1:8000/api/author/

Django Version: 1.11.3
Python Version: 3.6.1
Installed Applications:
['django.contrib.admin',
 'django.contrib.auth',
 'django.contrib.contenttypes',
 'django.contrib.sessions',
 'django.contrib.messages',
 'django.contrib.staticfiles',
 'statistic',
 'rest_framework']
Installed Middleware:
['django.middleware.security.SecurityMiddleware',
 'django.contrib.sessions.middleware.SessionMiddleware',
 'django.middleware.common.CommonMiddleware',
 'django.middleware.csrf.CsrfViewMiddleware',
 'django.contrib.auth.middleware.AuthenticationMiddleware',
 'django.contrib.messages.middleware.MessageMiddleware',
 'django.middleware.clickjacking.XFrameOptionsMiddleware']



Traceback:

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\django\core\handlers\exception.py" in inner
  41.             response = get_response(request)

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\django\core\handlers\base.py" in _get_response
  187.                 response = self.process_exception_by_middleware(e, request)

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\django\core\handlers\base.py" in _get_response
  185.                 response = wrapped_callback(request, *callback_args, **callback_kwargs)

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\django\views\decorators\csrf.py" in wrapped_view
  58.         return view_func(*args, **kwargs)

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\django\views\generic\base.py" in view
  68.             return self.dispatch(request, *args, **kwargs)

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\rest_framework\views.py" in dispatch
  489.             response = self.handle_exception(exc)

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\rest_framework\views.py" in handle_exception
  449.             self.raise_uncaught_exception(exc)

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\rest_framework\views.py" in dispatch
  486.             response = handler(request, *args, **kwargs)

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\rest_framework\generics.py" in get
  201.         return self.list(request, *args, **kwargs)

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\rest_framework\mixins.py" in list
  48.         return Response(serializer.data)

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\rest_framework\serializers.py" in data
  739.         ret = super(ListSerializer, self).data

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\rest_framework\serializers.py" in data
  263.                 self._data = self.to_representation(self.instance)

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\rest_framework\serializers.py" in to_representation
  657.             self.child.to_representation(item) for item in iterable

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\rest_framework\serializers.py" in <listcomp>
  657.             self.child.to_representation(item) for item in iterable

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\rest_framework\serializers.py" in to_representation
  488.                 attribute = field.get_attribute(instance)

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\rest_framework\fields.py" in get_attribute
  445.             return get_attribute(instance, self.source_attrs)

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\rest_framework\fields.py" in get_attribute
  104.                 instance = getattr(instance, attr)

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\django\db\models\query_utils.py" in __get__
  116.                 instance.refresh_from_db(fields=[self.field_name])

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\django\db\models\base.py" in refresh_from_db
  696.         db_instance = db_instance_qs.get()

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\django\db\models\query.py" in get
  384.             (self.model._meta.object_name, num)

Exception Type: MultipleObjectsReturned at /api/author/
Exception Value: get() returned more than one Stat -- it returned 877!

Any ideas or suggestions what is wrong ? Thank you in advance

Did you post all of your StatListView ? Looks like there is more code there — Jahongir Rahmonov
– Jahongir Rahmonov, Commented Sep 7, 2017 at 11:45
Yes, it's all. It works for simple query like "SELECT * FROM statistic_stat" — Neszwil
– Neszwil, Commented Sep 7, 2017 at 11:48
Without the traceback, it'll just be guesswork. Can you please provide it ? — Linovia
– Linovia, Commented Sep 7, 2017 at 13:36
Is /api/author/ really the endpoint that loads StatListView? — Vincent
– Vincent, Commented Sep 7, 2017 at 14:59

Vincent · Accepted Answer · 2017-09-18 11:12:20Z

2

Not sure if this is the proper answer since I can't reproduce the error you got, but this works for me;

from rest_framework.generics import ListAPIView
from rest_framework import serializers

# you're not serializing a model and since you want to display that count I guess, I'd suggest changing the serializer to this.
class StatSerializer(serializers.Serializer):
    parameter1 = serializers.CharField(max_length=128, required=True)
    count = serializers.integerField(required=True)

    class Meta:
        fields = ('parameter1', 'count')

class StatListView(ListAPIView):
    queryset = Stat.objects.raw("SELECT parameter1, COUNT(*) FROM 
               statistic_stat GROUP BY parameter1 order by count(*) desc 
               LIMIT 10")
    serializer_class = StatSerializer

    def list(self, request):
        queryset = self.get_queryset()
        # the serializer didn't take my RawQuerySet, so made it into a list
        serializer = StatSerializer(list(queryset), many=True)
        return Response(serializer.data)

edited Sep 18, 2017 at 11:12

answered Sep 7, 2017 at 15:26

Vincent

3241 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Neszwil Over a year ago

i tried to run this code in serializers.py file and got an error "name ListAPIView is not defined"

Neszwil Over a year ago

I try to change to generics.ListAPIView, but then i get an error: "Got AttributeError when attempting to get a value for field count on serializer StatSerializer. The serializer field might be named incorrectly and not match any attribute or key on the Stat instance. Original exception text was: 'Stat' object has no attribute 'count'."

Vincent Over a year ago

Added imports to fix the not defined. The "Got AttributeError" looks like you used the model serializer instead of the base serializer. Did you really use class StatSerializer(serializers.Serializer) or did you change it to class StatSerializer(serializers.ModelSerializer) ? You should use serializers.Serializer for your use case.

Collectives™ on Stack Overflow

Django Rest Framework- how to put complex SQL query in views.py

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related