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()