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)
