A document from MCS 275 Spring 2022, instructor David Dumas. You can also get the notebook file.

MCS 275 Spring 2022 Worksheet 14 Solutions

  • Course instructor: David Dumas
  • Solutions prepared by: David Dumas, Johnny Joyce

Topics

This worksheet focuses on Flask web applications that use databases.

Resources

These things might be helpful while working on the problems. Remember that for worksheets, we don't strictly limit what resources you can consult, so these are only suggestions.

1. SpeakerSlate web app has two unfinished functions

Download and extract this zip file which contains a partially-written Flask web application.

In short, the application provides:

  • All the HTML templates it requires
  • All the CSS it requires
  • Most of the Python code: A Flask application is provided that is just missing two functions.

Read below for an explanation of what the app is meant to do, and what part is missing. Then, add the missing code.

Application concept

SpeakerSlate is an application allowing speakers who have been invited to a conference to choose their speaking times on a first-come first-served basis. Initially, all of the conference's speaking times are open. As speakers visit the site and reserve their times, their choices are recorded in a database.

Specifically, a SQLite database stores rows representing the speaking slots at the conference, containing columns for the datetime (e.g. "Saturday 9am"), the speaker (NULL if this slot is available, or a name like "David Dumas" if it has been assigned), and an integer slotid that uniquely identifies the row.

In addition to providing a way for speakers to choose a speaking time, the application also lets anyone view a list of lectures that have been scheduled. There is also a feature meant for administrators that will delete all existing lecture time assignments.

Visual tour

  • The application's main page / is rendered from templates/front.html. It is generated by a function front() in speakerslate.py. It looks like this: front page
  • The link to choose a lecture time goes to /userinfo/ which results in the form shown below, rendered from templates/userinfo.html. It is generated by a function userinfo() in speakerslate.py. userinfo form
  • When the user information form is submitted, it posts its data to /choose/ which must be rendered from templates/choose.html after making a SQL query to determine what speaking slots are available. The HTML template is provided, but you must write the associated function showchoices() in speakerslate.py. speaking slot choices
  • The links in the speaking slot choice page go to URLs like /assign/6/to/David/ (meaning: Assign slotid 6 to speaker David). You must write the associated function assign_slot_to_person(slotid,username) in speakerslate.py. This function performs an action, but then redirects the browser to the schedule page (/schedule/). Thus, while you'll need to write some code, this route will not involve rendering any template.
  • The schedule page at /schedule/ is available in two ways: It is linked directly from the front page, and it is the destination the browser is redirected to after a speaker chooses their time. It is rendered from templates/schedule.html. It looks like this:

schedule

Hints

First, try running the application speakerslate.py in its current form. See what works and what doesn't work.

Then, take a look at the code in speakerslate.py. Look at the existing routes and try to understand how they work. The function for /schedule/ will probably be especially helpful, as it is quite similar to /choose/.

Check the database reset code for information about the table and column names.

Read template templates/choose.html, since this is going to be rendered by a function you write. Determine what variables it uses, and what values they are supposed to contain. Design database queries to retrieve those values.

In [ ]:
# MCS 275 Spring 2022 Worksheet 14
from flask import Flask, render_template, request, redirect
import os
import sqlite3

# Make sure the working directory is the directory that contains
# this script file.  (Fixes some errors caused by running from
# VS code, for example.)
os.chdir(os.path.dirname(os.path.realpath(__file__)))

# The database filename used by the application
DBFILE = "speakerslate.db"


def reset_db_to_initial_state():
    """
    Initialize the database file, erasing existing data if present.
    Used in two ways:
      * Runs at application startup if database cannot be found
      * Is called by the route for resetting the schedule
    """
    con = sqlite3.connect(DBFILE)
    con.execute("DROP TABLE IF EXISTS schedule;")
    con.execute(
        """
    CREATE TABLE schedule (
        slotid INTEGER PRIMARY KEY,
        datetime TEXT NOT NULL,
        speaker TEXT
    );"""
    )
    for s in [
        "Saturday 9am",
        "Saturday 10am",
        "Saturday 11am",
        "Saturday 2pm",
        "Sunday 11am",
        "Sunday 2pm",
    ]:
        con.execute(
            """
        INSERT INTO schedule (datetime) VALUES (?);
        """,
            [s],
        )
    con.commit()
    con.close()


app = Flask(__name__)

# -----------------------------------------------
#        START OF FLASK ROUTE DEFINITIONS
# -----------------------------------------------


@app.route("/")
def front():
    "Show the application front page"
    return render_template("front.html")


@app.route("/reset/")
def reset():
    "Erase all scheduled lectures"
    reset_db_to_initial_state()
    return render_template("reset_done.html")


@app.route("/userinfo/")
def userinfo():
    "Show user info form"
    return render_template("userinfo.html")


@app.route("/choose/", methods=["GET", "POST"])
def showchoices():
    """
    Route for submission of the user information form.
    With the username now known, presents choices of speaking
    slots that aren't already assigned.  Each choice links to
    a URL like
      /assign/<slotid>/to/<username>/
    so that clicking the link will assign the speaking slot to
    this user.
    """
    con = sqlite3.connect(DBFILE)
    res = con.execute("SELECT slotid,datetime FROM schedule WHERE speaker IS NULL;")
    available_slots = []
    for row in res:
        available_slots.append({"slotid": row[0], "datetime": row[1]})
    con.close()
    return render_template(
        "choose.html",
        username=request.values.get("username"),
        available_slots=available_slots,
    )


