CourseCatalogBack.py

# Name: Ben Hung & Daniel Wong
# Final Project
# Module: CourseCatalogBackend.py

import requests
import json
import sqlite3
from bs4 import BeautifulSoup
import re

def getQuarters():
    '''Gets a valid list of quarters from De Anza's internal API. Selects only non-modifiable data, e.g., view only terms. Stores them in a json.'''

    courseData = requests.get("https://reg-prod.ec.fhda.edu/StudentRegistrationSsb/ssb/classSearch/getTerms?searchTerm=&offset=1&max=1000").json()
    quarters = {}
    for quarter in courseData:
        if "De Anza (View Only)" in quarter["description"]:
            quarters[quarter["code"]] = quarter["description"].split(" (View Only)")[0]

    # Write the dictionary of quarter ids with their respective names to JSON file.
    with open('quarters.json', 'w') as fh:
        json.dump(quarters, fh, indent=3)

def getCourseListings():
    '''Scrapes webpage full of all of the CIS classes taught at De Anza. Used for cross-checking data, as the internal API has some naming errors occasionally.
        After linking each course number to their respective title, saves the dictionary to a json file.'''

    page = requests.get("https://www.deanza.edu/cis/schedule.html")
    soup = BeautifulSoup(page.content, 'lxml')

    courseListings = {}
    courses = soup.find_all("tr")[1:]  # Skipping the first row which contains the header.

    for course in courses:
        columns = course.find_all("td")

        # Extract course number and course title
        courseNumber = columns[0].text.strip()
        courseTitle = columns[1].text.strip()
        if "*" in courseNumber:
            courseNumber = courseNumber[:-1]
        courseListings[courseNumber] = courseTitle
    
    # Hardcoding CIS 15AG & CIS66, as well as honors classes as they are not listed on the site webpage.
    courseListings["CIS 15AG"] = "Introduction to Computer Programming Using C (Has been replaced with CIS 22A)"
    courseListings["CIS 66"] = "Introduction to Data Communication and Networking (now known as CIS 6)"
    courseListings["CIS 22CH"] = "Data Abstraction and Structures - HONORS"
    courseListings["CIS 22BH"] = "Intermediate Programming Methodologies in C++ - HONORS"

    # Editing text for some discontinued classes (was listed on webpage, but had too long of a description in their titles)
    courseListings["CIS 15BG"] = "Intermediate Problem Solving in C (Has been replaced with CIS 22B)"
    courseListings["CIS 15C"] = "Data Structures (Has been replaced with CIS 22C)"

    # Writing the dictionary to a json file.
    with open('courseListings.json', 'w') as fh:
        json.dump(courseListings, fh, indent=3)

def getCourseData():
    '''Iterates through the quarters.json file, setting the cookie needed for the accessing of the internal API, then scraping all the CIS data for that specified quarter. Adds all data to a json file.'''

    courseData = []
    for quarter in quarters.keys():
        s = requests.Session()
        s.get(f"https://reg-prod.ec.fhda.edu/StudentRegistrationSsb/ssb/term/search?mode=search&term={quarter}")
        url = f"https://reg-prod.ec.fhda.edu/StudentRegistrationSsb/ssb/searchResults/searchResults?txt_subject=CIS&txt_term={quarter}&startDatepicker=&endDatepicker=&pageOffset=0&pageMaxSize=100&sortColumn=subjectDescription&sortDirection=asc"
        r = s.get(url)
        courseData.extend(r.json()["data"])

    # Write the CIS class data across the scraped quarters to a json file.
    with open('data.json', 'w') as fh:
        json.dump(courseData, fh, indent=3)

def convertTime(inputTime):
    '''Converting the start and end dates from the api for database entry (E.g., 2030 -> 8:30 PM).
        Takes in a string for time and converts it to easy to read strings.'''
    
    if inputTime is None or len(inputTime) != 4:
        return ""
    try:
        hour = int(inputTime[:2])
    except ValueError:
        return ""
    if hour < 0 or hour > 23:
        return ""
    minutes = inputTime[2:]
    if hour == 0:
        return f"12:{minutes} AM"
    elif hour == 12:
        return f"12:{minutes} PM"
    elif hour < 12:
        return f"{hour}:{minutes} AM"
    else:
        return f"{hour - 12}:{minutes} PM"

