Movie App with Python & SQLite

We want to make an application like the following using Python and SQL

Movie App

In this application, there is a table called ‘movies’ and we will add movies to this table. Also, there is a table called ‘users’. We will add users to this table. If a user had watched a movie, then this user and the movie they had watched will be added to the ‘watched’ table.

This application will consist of 3 files: app.ipynb, database.py and data.db
- data.db : Database file that contains our tables.
- database.py : Contains the code that interacts with data.db
- app.ipynb : Contains the code that interacts with users.

database.py

import datetime
import sqlite3


def execute(
    query: str,
    parameters: tuple = (),
    database: str = "data.db",
    commit=True,
    return_cursor=False,
):
    # SQLite veritabanına bağlan
    connection = sqlite3.connect(database)
    cursor = connection.cursor()

    # İşlemi başlat
    cursor.execute("BEGIN TRANSACTION;")

    # Tabloyu oluştur
    cursor.execute(query, parameters)

    if return_cursor:
        return cursor, connection

    # İşlemi manuel olarak commit et veya etme
    if commit:
        connection.commit()
    else:
        connection.rollback()

    # Bağlantıyı kapat
    connection.close()


CREATE_MOVIES_TABLE = """CREATE TABLE IF NOT EXISTS movies (
    id INTEGER PRIMARY KEY,
    title TEXT,
    release_timestamp REAL
);"""

CREATE_USERS_TABLE = """CREATE TABLE IF NOT EXISTS users (
    username TEXT PRIMARY KEY
);"""

CREATE_WATCHED_TABLE = """CREATE TABLE IF NOT EXISTS watched (
    user_username TEXT,
    movie_id INTEGER,
    FOREIGN KEY(user_username) REFERENCES users(username),
    FOREIGN KEY(movie_id) REFERENCES movies(id)
);"""

INSERT_MOVIE = "INSERT INTO movies (title, release_timestamp) VALUES (?, ?)"
SELECT_ALL_MOVIES = "SELECT * FROM movies;"
SELECT_UPCOMING_MOVIES = "SELECT * FROM movies WHERE release_timestamp > ?;"
INSERT_USER = "INSERT INTO users (username) VALUES (?)"
INSERT_WATCHED_MOVIE = "INSERT INTO watched (user_username, movie_id) VALUES (?, ?)"
SELECT_WATCHED_MOVIES = """SELECT movies.*
FROM movies
JOIN watched ON watched.movie_id = movies.id
JOIN users ON users.username = watched.user_username
WHERE users.username = ?;
"""
SEARCH_MOVIE = """SELECT * FROM movies WHERE title LIKE ?;"""
DELETE_USER_WATCHED = "DELETE FROM watched WHERE user_username = ?;"
DELETE_USER_USERS = "DELETE FROM users WHERE username = ?;"
UNWATCH_MOVIE = "DELETE FROM watched WHERE user_username = ? AND movie_id = ?;"
ALL_USERS = "SELECT * FROM users;"


def create_tables():
    execute(CREATE_MOVIES_TABLE)
    execute(CREATE_USERS_TABLE)
    execute(CREATE_WATCHED_TABLE)


def add_movie(title, release_timestamp):
    execute(INSERT_MOVIE, (title, release_timestamp))


def get_movies(upcoming=False):
    if upcoming:
        today_timestamp = datetime.datetime.today().timestamp()
        cursor, connection = execute(
            SELECT_UPCOMING_MOVIES, parameters=(today_timestamp,), return_cursor=True
        )
    else:
        cursor, connection = execute(SELECT_ALL_MOVIES, return_cursor=True)

    movies = cursor.fetchall()
    connection.close()
    return movies


def add_user(username):
    execute(INSERT_USER, parameters=(username,))


def delete_user(username):
    execute(DELETE_USER_WATCHED, parameters=(username,))
    execute(DELETE_USER_USERS, parameters=(username,))


