This quiz must be submitted in Gradescope by 12:00pm CST on Tuesday, April 13, 2021.
This quiz covers databases and the Python module sqlite3 as discussed in Lectures 30--32.
Quizzes are INDIVIDUAL, closed book, and only allow access to specified resources. For this quiz you can access:
There are two problems on this quiz, numbered 2 and 3. The point breakdown is:
| Points | Item |
|---|---|
| 3 | autograder |
| 4 | problem 2 |
| 4 | problem 3 |
| 11 | total |
Problems 2 and 3 will both use a sqlite3 database books.db. Download it from the link below and save it into the directory where you will be working.
There is one table in the database, called books. Each row represents a book in your collection, and your progress reading it. The table has the following columns:
Here is some sample code to show you how the table was created, which also adds one of the rows that is in the sample database you downloaded. (The database you downloaded contains other rows that are not shown here.)
# Code included for clarity about how the database columns are named,
# you don't need to run this.
import sqlite3
# Open the connection
con = sqlite3.connect("books.db")
# If necessary, create a table books
con.execute("""CREATE TABLE IF NOT EXISTS books (
title TEXT PRIMARY KEY,
author TEXT,
year INT,
num_pages INT,
pages_read INT
);""")
# Add a row of data to the table
con.execute(
"INSERT INTO books VALUES (?,?,?,?,?);",
("The Cat in the Hat","Dr. Seuss",1957,61,0)
)
con.commit()
con.close()
# If you run this code in the same directory as the provided books.db,
# it will raise an exception because a book with that title is already
# present, and the table requires unique titles. That's expected
# behavior, but your solutions to the problems should not raise similar
# exceptions.
Write a script that connects to the books.db books table and answers the following prompts by printing the answer to the console. Also include the answer that you found, commented out in the body of the script.
What is the longest book (num_pages) in the database? Print the title and the number of pages.
List five books from the database that were published after 1960 (year). For each, print the title and the author.
Save your program to quiz12prob2.py and upload it to Gradescope.
Write a function flip_page(title) that updates the book identified by the title as if a single page has been flipped in the following way:
You may assume that titles are unique within the database.
The function should both open and close a connection to the database books.db. Since this function modifies the database, remember to commit your changes before closing the connection.
Here is some code to help you test your function. If your function works, and if pages_read is initially zero (as in the database you downloaded) then this will flip pages of The Cat in the Hat the exact number of times needed to finish the book and return the pages_read count to zero. We recommend printing the value of pages_read inside flip_page while you're testing it, so you can see this happening and debug any issues.
# Test code, to be added to the bottom of your script.
# Flip the page in The Cat in the Hat 62 times. Since there are 61
# pages, this should display the message that you finished the book.
for _ in range(61):
flip_page("The Cat in the Hat")
Save your program to quiz12prob3.py and upload it to Gradescope.