I'm trying to make line chart using data in sqlite database.

But I have no idea how to pass my data to views.py and html template file.

I want to make line chart ( x-axle : date , y-axle : count by date )

I tried to make it using raw queryset and other things...

requestCnt = ActivityLog.objects.raw("SELECT date(doDate), count(requestType) FROM mainApp_activitylog GROUP BY doDate")

OR

requestCnt = ActivityLog.objects.values('doDate').annotate(Count('requestType'))

AND ETC...

Models.py

class ActivityLog(models.Model):
    doDate = model.DateTimeField()
    userIP = models.CharField(max_length=200)
    userName =models.CharField(max_length=200)
    requestType = models.CharField(max_length=200)

Below is my raw sqlite query.

sqlite> SELECT date(doDate), count(requestType) FROM mainApp_activitylog GROUP BY doDate;

and the result is as below

2019-04-15|3
2019-04-16|16
2019-04-17|13
2019-04-18|10
2019-04-19|13
2019-04-22|24

How can I pass those results above to X-Axle and Y-axle ?

I want to make line chart (X-Axle:date , Y-axle: request count by date)

I have a little idea that I should use Json dumps or things like that.

I've already encountered error such as " cannot be serialized as Json file (?) "

I really hope someone can solve my problem.

Thx for your help in advance.

1 Answers

1
Aman Garg On

You can send the queryset to the template in the context and use the values there.

class YourTemplate(TemplateView):
    def get_context_data(self, **kwargs):
        requestCnt = ActivityLog.objects.annotate(request_count=Count('requestType')).values('doDate', 'request_count')
        context = super().get_context_data(**kwargs)
        context["data"] = requestCnt
        return context

Then in your template, you can use data variable to get the data for both the axis. The data will be of the format:

[{"doDate":"...", "request_count":"..."}, {"doDate":"...", "request_count":"..."}]