Skip to content

GP4: Views and Charts

Due: Monday, Nov 11th

Professionals analyzing charts on computer monitor
Image source: freepik.com

Overview

This assignment has two main goals:

  • Create a route for each of your views.
  • Add indexes to improve performance.

Example

A model solution for this assignment is available in the webapp folder of the profs repository.

Please notice, in particular:

  • queries/views.py has been added to select data from each view.
  • routes/views.py has been added to define a route to each view.
  • The templates folder has been organized into two subfolders:
    • templates/tables has templates for querying/editing tables (HW5)
    • templates/views has templates for querying views (this assignment)
  • A static folder has been added with supporting files

Instructions

Part 1: Stub out files

  1. Create a queries/views.py file that defines a Python function for each of your SQL views. For now, the function can simply SELECT * from the view:

    def event_schedule():
    # TODO implement function stub
    with db_connect() as con:
        with con.cursor() as cur:
            cur.execute("SELECT * FROM event_schedule")
            return cur.fetchall()

  2. Create a routes/views.py file that defines a route for each view. For now, you can simply call the database function and render a template:

    @app.route("/event_schedule")
def event_schedule():
    # TODO implement function stub
    data = db.event_schedule()
    return render_template("views/event_schedule.jinja", data=data)

  3. Add an import statement to routes/__init__.py so that the functions in routes/views.py are imported by app.py.

  4. Create a templates/views folder and create a .jinja file for each view. For example, the file event_schedule.jinja might contain the following code:

    <!DOCTYPE html>
<html>
<head>
    <title>event_schedule</title>
</head>
<body>
    <h1>Detailed schedule of workshops</h1>
</body>
</html>

  5. Add links at the end of templates/index.jinja so you can easily test each of your views in the web app. For example:

    <li><a href="event_schedule">event_schedule</a></li>

Tip

At this point, most files should be in place for the team to work on. Try running your web app to make sure each route runs without errors. Commit and push your changes so you can divide and conquer as a team.

Part 2: Implementation

The following instructions assume that each team member will be responsible for one of the views. See workshop_department in the profs repository for an example.

  1. In queries/views.py, modify your database function to have at least one parameter. The parameter(s) correspond to user input and must be used in the WHERE clause of the query that selects from the view.

  2. In routes/views.py, modify your route function to get user input from request.args. Pass the user input to the template, along with the query results.

  3. Add code to your .jinja template:

    • Create a <form> for user input. Feel free to use text boxes, dropdown lists, etc.
    • Display the query results in at least two ways. For example, you can show a table chart followed by some other chart (see Chart Gallery). Or you can show the results in some other visual way (Ex: bulleted list).

Optional

If you would like favicon for your application, refer to the example route in app.py in the profs repository.

Part 3: Create indexes

  1. Copy the query you wrote in queries/views.py and paste the code into WorkBench or pgAdmin. Run the query with EXPLAIN, and determine what index(es) might support the query.

  2. In the same folder as build.sh, create a new file named index.sql. Write a CREATE INDEX statement for each index you determined in the previous step.

  3. Add the following lines to the end of build.sh: 

    echo Creating indexes...
$CMD < index.sql

  4. Either create the indexes manually (by running the code in index.sql), or if desired, rerun the entire build.sh script.

Submission

Your project will be reviewed by the instructor and another team during class. In the end, you should be able to show each of your views in a web browser. And the views should run efficiently because of the indexes that you created.