One Pyton Test Project

 

main.py

# coding=utf-8
from wsgiref.simple_server import make_server
from urlparse import parse_qs
import json

import router

SERVER_PORT = 8080
API_TOKEN = 'z8675309q'

# Starts the server
def start_server():

    server = make_server('', SERVER_PORT, app)

    print "serving at port", SERVER_PORT
    server.serve_forever()


# Basic app hook
def app(environ, start_response):

    request_path = environ['PATH_INFO']
    query_params = parse_qs(environ['QUERY_STRING'])

    request_bodysize = int(environ['CONTENT_LENGTH'])
    request_body = environ['wsgi.input'].read(request_bodysize)
    json_body = json.loads(request_body)

    token = json_body.get('token')
    if token is not None and token != API_TOKEN:
        start_response('403 FORBIDDEN', [('Content-type', 'application/json')])
        return json.dumps({"ok": False, "error": "invalid_token"})

    # route the request
    start_response('200 OK', [('Content-type', 'application/json')])
    return json.dumps(router.HandleRouting(request_path, query_params, json_body))

start_server()

 

router.py

# coding=utf-8

import storage

def HandleRouting(path, query_parameters, body):

    # Divert based on path
    if path == '/api/course.create':
        return storage.create_course(body['name'], body['credits'], body['type'])
    if path == '/api/course.addOffering':
        return storage.create_course_offering(body['course_id'], body['instructor_id'], body['year'], body['term'], body['days'], body['time'], body['capacity'])
    if path == '/api/course.getOfferings':
        return storage.get_course_offerings(body['course_id'])
    if path == '/api/course.enroll':
        return storage.create_enrollment(body['course_offering_id'], body['student_id'])
    if path == '/api/student.add':
        return storage.addStudent(body['name'])
    if path == '/api/instructor.add':
        return storage.add_instructor(body['name'])

    # can't find an associated endpoint
    return {
        "ok": False,
        "error": "unknown_method"
    }

 storage.py

# coding=utf-8
import mysql.connector

# Creates a course in the database
def create_course(name, credits, type):

    connection = mysql.connector.connect(user='root', password='super-secret-password', host='localhost', database='registrar')
    cursor = connection.cursor()

    cursor.execute('INSERT INTO COURSES (name, credits, type) VALUES("' + name + '",' + str(credits) + ',"' + type + '")')
    course_id = cursor.lastrowid

    connection.commit()
    connection.close()

    return {
        "ok": True,
        "course_id": course_id,
    }


def addStudent(name):
    """Adds a new student by first name and last name. The combination must be unique."""

    connection = mysql.connector.connect(user='root', password='super-secret-password', host='localhost', database='registrar')
    cursor = connection.cursor()

    cursor.execute('INSERT INTO students (name) VALUES (%(name)s)', {"name": name})
    student_id = cursor.lastrowid

    connection.commit()
    connection.close()

    return {
        "ok": True,
        "student_id": student_id,
    }


# Adds a new instructor to the database
def add_instructor(name):

    connection = mysql.connector.connect(user='root', password='super-secret-password', host='localhost', database='registrar')
    cursor = connection.cursor()

    cursor.execute('INSERT INTO instructors (name) VALUES (%(name)s)', {"name": name})
    student_id = cursor.lastrowid

    connection.commit()
    connection.close()

    return {
        "ok": True,
        "student_id": student_id,
    }


# Adds a new course offering
def create_course_offering(course_id, instructor_id, year, term, days, time, capacity):

    # Validate days is in the allowed set
    allowed_days = ['M', 'W', 'F', 'MW', 'WF', 'MWF', 'T', 'Tr', 'TTr']
    found = False
    for allowed_days_option in allowed_days:
        if allowed_days_option == days:
            found = True
            break

    if not found:
        return {
            "ok": False,
            "error": "invalid_days",
        }

    # Create the course offering
    connection = mysql.connector.connect(user='root', password='super-secret-password', host='localhost', database='registrar')
    cursor = connection.cursor()

    cursor.execute('INSERT INTO course_offerings (course_id, instructor_id, year, term, days, time, capacity) VALUES (%s, %s, %s, %s, %s, %s, %s)', (course_id, instructor_id, year, term, days, time, capacity))
    course_offering_id = cursor.lastrowid

    connection.commit()
    connection.close()

    return {
        "ok": True,
        "course_offering_id": course_offering_id,
    }


