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

MCS 275 Spring 2022 Worksheet 14

  • Course instructor: David Dumas


This worksheet focuses on Flask web applications that use databases.


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:



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.

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.