""" movie_rental_db.py =================== Builds the small movie rental database used by the Section 4.11 practice notebook (movie_rental_practice.ipynb). Every problem in that notebook is designed to need a date, numeric, string, or conversion function from Section 4.11 to answer, not just a plain SELECT. HOW TO USE IT ------------- Put this file in the same folder as your notebook, then run: from movie_rental_db import create_database connection, cursor = create_database() That one call does everything: * opens the database file movie_rental.db * drops the three tables if they already exist * creates MOVIE, CUSTOMER and RENTAL * inserts all the sample rows * prints a short report so you can see it worked It is always safe to run it again. The tables are dropped and rebuilt every time, so you can reset your data at any point by re running that one cell. THE THREE TABLES ----------------- MOVIE 10 rows the store's catalog CUSTOMER 8 rows the store's members RENTAL 20 rows one row per rental transaction A few things about the data are deliberate, because the practice problems depend on them: * RENTAL stores only rental_date and return_date. It does NOT store a due date or a late fee anywhere; both have to be computed with SQL date and numeric functions, which is the whole point of the exercise. * Three rentals have a NULL return_date: the movie has not been brought back yet. Any query that computes "days rented" has to either skip these or handle the NULL on purpose. * Six rentals ran longer than the standard 7 day rental period, so "which rentals came back late" has a real, non empty answer. * Three customers have a last name starting with S (Sanchez, Smith, Stevens), spelled with different cases in mind for a case-insensitive search problem. * One movie title, "Starlight Odyssey", is deliberately longer than the 15 characters used in the title-shortening problem. """ import sqlite3 DB_FILENAME = "movie_rental.db" # --------------------------------------------------------------------------- # The data # --------------------------------------------------------------------------- # (movie_id, title, genre, rating, release_date, rental_price) MOVIE_ROWS = [ (1, "The Great Heist", "Action", "PG-13", "2023-05-12", 4.99), (2, "Whispering Pines", "Drama", "PG", "2021-11-03", 3.99), (3, "Starlight Odyssey", "Sci-Fi", "PG-13", "2023-08-22", 5.99), (4, "Comedy Night Live", "Comedy", "PG-13", "2022-02-14", 2.99), (5, "The Last Lighthouse", "Drama", "PG", "2020-09-30", 3.49), (6, "Shadow Protocol", "Action", "R", "2023-01-18", 4.99), (7, "Sunflower Summer", "Family", "G", "2019-06-01", 2.99), (8, "Midnight Mystery", "Thriller","PG-13", "2022-10-31", 4.49), (9, "Galactic Rangers", "Sci-Fi", "PG", "2023-03-09", 5.49), (10, "Quiet Storm", "Drama", "R", "2021-04-17", 3.99), ] # (customer_id, first_name, last_name, email, signup_date) CUSTOMER_ROWS = [ (1, "Sarah", "Sanchez", "sarah.sanchez@email.com", "2022-01-15"), (2, "James", "Kim", "james.kim@email.com", "2022-03-22"), (3, "Maria", "Alvarez", "maria.alvarez@email.com", "2021-11-05"), (4, "David", "Smith", "david.smith@email.com", "2023-02-10"), (5, "Priya", "Patel", "priya.patel@email.com", "2022-07-19"), (6, "Samuel", "Osei", "samuel.osei@email.com", "2023-05-01"), (7, "Linda", "Chen", "linda.chen@email.com", "2021-08-30"), (8, "Robert", "STEVENS", "robert.stevens@email.com", "2022-12-12"), ] # (rental_id, movie_id, customer_id, rental_date, return_date) # return_date of None means the movie has not been returned yet. # The standard rental period used throughout the notebook is 7 days. RENTAL_ROWS = [ (1, 1, 1, "2026-06-01", "2026-06-05"), # 4 days, on time (2, 3, 2, "2026-06-02", "2026-06-10"), # 8 days, LATE (3, 5, 3, "2026-06-03", "2026-06-06"), # 3 days, on time (4, 2, 4, "2026-06-04", None), # still out (5, 6, 1, "2026-06-05", "2026-06-09"), # 4 days, on time (6, 9, 5, "2026-06-06", "2026-06-15"), # 9 days, LATE (7, 1, 6, "2026-06-07", "2026-06-12"), # 5 days, on time (8, 8, 7, "2026-06-08", "2026-06-20"), # 12 days, LATE (9, 4, 8, "2026-06-09", "2026-06-11"), # 2 days, on time (10, 10, 2, "2026-06-10", None), # still out (11, 3, 3, "2026-06-11", "2026-06-14"), # 3 days, on time (12, 7, 4, "2026-06-12", "2026-06-25"), # 13 days, LATE (13, 6, 5, "2026-06-13", "2026-06-16"), # 3 days, on time (14, 2, 6, "2026-06-14", "2026-06-19"), # 5 days, on time (15, 9, 7, "2026-06-15", "2026-06-24"), # 9 days, LATE (16, 5, 8, "2026-06-16", "2026-06-18"), # 2 days, on time (17, 1, 3, "2026-06-17", None), # still out (18, 8, 1, "2026-06-18", "2026-06-21"), # 3 days, on time (19, 10, 4, "2026-06-19", "2026-06-28"), # 9 days, LATE (20, 4, 5, "2026-06-20", "2026-06-22"), # 2 days, on time ] # --------------------------------------------------------------------------- # The builder # --------------------------------------------------------------------------- def create_database(filename=DB_FILENAME, quiet=False): """Create the database and return (connection, cursor). filename : where to store the database. The default creates a file in the same folder as your notebook. Pass ":memory:" if you would rather it disappear when you close the notebook. quiet : pass True to skip the summary printout. """ connection = sqlite3.connect(filename) cursor = connection.cursor() for table in ["RENTAL", "CUSTOMER", "MOVIE"]: cursor.execute("DROP TABLE IF EXISTS " + table) cursor.execute(""" CREATE TABLE MOVIE ( movie_id INTEGER PRIMARY KEY, title TEXT NOT NULL, genre TEXT, rating TEXT, release_date TEXT, rental_price REAL ) """) cursor.execute(""" CREATE TABLE CUSTOMER ( customer_id INTEGER PRIMARY KEY, first_name TEXT, last_name TEXT NOT NULL, email TEXT, signup_date TEXT ) """) cursor.execute(""" CREATE TABLE RENTAL ( rental_id INTEGER PRIMARY KEY, movie_id INTEGER, customer_id INTEGER, rental_date TEXT NOT NULL, return_date TEXT, FOREIGN KEY (movie_id) REFERENCES MOVIE (movie_id), FOREIGN KEY (customer_id) REFERENCES CUSTOMER (customer_id) ) """) cursor.executemany( "INSERT INTO MOVIE VALUES (?, ?, ?, ?, ?, ?)", MOVIE_ROWS) cursor.executemany( "INSERT INTO CUSTOMER VALUES (?, ?, ?, ?, ?)", CUSTOMER_ROWS) cursor.executemany( "INSERT INTO RENTAL VALUES (?, ?, ?, ?, ?)", RENTAL_ROWS) connection.commit() if not quiet: print("Database ready:", filename) print() for table in ["MOVIE", "CUSTOMER", "RENTAL"]: cursor.execute("SELECT COUNT(*) FROM " + table) print(" " + table.ljust(10), cursor.fetchone()[0], "rows") print() cursor.execute("SELECT COUNT(*) FROM RENTAL WHERE return_date IS NULL") print(" Rentals still out: ", cursor.fetchone()[0]) print() print("You are ready to start Problem 1.") return connection, cursor def show_columns(cursor, table): """Print the column names of one table. Handy if you forget a name.""" cursor.execute("SELECT * FROM " + table + " LIMIT 1") names = [description[0] for description in cursor.description] print(table, "columns:") for name in names: print(" ", name) if __name__ == "__main__": connection, cursor = create_database(":memory:") print() show_columns(cursor, "RENTAL") connection.close()