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

Worksheet 13 Solutions

MCS 275 Spring 2021 - Instructor Emily Dumas

Solutions by Jennifer Vaccaro

Topics

This worksheet focuses on HTML, CSS, and the basics of the Python web framework Flask. (We'll continue working on Flask in the upcoming week, with more Flask-related exercises coming in Worksheet 14.)

The main references for these topics are:

Instructions

  • Problem 1 is handled differently than the others:
    • Tuesday discussion students: Problem 1 will be presented as an example at the start of discussion
    • Thursday discussion students: Please complete problem 1 before discussion and bring your solution to discussion
  • For the other problems:
    • Work on these problems in discussion.

Stars

As with the previous worksheet, you'll need to download the HYG star database in SQLite format to complete these problems.

And for reminders about how to work with that database, you might refer back to the

I promise this is the last worksheet that will be based on the star database!

1. Star stats

Write a Python program that connects to the star database and reports some statistics about it in HTML format. It should write to an output file called starstats.html, which when opened in a browser should look similar to the following:

Screenshot of star stats in browser

To be clear, the program should compute the numbers and insert them into the HTML document dynamically, based on database queries. That way, the same program will generate accurate statistics each time it is run even if the database is modified.

In [3]:
# MCS 275 Week 13 Problem 1
# J Vaccaro
# This work was written in collaboration with the Tuesday discussion section.
"""Creates an html file for displaying some basic star statisttics"""

import sqlite3

# Connect to the stars sqlite3 database
con = sqlite3.connect("hyg_data.sqlite")

# Using COUNT(*) complete three queries from the stars table
c = con.execute("SELECT COUNT(*) FROM stars;")
total_stars = c.fetchone()[0] # Unpacks the int variable from the tuple
c = con.execute("SELECT COUNT(*) FROM stars WHERE proper IS NOT NULL;")
named_stars = c.fetchone()[0]
c = con.execute("SELECT COUNT(*) FROM stars WHERE mag<3;")
visible_stars = c.fetchone()[0]

con.close() # Close the connection once the data has been read

HEAD = """<!doctype html>
<html>
<head>
<title>Star stats</title>
</head>
<body>
<h1>Star stats</h1>
<p>The database contains:</p>
<ul>\n"""

TAIL = """</ul>
</body>
</html>"""

# Now, write the html file.
fname = "starstats.html"
with open(fname, "wt") as out:
    out.write(HEAD)
    # Use format to put the variables into html bullet items
    out.write("<li>{} total stars</li>\n".format(total_stars))
    out.write("<li>{} named stars</li>\n".format(named_stars))
    out.write("<li>{} stars visible to the human eye (mag<3) </li>\n".format(visible_stars))
    out.write(TAIL)
In [4]:
# Code to display starstats.html

with open("starstats.html","rt") as f:
    for line in f:
        print(line,end="")
<!doctype html>
<html>
<head>
<title>Star stats</title>
</head>
<body>
<h1>Star stats</h1>
<p>The database contains:</p>
<ul>
<li>119614 total stars</li>
<li>146 named stars</li>
<li>175 stars visible to the human eye (mag<3) </li>
</ul>
</body>
</html>

2. Star superlatives

Here are three queries you could run against the SQLite database of stars we used for worksheet 12:

SELECT ra,dec,proper,mag FROM stars ORDER BY mag LIMIT 10;
SELECT ra,dec,proper,mag FROM stars ORDER BY mag LIMIT 10;
SELECT ra,dec,proper,ci FROM stars WHERE ci!='' AND mag<3 ORDER BY ci DESC LIMIT 10; -- see note below

Write a program that makes these queries and then writes the results in the form of an HTML document called superlatives.html formatted and structured as in the image below. The link near the top of the file should go to https://github.com/astronexus/HYG-Database.

Screenshot of rendered HTML report

The point of this exercise is to get some practice generating HTML from Python by joining prepared sections (header, footer, h tags) with tags generated by loops that handle the results of the queries.

Note: The ci!='' is only needed in the last query due to a bug in my conversion of the HYG database to SQLite format: Some stars whose color indices are not present in the database have a text value of "" (empty string) in the database. The right way to handle this would be to have a null value for ci in those rows, in which case you'd be able to instead include ci NOT NULL in the query.

In [7]:
# MCS 275 Week 13 Problem 2
# J Vaccaro
# I completed this work myself, in accordance with the syllabus.

import sqlite3
con = sqlite3.connect("hyg_data.sqlite")

def name_filter(proper,ra,dec):
    """When proper==None, returns a descriptive string. Otherwise returns proper."""
    if proper==None:
        return "An unnamed star at right ascension {} and declination {}".format(ra,dec)
    return proper

with open("superlatives.html", "wt") as out:
    HEAD = """
    <!doctype html>
    <html>
    <head>
    <title>Star superlatives</title>
    </head>
    <body>
    <h1>Star superlatives</h1>
    <p>Based on data from the <a href="https://github.com/astronexus/HYG-Database">HYG database</a>.
    """
    out.write(HEAD)

    LIST1 = """<h2>Brightest as seen from earth</h2>
    <p>Top ten in terms of magnitude:</p>
    <ul>
    """
    c1 = con.execute("SELECT ra,dec,proper,mag FROM stars ORDER BY mag LIMIT 10;")
    for ra,dec,proper,mag in c1:
        proper = name_filter(proper,ra,dec)
        LIST1 += "<li> {}, with magnitude {}</li>\n".format(proper,mag)
    LIST1 += "</ul>\n"
    out.write(LIST1)

    LIST2 = """<h2>Most light output</h2>
    <p>Top ten in terms of absolute magnitude:</p>
    <ul>
    """
    c1 = con.execute("SELECT ra,dec,proper,absmag FROM stars ORDER BY absmag LIMIT 10;")
    for ra,dec,proper,absmag in c1:
        proper = name_filter(proper,ra,dec)
        LIST2 += "<li> {}, with absolute magnitude {}</li>\n".format(proper,absmag)
    LIST2 += "</ul>\n"
    out.write(LIST2)

    LIST3 = """<h2>Most blue stars visible with the naked eye</h2>
    <p>Top ten indices among stars with magnitude less than 3:</p>
    <ul>
    """
    c1 = con.execute("SELECT ra,dec,proper,ci FROM stars WHERE ci!='' AND mag<3 ORDER BY ci DESC LIMIT 10;")
    for ra,dec,proper,ci in c1:
        proper = name_filter(proper,ra,dec)
        LIST3 += "<li> {}, with color index {}</li>\n".format(proper,ci)
    LIST3 += "</ul>\n"
    out.write(LIST3)

    TAIL = """</body>
    </html>"""
    out.write(TAIL)

# Close the connection to the database
con.close()
In [8]:
# J Vaccaro
# Code to display superlatives.html

with open("superlatives.html","rt") as f:
    for line in f:
        print(line,end="")
    <!doctype html>
    <html>
    <head>
    <title>Star superlatives</title>
    </head>
    <body>
    <h1>Star superlatives</h1>
    <p>Based on data from the <a href="https://github.com/astronexus/HYG-Database">HYG database</a>.
    <h2>Brightest as seen from earth</h2>
    <p>Top ten in terms of magnitude:</p>
    <ul>
    <li> Sol, with magnitude -26.7</li>
<li> Sirius, with magnitude -1.44</li>
<li> Canopus, with magnitude -0.62</li>
<li> Arcturus, with magnitude -0.05</li>
<li> Rigil Kentaurus, with magnitude -0.01</li>
<li> Vega, with magnitude 0.03</li>
<li> Capella, with magnitude 0.08</li>
<li> Rigel, with magnitude 0.18</li>
<li> Procyon, with magnitude 0.4</li>
<li> Achernar, with magnitude 0.45</li>
</ul>
<h2>Most light output</h2>
    <p>Top ten in terms of absolute magnitude:</p>
    <ul>
    <li> An unnamed star at right ascension 1.101407 and declination -46.718414, with absolute magnitude -16.68</li>
<li> An unnamed star at right ascension 8.677103 and declination -46.648744, with absolute magnitude -16.23</li>
<li> An unnamed star at right ascension 10.546853 and declination 9.306586, with absolute magnitude -16.16</li>
<li> An unnamed star at right ascension 18.229392 and declination -21.058834, with absolute magnitude -16.16</li>
<li> An unnamed star at right ascension 6.902209 and declination -24.184211, with absolute magnitude -16.11</li>
<li> An unnamed star at right ascension 11.143165 and declination -58.975037, with absolute magnitude -16.07</li>
<li> An unnamed star at right ascension 7.73013 and declination -28.954826, with absolute magnitude -16.06</li>
<li> An unnamed star at right ascension 0.549997 and declination 62.931783, with absolute magnitude -15.83</li>
<li> An unnamed star at right ascension 21.725127 and declination 58.780046, with absolute magnitude -15.77</li>
<li> An unnamed star at right ascension 21.75748 and declination 61.120806, with absolute magnitude -15.75</li>
</ul>
<h2>Most blue stars visible with the naked eye</h2>
    <p>Top ten indices among stars with magnitude less than 3:</p>
    <ul>
    <li> Antares, with color index 1.865</li>
<li> An unnamed star at right ascension 9.133268 and declination -43.432589, with color index 1.665</li>
<li> Scheat, with color index 1.655</li>
<li> Menkar, with color index 1.63</li>
<li> An unnamed star at right ascension 6.382673 and declination 22.513586, with color index 1.621</li>
<li> An unnamed star at right ascension 7.285711 and declination -37.09747, with color index 1.616</li>
<li> An unnamed star at right ascension 22.711115 and declination -46.884577, with color index 1.61</li>
<li> Gacrux, with color index 1.6</li>
<li> Zaurak, with color index 1.588</li>
<li> An unnamed star at right ascension 16.239094 and declination -3.694323, with color index 1.584</li>
</ul>
</body>
    </html>

3. Superlative style (?)

Create a CSS stylesheet for the superlatives.html document that was generated in exercise 2 that applies styles such as margins, widths, colors, borders, backgrounds, and fonts to the h1, h2, p, and li tags. As a first step, see if you can get the styled document to approximately match the image below. Then, if you like, you can experiment further with other ways to style the document.

(Rather than editing the superlatives.html document, I suggest you modify the original Python program that generated superlatives.html to also generate a link to a stylesheet, and then create the stylesheet by hand.)

Screenshot of styled HTML report

Note: I'm not claiming that the styled document above represents good design, or that it is aesthetically or functionally superior to the unstyled HTML from problem 1. This problem is only meant to give you experience using CSS selectors. And since it can be hard to tell from an image, the sample style shown above uses a monospace font for the headings and paragraphs, and a serif font for the list elements.

In [10]:
# J Vaccaro
# Changelog from Problem 2: 
# Only changes HEAD
# Adds the following line:
# <link rel="stylesheet" href="superlatives.css">

HEAD = """
<!doctype html>
<html>
<head>
<title>Star superlatives</title>
<link rel="stylesheet" href="superlatives.css">
</head>
<body>
<h1>Star superlatives</h1>
<p>Based on data from the <a href="https://github.com/astronexus/HYG-Database">HYG database</a>.
"""
In [11]:
# J Vaccaro
# Code to display superlatives.html

with open("superlatives.html","rt") as f:
    for line in f:
        print(line,end="")
<!doctype html>
<html>
<head>
<title>Star superlatives</title>
<link rel="stylesheet" href="superlatives.css">
</head>
<body>
<h1>Star superlatives</h1>
<p>Based on data from the <a href="https://github.com/astronexus/HYG-Database">HYG database</a>.
<h2>Brightest as seen from earth</h2>
    <p>Top ten in terms of magnitude:</p>
    <ul>
    <li> Sol, with magnitude -26.7</li>
<li> Sirius, with magnitude -1.44</li>
<li> Canopus, with magnitude -0.62</li>
<li> Arcturus, with magnitude -0.05</li>
<li> Rigil Kentaurus, with magnitude -0.01</li>
<li> Vega, with magnitude 0.03</li>
<li> Capella, with magnitude 0.08</li>
<li> Rigel, with magnitude 0.18</li>
<li> Procyon, with magnitude 0.4</li>
<li> Achernar, with magnitude 0.45</li>
</ul>
<h2>Most light output</h2>
    <p>Top ten in terms of absolute magnitude:</p>
    <ul>
    <li> An unnamed star at right ascension 1.101407 and declination -46.718414, with absolute magnitude -16.68</li>
<li> An unnamed star at right ascension 8.677103 and declination -46.648744, with absolute magnitude -16.23</li>
<li> An unnamed star at right ascension 10.546853 and declination 9.306586, with absolute magnitude -16.16</li>
<li> An unnamed star at right ascension 18.229392 and declination -21.058834, with absolute magnitude -16.16</li>
<li> An unnamed star at right ascension 6.902209 and declination -24.184211, with absolute magnitude -16.11</li>
<li> An unnamed star at right ascension 11.143165 and declination -58.975037, with absolute magnitude -16.07</li>
<li> An unnamed star at right ascension 7.73013 and declination -28.954826, with absolute magnitude -16.06</li>
<li> An unnamed star at right ascension 0.549997 and declination 62.931783, with absolute magnitude -15.83</li>
<li> An unnamed star at right ascension 21.725127 and declination 58.780046, with absolute magnitude -15.77</li>
<li> An unnamed star at right ascension 21.75748 and declination 61.120806, with absolute magnitude -15.75</li>
</ul>
<h2>Most blue stars visible with the naked eye</h2>
    <p>Top ten indices among stars with magnitude less than 3:</p>
    <ul>
    <li> Antares, with color index 1.865</li>
<li> An unnamed star at right ascension 9.133268 and declination -43.432589, with color index 1.665</li>
<li> Scheat, with color index 1.655</li>
<li> Menkar, with color index 1.63</li>
<li> An unnamed star at right ascension 6.382673 and declination 22.513586, with color index 1.621</li>
<li> An unnamed star at right ascension 7.285711 and declination -37.09747, with color index 1.616</li>
<li> An unnamed star at right ascension 22.711115 and declination -46.884577, with color index 1.61</li>
<li> Gacrux, with color index 1.6</li>
<li> Zaurak, with color index 1.588</li>
<li> An unnamed star at right ascension 16.239094 and declination -3.694323, with color index 1.584</li>
</ul>
</body>
    </html>
In [17]:
# J Vaccaro
# Code to display superlatives.css, which was provided by D. Dumas

with open("superlatives.css","rt") as f:
    for line in f:
        print(line,end="")
body {
    max-width: 40rem;
    margin: auto;
    font-size: 18px;
    font-family: monospace;
    background: #D0D0D0;
}

h1 {
    border:4px solid #D08080;
}

h1, h2 {
    text-align: center;
    background: #B0B0B0;
}

a {
    text-decoration: none;
    color: #800000;
    background:#E0E0E0;
}

ul {
    font-family:serif;
    margin-left: 30px;
    margin-right: 30px;
}

li {
   margin-top: 8px; 
}

4. Superlatives as a service

Write a Flask application that contains a single route, /superlatives/, which generates and returns the HTML document from problem 2 in response to HTTP GET requests.

As a first draft, you can make it so that it just returns a fixed string that you've prepared with the output of the program from problem 2. But to consider the problem finished, you should adapt the actual HTML generating code (which opens the database etc.) into the function which serves the route, so that the returned HTML is dynamically generated for each incoming request.

If your program is working correctly, then running it in the terminal should show something like

 * Serving Flask app "superlatives" (lazy loading)
 * Environment: production
   WARNING: This is a development server. Do not use it in a production deployment.
   Use a production WSGI server instead.
 * Debug mode: off
 * Running on http://127.0.0.1:5000/ (Press CTRL+C to quit)

and, while it is running, visiting http://localhost:5000/superlatives/ or http://127.0.0.1:5000/superlatives/ on the same computer should show the same HTML generated by the program in problem 2.

In [1]:
# MCS 275 Week 13 Problem 4
# J Vaccaro
# I completed this work myself, in accordance with the syllabus.
"""Runs a flask app at address 127.0.0.1:5000 with route /superlatives/ displaying star statistics"""

import sqlite3
from flask import Flask

# Create the flask app
app = Flask(__name__)


def name_filter(proper,ra,dec):
    """When proper==None, returns a descriptive string. Otherwise returns proper."""
    if proper==None:
        return "An unnamed star at right ascension {} and declination {}".format(ra,dec)
    return proper

#Create the route to return the html text, rather than writing it to an html file.
#Should query the stars database every time we refresh.
@app.route("/superlatives/")
def superlatives():
    """Returns the html string to display star superlatives."""

    # Open the connection to read data
    con = sqlite3.connect("hyg_data.sqlite")
    HEAD = """
    <!doctype html>
    <html>
    <head>
    <title>Star superlatives</title>
    </head>
    <body>
    <h1>Star superlatives</h1>
    <p>Based on data from the <a href="https://github.com/astronexus/HYG-Database">HYG database</a>.
    """

    LIST1 = """<h2>Brightest as seen from earth</h2>
    <p>Top ten in terms of magnitude:</p>
    <ul>
    """
    c1 = con.execute("SELECT ra,dec,proper,mag FROM stars ORDER BY mag LIMIT 10;")
    for ra,dec,proper,mag in c1:
        proper = name_filter(proper,ra,dec)
        LIST1 += "<li> {}, with magnitude {}</li>\n".format(proper,mag)
    LIST1 += "</ul>\n"

    LIST2 = """<h2>Most light output</h2>
    <p>Top ten in terms of absolute magnitude:</p>
    <ul>
    """
    c1 = con.execute("SELECT ra,dec,proper,absmag FROM stars ORDER BY absmag LIMIT 10;")
    for ra,dec,proper,absmag in c1:
        proper = name_filter(proper,ra,dec)
        LIST2 += "<li> {}, with absolute magnitude {}</li>\n".format(proper,absmag)
    LIST2 += "</ul>\n"

    LIST3 = """<h2>Most blue stars visible with the naked eye</h2>
    <p>Top ten indices among stars with magnitude less than 3:</p>
    <ul>
    """
    c1 = con.execute("SELECT ra,dec,proper,ci FROM stars WHERE ci!='' AND mag<3 ORDER BY ci DESC LIMIT 10;")
    for ra,dec,proper,ci in c1:
        proper = name_filter(proper,ra,dec)
        LIST3 += "<li> {}, with color index {}</li>\n".format(proper,ci)
    LIST3 += "</ul>\n"

    TAIL = """</body>
    </html>"""
    # Close the connection to the database
    con.close()
    return HEAD + LIST1 + LIST2 + LIST3 + TAIL

# Run the flask app, by default at 127.0.0.1:5000
app.run()
 * Serving Flask app "__main__" (lazy loading)
 * Environment: production
   WARNING: This is a development server. Do not use it in a production deployment.
   Use a production WSGI server instead.
 * Debug mode: off
 * Running on http://127.0.0.1:5000/ (Press CTRL+C to quit)
127.0.0.1 - - [17/Apr/2021 00:42:56] "GET / HTTP/1.1" 404 -
127.0.0.1 - - [17/Apr/2021 00:43:02] "GET /superlatives/ HTTP/1.1" 200 -
127.0.0.1 - - [17/Apr/2021 00:43:20] "GET /superlatives HTTP/1.1" 308 -
127.0.0.1 - - [17/Apr/2021 00:43:20] "GET /superlatives/ HTTP/1.1" 200 -
127.0.0.1 - - [17/Apr/2021 00:43:36] "GET / HTTP/1.1" 404 -
127.0.0.1 - - [17/Apr/2021 00:43:36] "GET /favicon.ico HTTP/1.1" 404 -

The solution matches the problem requirement to offer a route /superlatives/ (with a trailing slash). It is also common to have route names that do not end with trailing slash, e.g. /superlatives. It is recommended to use a trailing slash when the route is similar to a directory, in that it represents a composite object or collection, so that you might add additional subroutes later (like /superlatives/brightest to get just the brightness list as a HTML document), and to use no trailing slash when the resource is more like a file (a single entity with content, but having no sub-objects).

Flask also has some auto-redirect logic related to trailing slashes, which gives the following behavior:

Route name GET Result
/foo/ /foo 308 Permanent Redirect; browser loads and displays /foo/
/foo /foo/ 404 Not found

In other words, Flask will add a slash if helpful, but won't remove one. This is documented here.

If we wanted the CSS stylesheet to work in this app, we'd need to put it in the static folder of the Flask application and add a <link> tag that refers to it in that location, e.g.

<link rel="stylesheet" href="/static/superlatives.css">