init_db.py

import os

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from dotenv import load_dotenv

from helpers import custom_logger

from models.Based import Base
from models.User import User
from models.Course import Course
from models.Comment import Comment


def initialise_db(engine=None):
    logger = custom_logger('db_initialisation', 'db_init.log')

    logger.info("Initialising database")

    load_dotenv()

    if engine is None:
        database_uri = os.getenv('SQLALCHEMY_DATABASE_URI')
        engine = create_engine(database_uri, logging_name="db_engine")

    metadata = Base.metadata

    logger.info("Dropping all tables")
    metadata.drop_all(engine)
    logger.info("Creating all tables")
    metadata.create_all(engine)

    Session = sessionmaker(bind=engine)
    with Session() as session:
        logger.info("Starting database query session")

        try:
            courses = [Course(code="C1", name="Course 1", description="This is course 1. It is run by educator 1."),
                       Course(code="C2", name="Course 2", description="This is course 2."),
                       Course(code="C3", name="Course 3", description="This is course 3. This is run by educator 2.")]
            session.add_all(courses)
            session.commit()

            # note: these are only for testing purposes - actual users will be created through the registration form with secure passwords
            studentUser = User(email="student@website.com")
            studentUser2 = User(email="student2@website.com", password="password")
            adminUser = User(email="admin@website.com", password="password", role="admin")
            educatorUser = User(email="educator@website.com", password="password", role="educator")
            educatorUser2 = User(email="educator2@website.com", role="educator")
            expiredUser = User(email="graduate@website.com", password="password", role="expired")

            users = [studentUser, studentUser2, adminUser, educatorUser, educatorUser2, expiredUser]

            for user in users:
                user.set_password("password")

            studentUser.enrolled_courses.append(session.query(Course).filter_by(code="C1").first())
            studentUser.enrolled_courses.append(session.query(Course).filter_by(code="C2").first())
            studentUser.enrolled_courses.append(session.query(Course).filter_by(code="C3").first())

            studentUser2.enrolled_courses.append(session.query(Course).filter_by(code="C2").first())
            studentUser2.enrolled_courses.append(session.query(Course).filter_by(code="C3").first())

            educatorUser.teaching_courses.append(session.query(Course).filter_by(code="C1").first())
            educatorUser.teaching_courses.append(session.query(Course).filter_by(code="C2").first())

            educatorUser2.teaching_courses.append(session.query(Course).filter_by(code="C3").first())

            expiredUser.enrolled_courses.append(session.query(Course).filter_by(code="C1").first())

            session.add_all(users)
            session.commit()

            comments = [Comment(user_id=studentUser.id, course_id=courses[0].id, user=studentUser, course=courses[0], text="Student 1 comment on course 1"),
                        Comment(user_id=studentUser.id, course_id=courses[1].id, user=studentUser, course=courses[1], text="Student 1 comment on course 2"),
                        Comment(user_id=studentUser.id, course_id=courses[2].id, user=studentUser, course=courses[2], text="Student 1 comment on course 3"),
                        Comment(user_id=studentUser2.id, course_id=courses[1].id, user=studentUser2, course=courses[1], text="Student 2 comment on course 2"),
                        Comment(user_id=studentUser2.id, course_id=courses[2].id, user=studentUser2, course=courses[2], text="Student 2 comment on course 3"),
                        Comment(user_id=educatorUser.id, course_id=courses[0].id, user=educatorUser, course=courses[0], text="Educator 1 comment on course 1")]
            for comment in comments:
                comment.add_to_db(session)

            logger.info("Database initialised")
            return True
        except Exception as e:
            logger.error(f'Error occurred: {e.args[0]}')
            logger.error("An error occurred while initialising the database")
            return False
        finally:
            logger.info("Closing database query session")
            session.close()


if __name__ == '__main__':
    initialise_db()  # note that this will place the db in the directory of the project rather than in the instance folder
    print("Database initialised Successfully")