Flask tutorial: A student project recorder

This project is one of many that my students and I have worked on in class to improve their knowledge of Flask and web development in Python.

Prerequisites

1. Python
2. Pip
3. Basic Python knowledge

In this project, we are going to create a website that can save a student's name as well as their project details, and can display the list of already registered projects.

NB: If you already got the starter_files directory during class, don't bother downloading it from the repository. Just skip to the TODO section.

To begin, download the code from the repository. The starter files for this lesson are in the code/Lesson #1/starter_files directory, you can copy it to another location or modify it in place.
Here is the directory structure
.
└── Introduction to Flask and Django/
└── code/
├── Lesson #1 student-projects/
│ ├── starter_files/
│ ├── static/
│ ├── templates/
│ ├── app.py
│ ├── create_db.py
│ └── README.md
├── Flask notes
└── README.md

Now that we have our code in place, we need to install flask. Open a terminal and run the following
pip install flask

TODO

Here's what we are to do in our app

1. Create our sqlite database.
2. Complete the index() function such that it can save a new student and project to the database
3. Get the list of projects in our projects() function and return an HTML page with a table containing those projects

Ok let's start coding, open up the starter_files/ directory (or the copy you made) in a text editor.

1. Create our sqlite database.
   In the starter_files directory, run the create_db.py script to create the db.sqlite database and tables. Open a shell in this directory and type python create_db.py to run the script, after running you should see a db.sqlite file in your directory.

2. Complete the index() function such that it can save a new student and project to the database.
   Open the app.py file in the starter_files directory. Initially, it looks like this:
   

import sqlite3 # For sqlite database connection

from flask import Flask # used to create the app

# used to render HTML files to the browser
from flask import render_template

# used to access request information like form data, cookies, etc.
from flask import request

from create_db import get_path_to_sqlite_database

app = Flask(__name__) # create a Flask app

# gets the absolute path to this app's database, 
# the db.sqlite file created after running the create_db.py 
# script
path_to_db = get_path_to_sqlite_database(file_path=__file__)

@app.route('/', methods=["GET", "POST"])
def index():
    #TODO
    # add functionality to add a new project and student
    # to the database 
    return render_template("index.html")

@app.route('/projects')
def projects():
    #TODO
    # get list of projects from the database
    # return a dictionary of those projects to the browser
    # replace simple json output with an HTML page, containing 
    # a table of the projects
    # add styles and JavaScript to this page 
    return "Display list of projects to this page"

