CSc 250: Lab 11

This lab was designed to be completed during lab-time for cs250. This lab is not graded for correctness. However, you should work through all of the problems, as it will help you on assignments and exams. If you cannot complete the lab in the alloted time, finish at home.

This particular lab was designed to be completed on the Mac lab machines in GS 930

Problem 1 - Database setup

In this lab we are going to continue using the same database and data from lab 10. (If you did not work through the problem of lab 10, go back and review it thoroughly.)

We must create a new sqlite database file and then load the schema and data into it. Open a terminal window and cd to the Desktop directory. Start sqlite3 and create a new database file by running the following from bash:

$ sqlite3 lab11db

Now go to the lab 10 page, scroll to the bottom, and click the link to download the solutions. The solution file contains all of the CREATE and INSERT statements for the schema and data. Copy/paste the CREATE and INSERT statements into your sqlite session in the terminal window.

Run Each of the following commands in the sqlite session to make sure the data is loaded:

• .tables
• .schema
• SELECT * FROM movie;
• SELECT * FROM director;

We’ll be using this database in the remainder of the problems.

Problem 2

Assume a python program named lab-11-A.py exists in the same directory as the lab11db file (Desktop). lab-11-A.py has the following contents:

import sys
import sqlite3
conn = sqlite3.connect('lab11db')
rows = conn.execute("SELECT * FROM director")
for row in rows:
    print( str(row[0]) + ' | ' +str(row[2]))

What will this program print when run? Try to figure out the answer, then create the program and run it to check.

Problem 3

Assume a python program named lab-11-B.py exists in the same directory as the lab11db file (Desktop). lab-11-B.py has the following contents:

import sys
import sqlite3
conn = sqlite3.connect('lab11db')
rows = conn.execute("SELECT year, title, rt_rating FROM movie WHERE rt_rating > 70")
for row in rows:
    print( str(row[1]) + ' | ' +str(row[2]))

What will this program print when run? Try to figure out the answer, then create the program and run it to check.

Problem 4

Now, let’s write a very simple python program that accesses a table in this database. In the same directory that you created lab11db, create a python program named getmovies.py. This program will:

• Import sqlite3
• Connect to the lab11db database
• Executes SELECT * FROM movie;
• Loops through all of the rows that are returned and prints them out

If you forgot how to connect to a database and run a query from python, look at the previous problems or the lecture notes!

Assuming the database was populated correctly, the results of running this query should be:

$ python3 getmovies.py
King Kong | 2005 | 84 | 1 | 4
Flags of Our Fathers | 2006 | 73 | 2 | 3
Man of Steel | 2013 | 55 | 3 | 1
Super 8 | 2011 | 82 | 4 | 5
Open Range | 2003 | 79 | 5 | 7
The Kings Speech | 2010 | 95 | 6 | 2
Hacksaw Ridge | 2016 | 87 | 7 | 6

Problem 5

Write a python program named addmovies.py that uses the sqlite3 module to insert 3 new rows into the movie table using INSERT statements. You can add whichever 3 movies you want. Though, remember to choose movie_id values that have not already been used by another row! After writing the program, run it.

In bash, cd to the directory with the lab11db file in it and run sqlite3 lab11db to open it up. Run SELECT * FROM movies; and make sure the new rows appear!