def update_user():
connection = mysql.connect()
cursor = connection.cursor()
try:
# ----REQUEST-------
id = request.headers.get('id')
name = request.headers.get('name')
key_value = request.headers.get('key_value')
status = request.headers.get('status')
# ----UPDATE------
sql = "UPDATE users SET name=%s, key_value=%s, status=%s WHERE id=%s"
data = (name, key_value, status, id)
connection = mysql.connect()
cursor = connection.cursor()
cursor.execute(sql, data)
connection.commit()
# --- RESPONSE ----
response = jsonify('User updated successfully!')
response.status_code = 200
return response
except Exception as e:
print(e)
finally:
cursor.close()
connection.close()
def login_submit():
conn = None
cursor = None
_email = request.form['inputEmail']
_password = request.form['inputPassword']
_remember = request.form.getlist('inputRemember')
if 'email' in request.cookies:
username = request.cookies.get('email')
password = request.cookies.get('pwd')
conn = mysql.connect()
cursor = conn.cursor()
sql = "SELECT * FROM user WHERE user_email=%s"
sql_where = (username, )
cursor.execute(sql, sql_where)
row = cursor.fetchone()
if row and check_password_hash(row[3], password):
print(username + ' ' + password)
session['email'] = row[2]
cursor.close()
conn.close()
return redirect('/')
else:
return redirect('/login')
# validate the received values
elif _email and _password:
#check user exists
conn = mysql.connect()
cursor = conn.cursor()
sql = "SELECT * FROM user WHERE user_email=%s"
sql_where = (_email, )
cursor.execute(sql, sql_where)
row = cursor.fetchone()
if row:
if check_password_hash(row[3], _password):
session['email'] = row[2]
cursor.close()
conn.close()
if _remember:
resp = make_response(redirect('/'))
resp.set_cookie('email', row[2], max_age=COOKIE_TIME_OUT)
resp.set_cookie('pwd', _password, max_age=COOKIE_TIME_OUT)
resp.set_cookie('rem', 'checked', max_age=COOKIE_TIME_OUT)
return resp
return redirect('/')
else:
flash('Invalid password!')
return redirect('/login')
else:
flash('Invalid email/password!')
return redirect('/login')
else:
flash('Invalid email/password!')
return redirect('/login')
def get(self):
"""
Get all the tags in the database.
"""
# Validate the user
permission, user_id = validate_permissions()
if not permission or not user_id:
return errorMessage("Invalid user"), 401
try:
conn = mysql.connect()
cursor = conn.cursor()
query = "SELECT `tagName` FROM `tag`"
tags_from_db = getFromDB(query, None, conn, cursor)
tags = {"tags": []}
if tags_from_db and tags_from_db[0]:
for tag in tags_from_db:
if tag[0].lower() not in tags['tags']:
tags['tags'].append(tag[0].lower())
raise ReturnSuccess(tags, 200)
except ReturnSuccess as success:
conn.commit()
return success.msg, success.returnCode
except Exception as error:
conn.rollback()
return errorMessage(str(error)), 500
finally:
if (conn.open):
cursor.close()
conn.close()
def delete(self):
data = {}
data['questionID'] = getParameter("questionID", str, True, "")
data['termID'] = getParameter("termID", str, True, "")
data['groupID'] = getParameter("groupID", str, False,
"groupID is required if student is TA")
permission, user_id = validate_permissions()
if not permission or not user_id:
return errorMessage("Invalid user"), 401
if permission == 'st' and not is_ta(user_id, data['groupID']):
return errorMessagE("User not authorized to delete answers."), 400
try:
conn = mysql.connect()
cursor = conn.cursor()
query = "DELETE FROM `answer` WHERE `questionID`= %s AND `termID` = %s"
postToDB(query, (data['questionID'], data['termID']), conn, cursor)
raise ReturnSuccess("Deleted Answer", 201)
except CustomException as error:
conn.rollback()
return error.msg, error.returnCode
except ReturnSuccess as success:
conn.commit()
return success.msg, success.returnCode
except Exception as error:
conn.rollback()
return errorMessage(str(error)), 500
finally:
if (conn.open):
cursor.close()
conn.close()
def get(self):
permission, user_id = validate_permissions()
if not permission or not user_id:
return errorMessage("Invalid user"), 401
try:
conn = mysql.connect()
cursor = conn.cursor()
otc = otcGenerator()
query = "UPDATE `user` SET `otc`= %s WHERE `userID`= %s"
results = postToDB(query, (otc, user_id), conn, cursor)
raise ReturnSuccess({"otc": otc}, 200)
except CustomException as error:
conn.rollback()
return error.msg, error.returnCode
except ReturnSuccess as success:
conn.commit()
return success.msg, success.returnCode
except Exception as error:
conn.rollback()
return errorMessage(str(error)), 500
finally:
if (conn.open):
cursor.close()
conn.close()
def add_user():
try:
_json = request.json
_name = _json['name']
_email = _json['email']
_password = _json['password']
if _name and _email and _password and request.method == 'POST':
_hashed_password = generate_password_hash(_password)
sql = "INSERT INTO user(name, email, password) VALUES(%s, %s, %s)"
data = (
_name,
_email,
_hashed_password,
)
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sql, data)
conn.commit()
resp = jsonify('User created successfully!')
resp.status_code = 200
return resp
else:
return not_found()
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def update_user():
try:
_json = request.json
_id = _json['id']
_name = _json['name']
_email = _json['email']
_password = _json['password']
if _name and _email and _password and _id and request.method == 'POST':
_hashed_password = generate_password_hash(_password)
sql = "UPDATE user SET name=%s, email=%s, password=%s WHERE id=%s"
data = (
_name,
_email,
_hashed_password,
_id,
)
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sql, data)
conn.commit()
resp = jsonify('User updated successfully!')
resp.status_code = 200
return resp
else:
return not_found()
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def post(self):
data = {}
data['questionID'] = getParameter("questionID", str, False, "")
data['termID'] = getParameter("termID", str, True, "")
data['groupID'] = getParameter("groupID", str, False, "groupID")
permission, user_id = validate_permissions()
if not permission or not user_id:
return errorMessage("Invalid user"), 401
if permission == 'st' and not is_ta(user_id, data['groupID']):
return errorMessage("User not authorized to answers."), 400
try:
conn = mysql.connect()
cursor = conn.cursor()
query = "INSERT INTO answer (`questionID`, `termID`) VALUES (%s, %s)"
postToDB(query, (data['questionID'], data['termID']), conn, cursor)
raise ReturnSuccess("Successfully added answer!", 201)
except CustomException as error:
conn.rollback()
return error.msg, error.returnCode
except ReturnSuccess as success:
conn.commit()
return success.msg, success.returnCode
except Exception as error:
conn.rollback()
return errorMessage(str(error)), 500
finally:
if (conn.open):
cursor.close()
conn.close()
def add_user():
try:
_name = request.form.getlist('username')[0]
_email = request.form.getlist('email')[0]
_password = request.form.getlist('password')[0]
print(_password)
# validate the received values
if _name and _email and _password and request.method == 'POST':
#do not save password as a plain text
_hashed_password = generate_password_hash(_password)
# save edits
sql = "INSERT INTO user(username, email, password) VALUES(%s, %s, %s)"
data = (_name, _email, _hashed_password)
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sql, data)
conn.commit()
resp = jsonify('User added successfully!')
resp.status_code = 200
return resp
else:
return not_found()
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def check_user():
try:
_username = request.form.getlist('username')[0]
if _username and request.method == 'POST':
sql = "SELECT username FROM user WHERE BINARY username = %s"
data = (_username)
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sql, data)
rows = cursor.fetchall()
conn.commit()
# print(len(rows))
if rows:
resp = jsonify(valid=False)
else:
resp = jsonify(valid=True)
resp.status_code = 200
return resp
else:
return not_found()
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def users():
try:
connection = mysql.connect()
cursor = connection.cursor()
cursor.execute("SELECT * FROM users")
records = cursor.fetchall()
# Request
print("Request Headers:")
header_1 = request.headers.get('Header_from_request_1')
header_2 = request.headers.get('Header_from_request_2')
print(header_1)
print(header_2)
print(request.headers)
# Response
response = jsonify(records)
response.status_code = 200
response.headers[
'Response Header 1'] = 'This is One Custume Header from response'
response.headers[
'Response Header 2'] = 'This is Another Custume Header from response'
print(response.headers)
return response
except Exception as e:
print(e)
finally:
cursor.close()
connection.close()
def get(self):
permission, user_id = validate_permissions()
if not permission or not user_id:
return errorMessage("Invalid user"), 401
try:
conn = mysql.connect()
cursor = conn.cursor()
query = "SELECT `username` FROM `user`"
results = getFromDB(query, None, conn, cursor)
usernames = []
for username in results:
usernames.append(username[0])
raise ReturnSuccess(usernames, 201)
except CustomException as error:
conn.rollback()
return error.msg, error.returnCode
except ReturnSuccess as success:
conn.commit()
return success.msg, success.returnCode
except Exception as error:
conn.rollback()
return errorMessage(str(error)), 500
finally:
if (conn.open):
cursor.close()
conn.close()
def put(self):
permission, user_id = validate_permissions()
if not permission or not user_id:
return errorMessage("Invalid user"), 401
data = {}
data['newEmail'] = getParameter("newEmail", str, True, "")
try:
conn = mysql.connect()
cursor = conn.cursor()
if data['newEmail'] == '':
data['newEmail'] = None
update_email_query = "UPDATE `user` SET `email` = %s WHERE `user`.`userID` = %s"
postToDB(update_email_query, (data['newEmail'], user_id), conn,
cursor)
raise ReturnSuccess("Successfully changed email", 200)
except CustomException as error:
conn.rollback()
return error.msg, error.returnCode
except ReturnSuccess as success:
conn.commit()
return success.msg, success.returnCode
except Exception as error:
conn.rollback()
return errorMessage(str(error)), 500
finally:
if (conn.open):
cursor.close()
conn.close()
def get(self):
permission, user_id = validate_permissions()
if not permission or not user_id:
return errorMessage("Invalid user"), 401
try:
conn = mysql.connect()
cursor = conn.cursor()
query = "SELECT `group`.groupID, `group`.groupName, `group_user`.accessLevel \
FROM `group_user` \
INNER JOIN `group` \
ON `group`.groupId = `group_user`.groupID \
WHERE `group_user`.userID= %s"
results = getFromDB(query, user_id, conn, cursor)
userLevels = []
for userLevel in results:
userLevels.append(convertUserLevelsToJSON(userLevel))
raise ReturnSuccess(userLevels, 201)
except CustomException as error:
conn.rollback()
return error.msg, error.returnCode
except ReturnSuccess as success:
conn.commit()
return success.msg, success.returnCode
except Exception as error:
conn.rollback()
return errorMessage(str(error)), 500
finally:
if (conn.open):
cursor.close()
conn.close()
def get(self):
permission, user_id = validate_permissions()
if not permission or not user_id:
return errorMessage("Invalid user"), 401
try:
conn = mysql.connect()
cursor = conn.cursor()
query = "SELECT * FROM `user` WHERE `userID` = %s"
result = getFromDB(query, user_id, conn, cursor)
for row in result:
newUserObject = {}
newUserObject['id'] = row[0]
newUserObject['username'] = row[1]
newUserObject['permissionGroup'] = row[4]
newUserObject['email'] = row[6]
raise ReturnSuccess(newUserObject, 200)
except CustomException as error:
conn.rollback()
return error.msg, error.returnCode
except ReturnSuccess as success:
conn.commit()
return success.msg, success.returnCode
except Exception as error:
conn.rollback()
return errorMessage(str(error)), 500
finally:
if (conn.open):
cursor.close()
conn.close()
def delete(self):
data = {}
data['groupID'] = getParameter("groupID", str, True, "")
permission, user_id = validate_permissions()
if not permission or not user_id:
return errorMessage("Invalid user"), 401
try:
conn = mysql.connect()
cursor = conn.cursor()
# Only professors and superadmins can delete groups
if permission == 'st':
raise CustomException("Invalid permissions.", 400)
query = "DELETE FROM `group` WHERE `groupID` = %s"
deleteFromDB(query, data['groupID'], conn, cursor)
raise ReturnSuccess("Successfully deleted group.", 200)
except CustomException as error:
conn.rollback()
return error.msg, error.returnCode
except ReturnSuccess as success:
conn.commit()
return success.msg, success.returnCode
except Exception as error:
conn.rollback()
return errorMessage(str(error)), 500
finally:
if (conn.open):
cursor.close()
conn.close()
def add_blog():
try:
_username = request.form.getlist('username')[0]
_text = request.form.getlist('text')[0]
_title = request.form.getlist('title')[0]
_date = getLocalTime()
# print(_username)
# validate the received values
if _username and _text and _date and _title and request.method == 'POST':
# save edits
sql = "INSERT INTO blog(username, blogtext, datewritten, title) VALUES(%s, %s, %s, %s)"
data = (_username, _text, _date, _title)
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sql, data)
conn.commit()
resp = jsonify(status='Blog added successfully!')
resp.status_code = 200
return resp
else:
return not_found()
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def post(self):
logger.debug('POST: coupon')
try:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
data = request.get_json()
couponQuery = "INSERT INTO coupon( code, description, valid_from, valid_to, sub_id, per_user, per_sub, status) VALUES(%s, %s, %s, %s, %s, %s, %s, %s)"
couponValues = (data["code"], data["description"],
data["valid_from"], data["valid_to"],
data["sub_id"], data["per_user"], data["per_sub"],
data["status"])
cursor.execute(couponQuery, couponValues)
coupon_id = cursor.lastrowid
discountQuery = 'INSERT INTO discount(coupon_id, product_id, duration, amount, type) VALUES(%s, %s, %s, %s, %s)'
for product in data["products"]:
discountValues = (coupon_id, product["product_id"],
product["duration"], product["amount"],
product["type"])
cursor.execute(discountQuery, discountValues)
conn.commit()
response = jsonify({"message": "Coupon created successfully"})
response.status_code = 200
return response
except Exception as e:
print(e)
response = jsonify({"error": "Coupon creation failed"})
response.status_code = 404
return response
finally:
cursor.close()
conn.close()
def get(self):
permission, user_id = validate_permissions()
if not permission or not user_id:
return errorMessage("Invalid user"), 401
try:
conn = mysql.connect()
cursor = conn.cursor()
query = "SELECT * from `user_preferences` WHERE `userID` = %s"
user_preference = getFromDB(query, user_id, conn, cursor)
if not user_preference or not user_preference[0]:
return errorMessage("An error occured"), 500
raise ReturnSuccess(userPreferencesToJSON(user_preference[0]), 200)
except CustomException as error:
conn.rollback()
return error.msg, error.returnCode
except ReturnSuccess as success:
conn.commit()
return success.msg, success.returnCode
except Exception as error:
conn.rollback()
return errorMessage(str(error)), 500
finally:
if (conn.open):
cursor.close()
conn.close()
def get(self):
"""Get a specific session log and all the logged answers associated with it"""
sessionID = getParameter(
"sessionID", str, True,
"ID of session needed to retrieve is required")
# Validate the user
permission, user_id = validate_permissions()
if not permission or not user_id:
return errorMessage("Invalid user"), 401
try:
conn = mysql.connect()
cursor = conn.cursor()
sessionData = {"session": [], "logged_answers": []}
query = """SELECT `session`.*, `module`.`name` FROM `session`
INNER JOIN `module` ON `module`.`moduleID` = `session`.`moduleID` WHERE
`session`.`sessionID` = %s"""
results = getFromDB(query, sessionID, conn, cursor)
if results and results[0]:
sessionData['session'].append(convertSessionsToJSON(
results[0]))
if permission == 'st' and sessionData['session'][
'userID'] != user_id:
raise CustomException(
"Unauthorized to access this session", 400)
else:
raise CustomException("No sessions found for the given ID",
400)
query = "SELECT * FROM `logged_answer` WHERE `sessionID` = %s"
results = getFromDB(query, sessionID, conn, cursor)
if results and results[0]:
for log in results:
record = {
'logID': log[0],
'questionID': log[1],
'termID': log[2],
'sessionID': log[3],
'correct': log[4],
'mode': log[5]
}
sessionData['logged_answers'].append(record)
raise ReturnSuccess(sessionData, 200)
except ReturnSuccess as success:
conn.commit()
return success.msg, success.returnCode
except CustomException as error:
conn.rollback()
return error.msg, error.returnCode
except Exception as error:
conn.rollback()
return errorMessage(str(error)), 500
finally:
if (conn.open):
cursor.close()
conn.close()
def db_query(query = None, params = ()):
connection = mysql.connect()
cursor = connection.cursor(pymysql.cursors.DictCursor)
cursor.execute(query, params)
connection.commit() # equivalent of saving database changes
cursor.close()
connection.close()
return
def delete(self):
data = {}
data['questionID'] = getParameter("questionID", str, True, "")
data['groupID'] = getParameter(
"groupID", str, False, "groupID is required if student is a TA")
permission, user_id = validate_permissions()
if not permission or not user_id:
return errorMessage("Invalid user"), 401
if permission == 'st' and not is_ta(user_id, data['groupID']):
return errorMessage(
"User not authorized to delete questions."), 400
try:
conn = mysql.connect()
cursor = conn.cursor()
if (find_question(int(data['questionID']))):
question_query = "SELECT * FROM `question` WHERE `questionID` = %s"
question_data = getFromDB(question_query, data['questionID'],
conn, cursor)
delete_query = "INSERT INTO `deleted_question` (`questionID`, `audioID`, `imageID`, `type`, `questionText`) VALUES (%s, %s, %s, %s, %s)"
postToDB(delete_query,
(question_data[0][0], question_data[0][1],
question_data[0][2], question_data[0][3],
question_data[0][4]))
la_query = "SELECT `logID` FROM `logged_answer` WHERE `questionID` = %s"
la_results = getFromDB(la_query, question_data[0][0], conn,
cursor)
for log in la_results:
log_query = "UPDATE `logged_answer` SET `questionID` = %s, `deleted_questionID` = %s WHERE `logID` = %s"
postToDB(log_query, (None, question_data[0][0], log[0]),
None, conn, cursor)
query = "DELETE FROM `question` WHERE `questionID` = %s"
deleteFromDB(query, data['questionID'], conn, cursor)
raise ReturnSuccess(
"Successfully deleted question and answer set!", 201)
else:
raise CustomException("No question with that ID exist!", 201)
except CustomException as error:
conn.rollback()
return error.msg, error.returnCode
except ReturnSuccess as success:
conn.commit()
return success.msg, success.returnCode
except Exception as error:
conn.rollback()
return errorMessage(str(error)), 500
finally:
if (conn.open):
cursor.close()
conn.close()
def fetch(query = None):
connection = mysql.connect()
cursor = connection.cursor(pymysql.cursors.DictCursor)
cursor.execute(query)
connection.commit() # equivalent of saving database changes
dictionary = cursor.fetchall()
cursor.close()
connection.close()
return dictionary
def post(self):
data = {}
data['groupName'] = getParameter("groupName", str, True, "")
permission, user_id = validate_permissions()
if not permission or not user_id:
return errorMessage("Invalid user"), 401
try:
conn = mysql.connect()
cursor = conn.cursor()
if permission != 'pf':
raise CustomException("User cannot create classes.", 400)
# Checks if the groupName already exists
dupe_query = "SELECT `groupID` FROM `group` WHERE `groupName`= %s"
dupe_results = getFromDB(dupe_query, data['groupName'], conn,
cursor)
if dupe_results:
raise CustomException("groupName already exists.", 400)
else:
# Randomly generate 6-long string of numbers and letters
# String must be unique for each class
group_code = groupCodeGenerator()
gc_query = "SELECT `groupID` FROM `group` WHERE `groupCode`= %s"
gc_results = getFromDB(gc_query, group_code, conn, cursor)
if gc_results:
raise CustomException("groupCode already exists", 400)
query = "INSERT INTO `group` (`groupName`, `groupCode`) VALUES (%s, %s)"
postToDB(query, (data['groupName'], group_code), conn, cursor)
g_query = "SELECT `groupID` FROM `group` WHERE `groupName`= %s"
g_results = getFromDB(g_query, data['groupName'], conn, cursor)
group_id = g_results[0][0]
# Users who creates a class have their accesLevel default to 'pf'
gu_query = "INSERT INTO `group_user` (`userID`, `groupID`, `accessLevel`) VALUES (%s, %s, %s)"
postToDB(gu_query, (user_id, group_id, 'pf'), conn, cursor)
raise ReturnSuccess("Successfully created the class.", 200)
except CustomException as error:
conn.rollback()
return error.msg, error.returnCode
except ReturnSuccess as success:
conn.commit()
return success.msg, success.returnCode
except Exception as error:
conn.rollback()
return errorMessage(str(error)), 500
finally:
if (conn.open):
cursor.close()
conn.close()
def get(self):
permission, user_id = validate_permissions()
if not permission or not user_id:
return errorMessage("Invalid user"), 401
try:
conn = mysql.connect()
cursor = conn.cursor()
# Get the group's information and append the user's accessLevel in the group
query = "SELECT `group`.*, `group_user`.`accessLevel` \
FROM `group` \
INNER JOIN `group_user` \
ON `group_user`.`groupID` = `group`.`groupID` \
WHERE `group_user`.`userID` = %s"
results = getFromDB(query, user_id, conn, cursor)
# Get all users in the group
get_group_users_query = "SELECT `user`.`userID`, `user`.`username`, `group_user`.`accessLevel` \
FROM `group_user` \
INNER JOIN `user` \
ON `user`.`userID` = `group_user`.`userID` \
WHERE `group_user`.`groupID` = %s"
groups = []
if results and results[0]:
for group in results:
group_obj = convertGroupsToJSON(group)
if permission == 'pf' or permission == 'su':
group_users = []
group_users_from_db = getFromDB(
get_group_users_query, group_obj['groupID'], conn,
cursor)
for indv_group_user in group_users_from_db:
group_users.append(
convertUsersToJSON(indv_group_user))
group_obj['group_users'] = group_users
groups.append(group_obj)
raise ReturnSuccess(groups, 200)
except CustomException as error:
conn.rollback()
return error.msg, error.returnCode
except ReturnSuccess as success:
conn.commit()
return success.msg, success.returnCode
except Exception as error:
conn.rollback()
return errorMessage(str(error)), 500
finally:
if (conn.open):
cursor.close()
conn.close()
def cannot_remove_genre(id, error):
# This first query returns only the genre name.
connection = mysql.connect()
cursor = connection.cursor(pymysql.cursors.DictCursor)
select_stmt = "select genre.genre_id, genre.genre_name from Genres genre where genre.genre_id = " + id
cursor.execute(select_stmt)
genre_name= cursor.fetchall()
cursor.close()
connection.close()
# Second query finds any books in that genre
connection = mysql.connect()
cursor = connection.cursor(pymysql.cursors.DictCursor)
select_stmt = "select book.isbn, book.book_title from Books book join Genres_Books gb on gb.isbn = book.isbn join Genres genre on genre.genre_id = gb.genre_id where genre.genre_id = " + id
cursor.execute(select_stmt)
books_result = cursor.fetchall()
cursor.close()
connection.close()
return render_template('genre.html', genreinfo=genre_name, books=books_result, error=error)
def Delete_Term_Associations(term_id,
questionID=None,
given_conn=None,
given_cursor=None):
"""
Checks how many Answer records are associated with this term and deletes those answer records
Note: This function is called from delete method of Term.
And before deleting, it checks if the answer record's questionID is associated with only the term being deleted
If so, deletes the question as well
"""
try:
conn = mysql.connect() if given_conn == None else given_conn
cursor = conn.cursor() if given_cursor == None else given_cursor
deleteAnswerQuery = "DELETE FROM `answer` WHERE `questionID` = %s AND `termID` = %s"
deleteQuestionQuery = "DELETE FROM `question` WHERE `questionID` = %s"
getAssociatedQuestions = "SELECT * FROM `answer` WHERE `questionID` = %s"
query = "SELECT * FROM `answer` WHERE `termID` = %s"
answerRecords = getFromDB(query, term_id, conn, cursor)
for answer in answerRecords:
if answer:
questionInAnswers = getFromDB(getAssociatedQuestions,
str(answer[0]), conn, cursor)
if questionInAnswers and questionInAnswers[0] and len(
questionInAnswers) <= 1:
if questionInAnswers[0][1] != answer[1]:
raise CustomException(
"Something went wrong in the logic of deleting a term",
500)
deleteFromDB(deleteQuestionQuery, str(answer[0]))
deleteFromDB(deleteAnswerQuery,
(str(answer[0]), str(answer[1])), conn, cursor)
raise ReturnSuccess("Successfully deleted associated answer records",
200)
except CustomException as error:
if given_conn == None:
conn.rollback()
return 0
except ReturnSuccess as success:
if given_conn == None:
conn.commit()
return 1
except Exception as error:
if given_conn == None:
conn.rollback()
return 0
finally:
if (given_conn == None and conn.open):
cursor.close()
conn.close()
def download_report():
conn = None
cursor = None
try:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute(
"SELECT emp_id, emp_first_name, emp_last_name, emp_designation FROM employee"
)
result = cursor.fetchall()
pdf = FPDF()
pdf.add_page()
page_width = pdf.w - 2 * pdf.l_margin
pdf.set_font('Times', 'B', 14.0)
pdf.cell(page_width, 0.0, 'Employee Data', align='C')
pdf.ln(10)
pdf.set_font('Courier', '', 12)
col_width = page_width / 4
pdf.ln(1)
th = pdf.font_size
for row in result:
pdf.cell(col_width, th, str(row['emp_id']), border=1)
pdf.cell(col_width, th, row['emp_first_name'], border=1)
pdf.cell(col_width, th, row['emp_last_name'], border=1)
pdf.cell(col_width, th, row['emp_designation'], border=1)
pdf.ln(th)
pdf.ln(10)
pdf.set_font('Times', '', 10.0)
pdf.cell(page_width, 0.0, '- end of report -', align='C')
# pdf.output('employee.pdf', 'F')
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
return Response(pdf.output(dest='S').encode('latin-1'),
mimetype='application/pdf',
headers={
'Content-Disposition':
'attachment;filename=employee_report.pdf'
})
def get_one_from_db(query, vals=None):
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(query, vals) if vals else cursor.execute(query)
#result = cursor.fetchone()
result = []
for row in cursor:
result.append(list(row))
conn.commit()
conn.close()
return result
def users():
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute("SELECT * FROM user where id = 1")
rows = cursor.fetchall()
resp = jsonify(rows)
resp.status_code = 200
return resp