To run the app, open a terminal in the starter_files directory and type flask run --debug or python -m flask run --debug. Flask runs on port 5000, so to see the app in action, open your browser to (localhost:5000)[http://localhost:5000]. It should display a simple HTML form like below:

Now we want to only display this HTML page for GET requests, and for POST requests (i.e. when the user submits this form) we want to get the data from the form and create a new student and project.

To display the page only for GET requests, copy and paste this code to replace the index() function above:

@app.route('/', methods=["GET", "POST"])
def index():
    if request.method == "GET":
        return render_template("index.html")
    elif request.method == "POST":
        return "Underway..."

Now this HTML page is only displayed for GET requests, if we submit this form though, the browser will display Underway...

When we get a POST request we have to do the following

• Get the data from the form

if request.method == 'POST'
        student_name = request.form['name']
        project_name = request.form['project-name']
        project_description = request.form['project-description']

• Execute a query to insert the student into the database

        with sqlite3.connect(path_to_db) as connection:
            # save the student to the database
            cursor = connection.cursor()
            insert_into_student_table_query = "INSERT INTO Students (name) VALUES(?)"        
            cursor.execute(insert_into_student_table_query, (student_name, ))

• Get the id of the newly added student

            select_last_student_query = "SELECT id FROM Students ORDER BY id DESC LIMIT 1"
            result = cursor.execute(select_last_student_query).fetchone()

            latest_student_id = result[0]

• Execute a query to insert the project into the database

            inset_into_projects_table_query = "INSERT INTO Projects (student, name, description) VALUES (?,?,?)"

            cursor.execute(inset_into_projects_table_query, (latest_student_id, project_name, project_description))
            connection.commit()

• Return a success message (in class, we returned the form but it's still the same idea)

return "Successfully registered the project and student"

In the end, our index() function should look like this:

@app.route('/', methods=['GET', 'POST'])
def index():
    if request.method == 'GET':
        return render_template("index.html")
    elif request.method == 'POST':
        student_name = request.form['name']
        project_name = request.form['project-name']
        project_description = request.form['project-description']

        with sqlite3.connect(path_to_db) as connection:
            # save the student to the database
            cursor = connection.cursor()
            insert_into_student_table_query = "INSERT INTO Students (name) VALUES(?)"        
            cursor.execute(insert_into_student_table_query, (student_name, ))

            connection.commit()

            select_last_student_query = "SELECT id FROM Students ORDER BY id DESC LIMIT 1"
            result = cursor.execute(select_last_student_query).fetchone()

            latest_student_id = result[0]

            inset_into_projects_table_query = "INSERT INTO Projects (student, name, description) VALUES (?,?,?)"

            cursor.execute(inset_into_projects_table_query, (latest_student_id, project_name, project_description))
            connection.commit()

        return "<p>Project saved successfully. Go back to <a href="/">index page</a></p>"

Fill this form

1. Get the list of projects in our projects() function and return an HTML page with a table containing these projects.
2. Execute the query to select project details (student name, student id, project name, project description, project id) from the database.

def projects():
    with sqlite3.connect(path_to_db) as connection:
        cursor = connection.cursor()

        result = cursor.execute(
            """
                SELECT p.id, p.name, p.description, s.name as student, s.id as student_id 
                FROM Projects as p LEFT JOIN Students AS s ON p.student=s.id 
            """
        ).fetchall()

• Get the results from the query above and transform it into a dictionary

        result_dictionary = [
            { 
                "project": {
                    "name": f[1],
                    "id": f[0],
                    "description": f[2]
                },
                "student": {
                    "name": f[3],
                    "id": f[4]
                }
            } for f in result
        ]
        return result_dictionary

At this juncture, save the file and open your browser to (/projects)[http://localhost/projects] to see the list of projects you have created, returned as a JSON string.
Your app.py file should look like this:

import sqlite3 # For sqlite database connection

from flask import Flask # used to create the app

# used to render HTML files to the browser
from flask import render_template

# used to access request information like form data, cookies, etc.
from flask import request

from create_db import get_path_to_sqlite_database

app = Flask(__name__) # create a Flask app

# gets the absolute path to this app's database, 
# the db.sqlite file created after running the create_db.py 
# script
path_to_db = get_path_to_sqlite_database(file_path=__file__)

@app.route('/', methods=['GET', 'POST'])
def index():
    if request.method == 'GET':
        # TODO send the list of students to the template and display a select 
        # instead of the input text
        return render_template("index.html")
    elif request.method == 'POST':
        student_name = request.form['name']
        project_name = request.form['project-name']
        project_description = request.form['project-description']

        with sqlite3.connect(path_to_db) as connection:
            # save the student to the database
            cursor = connection.cursor()
            insert_into_student_table_query = "INSERT INTO Students (name) VALUES(?)"        
            cursor.execute(insert_into_student_table_query, (student_name, ))

            connection.commit()

            select_last_student_query = "SELECT id FROM Students ORDER BY id DESC LIMIT 1"
            result = cursor.execute(select_last_student_query).fetchone()

            latest_student_id = result[0]

            inset_into_projects_table_query = "INSERT INTO Projects (student, name, description) VALUES (?,?,?)"

            cursor.execute(inset_into_projects_table_query, (latest_student_id, project_name, project_description))
            connection.commit()

        return "<p>Project saved successfully. Go back to <a href='/'>index page</a></p>"

@app.route('/projects')
def projects():
    with sqlite3.connect(path_to_db) as connection:
        cursor = connection.cursor()

        result = cursor.execute(
            """
                SELECT p.id, p.name, p.description, s.name as student, s.id as student_id 
                FROM Projects as p LEFT JOIN Students AS s ON p.student=s.id 
            """
        ).fetchall()

        result_dictionary = [
            { 
                "project": {
                    "name": f[1],
                    "id": f[0],
                    "description": f[2]
                },
                "student": {
                    "name": f[3],
                    "id": f[4]
                }
            } for f in result
        ]
        return result_dictionary

• Render an HTML template with a table displaying these projects Now we want an HTML table instead of a JSON string, so we should replace the return result_dictionary statement with a return render_template('projects.html', projects=result_dictionary). This new statement renders our projects.html file in the templates directory and passes a variable called projects to it. We'll see how to access this variable in our template later. Now if you open the (/projects)[http://localhost/projects] link, it will display an HTML page with a paragraph Display projects here.
• Open the projects.html page in the templates/ directory in a text editor.
• In the head section of the page, we have to include some CSS and JS files, copy and paste the code below to include those files:

    <link
      rel="stylesheet"
      href="{{ url_for('static', filename='css/jquery.dataTables.min.css') }}"
    />
    <script src="{{ url_for('static', filename='js/bootstrap.min.js') }}"></script>
    <script src="{{ url_for('static', filename='js/jquery.min.js') }}"></script>
    <script src="{{ url_for('static', filename='js/jquery.dataTables.min.js') }}"></script>

This is an example of Python code execution in a template. The url_for function is a function in Flask used to get the url for specific resources e.g. web pages, images, etc. and in our case, we are using it to get the url for our static files (CSS & JavaScript).

• In the div in body of the page, copy and paste the following code

<table id="projects-table">
        <thead>
          <th>Student</th>
          <th>Project</th>
          <th>Project Description</th>
        </thead>
        {% for project in projects %}
        <tr>
          <td>{{ project.student.name }}</td>
          <td>{{ project.project.name }}</td>
          <td>{{ project.project.description }}</td>
        </tr>
        {% endfor %}
</table>

This code creates a table, loops through the list of projects passed to it (from the function in the app) and displays a row with the project's details for each project.
To access a variable in the template, use double enclosed curly braces e.g. {{ project.student.name }}

Going back to our (/projects)[http://localhost/projects] link, we should see a table displaying the list of projects that have been saved.
This table currently has no styling, to make it look better we will make use of the (DataTables for jQuery plugin)[https://datatables.net/] that will automatically style the table and add sorting and searching.
We have included the necessary files for the datatables, all that's left is to initialize the table. To do so copy this code and paste after the closing body tag (but before the closing HTML tag).

  <script>
    $("#projects-table").DataTable();
  </script>

Your projects.html file should look like this:

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>Projects</title>
    <link
      rel="stylesheet"
      href="{{ url_for('static', filename='css/bootstrap.min.css') }}"
    />
    <link
      rel="stylesheet"
      href="{{ url_for('static', filename='css/jquery.dataTables.min.css') }}"
    />
    <script src="{{ url_for('static', filename='js/bootstrap.min.js') }}"></script>
    <script src="{{ url_for('static', filename='js/jquery.min.js') }}"></script>
    <script src="{{ url_for('static', filename='js/jquery.dataTables.min.js') }}"></script>
  </head>
  <body>
    <div class="container mt-3">
      <table id="projects-table">
        <thead>
          <th>Student</th>
          <th>Project</th>
          <th>Project Description</th>
        </thead>
        {% for project in projects %}
        <tr>
          <td>{{ project.student.name }}</td>
          <td>{{ project.project.name }}</td>
          <td>{{ project.project.description }}</td>
        </tr>
        {% endfor %}
      </table>
    </div>
  </body>

  <script>
    $("#projects-table").DataTable();
  </script>
</html>

The (/projects)[http://localhost/projects] page now displays the list of students and projects in a more stylish table.

You can extend this project by:

• Adding a page solely for students, where you can create, update or delete a student.
• Adding update and delete functionality to our table in /projects
• Which ever other way you can think of

I hope this post was informative. Feel free to leave a comment or suggestion, I'll be more than happy to help.