MCS 275 Spring 2024 Homework 11 Solutions¶

Course Instructor: Emily Dumas

2. More powerplants queries¶

Helpful tip: First, here's an SQL feature that you should know about. If you have a query that selects a bunch of rows, e.g.

SELECT * FROM available_beverages WHERE caffeine_mg_per_liter > 400;

then you might want to know how many rows it returns. Instead of fetching them all and counting them, it is better to have SQLite do the counting. You can do that by replacing the * or the list of requested columns with COUNT(*). For example

SELECT COUNT(*) FROM available_beverages WHERE caffeine_mg_per_liter > 400;

is a query that returns a single row, which has a single column, which contains an integer. That integer is the number of rows matching the SELECT statement.

Your task: Write a python script hwk11prob2.py that connects to the power plants sqlite database (powerplants.sqlite) and performs appropriate SQL queries in order to print answers to the following questions:

A. What are the five oldest power plants in the database, in terms of the year they were commissioned? For each, list the name, the country, and the year it was commisioned (oldest first). Power plants for which no commissioning date is included should be excluded from consideration.
B. How many nuclear power plants in the database are located in the southern hemisphere?
C. What percentage of the power plants in the United Kingdom are wind power plants?
D. What percentage of the total power generation capacity in the United Kingdom comes from wind power plants?

Also, please paste the output of your program at the bottom of the code as a sequence of comments.

Note: In this problem terms "nuclear power plant" and "wind power plant" always refer to the primary fuel used by a facility.

Solutions¶

For exposition we show the queries and the results as a series of cells in this notebook. It would be simple to assemble these cells into a single script that does what was requested.

import sqlite3

def run_query(s):
    "Utility function to run one power plant query and return the results as a list"
    con = sqlite3.connect("powerplants.sqlite")
    res = con.execute(s)
    rows = res.fetchall()
    con.close()
    return rows
    
def value_query(s):
    "Utility function to run a query that gives a single row and column, which returns that one value"
    con = sqlite3.connect("powerplants.sqlite")
    res = con.execute(s)
    value = res.fetchone()[0]
    con.close()
    return value

A¶

run_query("""
SELECT name, country, year_commissioned
FROM powerplants
WHERE year_commissioned IS NOT NULL
ORDER BY year_commissioned
LIMIT 5;
""")

[('Granite', 'United States of America', 1896),
 ('Santa Ana 1', 'United States of America', 1899),
 ('Bompreço Fortaleza', 'Brazil', 1900),
 ('Telesp', 'Brazil', 1900),
 ('Samara GRES', 'Russia', 1900)]

B¶

value_query("""
SELECT COUNT(*)
FROM powerplants
WHERE primary_fuel = "Nuclear" AND latitude < 0;
""")

6

C¶

wind_uk = value_query("""
SELECT COUNT(*)
FROM powerplants
WHERE primary_fuel = "Wind" AND country = "United Kingdom";
""")
all_uk = value_query("""
SELECT COUNT(*)
FROM powerplants
WHERE country = "United Kingdom";
""")
print("{:.1f}%".format(100*wind_uk/all_uk))

28.4%

D¶

# List of UK wind facility generation capacities in MW
wind_capacities = [
    x[0] for x in run_query(
        """
        SELECT capacity_mw
        FROM powerplants
        WHERE primary_fuel = "Wind" AND country = "United Kingdom";
        """)
]

# List of all UK generation capacities in MW
uk_capacities = [
    x[0] for x in run_query(
        """
        SELECT capacity_mw
        FROM powerplants
        WHERE country = "United Kingdom";
        """)
]

# Sum and divide
print("{:.1f}%".format(100*sum(wind_capacities)/sum(uk_capacities)))

23.9%

3. I always have at least 10 things to do¶

Remember the first task list sample application we wrote, which works in the terminal? (Don't confuse it with the more complex web-based one we're working on now.) It can be found at:

tasks.py

It uses a database tasks.db containing a single table called tasks with this schema:

CREATE TABLE tasks (
id INTEGER PRIMARY KEY,
desc TEXT,
complete INTEGER DEFAULT 0 );

Write a python script hwk11prob3.py that opens and modifies the database tasks.db as follows:

Every task in the table is marked as not complete
If there are fewer than 10 tasks in the table, additional random tasks (see below) are added until the total number in the table is 10.

Thus, you could run this script if you were preparing to experiment with tasks.py, since having a bunch of incomplete tasks in the database would provide a helpful starting point.

In the description above, a random task should be understood to mean one whose description has the form

VERB the PLURAL_NOUN

where VERB is selected at random from the following list (given as a list of Python string literals, for your convenience!)

[
    "Organize",
    "Update",
    "Compile",
    "Review",
    "Calculate",
    "Design",
    "Innovate",
    "Schedule",
    "Research",
    "Present",
    "Analyze",
    "Brainstorm",
    "Digitize",
    "Archive",
    "Prioritize",
    "Customize",
    "Reimagine",
    "Streamline",
    "Negotiate",
    "Facilitate"
]

and PLURAL_NOUN is similarly selected at random from the list

[
    "MCS 275 projects",
    "Taft hall water fountains",
    "water leaks in the south stairwell of SEO",
    "library electrical outlets",
    "rain gutters",
    "spreadsheets",
    "ideas",
    "proposals",
    "widgets",
    "coffee cups",
    "emails",
    "invoices",
    "reports",
    "strategies",
    "presentations",
    "budgets",
    "meetings",
    "deadlines",
    "diplomatic cables",
    "break room chairs"
]

The script should not produce any output in the terminal. Its only function is to modify the tasks table stored in tasks.db.

Solution¶

import sqlite3
import random


verbs = [
    "Organize",
    "Update",
    "Compile",
    "Review",
    "Calculate",
    "Design",
    "Innovate",
    "Schedule",
    "Research",
    "Present",
    "Analyze",
    "Brainstorm",
    "Digitize",
    "Archive",
    "Prioritize",
    "Customize",
    "Reimagine",
    "Streamline",
    "Negotiate",
    "Facilitate"
]
nouns = [
    "MCS 275 projects",
    "Taft hall water fountains",
    "water leaks in the south stairwell of SEO",
    "library electrical outlets",
    "rain gutters",
    "spreadsheets",
    "ideas",
    "proposals",
    "widgets",
    "coffee cups",
    "emails",
    "invoices",
    "reports",
    "strategies",
    "presentations",
    "budgets",
    "meetings",
    "deadlines",
    "diplomatic cables",
    "break room chairs"
]


def random_task():
    "Make up a random task"
    return random.choice(verbs) + " the " + random.choice(nouns)

con = sqlite3.connect("tasks.db")

# Mark all incomplete
con.execute("UPDATE tasks SET complete=0;")

# Num rows
num_rows = con.execute("SELECT COUNT(*) from tasks;").fetchone()[0]

# Pad if needed
for _ in range(10 - num_rows):
    con.execute("INSERT INTO tasks (desc) VALUES (?);", [random_task()] )

con.commit()
con.close()

Revision history¶

2024-04-04 Initial publication