Writing the database.py file

Now that we've decided what queries we need, let's put them into a database.py file and start creating the functions that app.py will use.

We'll need both the datetime and sqlite3 modules for this file.

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


def add_movie(title, release_timestamp):
    pass


def get_movies(upcoming=False):
    pass


def watch_movie(movie_title):
    pass


def get_watched_movies():
    pass

Here's the functions:

• create_table will just execute the CREATE_MOVIES_TABLE query.
• add_movie has two parameters because we need to give it the data we want to insert into the table. It will call the INSERT_MOVIE query and pass the two values as arguments.
• get_movies will return the movies. It has an argument, upcoming. If that is True then we should only return movies that will be released in the future. Otherwise, return all movies.
• watch_movie takes in a movie title and will change the watched column in the table to have the value 1, which means we've watched it. We'll be implementing this function later as we need to learn about the UPDATE statement in SQL.
• get_watched_movies will return the movies that have watched=1.

Other than the watch_movie function, we already know how to implement all the others! That's assuming that the arguments the functions receive are all valid and reasonable! For this project we won't be trying to handle invalid values.

Creating the table

Creating the table is easy enough. We'll use a context manager^[1] to automatically commit the changes when we're done. Inside it, we'll execute our query:

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

Adding movies

To add a movie, we'll execute the relevant query and pass in the values of the parameters:

def add_movie(title, release_timestamp):
    with connection:
        connection.execute(INSERT_MOVIE, (title, release_timestamp))

Getting movies

To get movies we need to do a little bit more work. Let's first code up the function assuming that upcoming is always False:

def get_movies(upcoming=False):
    with connection:
        cursor = connection.cursor()
        cursor.execute(SELECT_ALL_MOVIES)
        return cursor.fetchall()

In this function we're creating a cursor, and then using it to execute the SELECT_ALL_MOVIES query.

TIP

Note I'm using a cursor because I think it makes sense to here, but you could still use the connection instead by doing cursor = connection.execute(SELECT_ALL_MOVIES).

If upcoming was True then we need to execute a different query instead:

def get_movies(upcoming=False):
    with connection:
        cursor = connection.cursor()
        if upcoming:
            pass
        else:
            cursor.execute(SELECT_ALL_MOVIES)
        return cursor.fetchall()

So what do we put instead of pass?

First, we'll need to calculate the timestamp of the current time. Then we'll find movies that have a release_date later than right now:

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

Getting watched movies

To get watched movies, we again just execute the relevant query that retrieves movies WHERE watched = 1:

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

Setting a movie to "watched"

Whenever we watch a movie, we need to go into the table and change a row. Or, as SQL likes to put it, UPDATE a row.

We're going to learn about updating in the next chapter!

---

1. Using context managers for automatic commit and rollback ↩︎