//Django 2.2.2 //sqlite3
I am passing various statistical values to Django template with Django Aggregate.
Avg, Count are working well. But Variance is not working and gives me OperationError.
Traceback (most recent call last):
File "/Users/miguel/Outsourcings/myProject/myvenv/lib/python3.6/site-packages/django/core/handlers/exception.py", line 34, in inner
response = get_response(request)
File "/Users/miguel/Outsourcings/myProject/myvenv/lib/python3.6/site-packages/django/core/handlers/base.py", line 115, in _get_response
response = self.process_exception_by_middleware(e, request)
File "/Users/miguel/Outsourcings/myProject/myvenv/lib/python3.6/site-packages/django/core/handlers/base.py", line 113, in _get_response
response = wrapped_callback(request, *callback_args, **callback_kwargs)
File "/Users/miguel/Outsourcings/myProject/project_app/views.py", line 116, in project_report
print(subject)
File "/Users/miguel/Outsourcings/myProject/myvenv/lib/python3.6/site-packages/django/db/models/query.py", line 250, in __repr__
data = list(self[:REPR_OUTPUT_SIZE + 1])
File "/Users/miguel/Outsourcings/myProject/myvenv/lib/python3.6/site-packages/django/db/models/query.py", line 274, in __iter__
self._fetch_all()
File "/Users/miguel/Outsourcings/myProject/myvenv/lib/python3.6/site-packages/django/db/models/query.py", line 1242, in _fetch_all
self._result_cache = list(self._iterable_class(self))
File "/Users/miguel/Outsourcings/myProject/myvenv/lib/python3.6/site-packages/django/db/models/query.py", line 55, in __iter__
results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
File "/Users/miguel/Outsourcings/myProject/myvenv/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1100, in execute_sql
cursor.execute(sql, params)
File "/Users/miguel/Outsourcings/myProject/myvenv/lib/python3.6/site-packages/debug_toolbar/panels/sql/tracking.py", line 186, in execute
return self._record(self.cursor.execute, sql, params)
File "/Users/miguel/Outsourcings/myProject/myvenv/lib/python3.6/site-packages/debug_toolbar/panels/sql/tracking.py", line 124, in _record
return method(sql, params)
File "/Users/miguel/Outsourcings/myProject/myvenv/lib/python3.6/site-packages/django/db/backends/utils.py", line 99, in execute
return super().execute(sql, params)
File "/Users/miguel/Outsourcings/myProject/myvenv/lib/python3.6/site-packages/django/db/backends/utils.py", line 67, in execute
return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
File "/Users/miguel/Outsourcings/myProject/myvenv/lib/python3.6/site-packages/django/db/backends/utils.py", line 76, in _execute_with_wrappers
return executor(sql, params, many, context)
File "/Users/miguel/Outsourcings/myProject/myvenv/lib/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
File "/Users/miguel/Outsourcings/myProject/myvenv/lib/python3.6/site-packages/django/db/utils.py", line 89, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File "/Users/miguel/Outsourcings/myProject/myvenv/lib/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
File "/Users/miguel/Outsourcings/myProject/myvenv/lib/python3.6/site-packages/django/db/backends/sqlite3/base.py", line 383, in execute
return Database.Cursor.execute(self, query, params)
django.db.utils.OperationalError: user-defined aggregate's 'finalize' method raised error
[03/Jun/2019 20:41:24] "GET /project/report/7 HTTP/1.1" 500 224730
Variance is working when I wrote in models method. But in views.py, not working. I do not know what the difference between these two is..
views.py
def project_report(request, pk):
project = get_object_or_404(ProjectModel.objects, pk=pk)
subject = project.subject_for_project.filter(status='on', party_status='join').annotate(feel_avg=Avg('datamodel__feeling'),
pain_avg=Avg('datamodel__pain'),
side_count=Count(Case(
When(datamodel__flag='side', then=1),
output_field=IntegerField(),
)),
# this part is not working
pain_variance=Variance('datamodel__pain')
)
print(subject)
context = {
'project':project,
'subject':subject,
}
return render(request, 'project/project_report.html, context)
models.py
class ProjectModel(models.Model):
project_name = models.CharField(max_length=100)
created_at = models.DateTimeField(auto_now_add=True)
updated_at = models.DateTimeField(auto_now=True)
status = models.CharField(choices=STATUS, max_length=3, default='on')
class SubjectModel(models.Model):
random_num = models.CharField(max_length=100, blank=True, unique=True)
project = models.ForeignKey(ProjectModel, on_delete=models.CASCADE, related_name='subject_for_project')
created_by_admin_at = models.DateTimeField(auto_now_add=True)
updated_at = models.DateTimeField(auto_now=True)
party_status = models.CharField(choices=(('join', 'Join'), ('break', 'Break')), default='join', max_length=5)
status = models.CharField(choices=STATUS, max_length=3, default='on')
class DataModel(models.Model):
user_id = models.ForeignKey(SubjectModel, on_delete=models.CASCADE)
drug = models.CharField(choices=DRUG, max_length=3, blank=True, null=True)
side_effect = models.CharField(max_length=50, blank=True, null=True)
side_effect_more = models.CharField(max_length=255, blank=True, null=True)
feeling = models.PositiveSmallIntegerField(choices=FEELING, blank=True, null=True)
pain = models.PositiveSmallIntegerField(blank=True, null=True)
date = models.DateTimeField(auto_now_add=True)
updated_at = models.DateTimeField(auto_now=True)
flag = models.CharField(choices=FLAG, max_length=6)
According to the django 2.2 release notes, I read that the sqlite environment also supports variance, but if I cannot use it in the view, I want to find another way. Thank you.
