# Stage 1: database queries

This first stage of the project is meant to replicate what you might do in a much larger project when you're getting started.

Many developers start planning and designing in their heads before even writing a single line of code. It's easy to fall into the trap of overcomplicating and overdesigning when you're doing this.

A much better approach is to pick one feature--one thing your project should be able to do--and then implement it in the simplest way possible.

For this first stage, the feature is "to store and retrieve watched movies".

TIP

You may think that we should start this project with a different feature. That's totally fine! Every developer does things differently, but the important thing is that you try to simplify as much as possible before you start working on it.

# Storing and retrieving watched movies

The simplest way I can think of to store watched movies is to just put them in a table:

title release_date watched

If we add movies to a table with that structure, we can tell whether we've watched them or not. I'm also adding release_date in there just to have a bit more information about each movie. You might instead choose to add the director's name, or include both the release data and the director's name. It's up to you.

If we put data in there, it could look like this:

title release_date watched
The Matrix 929112141 1
Gone Girl 1412261006 0
Green Book 1549032206 1

TIP

What's in release_date?

Often in computing instead of dates in human-readable formats, we'll store seconds that have passed since 1st January 1970 at midnight[1].

So for the date "2nd January 1970 at midnight" we would store 86400, or the number seconds in on day.

Our code will be able to turn this number into a date. We'll be learning how later on!

For a table like this one, we'll need queries to:

  • Create the table
  • Insert a movie
  • Select all movies
  • Select upcoming movies
  • Select watched movies
  • Set a movie to "watched"

The first few we already know how to do:

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

Setting the movie to watched requires some new SQL knowledge. More on that later!

Let's continue in the next chapter by writing our database.py file that uses these queries!


  1. Why does Unix time start at 1970-01-01? (opens new window) ↩ī¸Ž