db.py

import os
import dotenv

dotenv.load_dotenv(dotenv.find_dotenv())

mysql_credentials = ["MYSQL_USER", "MYSQL_PASSWORD", "MYSQL_HOST"]

def init_db() -> tuple[any, any]:

    mysql_valid = True
    for credential in mysql_credentials:
        try: 
            os.environ[credential]
        except KeyError:
            print(f'Missing {credential} in .env file')
            mysql_valid=False

    if mysql_valid:
        try:
            import mysql.connector

            print("Using provided MYSQL database")

            user = os.environ["MYSQL_USER"]
            password = os.environ["MYSQL_PASSWORD"]
            hostname = os.environ["MYSQL_HOST"]

            connection = mysql.connector.connect(user=user, password=password, host=hostname)

            cursor = connection.cursor()

            cursor.execute("CREATE DATABASE IF NOT EXISTS moviesdb;")
            connection.commit()

            cursor.execute("USE moviesdb;")
            connection.commit()

            cursor.execute("""
            CREATE TABLE IF NOT EXISTS movie (
                id INTEGER primary key AUTO_INCREMENT,
                name VARCHAR(500) NOT NULL,
                description VARCHAR(5000) NOT NULL
            );
            """)
            connection.commit()
        except Exception:
            raise Exception

    else:
        print("Using sql file as backend")
        import sqlite3
        connection = sqlite3.connect("movies.db", check_same_thread=False)

        cursor = connection.cursor()

        cursor.execute("""
        CREATE TABLE IF NOT EXISTS movie (
            id INTEGER primary key AUTOINCREMENT,
            name VARCHAR(500) NOT NULL,
            description VARCHAR(5000) NOT NULL
        );
        """)
        connection.commit()

    cursor.close()
    return connection