make_db.py

import sqlite3
import os  # for absolute paths - https://stackoverflow.com/a/51523


def make_book_db():
    """
    Function to create the book information database if it does not exist already

    :return: Nothing
    """
    conn = sqlite3.connect('db/BOOK_DB.sqlite3')
    cur = conn.cursor()
    book_path = os.path.abspath("db/books.sql")  # absolute path https://stackoverflow.com/a/51523
    with open(book_path) as books_db_script:  # execute sql script: https://stackoverflow.com/a/32372796
        cur.executescript(books_db_script.read())

    cur.close()
    conn.commit()
    conn.close()


def make_user_db():
    """
    Function to create the user information database if it does not exist already

    :return: Nothing
    """

    conn = sqlite3.connect('db/USER_DB.sqlite3')
    cur = conn.cursor()
    user_path = os.path.abspath("db/users.sql")  # absolute path https://stackoverflow.com/a/51523
    with open(user_path) as users_db_script:  # execute sql script: https://stackoverflow.com/a/32372796
        cur.executescript(users_db_script.read())

    cur.close()
    conn.commit()
    conn.close()


def init_db():
    """
    Creates all databases

    :return: Nothing
    """
    make_book_db()
    make_user_db()
    add_default_users()


def add_default_users():
    """
    Add the default users for testing purposes

    :return: Nothing
    """
    conn = sqlite3.connect("db/USER_DB.sqlite3")
    cur = conn.cursor()

    user_insert_path = os.path.abspath("db/insert_users.sql")  # absolute path https://stackoverflow.com/a/51523
    try:
        with open(user_insert_path) as users_insert_script:  # execute sql script: https://stackoverflow.com/a/32372796
            cur.executescript(users_insert_script.read())
    except sqlite3.Error:
        pass
        # If the default users are already in the system because of a server restart
        # just skip inserting the base users as otherwise this will cause a unique constraint to fail

    cur.close()
    conn.commit()
    conn.close()