Lab 5: Programming using SQLite

This lab is designed to help you practice writing programs that access relational databases. Specifically, you will write a program that queries and updates a SQLite database.

You should work on this assignment individually.

Instructions

Task 0: Find Teammates for Course Projects

• If you already have three teammates, you are all set!
• If you have a project idea, but don’t have a teammate (or only have two teammates and would like a third teammate), write your name and project idea on left-hand side of the board
• If you don’t have a project idea, write your name on the right-hand side of the board

For the first 5 minutes of lab, discuss with others to try and finish forming your project teams.

Task 1: SQLite Python Tutorial

Skim through this SQLite Python tutorial. You may find it helpful to refer to the tutorial as you write your programs.

Task 2: Download a SQLite Database

Download the university.db database, which you will use for this lab.

Task 3: Sketch the Database Schema

It will be easier to write your program if you understand the database schema. Draw a relational schema for the database, depicting primary keys and foreign keys. There’s no need to include sample data.

You may find it helpful to view the database schema using a tool like DB Browser for SQLite.

Task 4: Write a Simple Program

You should write a simple command-line program that interacts with the SQLite database. You must:

• Include at least three selects that display results
  • At least one should be relatively complex (e.g., joining multiple tables)
• Include at least one update that modifies the database

Here is an example of how your program might work. This example uses Python’s argparse module, which makes it easy to parse command-line arguments and display usage information.

> python3 university.py --help
usage: university.py [-h] [--add-student NAME] [--show-students] database

Interact with the university database

positional arguments:
  database            SQLite database file

optional arguments:
  -h, --help          show this help message and exit
  --add-student NAME  Add a student to the database
  --show-students     List the names of all students

> python3 university.py university.db --add-student Stuart
Recorded Stuart

> python3 university.py university.db --show-students
Students:
  Jill Jones
  Alan Turing
  Stuart
  ...

Here is some of my code to get you started:

import argparse

def main():
    argparser = argparse.ArgumentParser(
        description="Interact with the university database"
    )
    argparser.add_argument(
        "database",
        help="SQLite database file",
    )
    argparser.add_argument(
        "--add-student",
        metavar="NAME",
        help="Add a student to the database",
    )
    argparser.add_argument(
        "--show-students",
        action="store_true",
        help="List the names of all students",
    )
    args = argparser.parse_args()
    conn = db.connect(args.database)
    if args.add_student:
        add_student(conn, args.add_student)
    if args.show_students:
        show_students(conn)


if __name__ == "__main__":
    main()

Submit

You should upload a PDF to Moodle containing:

1. Your program’s code
2. Screenshot(s) demonstrating use of your program

The assignment will only be graded for completion.