Stage 2: watching movies

Alright, let's get started! We have most of the code already, so what we're going to change is:

• database.create_tables must now create both tables we'll need, instead of just one.
• database.watch_movie must now delete from one table and insert into the other.

There are also a couple queries that we'll need to create or modify.

These are the queries we had at the end of Stage 1:

CREATE_MOVIES_TABLE = """CREATE TABLE IF NOT EXISTS movies (
    title TEXT,
    release_timestamp REAL,
    watched INTEGER
);"""

INSERT_MOVIE = "INSERT INTO movies (title, release_timestamp, watched) VALUES (?, ?, 0);"
SELECT_ALL_MOVIES = "SELECT * FROM movies;"
SELECT_UPCOMING_MOVIES = "SELECT * FROM movies WHERE release_timestamp > ?;"
SET_MOVIE_WATCHED = "UPDATE movies SET watched = 1 WHERE title = ?;"
SELECT_WATCHED_MOVIES = "SELECT * FROM movies WHERE watched = 1;"

First, let's modify our existing queries to match the new data model:

CREATE_MOVIES_TABLE = """CREATE TABLE IF NOT EXISTS movies (
    title TEXT,
    release_timestamp REAL
);"""

INSERT_MOVIE = "INSERT INTO movies (title, release_timestamp) VALUES (?, ?);"
SELECT_ALL_MOVIES = "SELECT * FROM movies;"
SELECT_UPCOMING_MOVIES = "SELECT * FROM movies WHERE release_timestamp > ?;"
SELECT_WATCHED_MOVIES = "SELECT * FROM watched WHERE watcher_name = ?;"

And now let's add the new queries we need:

CREATE_MOVIES_TABLE = """CREATE TABLE IF NOT EXISTS movies (
    title TEXT,
    release_timestamp REAL
);"""

CREATE_WATCHLIST_TABLE = """CREATE TABLE IF NOT EXISTS watched (
    watcher_name TEXT,
    title TEXT
);"""

INSERT_MOVIE = "INSERT INTO movies (title, release_timestamp) VALUES (?, ?);"
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 = ?;"

Creating the tables

To create the two tables we'll need, we must modify the database.create_tables function:

def create_tables():
    with connection:
        connection.execute(CREATE_MOVIES_TABLE)
        connection.execute(CREATE_WATCHLIST_TABLE)

WARNING

Remember to delete your data.db file so that it doesn't keep the old tables!

Watching movies

To mark a movie as watched, we'll now need to delete the appropriate entry from movies and insert it into watched:

def watch_movie(username, movie_title):
    with connection:
        connection.execute(DELETE_MOVIE, (movie_title,))
        connection.execute(INSERT_WATCHED_MOVIE, (username, movie_title))

Notice that watch_movie now takes a username as well, since we're storing the person that watched the movie inside the watched table. Previously it only needed the movie title.

In app.py we can do something like this:

def prompt_watch_movie():
    username = input("Username: ")
    movie_title = input("Enter movie title you've watched: ")
    database.watch_movie(username, movie_title)

Getting watched movies

The database.get_watched_movies function looks like this:

def get_watched_movies():
    with connection:
        cursor = connection.cursor()
        cursor.execute(SELECT_WATCHED_MOVIES)
        return cursor.fetchall()

But now our SELECT_WATCHED_MOVIES query needs us to pass the watcher_name field, so that we can retrieve only those movies that have been watched by a particular user.

Our function therefore needs to change. It needs to accept an argument and use it in the query, like so:

def get_watched_movies(username):
    with connection:
        cursor = connection.cursor()
        cursor.execute(SELECT_WATCHED_MOVIES, (username,))
        return cursor.fetchall()

Displaying watched movies

Right now, in app.py we're doing something like this:

...
movies = database.get_watched_movies()
print_movie_list("Watched", movies)
...

As we've just changed the database.get_watched_movies() function, we need to pass in a username.

Let's collect the username from the user and pass it in:

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

We have to make one more change though. The database.get_watched_movies function retrieves data from a different table than it did when we wrote that code in Stage 1.

Because the new table has different structure, the print_movie_list function will not work correctly. The function right now expects data to be in this format:

title	release_timestamp

But our watched table has this format instead:

watcher_name	title

At the moment the print_movie_list() function looks like this:

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

However, movie data coming through database.get_watched_movies() will no longer have release date data. That's because our watched table does not have that column. Also, the name of the movie is now on column 1.

So instead of using print_movie_list("Watched", movies), we'll need to create a new function just for displaying watched movie data. We can some something like this:

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

And then in the menu:

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

I hope that all makes sense!

Although this has been a rather long chapter, when we're talking only about the code changes we've made, it's not very many.

That's because we separated the database and user logic in two files, so most of the app.py code didn't have to change when we changed the entire database structure!