def create_tag(create_tag: models.CreateTag) -> models.Tag:
conn = db.get_connection()
try:
tag = db.create_tag(conn, create_tag.name)
except db.DBException as e:
raise HTTPException(status.HTTP_400_BAD_REQUEST, str(e))
return tag
def find(student_name='%%', project_name='%%'):
query = ("SELECT "
"project_name,"
"student_name,"
"application_date,"
"status "
"FROM application "
"WHERE "
"project_name LIKE %(project_name)s and "
"student_name LIKE %(student_name)s")
multi = student_name == '%%' or project_name == '%%'
results = list() if multi else None
cnx = db.get_connection()
with cnx.cursor() as cursor:
cursor.execute(query, {
'project_name': project_name,
'student_name': student_name,
})
for result in cursor:
if multi:
results.append(
Application(is_new_application=False, **result))
else:
results = Application(is_new_application=False, **result)
return results
def update(self):
conn = db.get_connection()
cursor = conn.cursor()
cursor.execute('UPDATE task SET title=%s, status=%s WHERE task_id=%s', (self.title, self.status, str(self.task_id)))
cursor.close()
conn.commit()
conn.close()
def _mark_migration_as_executed(self):
conn = get_connection()
cursor = conn.cursor()
cursor.execute('INSERT INTO migrations (migration_key) values (%s)', (
self.MIGRATION_KEY,
))
cursor.close()
def find_by_name(name, fuzzy=False):
query = ("SELECT "
"name, "
"description, "
"advisor_name, "
"advisor_email, "
"est_num_students, "
"designation_name "
"FROM project WHERE name like %(name)s")
get_categories = (
"SELECT category_name FROM project_category WHERE project_name=%(name)s"
)
get_requirements = (
"SELECT requirement FROM project_requirement WHERE name=%(name)s")
# if fuzzy search, then name should become '%<name>%'
# ex) name = 'andrew' => name = '%andrew%'
name = name if fuzzy is False else '%%%s%%' % (name)
cnx = db.get_connection()
with cnx.cursor() as cursor:
projects = list()
cursor.execute(query, {'name': name})
for result in cursor:
data = dict(result)
p = Project(is_new_project=False,
categories=[],
requirements=[],
**data)
projects.append(p)
for p in projects:
cursor.execute(get_categories, {'name': p.name})
p.categories = cursor.fetchall()
cursor.execute(get_requirements, {'name': p.name})
p.requirements = cursor.fetchall()
return projects if fuzzy else projects[0]
def get_department(major):
query = ("SELECT department_name FROM major WHERE name=%(major)s")
cnx = db.get_connection()
with cnx.cursor() as cursor:
cursor.execute(query, {'major': major})
department = cursor.fetchone()['department_name']
return department
def convert_to_name(year):
query = ("SELECT name FROM year_name WHERE year=%(year)s")
cnx = db.get_connection()
with cnx.cursor() as cursor:
cursor.execute(query, {'year': year})
name = cursor.fetchone()['name']
return name
def save(self):
insert_course = (
"INSERT INTO course"
"(name,"
"course_number,"
"instructor,"
"est_num_students,"
"designation_name)"
"VALUES"
"(%(name)s,"
"%(course_number)s,"
"%(instructor)s,"
"%(est_num_students)s,"
"%(designation_name)s)"
)
insert_category = (
"INSERT INTO course_category (course_name, category_name)"
"VALUES (%(name)s, %(category)s)"
)
cnx = db.get_connection()
with cnx.cursor() as cursor:
if self.is_new_course:
cursor.execute(insert_course, vars(self))
for c in filter(lambda c: c is not None, self.categories):
cursor.execute(insert_category, {'name': self.name, 'category': c})
else:
raise NotImplementedError('courses can not be modified')
cnx.commit()
self.is_new_course = False
def convert_to_year(name):
query = ("SELECT year from year_name WHERE name=%(name)s")
cnx = db.get_connection()
with cnx.cursor() as cursor:
cursor.execute(query, {'name': name})
year = cursor.fetchone()['year']
return year
def update_todo(todo_update: models.TodoUpdate) -> models.Todo:
conn = db.get_connection()
try:
todo = db.update_todo(conn, todo_update)
except db.DBException as e:
raise HTTPException(status.HTTP_400_BAD_REQUEST, str(e))
return todo
def create_todo(create_todo: models.CreateTodo) -> models.Todo:
conn = db.get_connection()
try:
todo = db.create_todo(conn, create_todo.contents, create_todo.tags)
except db.DBException as e:
raise HTTPException(status.HTTP_400_BAD_REQUEST, str(e))
return todo
def get_contact_by_id(contact_id):
connection = get_connection()
cursor = connection.cursor()
cursor.execute("SELECT * FROM contact WHERE contact_id = ?", [contact_id.value])
row = cursor.fetchone()
contact = Contact.create(row[1], row[2], row[3], ContactId(row[0]), row[4])
return contact
def search(title=None,
category=None,
designation=None,
major=None,
year=None,
project=True,
course=True):
course_str = "SELECT name, 'Course' AS type FROM course WHERE %s"
project_str = "SELECT name, 'Project' AS type FROM project WHERE %s"
title_str = "name = '%s'"
course_category_str = "name IN (SELECT course_name FROM course_category WHERE category_name IN (%s))"
project_category_str = "name IN (SELECT project_name FROM project_category WHERE category_name IN (%s))"
designation_str = "designation_name = '%s'"
requirement_str = "name IN (SELECT name FROM project_requirement WHERE requirement IN (%s))"
# query parts
course_qp = []
project_qp = []
if title is not None and len(title) != 0:
course_qp.append(title_str % title)
project_qp.append(title_str % title)
if category is not None:
category = filter(lambda c: c is not None, category)
if len(category) != 0:
category_str = ','.join(["'%s'" % c for c in category])
course_qp.append(course_category_str % category_str)
project_qp.append(project_category_str % category_str)
if designation is not None:
course_qp.append(designation_str % designation)
project_qp.append(designation_str % designation)
if major is not None or year is not None:
in_clause = []
if major is not None:
in_clause.append("'%s'" % major)
if year is not None:
in_clause.append("'%s'" % year)
project_qp.append(requirement_str % ','.join(in_clause))
if len(course_qp) == 0:
course_qp.append('TRUE')
if len(project_qp) == 0:
project_qp.append('TRUE')
query_parts = []
if course:
query_parts.append(course_str % ' AND '.join(course_qp))
if project:
query_parts.append(project_str % ' AND '.join(project_qp))
query = ' UNION ALL '.join(query_parts)
cnx = db.get_connection()
with cnx.cursor() as cursor:
try:
cursor.execute(query)
results = cursor.fetchall()
finally:
print cursor._last_executed
return results
def delete(self):
conn = db.get_connection()
cursor = conn.cursor()
cursor.execute('DELETE FROM task WHERE task_id=%s', (str(self.task_id)))
cursor.execute('DELETE FROM user_has_task WHERE fk_task_id=%s', (str(self.task_id)))
cursor.close()
conn.commit()
conn.close()
def get_type(requirement):
query = ("SELECT requirement_type FROM requirement "
"WHERE requirement_name=%(requirement)s")
cnx = db.get_connection()
with cnx.cursor() as cursor:
cursor.execute(query, {'requirement': requirement})
r_type = cursor.fetchone()['requirement_type']
return r_type
def get_department_mapping():
query = ("SELECT name, department_name FROM major")
cnx = db.get_connection()
with cnx.cursor() as cursor:
cursor.execute(query)
mapping = dict([(m['name'], m['department_name'])
for m in cursor.fetchall()])
return mapping
def create_contact(contact_id, name, mobile, email, comments):
connection = get_connection()
cursor = connection.cursor()
cursor.execute("INSERT INTO contact (contact_id, name, mobile, email, comments) VALUES (?, ?, ?, ?, ?)",
(contact_id.value, name, mobile, email, comments))
connection.commit()
def get_popular_projects():
query = (
"SELECT project_name, num_applicants "
"FROM admin_popular_project")
cnx = db.get_connection()
with cnx.cursor() as cursor:
cursor.execute(query)
return cursor.fetchall()
def get_all_applications():
query = (
"SELECT project_name, student_name, major, year, status "
"FROM admin_application_view")
cnx = db.get_connection()
with cnx.cursor() as cursor:
cursor.execute(query)
return cursor.fetchall()
def get_all(include_none=False):
query = ("SELECT name FROM designation")
cnx = db.get_connection()
with cnx.cursor() as cursor:
cursor.execute(query)
all_data = cursor.fetchall()
if include_none:
all_data.insert(0, {'name': 'None'})
return all_data
def get_all(include_none=False):
query = ("SELECT year, name from year_name")
cnx = db.get_connection()
with cnx.cursor() as cursor:
cursor.execute(query)
all_data = list(cursor.fetchall())
if include_none:
all_data.insert(0, {'name': 'None', 'year': None})
return all_data
def get_all(include_none=False):
query = ("SELECT name, department_name FROM major")
cnx = db.get_connection()
with cnx.cursor() as cursor:
cursor.execute(query)
all_data = list(cursor.fetchall())
if include_none:
all_data.insert(0, {'name': 'None'})
return all_data
def insert(user_id, title):
conn = db.get_connection()
cursor = conn.cursor()
cursor.execute('INSERT INTO task(title) VALUES (%s)', (title))
cursor.execute('SELECT last_insert_id()')
task_id = int(cursor.fetchone()[0])
cursor.execute('INSERT INTO user_has_task(fk_user_id, fk_task_id) VALUES (%s, %s)', (user_id, task_id))
cursor.close()
conn.commit()
conn.close()
def _get_by_type(r_type, include_none=False):
query = ("SELECT requirement_name FROM requirement "
"WHERE requirement_type=%(r_type)s")
cnx = db.get_connection()
with cnx.cursor() as cursor:
cursor.execute(query, {'r_type': r_type})
reqs = cursor.fetchall()
if include_none:
reqs.append({'requirement_name': None})
return reqs
def find_by_username(username):
query = (
"SELECT username, password, email, year, major, is_admin FROM user WHERE username=%(username)s"
)
cnx = db.get_connection()
with cnx.cursor() as cursor:
cursor.execute(query, {'username': username})
raw_data = cursor.fetchone()
user = User(is_new_user=False, **
raw_data) if raw_data is not None else None
return user
def _update_status(self, new_status):
update_query = ("UPDATE application SET status=%(status)s "
"WHERE "
"project_name=%(project_name)s and "
"student_name=%(student_name)s")
self.status = new_status
cnx = db.get_connection()
with cnx.cursor() as cursor:
cursor.execute(update_query, vars(self))
cnx.commit()
def set_password(self, password):
password_hash = generate_password_hash(password)
set_password = "******"
cnx = db.get_connection()
with cnx.cursor() as cursor:
result = cursor.execute(set_password, {
'username': self.username,
'password': password_hash,
})
cnx.commit()
return result
def get_user_task(user_id, task_id):
conn = db.get_connection()
cursor = conn.cursor()
cursor.execute('SELECT * FROM user_has_task INNER JOIN task ON fk_task_id = task_id WHERE fk_user_id=%s AND fk_task_id=%s', (str(user_id), task_id))
result = cursor.fetchone()
cursor.close()
conn.close()
if result == None:
return None
(fk_user_id, fk_task_id, task_id, title, begin, end, status) = result
return Task(fk_user_id, task_id, title, begin, end, status)
def save(self):
insert_project = ("INSERT INTO project "
"(name,"
"description,"
"advisor_name,"
"advisor_email,"
"est_num_students,"
"designation_name)"
"VALUES ("
"%(name)s,"
"%(description)s,"
"%(advisor_name)s,"
"%(advisor_email)s,"
"%(est_num_students)s,"
"%(designation_name)s)")
insert_category = (
"INSERT INTO project_category (project_name, category_name) "
"VALUES (%(name)s, %(category)s)")
insert_requirement = (
"INSERT INTO project_requirement (name, requirement) "
"VALUES (%(name)s, %(requirement)s)")
cnx = db.get_connection()
with cnx.cursor() as cursor:
project_dict = {
'name': self.name,
'description': self.description,
'advisor_name': self.advisor_name,
'advisor_email': self.advisor_email,
'est_num_students': self.est_num_students,
'designation_name': self.designation_name,
}
if self.is_new_project:
cursor.execute(insert_project, project_dict)
for c in filter(lambda c: c is not None, self.categories):
cursor.execute(insert_category, {
'category': c,
'name': self.name
})
for r in filter(lambda r: r is not None, self.requirements):
try:
cursor.execute(insert_requirement, {
'requirement': r,
'name': self.name
})
finally:
print cursor._last_executed
else:
raise NotImplementedError('projects can not be modified')
cnx.commit()
def get_user_tasks(user_id):
tasks = []
conn = db.get_connection()
cursor = conn.cursor()
cursor.execute('SELECT * FROM user_has_task INNER JOIN task ON fk_task_id = task_id WHERE fk_user_id=%s', (str(user_id)))
results = cursor.fetchall()
cursor.close()
conn.close()
for result in results:
(fk_user_id, fk_task_id, task_id, title, begin, end, status) = result
tasks.append(Task(fk_user_id, task_id, title, begin, end, status))
return tasks
def save(self):
insert_user = "******"
update_user = "******"
cnx = db.get_connection()
with cnx.cursor() as cursor:
try:
if self.is_new_user:
cursor.execute(insert_user, vars(self))
self.is_new_user = False
else:
cursor.execute(update_user, vars(self))
except:
print cursor._last_executed
raise
cnx.commit()
def get(username, password):
conn = db.get_connection()
cursor = conn.cursor()
cursor.execute('SELECT user_id, password FROM user WHERE username=%s', (username))
result = cursor.fetchone()
if result == None:
raise UserException('User not found.')
cursor.close()
conn.close()
(user_id, real_hashed_password) = result
hashed_password = hashlib.sha256(password.encode('utf-8')).hexdigest()
if real_hashed_password != hashed_password:
raise UserException('Incorrect password.')
return User(user_id, username)
def get_application_report():
get_applications = (
"SELECT project_name, num_applicants, accept_rate "
"FROM admin_application_report")
get_majors = (
"SELECT major "
"FROM admin_application_report_top_majors "
"WHERE project_name=%(project_name)s")
cnx = db.get_connection()
with cnx.cursor() as cursor:
cursor.execute(get_applications)
projects = cursor.fetchall()
for project in projects:
cursor.execute(get_majors, {'project_name': project['project_name']})
project['majors'] = [result['major'] for result in cursor.fetchall()][:3]
return projects
def conn(self):
return get_connection(self.DB_KEY)
