CODEWITHSUNDEEP
pythonmysqldjango
LeoHurab
LeoHurab

Reputation: 37

MySQL query not visualizing on Django HTML page

I'm currently trying to take specific queries of databases and display them on an HTML page. I'm working on the Django framework with mySQL as the DB. I've run the queries successfully in mySQL and the server isn't giving me any errors so I'm lost as where to go next.

My views.py file where I have the queries

from django.shortcuts import render
from django.http import HttpResponse
from django.contrib.auth.decorators import login_required
from student.models import StudentDetails, CourseDetails
from django.db import connection
from django.core.paginator import Paginator

def dictfetchall(cursor):
    "Return all rows from a cursor as a dict"
    columns = [col[0] for col in cursor.description]
    return [
        dict(zip(columns, row))
        for row in cursor.fetchall()
    ]
# Create your views here.
@login_required
def home(request):

    cursor = connection.cursor()
    cursor.execute("SELECT COUNT(*) 'totalstudents' FROM student_studentdetails")
    cursor.execute("SELECT COUNT(*) 'totalseniors' FROM student_studentdetails WHERE year='Senior' ")
    cursor.execute("SELECT COUNT(*) 'totaljuniors' FROM student_studentdetails WHERE year='Junior' ")
    cursor.execute("SELECT COUNT(*) 'totalfreshman' FROM student_studentdetails WHERE year='Freshman' ")
    cursor.execute("SELECT COUNT(*) 'coursecount' FROM student_coursedetails")
    cursor.execute("SELECT AVG(GPA) 'averagegpa' FROM student_studentdetails")
    homedata =  dictfetchall(cursor)


    return render(request, 'student/home.html', {'data' : homedata})

My home.html where they should be loading. The aesthetics of how they are displayed is secondary right now, I'm still trying to figure out how to pull the data without having to use rows but that's for another day. 

{% extends 'student/base.html' %}

{% block content %}

<tbody>
  {% for row in data %}
    <tr>
        <td> {{ row.averagegpa }} </td>
        <td> {{ row.totalstudents}} </td>
        <td> {{ row.totalseniors}} </td>
        <td> {{ row.totaljuniors}} </td>
        <td> {{ row.totalfreshman}} </td>
        <td> {{ row.coursecount}} </td>
    </tr>
  {% endfor %}
</tbody>



{% endblock %}

and this is what I see when I run my server and go to the homepage

Only averaggpa is showing

Upvotes: 1

Views: 174

Answers (1)

GMB
GMB

Reputation: 222482

The problem is that you are executing queries one after the other, without actually recovering the results in between - excepted for last query, that gives you the average GPA for all students.

Even if you were, this still seems like a suboptimal approach. Apart from the course count, which comes from another table, you can get all the information in just one query, using conditional aggregation on table student_studentdetails:

select
    count(*) totalstudents,
    sum(year = 'Senior') totalseniors,
    sum(year = 'Junior') totaljuniors,
    sum(year = 'Freshman') totalfreshman,
    avg(gpa) average gpa
from student_studentdetails

That way, you just need two queries to get the data (you need fo fetch in between!).

You can even merge the other query as follows:

select
    count(*) totalstudents,
    sum(year = 'Senior') totalseniors,
    sum(year = 'Junior') totaljuniors,
    sum(year = 'Freshman') totalfreshman,
    avg(gpa) average gpa,
    (select count(*) from student_coursedetails) course_count
from student_studentdetails

Upvotes: 1

Related Questions