class Skills:
def __init__(self):
self.connection = Connection()
self.connection.connect_database()
# check user exists or not
def exists(self, user_id):
query = "SELECT user_id FROM interns WHERE user_id = '{}'".format(
user_id)
self.connection.cursor.execute(query)
user_id = self.connection.cursor.fetchone()
print("user_id: ", user_id)
if user_id is None:
return False
else:
return True
# get existing skills
def current_skills(self, user_id):
# now get existing skills from database.
query = "SELECT skills FROM interns WHERE user_id = '{}'".format(
user_id)
self.connection.cursor.execute(query)
self.existing_skills = self.connection.cursor.fetchone()
return self.existing_skills[0]
# validation for empty skill and given information is in list format
def skill_validation(self, content):
try:
self.skills = content['skills']
except Exception as e:
self.payload = "Error " + str(e), 500
print(self.payload)
return self.payload
if type(self.skills) is not list:
self.payload = "given argument must be in list format", 200
print(self.payload)
return self.payload
if len(self.skills) == 0:
self.payload = "list can not be empty", 200
print(self.payload)
return self.payload
for skill in self.skills:
length = len(skill.strip())
if length == 0:
self.payload = "skill can not be empty", 200
print(self.payload)
return self.payload
if skill.isalnum() is False:
self.payload = "only alphanumeric allowed", 200
print(self.payload)
return self.payload
# get all skills in json format
def get_skills(self, user_id):
if self.exists(user_id):
query = "SELECT user_id, skills FROM interns WHERE user_id = '{}'".format(
user_id)
try:
self.connection.cursor.execute(query)
existing_skills = self.connection.cursor.fetchone()
print("existing_skills", existing_skills)
# getting needed row headers.
row_header = [x[0] for x in self.connection.cursor.description]
print("row_header", row_header)
self.connection.close()
# display data in json format
payload = {
'status': 200,
'data': dict(zip(row_header, existing_skills))
}
print(payload)
return payload
except Exception as e:
payload = "Error : " + str(e), 500
print(payload)
return payload
else:
self.connection.close()
payload = 'Intern not found'
print(payload)
return payload
# add skills
def add_skills(self, user_id, content):
# add skills
if self.skill_validation(content):
return self.payload
if self.exists(user_id):
# getting items in string format, will convert it into set.
requested_skills = set(self.skills)
print("requested_skills", requested_skills)
print("requested_skills get successfully")
# now we have only unique skills left, will convert into str.
skills = ','.join(requested_skills)
print("skills", skills, type(skills))
try:
# add skill to database
query = "UPDATE interns SET skills = %s WHERE user_id = %s"
values = (skills, user_id)
self.connection.cursor.execute(query, values)
self.connection.conn.commit()
self.connection.close()
payload = "skills successfully added", 200
print(payload)
return payload
except Exception as e:
self.connection.close()
payload = "Error: " + str(e), 500
print(payload)
return payload
else:
self.connection.close()
payload = "Intern not found", 404
print(payload)
return payload
# update skills
def update_skills(self, user_id, content):
if self.skill_validation(content):
return self.payload
if self.exists(user_id):
if self.current_skills(user_id) is None:
payload = "skills does not exist"
print(payload)
return payload
else:
# getting existing skills in tuple format, first convert into string then set.
self.existing_skills = set(','.join(
self.existing_skills).split(','))
# getting items in list format, will convert it into set.
requested_skills = set(self.skills)
print("requested_skills get successfully")
# we have two sets one is existing skills and second is requested skills.
# to add skills in database it must be in string format.
new_skills = ','.join(
self.existing_skills.union(requested_skills))
print("new_skills get successfully")
# add skills to database.
query = "UPDATE interns SET skills = %s WHERE user_id = %s"
values = (new_skills, user_id)
try:
self.connection.cursor.execute(query, values)
self.connection.conn.commit()
payload = "skills successfully updated", 200
print(payload)
return payload
except Exception as e:
payload = "Error: " + str(e), 500
print(payload)
return payload
finally:
self.connection.close()
else:
self.connection.close()
payload = "Intern not found", 404
print(payload)
return payload
# delete skills
def delete_skills(self, user_id, content):
if self.skill_validation(content):
return self.payload
if self.exists(user_id):
if self.current_skills(user_id) is None:
payload = "nothing added in skills yet"
print(payload)
return payload
else:
# getting existing skills in tuple format, first convert into string then set.
self.existing_skills = set(','.join(
self.existing_skills).split(','))
# getting items in string format, will convert it into set.
requested_skills = set(self.skills)
print("requested_skills get successfully")
# from existing skills remove requested skills.
payload = []
for requested_skill in requested_skills:
if requested_skill in self.existing_skills:
self.existing_skills.remove(requested_skill)
# to add skills in database it must be in string format.
new_skills = ','.join(set(self.existing_skills))
print("new_skills", new_skills)
print("new_skills get successfully")
# add skills to database.
query = "UPDATE interns SET skills = %s WHERE user_id = %s"
values = (new_skills, user_id)
try:
self.connection.cursor.execute(query, values)
self.connection.conn.commit()
payload = "skills deleted successfully " + requested_skill, 200
print(payload)
except Exception as e:
payload = "Error: " + str(e), 500
print(payload)
return payload
finally:
self.connection.close()
else:
payload = "skills does not exist " + requested_skill, 404
print(payload)
return payload
else:
self.connection.close()
payload = "Intern not found", 404
print(payload)
return payload
from conn import Connection
# create object for class "Connection"
connection = Connection()
# create database "terntop"
try:
connection.cursor.execute("CREATE DATABASE terntop")
print('database created successfully')
except Exception as e:
print('Error: ', str(e))
# connect to sql database "terntop"
connection.connect_database()
# create table "interns"
try:
connection.cursor.execute(
"CREATE TABLE interns (user_id VARCHAR(10) NOT NULL UNIQUE PRIMARY KEY, "
"email_id VARCHAR(50), pw_hash VARCHAR(100) NOT NULL, "
"first_name VARCHAR(20), last_name VARCHAR(40), full_name VARCHAR(20),"
"image_url TEXT,phone BIGINT(10),country_code VARCHAR(2), city VARCHAR(20),"
"state VARCHAR(20), country VARCHAR(20) ,date_registered TIMESTAMP, "
"date_updated TIMESTAMP, skills TEXT, education TEXT,"
"experience TEXT, applications TEXT)")
print('"interns" table created successfully')
except Exception as e:
print(str(e))
# create table "employers"
try:
class Education:
def __init__(self):
self.connection = Connection()
self.connection.connect_database()
def education_validation(self, content):
try:
self.content = content
for edu in self.content:
# check dictionary is empty or not
if not edu:
self.payload = "dictionary can not be empty", 200
return self.payload
if type(edu['type']) is not str:
self.payload = "degree type must be string", 200
return self.payload
if type(edu['name']) is not str:
self.payload = "degree name must be string", 200
return self.payload
if type(edu['is_completed']) is not bool:
self.payload = "is_completed must be boolean", 200
return self.payload
if type(edu['year']) is not list:
self.payload = "degree year must be list", 200
return self.payload
# check year is int or not
for year in edu['year']:
if type(year) is not int:
self.payload = "degree year must be int", 200
return self.payload
if len(edu['year']) > 2:
self.payload = 'year exceeds the maximum allowed length of 2', 200
return self.payload
except Exception as e:
payload = "Error : " + str(e), 500
print(payload)
return payload
def add_education(self, user_id, content):
if self.education_validation(content):
return self.payload
if skills.exists(user_id):
query = "UPDATE interns SET education = %s WHERE user_id = %s"
values = (str(self.content), user_id)
try:
self.connection.cursor.execute(query, values)
self.connection.conn.commit()
self.connection.close()
payload = "education successfully updated", 200
print(payload)
return payload
except Exception as e:
payload = "Error : " + str(e), 500
print(payload)
return payload
else:
payload = "intern not found", 200
print(payload)
return payload
def get_education(self, user_id):
query = "SELECT user_id, education FROM interns WHERE user_id = '{}'".format(
user_id)
self.connection.cursor.execute(query)
existing_education = self.connection.cursor.fetchone()
print("existing_education : ", existing_education)
row_header = [x[0] for x in self.connection.cursor.description]
print("row_header : ", row_header)
self.connection.close()
payload = {
'status': 200,
'data': dict(zip(row_header, existing_education))
}
print(payload)
return payload
class Interns:
def __init__(self):
# connect to database
self.connection = Connection()
self.connection.connect_database()
def exists(self, user_id):
# get user_id exists or not
query = "SELECT user_id FROM interns WHERE user_id = '{}'".format(
user_id)
self.connection.cursor.execute(query)
user_id = self.connection.cursor.fetchone()
print(user_id)
if user_id is None:
return False
else:
return True
# register intern
def register_intern(self, content):
print(content)
try:
user_id = content['user_id']
pw = content['pw']
date_registered = datetime.now()
# pw to hash
hash_pw = hashlib.sha256(str(pw).encode('utf-8')).hexdigest()
if self.exists(user_id):
payload = "user_id already registered", 200
print(payload)
return payload
else:
query = "INSERT INTO interns (user_id, pw_hash, date_registered) VALUES (%s, %s, %s)"
values = (user_id, hash_pw, date_registered)
self.connection.cursor.execute(query, values)
payload = "Intern registered successfully", 200
print(payload)
return payload
except Exception as e:
payload = "Error : " + str(e), 500
print(payload)
return payload
finally:
self.connection.conn.commit()
self.connection.close()
# update intern's info using user_id
def update_intern(self, user_id, content):
print(content)
if self.exists(user_id):
try:
email_id = content['email_id']
first_name = content['first_name']
last_name = content['last_name']
full_name = first_name + ' ' + last_name
image_url = content['image_url']
phone = content['phone']
country_code = content['country_code']
city = content['city']
state = content['state']
country = content['country']
date_updated = datetime.now()
except Exception as e:
payload = "invalid" + str(e), 500
print(payload)
return payload
query = "UPDATE interns SET" \
" email_id = %s," \
" first_name = %s," \
" last_name = %s," \
" full_name = %s," \
" image_url = %s, " \
" phone = %s," \
" country_code = %s," \
" city = %s," \
" state = %s," \
" country = %s," \
" date_updated = %s" \
" WHERE user_id = %s"
values = (email_id, first_name, last_name, full_name, image_url,
phone, country_code, city, state, country, date_updated,
user_id)
try:
self.connection.cursor.execute(query, values)
self.connection.conn.commit()
self.connection.close()
payload = "Intern updated successfully", 200
print(payload)
return payload
except Exception as e:
payload = "Error : " + str(e), 500
print(payload)
return payload
else:
self.connection.close()
payload = "Intern not registered", 200
print(payload)
return payload
# get intern using user_id
def get_intern(self, user_id):
try:
if self.exists(user_id):
query = "SELECT * FROM interns WHERE user_id = '{}'".format(
user_id)
print(query)
self.connection.cursor.execute(query)
user = self.connection.cursor.fetchone()
print(user)
# getting needed row headers.
row_header = [x[0] for x in self.connection.cursor.description]
print(row_header)
payload = {'status': 200, 'data': dict(zip(row_header, user))}
print(payload)
return payload
else:
payload = 'Intern not found'
print(payload)
return payload
except Exception as e:
payload = "Error : " + str(e), 500
print(payload)
return payload
finally:
self.connection.conn.commit()
self.connection.close()
# delete intern using user_id
def delete_intern(self, user_id):
try:
if self.exists(user_id):
query = "DELETE FROM interns WHERE user_id = '{}'".format(
user_id)
print(query)
self.connection.cursor.execute(query)
payload = "Intern deleted successfully", 200
print(payload)
return payload
else:
payload = "Intern not found", 200
print(payload)
return payload
except Exception as e:
payload = "Error : " + str(e), 500
print(payload)
return payload
finally:
self.connection.conn.commit()
self.connection.close()
