Getting result of query in django view in d3

Question

I've researched this topic for forever, but I can't seem to get there. I have a brand new Django site, with an app called dashboard. I've fed my sqlite db from Django with external data and I have a table called resource (matched to a Model Resource). I would like to be able to go to http://localhost:8000/resource/xxxxx, where xxxxx is a resource_id and see the result of a query on this particular resource_id presented on a web page (with d3.js graph built on the query result).

urls.py

from django.urls import path
from django.conf.urls import url

from . import views

urlpatterns = [
    path('resource/<resource_id>', views.resource_profile, name='resource_profile'),
    path(r'api_utilization_per_resource', views.api_utilization_per_resource, name='api_utilization_per_resource'),
]

I've borrowed the api trick from https://stackoverflow.com/a/26455798/1374353.

views.py

def resource_profile(request, resource_id):
    return render(request, 'dashboard/dashboard.html', { 'request': request, 'resource_id' : resource_id, })

def api_utilization_per_resource(request):

    resource_id = request.GET.get('resource_id')
    with connection.cursor() as cursor:
        #This is the current query that (obviously) does not include the resource_id, but the graph shows up.
        result = cursor.execute("SELECT tdate, period,  period_utilization, year_utilization FROM timedetails_utilization WHERE gpn = 'NL01002' GROUP BY period ORDER BY period")

        #This is the query (I think) I need.
        #result = cursor.execute("SELECT tdate, period,  period_utilization, year_utilization FROM timedetails_utilization WHERE gpn = %s GROUP BY period ORDER BY period", resource_id)
        rows = cursor.fetchall()

    rlist = []
    for r in rows:
        d, p, pe, ye = r
        t = dict(d=d, p=p,pe=pe,ye=ye)
        rlist.append(t)

    return JsonResponse(rlist, safe=False)

dashboard.html

{% extends "dashboard/base.html" %}

{% block title %}{{ resource_id }}{% endblock %}

{% block content %}

<script>
...

// Get the data
d3.json("{% url "api_utilization_per_resource"  %}", function(error, data) {
    console.log(data);
  if (error) throw error;

  // format the data
  data.forEach(function(d) {
      d.d = parseTime(d.d);
      d.p = +d.p;
      d.pe = +d.pe;
      d.ye = +d.ye;
  });
...

</script>
{% endblock %}

Still learning Django, but it seems the logic is that a request to /resource/xxxxx matches the first url, which calls the view resource_profile, which renders the template dashboard.html. In dashboard.html, the d3.json call requests the api_utilization_per_resource url, which is matched to the second url (in urls.py), which request the view api_utilization_per_resource, where the query is executed and returned as json.

This works. I can see in console the correct data (and this data is correctly proccessed further to produce a graph, not shown here). Also, the title of dashboard.html correctly displays as the resource_id from the entered url.

Question: where do I need to add/modify what so that I can get my query to 'see' the resource_id? Or is there another way which is far easier than this?

score 0 · Answer 1 · answered Feb 24 '18 at 20:51

Solved it! I can use resource_id in my query in the view that outputs json data that can be read directly in d3.

urls.py

from django.urls import path
from . import views

urlpatterns = [
    path('', views.index, name='index'),
    path('resource/<resource_id>', views.resource_profile, name='resource_profile'),
]

views.py

def resource_profile(request, resource_id):
    utilization = utilization_per_resource(resource_id)
    return render(request, 'dashboard/dashboard.html', { 'request': request, 'resource_id' : resource_id, 'utilization' : utilization })

def utilization_per_resource(resource_id):

    with connection.cursor() as cursor:

        result = cursor.execute("SELECT tdate, period,  period_utilization, year_utilization FROM timedetails_utilization WHERE gpn = %s GROUP BY period ORDER BY period", [resource_id])
        rows = cursor.fetchall()

    rlist = []
    for r in rows:
        d, p, pe, ye = r
        t = dict(d=datetime_handler(d), p=p,pe=pe,ye=ye)
        rlist.append(t)

    return json.dumps(rlist)

dashboard.html

{% extends "dashboard/base.html" %}

{% block title %}{{ resource_id }}{% endblock %}

{% block content %}

<script>
...
// Get the data

data = {{ utilization|safe }};

  // format the data
  data.forEach(function(d) {
      d.d = parseTime(d.d);
      d.p = +d.p;
      d.pe = +d.pe;
      d.ye = +d.ye;
  });
...

</script>
{% endblock %}

Getting result of query in django view in d3

1 Answers1