Stage 3: Using JOINs to retrieve watched movies

Now that we've learned about JOINs, let's get to finishing our Stage 3 database.py file!

Table of Contents (long chapter!)

• Review of the code
• Changes in database.py
• Changes in app.py
  • Small improvement for inactive users

Review of the code

As a reminder, here's what the code looks like right now:

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()

Changes in database.py

We have a problem with the SELECT_WATCHED_MOVIES query and potentially with the get_watched_movies() function.

The new query will look like this:

SELECT movies.*
FROM movies
JOIN watched ON watched.movie_id = movies.id
JOIN users ON users.username = watched.user_username
WHERE users.username = ?;

We want to:

• Select all the columns of the movies table with movies.*.
• From the movies table.
  • And the watched table, making sure to join rows on matching id of movies.
    • And the users table, making sure to join rows on matching username with the watched table.
• Where the users.username column has a value that we specify.

Confused? Don't worry!

This is by far the most complicated query we've written, but hopefully it makes some sense. A lot of this won't truly click until you've played around with it a bit and made a few mistakes.

That's why we always recommend building a few other similar but different projects that will make you think past what we're telling you here!

From all this we'll get movie data of movies that a certain user has watched.

The big reason to only get movies.* is because we want to select watched movies. We don't want any data from the user or watched table in here, as that is not strictly "movie data".

In addition, since we're getting just the movies columns and the query needs the same data it needed before the changes (the username column), we have to make no changes to get_watched_movies(). Hurrah!

Changes in app.py

The big difference for app.py is that in Stage 2 we had a watched table that contained the name of the person watching, and the movie title.

At the moment in app.py we're still expect data to come back to us in that format.

However, with our recent changes, data on watched movies will have the same format that database.get_movies() returns.

That means that we can re-use our print_movies_list() function in app.py!

At the moment the code in app.py related to this is:

def print_watched_movie_list(username, movies):
    print(f"-- {username}'s watched movies --")
    for movie in movies:
        print(f"{movie[1]}")
    print("---- \n")


...
    elif user_input == "5":
        username = input("Username: ")
        movies = database.get_watched_movies(username)
        print_watched_movie_list(username, movies)
...

But now we'll just get the movies and pass them to the same function that the other if statement branches are using. I'm also creating a function here because the code is getting a bit longer!

def prompt_show_watched_movies():
    username = input("Username: ")
    movies = database.get_watched_movies(username)
    print_movie_list("Watched", movies)

...
    elif user_input == "5":
        prompt_show_watched_movies()
...

You can delete the print_watched_movie_list() function.

Small improvement for inactive users

In applications like these, many users will not have watched any movies. We can quite easily make an improvement to our application to show a nice message when this happens:

...
    def prompt_show_watched_movies():
        username = input("Username: ")
        movies = database.get_watched_movies(username)
        if movies:
            print_movie_list("Watched", movies)
        else:
            print("That user has watched no movies yet!")
...

TIP

You can do the same with the other movie-retrieval branches: all movies and upcoming movies. To save you time we won't do it here, but it would be quite similar!

That's about it for this chapter. I hope you're still with me, even if a little bit confused.

Remember you can join our Discord server if you have any questions!