def write_to_db(): """ This function writes the completed annotations a user did to the database """ if not request.is_json: return "Unexpected Parameters" data = request.get_json() data['user'] = current_user.get_id() print(data) if str(data['id']) in current_user.get_annotated().split(): raise error_handler.DatabaseError("Already annotated", 500) # try: db = get_db() cursor = db.execute('select * from annotations') allowed_columns = [d[0] for d in cursor.description] for key, v in list(data.items()): if key not in allowed_columns: del data[key] else: data[key] = str(v) db.execute( 'INSERT INTO annotations ({0}) VALUES ({1})'.format( ', '.join(('"' + str(key) + '"' for key in data)), ', '.join( ('?' for key in data))), tuple((data[key]) for key in data)) db.execute('UPDATE user set annotated = ? WHERE id = ?', (" ".join([current_user.get_annotated(), str(data['id'])]), current_user.get_id())) db.commit() return 'Success'
def get_data() -> pd.DataFrame: """ Read data in database and return them as a DataFrame """ try: db = get_db() df_data = pd.read_sql_query("SELECT * FROM data", db) df_annotations_per_data = pd.read_sql_query( "SELECT data_id, COUNT(*) FROM annotations GROUP BY data_id", db) except Exception as e: app.logger.error("Database Error:" + str(e)) raise error_handler.DatabaseError(str(e)) try: annotations_per_data_dict = pd.Series( df_annotations_per_data.get("COUNT(*)").values, index=df_annotations_per_data.data_id).to_dict() df_data.insert(loc=1, column="annotations", value=df_data["id"].map(annotations_per_data_dict)) df_data.fillna({"annotations": 0}, inplace=True) #TODO restrict to annotations column df_data.annotations = df_data.annotations.astype(int) df_data = df_data.sort_values(by=['id']) return df_data except Exception as e: app.logger.error("Error:" + str(e)) raise error_handler.UnknownError(str(e))
def get_annotations() -> pd.DataFrame: """ Read annotations in database and return them as a Dataframe """ try: db = get_db() df_annotations = pd.read_sql_query("SELECT * FROM annotations", db) df_data = pd.read_sql_query("SELECT * FROM data", db) except Exception as e: app.logger.error("Database Error:" + str(e)) raise error_handler.DatabaseError(str(e)) try: comments_dict = pd.Series(df_data.content.values, index=df_data.id).to_dict() df_annotations.insert( loc=1, column='comment', value=df_annotations['data_id'].map(comments_dict)) df_annotations = df_annotations.sort_values(by=['id']) #df_annotations.drop(['user'],inplace=True,axis=1) return df_annotations except Exception as e: app.logger.error("Error:" + str(e)) raise error_handler.UnknownError(str(e))
def register_user(username,email,fname,lname,password,confirm_password): """ Function that creates a new user by adding an entry to the database. Params: username: Input, username of the user. type: str email: Input, email of the user. type: str fname: Input, first-name of the user. type: str lname: Input, last-name of the user. type: str password: Input, password (plain text) of the user. type: str (Should be atleast 4 chars) confirm_password: Input, confirm password (plain text) of the user. type: str returns: 2 params: "Success message"(or None), "Failure message"(or None) """ app.logger.debug("from register_user()") error_message = "" ## Handle length of the password and check of confirmation password is same as the original if len(username)<4: error_message = "Username must be atleast 4 characters long!" if len(password)<4: error_message = "Password must be atleast 4 characters long!" if password!=confirm_password: error_message = "The entered password is not same as the confirmation password!" ## Check if the email address is valid (Basic regex handler is used. It must have the format [email protected][.pq]) email_regex = r'[^@]+@[^@]+\.[^@]+' if not re.match(email_regex,email): app.logger.debug("email id entered did not match regex: {}".format(email_regex)) error_message += " Enter valid email address!" if error_message != "": app.logger.debug("Error message present: {}".format(message)) return None, error_message db = get_db() ## return error if the username already exists if db.execute('SELECT id,username FROM user WHERE username = ?', (username,)).fetchone(): app.logger.debug("Username {} already present in the database, did not add new user".format(username)) return None, "username already exists!" ## hash the password before storing password_hash_object = hashlib.sha256(password.encode('utf-8')) password_hash = password_hash_object.hexdigest() try: ## Insert the data into the database db.execute('INSERT INTO user (username, email, given_name, surname, password, user_type, is_approved) VALUES (?, ?, ?, ?, ?, ?, ?)', (username, email, fname, lname, password_hash,"normal","no")) db.commit() app.logger.debug("New user added to db, db-commit successful") return "Successfully added user", None except Exception as e: app.logger.error("Exception occurred : {}".format(str(e))) app.logger.error("rolling back DB") db.rollback() app.logger.error("Error occurred while adding user, DB rolled-back") raise error_handler.DatabaseError(str(e))
def change_options(): """ Function that gives access to the admin console. Access should be restricted only to the admins. Actions that can be further performed by the admin through this console: 1. activate or deactivate a user. 2. change password for the user. """ app.logger.debug("from options") ## Verify if the logged in user is a admin user if not current_user.admin: app.logger.info("the user {} is not admin".format(current_user)) return render_template('login.html', error="login as admin to proceed") success, failure = "", "" db = get_db() row = db.execute('SELECT max_annotations FROM options').fetchone() max_annotations = row['max_annotations'] app.logger.debug("max_annotations in database: " + str(max_annotations)) if request.method == 'POST': max_annotations = request.form['max_annotations'] try: max_annotations = int(max_annotations) except Exception as e: app.logger.error("max_annotations entered is not integer") return render_template('options.html', user=current_user.fname, admin=current_user.admin, error=failure) app.logger.debug("max_annotations: {}".format(max_annotations)) try: db.execute('UPDATE options set max_annotations = ?', (max_annotations, )) db.commit() success = "Successfully changed Maximum annotations per data item: {}".format( max_annotations) except Exception as e: app.logger.error("Exception occurred : {}".format(str(e))) app.logger.error("rolling back DB") db.rollback() app.logger.error( "Error occurred while updating option, DB rolled-back") raise error_handler.DatabaseError(str(e)) return render_template('options.html', user=current_user.fname, admin=current_user.admin, max_annotations=int(max_annotations), success=success) return render_template('options.html', user=current_user.fname, admin=current_user.admin, max_annotations=int(max_annotations))
def change_password(username, password, confirm_password, new_password, confirm_new_password): """ Function that changes the user login password. Databse is updated upon password change. Params: username: Input, username of the user. type: str password: Input, password (plain text) of the user. type: str (Should be atleast 4 chars) confirm_password: Input, confirm password (plain text) of the user. type: str new_password: Input, new password (plain text) of the user. type: str (Should be atleast 4 chars) confirm_new_password: Input, confirm new password (plain text) of the user. type: str returns: 2 params: "Success message"(or None), "Failure message"(or None) """ app.logger.debug("from change_password()") error_message = "" ## Check if the original password and confirm_password are same if password != confirm_password: error_message += " The entered current password is not same as the confirmation password!" ## Check if the new_password is atleast 4 characters long if len(new_password) < 4: error_message += " New password must be atleast 4 characters long!" ## Check if the new_password and the confirmation of new password (confirm_new_password) are same if new_password != confirm_new_password: error_message += " The entered new password is not same as the confirmation password!" db = get_db() row = db.execute('SELECT password FROM user WHERE username = ?', (username, )).fetchone() old_password_hash_object = hashlib.sha256((password).encode('utf-8')) old_password_hash = old_password_hash_object.hexdigest() if old_password_hash != row[0]: error_message = "Incorrect Current Password!" if error_message != "": ## Return Failure return None, error_message new_password_hash_object = hashlib.sha256(new_password.encode('utf-8')) new_password_hash = new_password_hash_object.hexdigest() try: db.execute('UPDATE user SET password=? WHERE username=?', (new_password_hash, username)) db.commit() return "Successfully changed the password", None except Exception as e: app.logger.error("Exception occurred : {}".format(str(e))) app.logger.error("rolling back DB") db.rollback() app.logger.error("Error occurred while adding user, DB rolled-back") raise error_handler.DatabaseError(str(e))
def get_users() -> pd.DataFrame: try: db = get_db() df_user = pd.read_sql_query("SELECT * FROM user", db) del df_user['password'] annotated_amount = [len(row.split()) for row in df_user['annotated']] df_user['annotated_amount'] = annotated_amount return df_user except Exception as e: app.logger.error("Database Error:" + str(e)) raise error_handler.DatabaseError(str(e))
def change_password_admin(username, password, confirm_password, admin_username, admin_password): """ Function that changes the user login password through an admin user. Databse is updated upon password change. Params: username: Input, username of the user whose password is desired to be changed. type: str password: Input, password (plain text) for the username. type: str (Should be atleast 4 chars) confirm_password: Input, confirm password (plain text) for the username. type: str admin_username: Input, username of the admin whose is changeing the password fot the user. type: str admin_password: Input, password (plain text) for the admin_username. type: str (Should be atleast 4 chars) returns: 2 params: "Success message"(or None), "Failure message"(or None) """ app.logger.debug("from change_password_admin()") db = get_db() error_message = "" if password != confirm_password: error_message += " password and confirmation password are not the same" if len(password) < 4: error_message += " New password must be atleast 4 characters long!" ## Authenticate admin password row = db.execute('SELECT password FROM user WHERE username = ?', (admin_username, )).fetchone() admin_password_hash_object = hashlib.sha256(admin_password.encode('utf-8')) admin_password_hash = admin_password_hash_object.hexdigest() if admin_password_hash != row[0]: error_message += " Incorrect Password" if error_message != "": return None, error_message password_hash_object = hashlib.sha256(password.encode('utf-8')) password_hash = password_hash_object.hexdigest() try: db.execute('UPDATE user SET password=? WHERE username=?', (password_hash, username)) db.commit() return "Successfully changed password for user " + username, None except Exception as e: app.logger.error("Exception occurred : {}".format(str(e))) app.logger.error("rolling back DB") db.rollback() app.logger.error("Error occurred while adding user, DB rolled-back") raise error_handler.DatabaseError(str(e))
def deactivate_user(username): """ Function that deactivates 'active' user. Params: username: Input, username of the user. type: str returns: 2 params: "Success message"(or None), "Failure message"(or None) """ app.logger.debug("from deactivate_user()") db = get_db() try: db.execute('UPDATE user SET is_approved=? WHERE username=?',("no",username)) db.commit() return "Successfully deactivated the user "+username,None except Exception as e: app.logger.error("Exception occurred : {}".format(str(e))) app.logger.error("rolling back DB") db.rollback() app.logger.error("Error occurred while adding user, DB rolled-back") raise error_handler.DatabaseError(str(e))
def write_to_db(self, **kwargs): # print('write to db', self.annotations) # self.to_start() # return data = self.annotations # copy of write to db in routes.py if not data: return "No Annotations" else: data['user_id'] = current_user.get_id() if str(self.annotations['data_id']) in current_user.get_annotated( ).split(): raise error_handler.DatabaseError("Already annotated", 500) # try: db = get_db() cursor = db.execute('select * from annotations') allowed_columns = [d[0] for d in cursor.description] for key, v in list(data.items()): if key not in allowed_columns: del data[key] else: data[key] = str(v) db.execute( 'INSERT INTO annotations ({0}) VALUES ({1})'.format( ', '.join(('"' + str(key) + '"' for key in data)), ', '.join(('?' for key in data))), tuple((data[key]) for key in data)) db.execute( 'UPDATE user set annotated = ? WHERE id = ?', (" ".join([current_user.get_annotated(), str(data['data_id'])]), current_user.get_id())) # Unconditionally set. If the user completed an annotation, it was current_annotation. db.execute("UPDATE user SET current_annotation = 0 WHERE id = ?", (current_user.get_id(), )) db.commit() # TODO comment in again # transition to start self.to_start()
def data_download(): """ Download data in database after converting it to csv/tsv. (Downloading of the data to be restricted to the admin.) """ app.logger.debug("Requested dowload page") ## Verify if the logged in user is admin if not current_user.admin: app.logger.info("data_downloaded requested by non-admin user") return render_template('login.html', error="login as admin to proceed") if request.method == 'POST': try: os.makedirs('download_data/', exist_ok=False) except Exception as e: app.logger.info("Folder already exists") try: db = get_db() df = pd.read_sql_query("SELECT * FROM annotations", db) df.to_csv('download_data/annotations.csv', index_label='index', sep=";") df = pd.read_sql_query("SELECT * FROM user", db) df.to_csv('download_data/user.csv', index_label='index', sep=";") df = pd.read_sql_query("SELECT * FROM data", db) df.to_csv('download_data/data.csv', index_label='index', sep=";") except Exception as e: app.logger.error("Database Error:" + str(e)) raise error_handler.DatabaseError(str(e)) try: annotation_df = pd.read_csv('download_data/annotations.csv', sep=';', encoding='utf-8', header=0) users_df = pd.read_csv('download_data/user.csv', sep=';', encoding='utf-8', header=0) data_df = pd.read_csv('download_data/data.csv', sep=';', encoding='utf-8', header=0) users_dict = pd.Series(users_df.given_name.values, index=users_df.id).to_dict() comments_dict = pd.Series(data_df.content.values, index=data_df.id).to_dict() annotation_df.insert(loc=1, column='Name', value=annotation_df['user'].map(users_dict)) annotation_df.insert(loc=1, column='comment', value=annotation_df['id'].map(comments_dict)) annotation_df = annotation_df.sort_values(by=['id', 'Name']) annotation_df.drop(['index', 'user'], inplace=True, axis=1) annotation_df.to_excel( 'download_data/Annotation_comparisions.xlsx') return send_file('../download_data/Annotation_comparisions.xlsx', as_attachment=True) except Exception as e: app.logger.error("Error:" + str(e)) raise error_handler.UnknownError(str(e)) return render_template('data_console.html', user=current_user.fname, admin=current_user.admin)