def createDB():
    '''Creates the needed database for the GUI, consisting of 7 total tables with 6 tables to prevent the duplication of data.
        Verifies that the course number read from the internal API data is valid using courseListings.json.'''

    # Getting the needed data.
    with open('data.json', 'r') as fh:
        courseData = json.load(fh)
    
    with open('courseListings.json', 'r') as fh:
        courseListings = json.load(fh)

    # Initializing tables.
    conn = sqlite3.connect('CourseData.db')
    cur = conn.cursor()
    cur.execute("DROP TABLE IF EXISTS CoursesDB")
    cur.execute('''CREATE TABLE CoursesDB(
                    courseNumId INTEGER NOT NULL,
                    profId INTEGER NOT NULL,
                    subjectId INTEGER NOT NULL,
                    titleId INTEGER NOT NULL,
                    roomId INTEGER NOT NULL,
                    termId INTEGER NOT NULL,
                    startTime TEXT,
                    endTime TEXT,
                    sunday INTEGER NOT NULL,
                    monday INTEGER NOT NULL,
                    tuesday INTEGER NOT NULL,
                    wednesday INTEGER NOT NULL,
                    thursday INTEGER NOT NULL,
                    friday INTEGER NOT NULL,
                    saturday INTEGER NOT NULL)''')
    cur.execute("DROP TABLE IF EXISTS ProfessorsDB")
    cur.execute('''CREATE TABLE ProfessorsDB(     
                id INTEGER PRIMARY KEY UNIQUE NOT NULL,
                name TEXT UNIQUE ON CONFLICT IGNORE NOT NULL )''')
    cur.execute("DROP TABLE IF EXISTS SubjectsDB")
    cur.execute('''CREATE TABLE SubjectsDB(     
                id INTEGER PRIMARY KEY UNIQUE NOT NULL,
                subject TEXT UNIQUE ON CONFLICT IGNORE NOT NULL )''')
    cur.execute("DROP TABLE IF EXISTS CoursesNumDB")
    cur.execute('''CREATE TABLE CoursesNumDB(     
                id INTEGER PRIMARY KEY UNIQUE NOT NULL,
                number TEXT UNIQUE ON CONFLICT IGNORE NOT NULL )''')
    cur.execute("DROP TABLE IF EXISTS CourseTitlesDB")
    cur.execute('''CREATE TABLE CourseTitlesDB(     
                id INTEGER PRIMARY KEY,
                title TEXT UNIQUE ON CONFLICT IGNORE NOT NULL )''')
    cur.execute("DROP TABLE IF EXISTS RoomsDB")
    cur.execute('''CREATE TABLE RoomsDB(     
                id INTEGER PRIMARY KEY,
                room TEXT UNIQUE ON CONFLICT IGNORE NOT NULL)''')
    cur.execute("DROP TABLE IF EXISTS QuartersDB")
    cur.execute('''CREATE TABLE QuartersDB(     
                id INTEGER NOT NULL,
                quarter TEXT UNIQUE ON CONFLICT IGNORE NOT NULL )''')

    # Insert operations.
    for course in courseData:

        # Setting room to be a default value if null.
        if course["meetingsFaculty"][0]["meetingTime"].get("room"):
            room = course["meetingsFaculty"][0]["meetingTime"]["room"]
        else:
            room = "N/A"
        
        # Redefining course titles, based on actual course offerings.
        subject = course["subject"]
        courseNum = course["courseNumber"]
        title = course["courseTitle"]
        courseNum = re.sub("D0*", "", courseNum)
        if "." in courseNum:
            courseNum = courseNum[:-1]

        # Hardcoding some very specific classes (due to internal api errors).
        if courseNum == "95":
            courseNum = "95D"
        elif courseNum == "64":
            courseNum = "64D"
        
        # Replacing the course numbers.
        newCourseTitle = courseListings.get(f"{subject} {courseNum}", None)
        if newCourseTitle:
            title = newCourseTitle
        else:
            title = title.title()
        
        # Linking tables.
        cur.execute('''INSERT INTO ProfessorsDB
                    (name)
                    VALUES
                    (?)''', (course["faculty"][0]["displayName"],))
        cur.execute("SELECT id FROM ProfessorsDB  WHERE name = ?", (course["faculty"][0]["displayName"],))
        profId = cur.fetchone()[0]
        cur.execute('''INSERT INTO SubjectsDB
                    (subject)
                    VALUES
                    (?)''', (course["subject"],))
        cur.execute("SELECT id FROM SubjectsDB  WHERE subject = ?", (course["subject"],))
        subjectId = cur.fetchone()[0]
        cur.execute('''INSERT INTO CoursesNumDB
                    (number)
                    VALUES
                    (?)''', (courseNum,))
        cur.execute("SELECT id FROM CoursesNumDB  WHERE number = ?", (courseNum,))
        courseNum = cur.fetchone()[0]
        cur.execute('''INSERT INTO CourseTitlesDB
                    (title)
                    VALUES
                    (?)''', (title,))
        cur.execute("SELECT id FROM CourseTitlesDB  WHERE title = ?", (title,))
        titleId = cur.fetchone()[0]

        cur.execute('''INSERT INTO RoomsDB
                    (room)
                    VALUES
                    (?)''', (room,))
        cur.execute("SELECT id FROM RoomsDB  WHERE room = ?", (room,))
        roomId = cur.fetchone()[0]

        cur.execute('''INSERT INTO QuartersDB
                    (id, quarter)
                    VALUES
                    (?, ?)''', (int(course["term"]), quarters[course["term"]]))
    
        # Inserting info into the main database.
        cur.execute('''INSERT INTO CoursesDB
                    VALUES
                    (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)''',
                    (courseNum, profId, subjectId, titleId, roomId, int(course["term"]), 
                     convertTime(course["meetingsFaculty"][0]["meetingTime"]["beginTime"]), 
                     convertTime(course["meetingsFaculty"][0]["meetingTime"]["endTime"]),
                     int(course["meetingsFaculty"][0]["meetingTime"]["sunday"]),
                     int(course["meetingsFaculty"][0]["meetingTime"]["monday"]),
                     int(course["meetingsFaculty"][0]["meetingTime"]["tuesday"]),
                     int(course["meetingsFaculty"][0]["meetingTime"]["wednesday"]),
                     int(course["meetingsFaculty"][0]["meetingTime"]["thursday"]),
                     int(course["meetingsFaculty"][0]["meetingTime"]["friday"]),
                     int(course["meetingsFaculty"][0]["meetingTime"]["saturday"])))

    conn.commit()
    conn.close()

# Calling functions required to setup database.
getQuarters()
with open('quarters.json', 'r') as fh:
        quarters = json.load(fh)
getCourseListings()
getCourseData()
createDB()