# Stage 3: watching movies
In this chapter, let's modify the Stage 2 code to use the three tables we've discussed.
Table of Contents (long chapter!)
# Review of the code
As a reminder, here's what the database.py code looked like at the end of Stage 2.
import datetime
import sqlite3
CREATE_MOVIES_TABLE = """CREATE TABLE IF NOT EXISTS movies (
name TEXT,
release_timestamp REAL
);"""
CREATE_WATCHLIST_TABLE = """CREATE TABLE IF NOT EXISTS watched (
watcher_name TEXT,
name TEXT
);"""
INSERT_MOVIE = "INSERT INTO movies (title, release_timestamp) VALUES (?, ?);"
FIND_MOVIE = "SELECT * FROM movies WHERE title = ?;"
DELETE_MOVIE = "DELETE FROM movies WHERE title = ?;"
SELECT_ALL_MOVIES = "SELECT * FROM movies;"
SELECT_UPCOMING_MOVIES = "SELECT * FROM movies WHERE release_timestamp > ?;"
INSERT_WATCHED_MOVIE = "INSERT INTO watched (watcher_name, title) VALUES (?, ?);"
SELECT_WATCHED_MOVIES = "SELECT * FROM watched WHERE watcher_name = ?;"
connection = sqlite3.connect("data.db")
def create_tables():
with connection:
connection.execute(CREATE_MOVIES_TABLE)
connection.execute(CREATE_WATCHLIST_TABLE)
def add_movie(title, release_timestamp):
with connection:
connection.execute(INSERT_MOVIE, (title, release_timestamp))
def get_movies(upcoming=False):
with connection:
cursor = connection.cursor()
if upcoming:
today_timestamp = datetime.datetime.today().timestamp()
cursor.execute(SELECT_UPCOMING_MOVIES, (today_timestamp,))
else:
cursor.execute(SELECT_ALL_MOVIES)
return cursor.fetchall()
def watch_movie(username, movie_title):
with connection:
connection.execute(DELETE_MOVIE, (movie_title,))
connection.execute(INSERT_WATCHED_MOVIE, (username, movie_title))
def get_watched_movies(username):
with connection:
cursor = connection.cursor()
cursor.execute(SELECT_WATCHED_MOVIES, (username,))
return cursor.fetchall()
# Creating the new tables
Let's start off by writing our new queries for creating our tables:
CREATE_MOVIES_TABLE = """CREATE TABLE IF NOT EXISTS movies (
id INTEGER PRIMARY KEY,
title TEXT,
release_timestamp REAL
);"""
CREATE_USERS_TABLE = """CREATE TABLE IF NOT EXISTS users (
username TEXT PRIMARY KEY
);"""
CREATE_WATCHED_TABLE = """CREATE TABLE IF NOT EXISTS watched (
user_username TEXT,
movie_id INTEGER,
FOREIGN KEY(user_username) REFERENCES users(username),
FOREIGN KEY(movie_id) REFERENCES movies(id)
);"""
Remember to use these queries in the create_tables() function:
def create_tables():
with connection:
connection.execute(CREATE_MOVIES_TABLE)
connection.execute(CREATE_USERS_TABLE)
connection.execute(CREATE_WATCHED_TABLE)
# Adding a new movie
To add a new movie, we'll go ahead and modify the INSERT_MOVIE query.
When we're using INTEGER PRIMARY KEY, SQLite will automatically provide us a unique value for the column. That means we don't have to worry about putting a value in it ourselves.
INSERT_MOVIE = "INSERT INTO movies (title, release_timestamp) VALUES (?, ?)"
The function add_movie() remains unchanged, since we're still adding the same data.
# Getting and displaying movies
Getting and displaying movies remains unchanged in database.py, but we need to make a small change in app.py.
We've added an extra column to our table, so we need to account for that when printing out the list of movies. In addition, we'll need the movie_id for marking a movie as "watched", so we'll have to print it out as well.
At the moment, in app.py, we have a function called print_movie_list():
def print_movie_list(heading, movies):
print(f"-- {heading} movies --")
for movie in movies:
movie_date = datetime.datetime.fromtimestamp(movie[1])
human_date = movie_date.strftime("%d %b %Y")
print(f"{movie[0]} (on {human_date})")
print("---- \n")
We will make a small change to the column numbers and make sure to display them all:
def print_movie_list(heading, movies):
print(f"-- {heading} movies --")
for movie in movies:
movie_date = datetime.datetime.fromtimestamp(movie[2])
human_date = movie_date.strftime("%d %b %Y")
print(f"{movie[0]}: {movie[1]} (on {human_date})") # id: name (on date)
print("---- \n")
Also remember we can use tuple destructuring[1] to make the loop a bit more readable by using variable names:
def print_movie_list(heading, movies):
print(f"-- {heading} movies --")
for _id, title, release_date in movies:
movie_date = datetime.datetime.fromtimestamp(release_date)
human_date = movie_date.strftime("%d %b %Y")
print(f"{_id}: {title} (on {human_date})")
print("---- \n")
# Marking a movie as "watched"
Here's where there are more radical differences.
To mark a movie as watched, we need to insert a row into the watched table. This table requires username and movie_id values.
Let's write a query first of all that does this insertion:
INSERT_WATCHED_MOVIE = "INSERT INTO watched (user_username, movie_id) VALUES (?, ?)"
Then let's modify our watch_movie() function to use that query instead of deleting from the movies table and inserting into the old watched table:
def watch_movie(username, movie_id):
with connection:
connection.execute(INSERT_WATCHED_MOVIE, (username, movie_id))
You can see that this function now is simpler, because all our data changes involve just inserting a row into a database. We don't have to do any data manipulation. This will be simpler but also faster to execute.
In app.py, we were adding a movie by passing in the username and movie_title in Stage 2. This is what the prompt_watch_movie() looked like:
def prompt_watch_movie():
username = input("Username: ")
movie_title = input("Enter movie title you've watched: ")
database.watch_movie(username, movie_title)
Now we'll need username and movie_id:
def prompt_add_watched_movie():
username = input("Username: ")
movie_id = input("Movie ID: ")
database.watch_movie(username, movie_id)
# Adding users
In order to be able to watch a movie, we need a user to have been created first (otherwise, the foreign key doesn't reference anything).
That means that we need to have a query for inserting a new user into the users table, and a function that uses the query:
INSERT_USER = "INSERT INTO users (username) VALUES (?)"
...
def add_user(username):
with connection:
connection.execute(INSERT_USER, (username,))
In app.py, we'll need to go and allow our user to add data to the users table.
At the moment our main loop looks like this:
while (user_input := input(menu)) != "6":
if user_input == "1":
prompt_add_movie()
elif user_input == "2":
movies = database.get_movies(True)
print_movie_list("Upcoming", movies)
elif user_input == "3":
movies = database.get_movies()
print_movie_list("All", movies)
elif user_input == "4":
prompt_watch_movie()
elif user_input == "5":
username = input("Username: ")
movies = database.get_watched_movies(username)
print_watched_movie_list(username, movies)
else:
print("Invalid input, please try again!")
We'll add a new branch to the if statement for input "6":
while (user_input := input(menu)) != "7":
if user_input == "1":
prompt_add_movie()
elif user_input == "2":
movies = database.get_movies(upcoming=True)
print_movie_list("Upcoming", movies)
elif user_input == "3":
movies = database.get_movies()
print_movie_list("All", movies)
elif user_input == "4":
prompt_watch_movie()
elif user_input == "5":
username = input("Username: ")
movies = database.get_watched_movies(username)
print_watched_movie_list(username, movies)
elif user_input == "6":
prompt_add_user()
else:
print("Invalid input, please try again!")
This means that we of course need to change our menu prompt variable:
menu = """Please select one of the following options:
1) Add new movie.
2) View upcoming movies.
3) View all movies
4) Add watched movie
5) View watched movies.
6) Add user to the app.
7) Exit.
Your selection: """
# Conclusion of these changes
After we've made all these changes, the code looks like this:
import datetime
import sqlite3
CREATE_MOVIES_TABLE = """CREATE TABLE IF NOT EXISTS movies (
id INTEGER PRIMARY KEY,
title TEXT,
release_timestamp REAL
);"""
CREATE_USERS_TABLE = """CREATE TABLE IF NOT EXISTS users (
username TEXT PRIMARY KEY
);"""
CREATE_WATCHED_TABLE = """CREATE TABLE IF NOT EXISTS watched (
user_username TEXT,
movie_id INTEGER,
FOREIGN KEY(user_username) REFERENCES users(username),
FOREIGN KEY(movie_id) REFERENCES movies(id)
);"""
INSERT_MOVIE = "INSERT INTO movies (title, release_timestamp) VALUES (?, ?)"
SELECT_ALL_MOVIES = "SELECT * FROM movies;"
SELECT_UPCOMING_MOVIES = "SELECT * FROM movies WHERE release_timestamp > ?;"
INSERT_USER = "INSERT INTO users (username) VALUES (?)"
INSERT_WATCHED_MOVIE = "INSERT INTO watched (user_username, movie_id) VALUES (?, ?)"
SELECT_WATCHED_MOVIES = "SELECT * FROM watched WHERE watcher_name = ?;"
connection = sqlite3.connect("data.db")
def create_tables():
with connection:
connection.execute(CREATE_MOVIES_TABLE)
connection.execute(CREATE_USERS_TABLE)
connection.execute(CREATE_WATCHED_TABLE)
def add_movie(title, release_timestamp):
with connection:
connection.execute(INSERT_MOVIE, (title, release_timestamp))
def get_movies(upcoming=False):
with connection:
cursor = connection.cursor()
if upcoming:
today_timestamp = datetime.datetime.today().timestamp()
cursor.execute(SELECT_UPCOMING_MOVIES, (today_timestamp,))
else:
cursor.execute(SELECT_ALL_MOVIES)
return cursor.fetchall()
def add_user(username):
with connection:
connection.execute(INSERT_USER, (username,))
def watch_movie(username, movie_id):
with connection:
connection.execute(INSERT_WATCHED_MOVIE, (username, movie_id))
def get_watched_movies(username):
with connection:
cursor = connection.cursor()
cursor.execute(SELECT_WATCHED_MOVIES, (username,))
return cursor.fetchall()
The last thing we much change is the code to retrieve watched movies.
At the moment the query is plain wrong, because we don't even have a watcher_name column in the watched table. But also, it'll have to be a bit more complicated because now it has to access data from multiple tables.
The code in app.py for retrieving and showing watched movies will also need to change.
To retrieve the watched movie data, we'll need JOINs. We'll be learning about that in the next couple chapters!