This homework assignment must be submitted in Gradescope by Noon central time on Tuesday April 11, 2023.
Collaboration is prohibited, and you may only access resources (books, online, etc.) listed below.
This homework is about SQLite.
Most relevant:
Less likely to be relevant, but also allowed:
This homework assignment has 2 problems, numbered 2 and 3. The grading breakdown is:
| Points | Item |
|---|---|
| 3 | Autograder |
| 6 | Problem 2 |
| 6 | Problem 3 |
| 15 | Total |
The part marked "autograder" reflects points assigned to your submission based on some simple automated checks for Python syntax, etc. The result of these checks is shown immediately after you submit.
Ask your instructor or TA a question by email, in office hours, or on discord.
The work for this problem should go in a file called hwk12prob2.py.
If you don't already have it, download the powerplants SQLite database from Worksheet 12 and save it in a place you can find:
As a reminder, the only table is called powerplants and the columns present in that table are described at
Below you'll find a basic Python script that opens that database and prints the total number of columns. I'm providing it so you focus more on queries than on Python script details. The script requires powerplants.sqlite to be in the same directory.
"Count rows in the powerplants table"
import sqlite3
# Open connection
con = sqlite3.connect("powerplants.sqlite")
# Run query
res = con.execute("SELECT COUNT(*) FROM powerplants;")
# Get one result row; there is also a .fetchall() method to get
# a list of rows, or you can iterate over result rows in a for loop.
first_result_row = res.fetchone()
# Now first_result_row is a tuple like (30000,)
# We want its first entry
numrows = first_result_row[0]
# Close connection
con.close()
print("The number of rows in table `powerplants` is {}.".format(numrows))
Compose appropriate SQL queries and modify this Python script so that it prints answers to all of the following questions:
Determine which of these companies own power plants in at least two different countries:
BiffaEDFEskomIPPPacifiCorpFor any that own plants in two or more countries, print the company name.
Note: I don't mean you should run the queries yourself and then copy the answers into print() calls. Instead, the Python script you submit should execute the queries each time it is run.
todo.py for demos¶The work for this problem should go in a file called hwk12prob3.py.
This problem requires you to be familiar with the "todo list" sample application from the course sample code repository, which uses a SQLite database. It can be found at:
Download it, save it somewhere, try it out in the terminal, and take a look at the code. That script will make its own database the first time you run it. Try commands like:
python3 todo.py add Submit homework 12
python3 todo.py add Finish project 4
python3 todo.py list
python3 todo.py done 1
python3 todo.py list
python3 todo.py listallAs you see, this script stores a task list in a database table, and lets you add, remove, or modify items in that list by running the script with appropriate command line arguments.
For reference, here's the table schema it uses in todo.sqlite:
CREATE TABLE tasks (
id INTEGER PRIMARY KEY,
desc TEXT,
complete INTEGER DEFAULT 0 );
Imagine you're planning to use todo.py as part of a lecture demonstration. It might not be ideal to start with an empty database, and it might also be helpful to have a way to mark every task as not completed (undone) as a sort of "reset" to allow you to once again show how to mark a few of them completed (done).
Write a script hwk12prob3.py that expects one command line argument. The behavior should be:
reset, sampledata, or cleardone, print an error message and exit.todo.sqlite.tasks doesn't already exist, create a table with the columns id, description, completed as described above.reset, delete all rows from the table taskssampledata, add 10 rows into the database with distinct descriptions. Have all of them start out with completed set to 0. Don't set the id column values explicitly, so if you run this twice, 10 more rows will be added.cleardone, set completed to 0 in every rowThis could therefore be used as a companion to todo.py for demonstrations, e.g.
python3 hwk12prob3.py reset and then python3 hwk12prob3.py sampledata so you have a known set of sample tasks (none of them done) in the database.python3 hwk12prob3.py cleardoneNote: The script hwk12prob3.py does not need to duplicate any features of todo.py and should not be written by modifying todo.py. Instead, the two scripts are meant to be used together, accessing the same database but doing different things.