# 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
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_tablewill just execute the
add_moviehas two parameters because we need to give it the data we want to insert into the table. It will call the
INSERT_MOVIEquery and pass the two values as arguments.
get_movieswill return the movies. It has an argument,
upcoming. If that is
Truethen we should only return movies that will be released in the future. Otherwise, return all movies.
watch_movietakes in a movie title and will change the
watchedcolumn 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
UPDATEstatement in SQL.
get_watched_movieswill return the movies that have
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 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
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
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).
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
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!