# Setting movies to "watched"
Now that we've learned about the
UPDATE statement, let's implement the
watch_movie function in our
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:
- Write the SQL query that will take care of updating a movie and setting it to "watched".
- Call that query from the
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
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".