<filename>midterm-demo/backend/main.py
import cherrypy
import cherrypy_cors
import MySQLdb # pip3 install mysqlclient
from MySQLdb.cursors import DictCursor

admin_user = "me" or "cs411ppdb_admin"
db_name = "cs411ppdb_experimental" + "_local"

connection = MySQLdb.connect(
    cursorclass=DictCursor,
    user=admin_user,
    passwd="<PASSWORD>",
    db=db_name
)

class BackendApp():
    @cherrypy.expose
    def index(self):
        return "Midterm server is running"

    @cherrypy.expose
    @cherrypy.tools.json_out()
    def highgpaprofs(self, gpa=3.5, count=3, limit=50):
        query = """
        SELECT p.FirstName, p.LastName
        FROM Professor p JOIN TeachingCourse tc ON tc.ProfessorLastName=p.LastName AND tc.ProfessorFirstName=p.FirstName
        JOIN Section s ON tc.Crn=s.Crn JOIN Course c ON s.CourseNumber=c.Number AND s.CourseDepartment=c.Department
        WHERE c.averageGPA > %s
        GROUP BY p.FirstName, p.LastName
        HAVING COUNT(c.averageGPA) >= %s
        ORDER BY p.LastName, p.FirstName
        LIMIT %s
        """
        cur = connection.cursor()
        cur.execute(query, (float(gpa), int(count), int(limit),))
        results = cur.fetchall()
        return results

    @cherrypy.expose
    @cherrypy.tools.json_out()
    def coursecontext(self, dept, num, limit=50):
        query = """
        SELECT * FROM (SELECT
                pre.CourseNumber,
                pre.CourseDepartment,
                pre.RequiringCourseNumber,
                pre.RequiringCourseDepartment,
                pre.RequirementGroupId,
                req.ReqType
            FROM
                (PrereqCourses pre
                JOIN RequirementGroup req
                ON
                    pre.RequirementGroupId=req.Id
                    AND pre.RequiringCourseNumber=req.RequiringCourseNumber
                    AND pre.RequiringCourseDepartment=req.RequiringCourseDepartment) -- Get entire requirement table
                WHERE pre.CourseNumber=%s
                AND pre.CourseDepartment=%s) AS unlockables
        UNION
            (SELECT
                pre.CourseNumber,
                pre.CourseDepartment,
                pre.RequiringCourseNumber,
                pre.RequiringCourseDepartment,
                pre.RequirementGroupId,
                req.ReqType
            FROM
                (PrereqCourses pre
                JOIN RequirementGroup req
                ON
                    pre.RequirementGroupId=req.Id
                    AND pre.RequiringCourseNumber=req.RequiringCourseNumber
                    AND pre.RequiringCourseDepartment=req.RequiringCourseDepartment) -- Get entire requirement table
                WHERE req.RequiringCourseNumber=%s
                AND req.RequiringCourseDepartment=%s)
        LIMIT %s;
        """
        cur = connection.cursor()
        cur.execute(query, (num, dept, num, dept, int(limit)))
        results = cur.fetchall()
        return results

    @cherrypy.expose
    @cherrypy.tools.json_out()
    def makecourse(self, dept, num, title, avgGpa=None):
        query = """
        INSERT IGNORE INTO Course
        (`Title`,
        `Number`,
        `Department`,
        `averageGPA`)
        VALUES
        (%s, %s, %s, %s);
        """
        cur = connection.cursor()
        cur.execute(query, (title, num, dept, avgGpa))
        insert_count = cur.rowcount
        connection.commit()
        return [{ 'insertedRows': insert_count }]

    @cherrypy.expose
    @cherrypy.tools.json_out()
    def profrating(self, firstname, lastname, rating=None):
        query = """
        UPDATE Professor
        SET
        Rating = %s
        WHERE FirstName = %s AND LastName = %s;
        """
        cur = connection.cursor()
        cur.execute(query, (rating, firstname, lastname))
        impact_count = cur.rowcount
        connection.commit()
        return [{ 'updatedRows': impact_count }]

    @cherrypy.expose
    @cherrypy.tools.json_out()
    def delrestriction(self, crn, detail):
        query = """
        DELETE FROM Restriction rst
        WHERE rst.Crn = %s AND rst.Detail = %s
        """
        cur = connection.cursor()
        cur.execute(query, (crn, detail))
        delete_count = cur.rowcount
        connection.commit()
        return [{ 'deletedRows': delete_count }]


    @cherrypy.expose
    @cherrypy.tools.json_out() # Automatically respond with json
    def courses(self, keyword, limit=10):
        # you can hit this endpoint at /courses?keyword=whatever
        query = """
        SELECT * FROM Course c
        WHERE LOWER(c.Title) LIKE %s
        ORDER BY c.Title
        LIMIT %s
        """
        cur = connection.cursor()
        cur.execute(query, ("%{}%".format(keyword.lower()), int(limit)))
        results = cur.fetchall()
        return results
    
    @cherrypy.expose
    @cherrypy.tools.json_out()
    def findprof(self, lastname):
        query = """
        SELECT * FROM Professor p
        WHERE p.lastname LIKE %s
        ORDER BY p.lastname, p.firstname
        """
        cur = connection.cursor()
        cur.execute(query, ("%{}%".format(lastname),))
        results = cur.fetchall()
        return results

    @cherrypy.expose
    @cherrypy.tools.json_out()
    def findrestrict(self, detail):
        query = """
        SELECT * FROM Restriction rst
        WHERE rst.Detail LIKE %s
        ORDER BY rst.Crn
        """
        cur = connection.cursor()
        cur.execute(query, ("%{}%".format(detail),))
        results = cur.fetchall()
        return results

