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