@app.route("/assign/<int:slotid>/to/<username>/")
def assign_slot_to_person(slotid, username):
    """
    Record that `username` is speaking at slot with id `slotid`,
    then redirect to the schedule page.
    """
    con = sqlite3.connect(DBFILE)
    con.execute("UPDATE schedule SET speaker=? WHERE slotid=?;", (username, slotid))
    con.commit()
    con.close()
    return redirect("/schedule/")


@app.route("/schedule/")
def show_schedule():
    "Display the schedule of all assigned speaking slots"
    con = sqlite3.connect(DBFILE)
    res = con.execute(
        "SELECT slotid,datetime,speaker FROM schedule WHERE speaker IS NOT NULL;"
    )
    # Now we have all the slots as an iterable of tuples.
    # The template expects a list of dictionaries, so we convert.
    scheduled_lectures = []
    for row in res:
        scheduled_lectures.append(
            {"slotid": row[0], "datetime": row[1], "speaker": row[2]}
        )
    con.close()
    # Pass the list of dicts to the template.
    # NOTE: This template doesn't require the username, since the schedule
    # can be viewed without entering any information.
    return render_template("schedule.html", scheduled_lectures=scheduled_lectures)


# -----------------------------------------------
#        END OF FLASK ROUTE DEFINITIONS
# -----------------------------------------------


# -----------------------------------------------
#                MAIN PROGRAM
# -----------------------------------------------

# Make sure the database exists and is ready
if not os.path.exists(DBFILE):
    print("The database file '{}' does not exist.  Initializing it.".format(DBFILE))
    reset_db_to_initial_state()

# Start the web server
app.run()

2. New speaking slot feature for SpeakerSlate

Add a new feature to SpeakerSlate, where additional speaking times can be created. This should consist of:

  • A new link on the front page that reads "Add a new speaking slot" and links to /newslot/
  • A form that is rendered as the response to /newslot/, asking for the datetime of the new speaking slot. This application treats datetimes as opaque strings; there's no special handling or parsing with Python's datetime module. When submitted, this form should make a POST request to /createslot/
  • A function handling POST requests on /createslot/ that actually INSERTs a new row into the database.

Solution

Updated version of the HTML template front.html (allowing for the link to "Add a new speaking slot"):

In [ ]:
<!doctype html>
<html>
  <head>
    <title>SpeakerSlate</title>
    <link rel="stylesheet" href="/static/speakerslate.css">
  </head>
  <body>
      <div class="logo-header">
      <h1>SpeakerSlate Pro XP+ 2000</h1>
      </div>

      <div class="content-main">
        <h2>Select an action:</h2>
        <ul>
            <li><a href="/userinfo/">Choose a lecture time</a></li>
            <li><a href="/schedule/">View scheduled lectures</a></li>
            <li><a href="/reset/">Clear the schedule</a></li>
            <li><a href="/newslot/">Add a new speaking slot</a></li>
        </ul>
      </div>
  </body>
</html>

We also want a new HTML template for adding a slot newslot.html. This will handle the form where the user can enter a new datetime (it's just a very slightly modified version of userinfo.html). Here it is:

In [ ]:
<!doctype html>
<html>
  <head>
    <title>Add a new time slot</title>
    <link rel="stylesheet" href="/static/speakerslate.css">
  </head>
  <body>
      <div class="logo-header">
      <h1>SpeakerSlate Pro XP+ 2000</h1>
      </div>

      <div class="content-main">
        <h2>Add a new time slot</h2>
        <form action="/createslot/" method="post">
            <label for="slot_time">
                Time:
            </label>
            <input type="text" size="30" name="slot_time" id="slot_time">
            <input type="submit" value="Enter time">
        </form>
      </div>
  </body>
</html>

One more HTML template called newslot_done.html to confirm successful addition of a time slot (it's a very slightly modified version of reset_done.html). This isn't strictly needed, but it's nice to show confirmation to the user. Also has room for one variable {{slot_time}} to show the user the value they entered:

In [ ]:
<!doctype html>
<html>
  <head>
    <title>Time slot added</title>
    <link rel="stylesheet" href="/static/speakerslate.css">
  </head>
  <body>
      <div class="logo-header">
      <h1>SpeakerSlate Pro XP+ 2000</h1>
      </div>

      <div class="content-main">
        <h2>Time slot added</h2>
        <p>The new time slot "{{slot_time}}" has successfully been created.</p>
        <p><a href="/">Return to the main page.</a></p>
      </div>
  </body>
</html>

Finally, here are the new methods added to speakerslate.py:

In [ ]:
@app.route("/newslot/")
def newslot():
    """Show datetime entry form"""
    return render_template("newslot.html")


@app.route("/createslot/", methods=["GET", "POST"])
def createslot():
    """After user has entered a datetime, we INSERT the value into the database"""
    con = sqlite3.connect(DBFILE)
    slot_time = request.values.get("slot_time") # In form `newslot.html`, the id of the user's entry is `slot_time`, so use that
    con.execute("INSERT INTO schedule (datetime) VALUES (?);", (slot_time,))
    con.commit()
    con.close()

    return render_template("newslot_done.html", slot_time = slot_time) # Show the confirmation page