cherrypy_cors.install()

config = {
    '/': {
        'cors.expose.on': True
    }
}

cherrypy.quickstart(BackendApp(), config=config)
<filename>midterm-demo/backend/main.py
import cherrypy
import cherrypy_cors
import MySQLdb # pip3 install mysqlclient
from MySQLdb.cursors import DictCursor

admin_user = "me" or "cs411ppdb_admin"
db_name = "cs411ppdb_experimental" + "_local"

connection = MySQLdb.connect(
    cursorclass=DictCursor,
    user=admin_user,
    passwd="<PASSWORD>",
    db=db_name
)

class BackendApp():
    @cherrypy.expose
    def index(self):
        return "Midterm server is running"

    @cherrypy.expose
    @cherrypy.tools.json_out()
    def highgpaprofs(self, gpa=3.5, count=3, limit=50):
        query = """
        SELECT p.FirstName, p.LastName
        FROM Professor p JOIN TeachingCourse tc ON tc.ProfessorLastName=p.LastName AND tc.ProfessorFirstName=p.FirstName
        JOIN Section s ON tc.Crn=s.Crn JOIN Course c ON s.CourseNumber=c.Number AND s.CourseDepartment=c.Department
        WHERE c.averageGPA > %s
        GROUP BY p.FirstName, p.LastName
        HAVING COUNT(c.averageGPA) >= %s
        ORDER BY p.LastName, p.FirstName
        LIMIT %s
        """
        cur = connection.cursor()
        cur.execute(query, (float(gpa), int(count), int(limit),))
        results = cur.fetchall()
        return results

    @cherrypy.expose
    @cherrypy.tools.json_out()
    def coursecontext(self, dept, num, limit=50):
        query = """
        SELECT * FROM (SELECT
                pre.CourseNumber,
                pre.CourseDepartment,
                pre.RequiringCourseNumber,
                pre.RequiringCourseDepartment,
                pre.RequirementGroupId,
                req.ReqType
            FROM
                (PrereqCourses pre
                JOIN RequirementGroup req
                ON
                    pre.RequirementGroupId=req.Id
                    AND pre.RequiringCourseNumber=req.RequiringCourseNumber
                    AND pre.RequiringCourseDepartment=req.RequiringCourseDepartment) -- Get entire requirement table
                WHERE pre.CourseNumber=%s
                AND pre.CourseDepartment=%s) AS unlockables
        UNION
            (SELECT
                pre.CourseNumber,
                pre.CourseDepartment,
                pre.RequiringCourseNumber,
                pre.RequiringCourseDepartment,
                pre.RequirementGroupId,
                req.ReqType
            FROM
                (PrereqCourses pre
                JOIN RequirementGroup req
                ON
                    pre.RequirementGroupId=req.Id
                    AND pre.RequiringCourseNumber=req.RequiringCourseNumber
                    AND pre.RequiringCourseDepartment=req.RequiringCourseDepartment) -- Get entire requirement table
                WHERE req.RequiringCourseNumber=%s
                AND req.RequiringCourseDepartment=%s)
        LIMIT %s;
        """
        cur = connection.cursor()
        cur.execute(query, (num, dept, num, dept, int(limit)))
        results = cur.fetchall()
        return results

    @cherrypy.expose
    @cherrypy.tools.json_out()
    def makecourse(self, dept, num, title, avgGpa=None):
        query = """
        INSERT IGNORE INTO Course
        (`Title`,
        `Number`,
        `Department`,
        `averageGPA`)
        VALUES
        (%s, %s, %s, %s);
        """
        cur = connection.cursor()
        cur.execute(query, (title, num, dept, avgGpa))
        insert_count = cur.rowcount
        connection.commit()
        return [{ 'insertedRows': insert_count }]

    @cherrypy.expose
    @cherrypy.tools.json_out()
    def profrating(self, firstname, lastname, rating=None):
        query = """
        UPDATE Professor
        SET
        Rating = %s
        WHERE FirstName = %s AND LastName = %s;
        """
        cur = connection.cursor()
        cur.execute(query, (rating, firstname, lastname))
        impact_count = cur.rowcount
        connection.commit()
        return [{ 'updatedRows': impact_count }]

    @cherrypy.expose
    @cherrypy.tools.json_out()
    def delrestriction(self, crn, detail):
        query = """
        DELETE FROM Restriction rst
        WHERE rst.Crn = %s AND rst.Detail = %s
        """
        cur = connection.cursor()
        cur.execute(query, (crn, detail))
        delete_count = cur.rowcount
        connection.commit()
        return [{ 'deletedRows': delete_count }]


    @cherrypy.expose
    @cherrypy.tools.json_out() # Automatically respond with json
    def courses(self, keyword, limit=10):
        # you can hit this endpoint at /courses?keyword=whatever
        query = """
        SELECT * FROM Course c
        WHERE LOWER(c.Title) LIKE %s
        ORDER BY c.Title
        LIMIT %s
        """
        cur = connection.cursor()
        cur.execute(query, ("%{}%".format(keyword.lower()), int(limit)))
        results = cur.fetchall()
        return results
    
    @cherrypy.expose
    @cherrypy.tools.json_out()
    def findprof(self, lastname):
        query = """
        SELECT * FROM Professor p
        WHERE p.lastname LIKE %s
        ORDER BY p.lastname, p.firstname
        """
        cur = connection.cursor()
        cur.execute(query, ("%{}%".format(lastname),))
        results = cur.fetchall()
        return results

    @cherrypy.expose
    @cherrypy.tools.json_out()
    def findrestrict(self, detail):
        query = """
        SELECT * FROM Restriction rst
        WHERE rst.Detail LIKE %s
        ORDER BY rst.Crn
        """
        cur = connection.cursor()
        cur.execute(query, ("%{}%".format(detail),))
        results = cur.fetchall()
        return results

