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 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)
def upload_file():
"""
Function to upload the annotation file to the tool.
(Uploading of the file to be restricted to the admin.)
"""
app.logger.debug("Requested file upload page")
## Verify if the logged in user is admin
if not current_user.admin:
app.logger.info("upload_file requested by non-admin user")
return render_template('login.html', error="login as admin to proceed")
if request.method == 'POST':
files = request.files.getlist('files[]')
app.logger.info("Files: " + str(files))
## Handling Zero inputs
if len(files) == 1:
if files[0].filename == '':
app.logger.info("Submitted 0 (zero) files")
return render_template('data_console.html',
error=str("Upload atleast one file"),
user=current_user.fname,
admin=current_user.admin)
# Save the files on the server.
for file_ in files:
file_.save('uploaded_files/' + secure_filename(file_.filename))
# Process files and save in DB
db = get_db()
failed_files = ""
success_files = 0
try:
id_list = db.execute('SELECT id FROM data').fetchall()
init_id = 0
for i in id_list:
if int(i[0]) > init_id:
init_id = int(i[0])
app.logger.debug(
'from: upload_file: init_id:{}; len_list:{}'.format(
init_id, len(id_list)))
except Exception as e:
app.logger.critical(
'Exception occurred while processing the database while uploading the file:'
+ str(e))
raise error_handler.UnknownError(e)
for file_ in files:
try:
df = pd.read_csv('uploaded_files/' +
secure_filename(file_.filename),
sep='\t',
header=0,
names=['content', 'context', 'meta'])
for index, row in df.iterrows():
init_id += 1
db.execute(
'INSERT INTO data (id, content, context, meta) VALUES (?, ?, ?, ?)',
(init_id, row['content'], row['context'], row['meta']))
db.commit()
success_files += 1
except Exception as e:
failed_files += file_.filename + " "
app.logger.error(
"Exception occurred while processing the database while uploading the file:"
+ str(e))
if failed_files != "":
app.logger.error("Failed to upload one or more files:" +
str(failed_files))
if success_files == 0:
return render_template(
'data_console.html',
error=str("Error uploading the files: " + failed_files),
user=current_user.fname,
admin=current_user.admin)
else:
return render_template(
'data_console.html',
error=str("Error uploading the files: " + failed_files),
success=str(success_files) +
" files uploaded successfully",
user=current_user.fname,
admin=current_user.admin)
app.logger.info("Files uploaded successfully:" + str(success_files))
return render_template('data_console.html',
success=str(success_files) +
" files uploaded successfully",
user=current_user.fname,
admin=current_user.admin)
else:
app.logger.debug("from upload_file current User:{}".format(
current_user.fname))
return render_template('data_console.html',
user=current_user.fname,
admin=current_user.admin)