def watch_movie(username, movie_id):
    execute(INSERT_WATCHED_MOVIE, parameters=(username, movie_id))


def get_watched_movies(username):
    cursor, connection = execute(
        SELECT_WATCHED_MOVIES, parameters=(username,), return_cursor=True
    )
    movies = cursor.fetchall()
    connection.close()
    return movies


def search_movies(search_term):
    cursor, connection = execute(
        SEARCH_MOVIE, (f"%{search_term}%",), return_cursor=True
    )
    result = cursor.fetchall()
    connection.close()
    return result


def unwatch_movie(username, movie_id):
    execute(UNWATCH_MOVIE, parameters=(username, movie_id))


def all_users():
    cursor, connection = execute(ALL_USERS, return_cursor=True)
    users = cursor.fetchall()
    connection.close()
    return users

app.ipynb

import database
import datetime


# FUNCTIONS

def prompt_add_movie():
    title = input("Movie title: ")
    release_date = input("Release date (dd-mm-YYYY): ")

    parsed_date = datetime.datetime.strptime(release_date, "%d-%m-%Y")
    timestamp = parsed_date.timestamp()

    database.add_movie(title, timestamp)



def prompt_watch_movie():
    username = input("Username: ")
    movie_id = int(input("Enter movie ID you've watched: "))
    database.watch_movie(username, movie_id)



def prompt_unwatch_movie():
    username = input("Username: ")
    movie_id = int(input("Enter movie ID that you want to remove from watched list: "))
    database.unwatch_movie(username, movie_id)



def print_movie_list(heading, movies):
    print(f"-- {heading} movies --")
    for movie in movies:
        movie_date = datetime.datetime.fromtimestamp(movie[2])
        human_date = movie_date.strftime("%d %b %Y")
        print(f"ID: {movie[0]} {movie[1]} ({human_date})")  
    print("---- \n")



def prompt_show_watched_movies():
    username = input("Username: ")
    movies = database.get_watched_movies(username)
    print_movie_list("Watched", movies)



def prompt_search_movies():
    search_term = input("Enter partial movie title: ")
    movies = database.search_movies(search_term)
    return movies



def prompt_add_user():
    username = input("Username: ")
    database.add_user(username)


def prompt_get_watched_movies():
    username = input("Username: ")
    return database.get_watched_movies(username)


def prompt_delete_user():
    username = input("Username: ")
    database.delete_user(username)


def prompt_all_users():
    print("---All users in database---")
    users = database.all_users()
    for user in users:
        print(user[0])
    print("---- \n")







menu = """Please select one of the following options:
0) Exit.
1) Add new movie.
2) View upcoming movies.
3) View all movies.
4) Add watched movie.
5) Remove a movie from watched list.
6) View watched movies.
7) Add new user.
8) Delete a user.
9) See all users.
10) Search for a movie.


Your selection: """
welcome = "Welcome to the watchlist app!"


print(welcome)
database.create_tables()

while (user_input := input(menu)) != "0":
    if user_input == "1":
        prompt_add_movie()
    elif user_input == "2":
        movies = database.get_movies(upcoming=True)
        print_movie_list("Upcoming", movies)
    elif user_input == "3":
        movies = database.get_movies(upcoming=False)
        print_movie_list("All", movies)
    elif user_input == "4":
        prompt_watch_movie()
    elif user_input == "5":
        prompt_unwatch_movie()
    elif user_input == "6":
        movies = prompt_get_watched_movies()
        if movies:
            print_movie_list("Watched", movies)
        else:
            print("That user has watched no movies yet!")
    elif user_input == "7":
        prompt_add_user()
    elif user_input == "8":
        prompt_delete_user()
    elif user_input == "9":
        prompt_all_users()
    elif user_input == "10":
        movies = prompt_search_movies()
        if movies:
            print_movie_list("Movies found", movies)
        else:
            print("Found no movies for that search term!")
    else:
        print("Invalid input, please try again!")