def find_course(name: str, num: str, uni: str) -> Row: name = name.strip() num = num.strip() uni = uni.strip() result = con.execute('''SELECT * FROM course WHERE universityName = ? AND courseTitle = ? AND courseNumber = ?''', (uni, name, num)) \ .fetchone() if result is not None: return result result = con.execute('''SELECT * FROM course WHERE universityName = ? AND courseNumber = ?''', (uni, num)) \ .fetchone() if result is not None: return result result = con.execute('''SELECT * FROM course WHERE universityName = ? AND courseTitle = ?''', (uni, name)) \ .fetchone() return result # may be None, but that's ok if none of the cases match
def update_experience(userID: int, posID: int, **kwargs) -> bool: new_industry = kwargs.get('industry') new_salary = kwargs.get('salary') new_type = kwargs.get('type') new_rating = kwargs.get('rating') n = 0 if new_industry: if isinstance(new_industry, Industry): new_industry = new_industry.value n += con.execute('''UPDATE experience SET industry = :industry WHERE userID = :user AND positionID = :pos ''', { 'industry': new_industry, 'user': userID, 'pos': posID, }) \ .rowcount if new_salary: n += con.execute('''UPDATE experience SET salary = :salary WHERE userID = :user AND positionID = :pos ''', { 'salary': new_salary, 'user': userID, 'pos': posID, }) \ .rowcount if new_type: if isinstance(new_type, JobType): new_type = new_type.value n += con.execute('''UPDATE experience SET type = :type WHERE userID = :user AND positionID = :pos ''', { 'type': new_type, 'user': userID, 'pos': posID, }) \ .rowcount if new_rating: n += con.execute('''UPDATE experience SET rating = :rating WHERE userID = :user AND positionID = :pos ''', { 'rating': new_rating, 'user': userID, 'pos': posID, }) \ .rowcount return n > 0
def update_education(id: int, **kwargs) -> bool: new_year = kwargs.get('year') new_degree = kwargs.get('degree') new_major = kwargs.get('major') new_gpa = kwargs.get('gpa') n = 0 if new_year is not None: n += con.execute('''UPDATE education SET year = :year WHERE id = :id ''', { 'year': new_year, 'id': id }) \ .rowcount if new_degree is not None: if isinstance(new_degree, DegreeType): new_degree = new_degree.value n += con.execute('''UPDATE education SET degree = :degree WHERE id = :id ''', { 'degree': new_degree, 'id': id }) \ .rowcount if new_major is not None: new_major = new_major.strip().title() n += con.execute('''UPDATE education SET major = :major WHERE id = :id ''', { 'major': new_major, 'id': id }) \ .rowcount if new_gpa is not None: n += con.execute('''UPDATE education SET gpa = :gpa WHERE id = :id ''', { 'gpa': new_gpa, 'id': id, }) \ .rowcount return n > 0
def create_experience( userID: int, posID: int, industry: Industry = None, salary: int = None, type: JobType = None, rating: int = None, ): con.execute('INSERT INTO experience (userID, positionID) VALUES (?, ?)', (userID, posID)) update_experience(userID, posID, industry=industry, salary=salary, type=type, rating=rating)
def get_courses_for_edu(eduID: int) -> List[Row]: return con.execute('''SELECT * FROM course JOIN enrollment WHERE course.id = enrollment.courseID AND enrollment.educationID = ? ORDER BY courseNumber, courseTitle ''', (eduID,)) \ .fetchall()
def check_enrollment_all(userID: int, courseID: int) -> bool: r = con.execute( '''SELECT * FROM enrollment e JOIN education edu ON e.educationID = edu.id WHERE userID = ? AND courseID = ?''', (userID, courseID)).fetchall() return len(r) > 0
def find_education(userID: int, uni: str, year: int) -> Row: uni = uni.strip() return con.execute('''SELECT * FROM education WHERE userID = ? AND university = ? AND year = ?''', (userID, uni, year)) \ .fetchone()
def update_user(userID: int, new_skills: List[str]) -> bool: if new_skills is not None: new_skills = ','.join(new_skills) n = con.execute('UPDATE user SET skills = :skills WHERE id = :id', { "id": userID, "skills": new_skills }).rowcount return n > 0
def find_position(employer: str, title: str) -> Row: employer = employer.strip() title = title.strip() return con.execute('''SELECT * FROM position WHERE employerName = ? AND jobTitle = ?''', (employer, title)) \ .fetchone()
def create_position(employer: str, title: str) -> int: title = title.strip() emp = read_employer(employer) if emp is None: emp = create_employer(employer) return con.execute('''INSERT INTO position (employerName, jobTitle) VALUES (?, ?)''', (emp['name'], title)) \ .lastrowid
def update_course(id: int, name: str, num: str) -> bool: """Change the name/number of a course and update all references to it.""" n = con.execute( '''UPDATE course SET courseTitle = :name, courseNumber = :num WHERE id = :id ''', { 'id': id, 'name': name, 'num': num }).rowcount return n > 0
def create_course(name: str, num: str, uni: str) -> int: # standardize course name and number before inserting name = name.strip() num = num.strip().upper() university = read_university(uni) if university is None: create_university(uni) return con.execute('''INSERT INTO course (universityName, courseTitle, courseNumber) VALUES (?, ?, ?)''', (uni, name, num)) \ .lastrowid
def get_popular_companies(school: str, limit: int): rows = con.execute('''SELECT employerName, COUNT(userID) as cnt FROM position p JOIN experience ex ON p.id = ex.positionID NATURAL JOIN education WHERE university = ? GROUP BY employerName ORDER BY cnt DESC LIMIT ? ''', (school, limit)) \ .fetchall() results = list(map(lambda x: x['employerName'], rows)) return {'status': 'OK', 'results': results}
def update_position(id: int, **kwargs) -> bool: new_employer = kwargs.get('employer') new_title = kwargs.get('title') if new_employer: new_employer = 'employerName = ' + new_employer if new_title: new_title = 'title = ' + new_title set_clause = ', '.join((new_employer, new_title)) if set_clause == '': return False n = con.execute('''UPDATE position SET ? WHERE id = ?''', (set_clause, id)) \ .rowcount return n > 0
def create_education( userID: int, uni: str, year: int, degree: DegreeType = None, major: str = None, gpa: float = None, ) -> int: university = read_university(uni) if university is None: university = create_university(uni) id = con.execute('''INSERT INTO education (userID, university, year) VALUES (?, ?, ?)''', (userID, uni, year)) \ .lastrowid update_education(id, degree=degree, major=major, gpa=gpa) return id
def create_university(name: str) -> Row: name = name.strip() con.execute('INSERT INTO university (name) VALUES (?)', (name, )) return read_university(name)
def read_course(id: int) -> Row: return con.execute('SELECT * FROM course WHERE id = ?', (id,)) \ .fetchone()
def create_user(skills: List[str] = None) -> int: if skills is not None: skills = ','.join(skills) return con.execute('INSERT INTO user (skills) VALUES (?)', (skills,)) \ .lastrowid
def delete_university(name: str) -> bool: name = name.strip() n = con.execute('DELETE FROM university WHERE name = ?', (name, )).rowcount return n > 0
def remove_enrollment(educationID: int, courseID: int) -> bool: n = con.execute( '''DELETE FROM enrollment WHERE educationID = ? AND courseID = ?''', (educationID, courseID)).rowcount return n > 0
def check_enrollment(educationID: int, courseID: int) -> bool: r = con.execute( '''SELECT * FROM enrollment WHERE educationID = ? AND courseID = ?''', (educationID, courseID)).fetchall() return len(r) > 0
def get_experience_for_user(userID: int) -> List[Row]: return con.execute('SELECT * FROM experience WHERE userID = ?', (userID,)) \ .fetchall()
def delete_education(id: int) -> bool: n = con.execute('DELETE FROM education WHERE id = ?', (id, )).rowcount return n > 0
def read_experience(userID: int, posID: int) -> Row: return con.execute('''SELECT * FROM experience WHERE userID = ? AND positionID = ?''', (userID, posID)) \ .fetchone()
def get_education_for_user(userID: int) -> List[Row]: return con.execute('SELECT * FROM education WHERE userID = ?', (userID,)) \ .fetchall()
def delete_experience(userID: int, posID: int) -> bool: n = con.execute('''DELETE FROM experience WHERE userID = ? AND positionID = ?''', (userID, posID)) \ .rowcount return n > 0
def get_classes_for_career(industry: str, job: str = None, school: str = None, threshold: int = 3): """Return a list of potential classes related to the given industry. Classes are filtered by users who work in a particular position/industry who have reported taking those classes. If a specific job title is given, only users who have held that title are considered. A university name may be provided to restrict results to a particular university. Returns a dictionary mapping university names to a list of course numbers and titles, sorted aplhabetically """ params = [f'%{industry}%'] # fuzzy match industry query_join_position = '' query_filter_job = '' query_filter_school = '' if job is not None: params.append(f'%{job}%') # fuzzy match job query_join_position = 'JOIN position p ON ex.positionID = p.id' query_filter_job = 'AND jobTitle LIKE ?' if school is not None: params.append(f'%{school}%') # fuzzy match school query_filter_school = 'AND university LIKE ?' base_query = '''SELECT courseNumber || ': ' || courseTitle as course, universityName as university FROM (experience NATURAL JOIN education) ex JOIN enrollment e ON e.educationID = ex.id JOIN course c ON e.courseID = c.id {} {} ''' where_clause = 'WHERE industry LIKE ? {} {}'.format( query_filter_job, query_filter_school) positive_query = base_query.format(query_join_position, where_clause) print(positive_query) positive_matches = con.execute(positive_query, params).fetchall() where_clause = 'WHERE' if job is not None: where_clause += ' (industry NOT LIKE ? OR jobTitle NOT LIKE ?)' else: where_clause += ' industry NOT LIKE ?' if school is not None: where_clause += ' AND university LIKE ?' negative_query = base_query.format(query_join_position, where_clause) negative_matches = con.execute(negative_query, params).fetchall() # build a dictionary mapping universities to courses, with each course having # an associate count (as a relevance metric) results = defaultdict(lambda: defaultdict(int)) for row in positive_matches: results[row['university']][row['course']] += 1 for row in negative_matches: results[row['university']][row['course']] -= 1 # for each university (key k), sort the list of courses by relevance for k, v in results.items(): # filter out any courses with negative relevance courses = list(filter(lambda x: x[1] > 0, v.items())) # courses = v.items() # sort alphabetically by course first, then sort by descending count # items with the same count wont be reordered courses = sorted(courses, key=lambda x: x[0], reverse=False) courses = sorted(courses, key=lambda x: x[1], reverse=True) # strip off the count, we don't need to show that to the user # also limit results to 20 per university results[k] = list(map(lambda x: x[0], courses))[:20] # elide any universities which have no courses results = {k: v for k, v in results.items() if len(v) > 0} if len(results) == 0: return ('', 204) return results
def read_university(name: str) -> Row: # `name` column is `COLLATE NOCASE` so we just need to strip whitespace name = name.strip() return con.execute('SELECT * FROM university WHERE name = ?', (name,)) \ .fetchone()
def get_job_for_education_background(userID: int, limit: int = 20): """Get a list of jobs held by users of similar educational background. For each entry in this user's education, attempt to match other users by major, degree, and university. Major is required for a valid match, but degree and university may vary. For each match, the weight of the result is increased. Weights are computed in three dimensions: major matches, degree matches, and university matches. The results are ranked by the weighted average of these dimensions, with relative weights of 3, 1, 2 respectively (i.e. people in your major are better indicators than anyone at your university, which itself is a better indicator than just anyone with a bachelors degree). """ # Raises a 404 if the user is not found. That is exactly what we want _ = get_user(userID) # Alright, this is a bit complicated. We have two primary queries here: A # and B. # # Query B is easy, it just gets the User and Degree information of the user # we want to look up from the education table. # # Query A is harder. We start with education for the same info as B. Then # we join with experience to gain access to the postionID column, which we # then use to join with position, which gives us jobTitle and employerName. # We filter this by users that still exist in the table (in case a user was # deleted without removing their experience). Since this query does not # depend on variables, we can store it as a view to simplify the full query. # # Next, we join A and B on the condition that the userID is different, # essentially getting a cross product of our user with the work experience # of every other (current) user. # # We then group by the job info and aggregate using our custom weight # function which takes the education info of our user and the other user # to determine a relationship factor. We exclude the groups (jobs) which # have no relationship. # # Finally, we select the job title and employer name concatenated together # and the custom weight value and sort by descending weight. # NOTE: to change this view, the old version must first be DROP'ed # as views are read-only in SQLite con.execute('''CREATE VIEW IF NOT EXISTS all_jobs (jobTitle, employerName, userID, university, degree, major) AS SELECT jobTitle, employerName, userID, university, degree, major FROM (education NATURAL JOIN experience) e JOIN position p ON e.positionID = p.id WHERE userID in (SELECT id FROM user) ''') rows = con.execute( ''' SELECT jobTitle || ', ' || employerName as job, CAREER_WEIGHT( a.major, a.university, a.degree, b.major, b.university, b.degree ) as weight FROM all_jobs a JOIN ( SELECT userID, university, degree, major FROM education WHERE userID = ? ) b ON a.userID <> b.userID GROUP BY jobTitle, employerName HAVING CAREER_WEIGHT( a.major, a.university, a.degree, b.major, b.university, b.degree ) > 0 ORDER BY weight DESC LIMIT ? ''', (userID, limit)).fetchall() results = [{'role': r['job'], 'relevance': r['weight']} for r in rows] return {'status': 'OK', 'results': results}
def add_enrollment(educationID: int, courseID: int) -> bool: n = con.execute('INSERT INTO enrollment VALUES (?, ?)', (educationID, courseID)) \ .rowcount return n > 0