Showing my students their grades with AWS and Python

If you have been following my blog, and I hope you have, you will know that I like to automate things. Recently, I found myself needing to show my students their grades during a time when the official server of the university did not have the grades entry system open.

There are many ways to do this, including sending an excel file or PDF with IDs only to my students in WhatsApp groups. But I am a computer geek, if something can be programmed, I will go that route. I have previously shared the automation of my credit card bill analysis and step count, among other things.

So, lets get down to it. How did I plan to accomplish my goal of getting the grades to my students without violating their privacy? I decided that I would create a simple web page that asked them for their ID and then read their grades from an excel file and displayed it. The next section discusses the high-level decisions I made while setting up the environment for this task.

What do I need for this?

Here are the things I needed for this

• a server to host my application — preferably free, but paid if I had no other choice
• an http server program running on the physical server
• a method to run server side code to get the student grades
  

Choosing the appropriate tools for each of the above is an engineering decision — and as I like to tell my students all the time, there is no Goldilocks solution. No solution is “just right” — every engineering solution is a trade off. So here are the decisions that I made, hopefully you will understand why I made them. You don’t necessarily have to make the same trade offs. There are alternate routes at each step, I’ll try to list them in case you would like to consider them if you want to do something similar.

• I decided to use Apache2 for my http server. There are other options, but I have worked with a LAMP stack before, and this was just easier for me to deal with. (Alternatives: tomcat, NGINX, LiteSpeed, Lighttpd, etc)
  
• I decided to use Amazon Web Services (aws) to host my server. Why? Because there is a free tier that offers me 750 hours of compute resources per month for one year. I could run my server for one year and pay nothing — I chose to create an EC2 instance. I chose the t2.micro service running Ubuntu, as it is eligible for the free tier and I associated an Elastic IP to my instance to be able to access it remotely using a consistent IP. I felt it would be overkill to buy a domain name for this, a static IP is enough Since I first wrote this, I bought the domain sheriffadelfahmy.work to host this solution. (Alternatives: Google Cloud Services, Azure Cloud Services, or a VPS at any of a number of different hosting services)
  
• I decided to use cgi-bin to run my server side code. I know, I know. You are going to shout “who uses cgi-bin nowadays?” I know there are more modern server-side frameworks, but they are just overkill for the simple application I wanted to develop. (Alternatives: Node.js, Ruby on Rails, Django, etc)
  
• Finally I needed to choose a language to write my server side script. It needed to be a language that offered high level libraries — I was particularly interested in an easy way to handle excel files — and one that I was familiar with to speed up development. I chose python. I am very familiar with the language as I use it in most of my research nowadays, and it has a very rich set of libraries, including libraries for handling excel data. (Alternatives: C, C#, PHP, etc).
  

I will not go into the details of installing LAMP or setting up the AWS instance. If you are interested in this, please let me know in the comments and I will write another post about it. What I will do is discuss how I configured apache2 for cgi-bin execution and the code I wrote to get the task done. Let us begin with setting up apache2 for cgi-bin execution.

How to configure apache2 for cgi-bin

The first thing I did was to enable the cgi module on apache, here is the shell command to do this

sudo a2enmod cgi

Once the module was enabled, I needed to configure the virtual host for my website to execute cgi scripts. To do that, I opened the default configuration file — I have only one site on this AWS instance, so its under /var/www/html — using the following command

sudo nano /etc/apache2/sites-available/000-default.conf

I then added the following lines to enable cgi scripts for my site

ScriptAlias /cgi-bin/ /var/www/html/cgi-bin/
<Directory "/var/www/html/cgi-bin">
    Options +ExecCGI
    SetHandler cgi-script
</Directory>

I added this just before the closing </VirtualHost> tag.

I then created the cgi-bin folder that will house my server side code.

sudo mkdir /var/www/html/cgi-bin

And moved my python script into it, I will discuss the python script in the next section.

sudo mv retrieve_grades.py /var/www/html/cgi-bin/

I then used chmod to make the folder and the files executable.

sudo chmod +x /var/www/html/cgi-bin/retrieve_grades.py
sudo chmod +x /var/www/html/cgi-bin

Finally, I restarted apache2 to make sure that the changes were applied.

sudo service apache2 restart

I placed the excel file in the folder /var/data, which I created using the following command.

sudo mkdir /var/data

That was it! I had configured my environment and was ready to code. More about that in the next section.

The code that does the actual work

So, first I needed to write the html code that displayed the form requesting the students enter their ID. Here is the simple html code for this.

<!DOCTYPE html>

<html>

<head>
  <title>Student Grades</title>
</head>

<body>
  <h1>Student Grades</h1>
  <form method="POST" action="retrieve_grades.py">
    <label for="studentId">Student ID:</label>
    <input type="text" id="studentId" name="studentId" required>
    <button type="submit">Retrieve Grades</button>
  </form>
</body>

</html>

As you can see, I associated the python script with the POST method. The form would call the script when the submit button is pressed. The script would then do it’s thing and return the grades.

Here is the python code that does the actual processing.

#!/usr/bin/env python3

import cgi
import openpyxl

# Assuming the Excel file is named "grades.xlsx"
excel_file = '/var/data/grades.xlsx'

# Read the student ID from the form data
form = cgi.FieldStorage()
student_id = form.getvalue('studentId')
student_id=int(student_id)

# Load the Excel file and retrieve the grades for the student ID
workbook = openpyxl.load_workbook(excel_file)
worksheet = workbook.active
grades_row = None
for row in worksheet.iter_rows(values_only=True):
    if row[0] == student_id:
        grades_row = row
        break

# Generate the HTML response
print("Content-type: text/html")
print()
print("<html>")
print("<head>")
print("  <title>Student Grades</title>")
print("</head>")
print("<body>")
print("<h1>Student Grades</h1>")
if grades_row is None:
    print("<p>No grades found for the provided student ID.</p>")
else:
    print("<p>Grades for student ID:", student_id, "</p>")
    print("<ul>")
    for grade in grades_row\[1:]:
        print("<li>", grade, "</li>")
    print("</ul>")
print("</body>")
print("</html>")

I hope the code is self-explanatory, but here is a quick run down. First I include the cgi library to enable this functionality in the code and then the openpyxl library to be able to access the excel file. After assigning the excel file path to a variable, I read the value of the student ID from the submitted form.

I then open the excel file and set the current excel sheet to the active sheet. This is followed by a for loop that iterates over all the rows in the excel sheet until it finds a matching ID.

The last section of the code displays the result by printing the required html to the browser.

And there you have it, ladies and gents. In under 40 mins I was able to setup a system that would allow my students to see their grades until the university grades entry system opened up again.