Setting movies to "watched"

Now that we've learned about the UPDATE statement, let's implement the watch_movie function in our database.py file.

Just to remind you, this is what the code looks like at the moment:

import datetime
import sqlite3

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 > ?;"
SELECT_WATCHED_MOVIES = "SELECT * FROM movies WHERE watched = 1;"

connection = sqlite3.connect("data.db")


def create_tables():
    with connection:
        connection.execute(CREATE_MOVIES_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(movie_title):
    pass


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

We need to do two things:

1. Write the SQL query that will take care of updating a movie and setting it to "watched".
2. Call that query from the watch_movie function.

In order to update a movie, we'll need to be able to identify that movie in the table. That's why the function is already accepting an argument: the movie_title parameter.

The query could look something like this:

UPDATE movies SET watched = 1 WHERE title = ?;

Then we can use that inside our function, making sure to pass the movie title as an argument.

def watch_movie(movie_title):
    with connection:
        connection.execute(SET_MOVIE_WATCHED, (movie_title,))

That's about it! With that function, we can now set movies to "watched".