cherrypy_cors.install()

config = {
    '/': {
        'cors.expose.on': True
    }
}

cherrypy.quickstart(BackendApp(), config=config)
# pip3 install mysqlclient SELECT p.FirstName, p.LastName         FROM Professor p JOIN TeachingCourse tc ON tc.ProfessorLastName=p.LastName AND tc.ProfessorFirstName=p.FirstName         JOIN Section s ON tc.Crn=s.Crn JOIN Course c ON s.CourseNumber=c.Number AND s.CourseDepartment=c.Department         WHERE c.averageGPA > %s         GROUP BY p.FirstName, p.LastName         HAVING COUNT(c.averageGPA) >= %s         ORDER BY p.LastName, p.FirstName         LIMIT %s SELECT * FROM (SELECT                 pre.CourseNumber,                 pre.CourseDepartment,                 pre.RequiringCourseNumber,                 pre.RequiringCourseDepartment,                 pre.RequirementGroupId,                 req.ReqType             FROM                 (PrereqCourses pre                 JOIN RequirementGroup req                 ON                     pre.RequirementGroupId=req.Id                     AND pre.RequiringCourseNumber=req.RequiringCourseNumber                     AND pre.RequiringCourseDepartment=req.RequiringCourseDepartment) -- Get entire requirement table                 WHERE pre.CourseNumber=%s                 AND pre.CourseDepartment=%s) AS unlockables         UNION             (SELECT                 pre.CourseNumber,                 pre.CourseDepartment,                 pre.RequiringCourseNumber,                 pre.RequiringCourseDepartment,                 pre.RequirementGroupId,                 req.ReqType             FROM                 (PrereqCourses pre                 JOIN RequirementGroup req                 ON                     pre.RequirementGroupId=req.Id                     AND pre.RequiringCourseNumber=req.RequiringCourseNumber                     AND pre.RequiringCourseDepartment=req.RequiringCourseDepartment) -- Get entire requirement table                 WHERE req.RequiringCourseNumber=%s                 AND req.RequiringCourseDepartment=%s)         LIMIT %s; INSERT IGNORE INTO Course         (`Title`,         `Number`,         `Department`,         `averageGPA`)         VALUES         (%s, %s, %s, %s); UPDATE Professor         SET         Rating = %s         WHERE FirstName = %s AND LastName = %s; DELETE FROM Restriction rst         WHERE rst.Crn = %s AND rst.Detail = %s # Automatically respond with json # you can hit this endpoint at /courses?keyword=whatever SELECT * FROM Course c         WHERE LOWER(c.Title) LIKE %s         ORDER BY c.Title         LIMIT %s SELECT * FROM Professor p         WHERE p.lastname LIKE %s         ORDER BY p.lastname, p.firstname SELECT * FROM Restriction rst         WHERE rst.Detail LIKE %s         ORDER BY rst.Crn