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.
# MCS 275 Quiz 12 Problem 2
# J Vaccaro
# I wrote this code myself, in accordance with the syllabus
"""Read the longest book and books written since 1960 from books.db"""
import sqlite3
# Open the connection
con = sqlite3.connect("books.db")
# 2A: Longest book
c_2A = con.execute("SELECT title, num_pages FROM books ORDER BY num_pages DESC LIMIT 1;")
title,num_pages = c_2A.fetchone()
print("The longest book, {}, has {} pages".format(title,num_pages))
# The longest book, Watership Down, has 475 pages
# 2B: Books since 1960
c_2B = con.execute("SELECT title,author FROM books WHERE year>1960 LIMIT 5;")
print("\nFive books published since 1960:")
for title,author in c_2B:
print("* {} by {}".format(title,author))
# * Slaughterhouse-Five by Kurt Vonnegut
# * Watership Down by Richard Adams
# * The Road by Cormac McCarthy
# * One Hundred Years of Solitude by Gabriel Garcia Marquez
# * Life of Pi by Yann Martel
# Close the connection, no need to commit because no changes were made
con.close()
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.
# MCS 275 Quiz 12 Problem 3
# J Vaccaro
# I wrote this code myself, in accordance with the syllabus.
"""Flip a page in the books.db sqlite3 database"""
import sqlite3
def flip_page(title):
"""Increases the pages_read count in the book entry of a given title"""
# Open the connection
con = sqlite3.connect("books.db")
# Read the current pages_read and num_pages for the entry with the given title
c = con.execute("SELECT pages_read,num_pages FROM books WHERE title=?",(title,))
pages_read,num_pages = c.fetchone()
# Check whether we will finish the book here.
if pages_read<num_pages:
pages_read+=1
else:
print("Finished the book: {}".format(title))
pages_read=0
# Update the pages_read value for the entry with the given title
con.execute("UPDATE books SET pages_read=? WHERE title=?",(pages_read,title))
# Must commit, because we made a change
con.commit()
con.close()
# Test code, to be added to the bottom of your script.
# Flip the page in The Cat in the Hat 61 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.