def get_course_offerings(course_id):
    """Gets course offering information for the specified course id"""

    connection = mysql.connector.connect(user='root', password='super-secret-password', host='localhost', database='registrar')
    c1 = connection.cursor(buffered=True)
    c1.execute('SELECT id, instructor_id, year, term, days, time, capacity FROM course_offerings WHERE course_id=%(course_id)s', {"course_id": course_id})

    # Enumerate the retrieved course offerings
    offerings = []
    for (id, instructor_id, year, term, days, time, capacity) in c1:

        # Get instructor name
        c2 = connection.cursor()
        c2.execute('SELECT name FROM instructors WHERE id=%(id)s', {"id": instructor_id})
        row = c2.fetchone()

        if row is not None:
            instructor_name = row[0]

        # Determine remaining capacity
        c3 = connection.cursor()
        c3.execute('select count(*) as count from enrollments where course_offering_id=%(id)s', {"id": id})
        row = c3.fetchone()
        count = row[0]
        remaining_capacity = capacity - count

        offerings.append({
            'course_offering_id': id,
            'instructor': instructor_name,
            'year': year,
            'term': term,
            'days': days,
            'time': time,
            'remaining_capacity': remaining_capacity
        })

    connection.close()
    
    # Return results
    return {
        "ok": True,
        "course_offerings": offerings,
    }


def create_enrollment(course_offering_id, student_id):
    """Enrolls a student to a specified course offering"""
    connection = mysql.connector.connect(user='root', password='super-secret-password', host='localhost', database='registrar')

    # First check if there's space in the course offering
    c1 = connection.cursor()
    c1.execute('SELECT capacity FROM course_offerings WHERE id=%(id)s', {"id": course_offering_id})
    capacity = c1.fetchone()[0]

    c2 = connection.cursor()
    c2.execute('SELECT COUNT(*) FROM enrollments WHERE course_offering_id=%(id)s', {"id": course_offering_id})
    count = c2.fetchone()[0]
    
    if count >= capacity:
        return {
            "ok": False,
            "error": "capacity_reached",
        }

    # Enroll the student
    c3 = connection.cursor()
    c3.execute('INSERT INTO enrollments (course_offering_id, student_id) VALUES (%s, %s)', (course_offering_id, student_id))

    connection.commit()
    connection.close()

    return {
        "ok": True,
    }

 

 

 

 

schema.sql

 

DROP DATABASE registrar;
CREATE DATABASE registrar;
USE registrar;

CREATE TABLE `COURSES` (
    `id` int NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    `credits` int(11) NOT NULL,
    `type` enum('course','lab','combined') NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `name_type_unique` (`name`, `type`),
    KEY `type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `course_offerings` (
    `id` int NOT NULL AUTO_INCREMENT,
    `course_id` int NOT NULL,
    `instructor_id` int NOT NULL,
    `year` tinyint NOT NULL,
    `term` enum('summer','spring','fall') NOT NULL,
    `days` varchar(100) NOT NULL,
    `time` varchar(100) NOT NULL,
    `capacity` tinyint NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `students` (
    `id` tinyint NOT NULL AUTO_INCREMENT,
    `name` varchar(100) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `id_name_unique` (`id`, `name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `instructors` (
    `id` int NOT NULL AUTO_INCREMENT,
    `name` varchar(100) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `enrollments` (
    `id` int NOT NULL AUTO_INCREMENT,
    `course_offering_id` int(11) NOT NULL,
    `student_id` int(11) NOT NULL,
    `student_name` varchar(100) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `id_student_id_unique` (`id`, `student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

 

 

Course Enrollment System (Registrar)

Prompt

You are on a 2-person developer team tasked with writing a rudimentary course enrollment system for a small college. Your solution includes a backend written in Python and uses a MySQL database for permanent data storage.

You have been tasked with reviewing your fellow engineer's latest pull request! Please review the submitted changes for correctness and best practices, all the while ensuring that it fulfills the project requirements listed below. If you’re not a Python expert, no worries; we won’t be looking for comments specific to the sample’s programming language.

This is a lengthy pull request with a wide range of mistakes throughout; We do not require you to enumerate all of the mistakes in order to receive a passing grade, but ensure that you've provided feedback on a few different types of problems. We recommend reading the code completely at least once before beginning to provide feedback. Please spend no more than about an hour reviewing the pull request.

For inspiration, read our engineering blog post about code review best practices.

System Requirements

• Add courses and course offerings
• Link instructors to course offerings
• Add students
• Enroll students in course offerings
  • Course offerings have limited capacity
  • Students also have limited capacity - no single student can enroll in more than five course offerings per semester

API Usage

The following examples demonstrate how to use the enrollment system's API.

curl localhost:8080/api/student.add -d '{"name": "Yoshi", "token": "z8675309q"}'

curl localhost:8080/api/course.create -d '{"name": "COMP200", "credits": 3, "type": "lab", "token": "z8675309q"}'

curl localhost:8080/api/instructor.add -d '{"name": "Dr. Mario", "token": "z8675309q"}'

curl localhost:8080/api/course.addOffering -d '{"course_id": 1, "instructor_id": 2, "year": 2018, "term": "Spring", "days": "MWF", "time": "9:00", "capacity": 10, "token": "z8675309q"}'

curl localhost:8080/api/course.getOfferings -d '{"course_id": "1", "token": "z8675309q"}'

curl localhost:8080/api/course.enroll -d '{"course_offering_id": "1", "student_id": 1, "token": "z8675309q"}'