def vote(type_, direction, id, answer_id=0): if type_ == "question": query = "SELECT vote_number FROM {0} WHERE id={1}".format(type_, id) elif type_ == "answer": query = "SELECT vote_number FROM {0} WHERE id={2} AND question_id = {1}".format( type_, id, answer_id) else: pass updated_number = 0 vote_num = data_manager.run_query(query) if direction == "vote-up" and vote_num: updated_number = vote_num[0][0] + 1 elif direction == "vote-down" and vote_num: updated_number = vote_num[0][0] - 1 else: pass if type_ == "question": update_query = """UPDATE {0} SET vote_number = {1} WHERE id = {2}""".format( type_, updated_number, id) elif type_ == "answer": update_query = """UPDATE {0} SET vote_number = {1} WHERE id = {3} AND question_id = {2}""".format(type_, updated_number, id, answer_id) else: pass data_manager.run_query(update_query)
def add_new_question(): """Add new story to list, then redirect to /list page""" title = request.form["title"] message = request.form["message"] username = request.form["username"] query = """SELECT id From users WHERE name='{}';""".format(username, ) users_id = data_manager.run_query(query)[0][0] columns = ('title', 'message', 'users_id') values = (title, message, users_id) data_manager.safe_insert('question', columns, values) query = "SELECT id FROM question WHERE title='{0}';".format(title) question_id = data_manager.run_query(query) selected_tag_name = [] tag_names = common.tag_names() lit_of_tag_names = [i[0] for i in tag_names] for request_string in request.form: if request_string in lit_of_tag_names: selected_tag_name.append(request_string) ids = [] for name in selected_tag_name: ids.append(common.id_of_tag_where_name_is(name)[0][0]) for tag_id in ids: common.update_tag(tag_id, question_id[0][0]) return redirect("/question/" + str(question_id[0][0]))
def edit_question(question_id): """Update question in the database, then go back to question's page""" title = request.form["title"] message = request.form["message"] query = "UPDATE question SET title = '{0}', message = '{1}' WHERE id = '{2}';".format( title, message, question_id) data_manager.run_query(query) selected_tag_name = [] tag_names = common.tag_names() lit_of_tag_names = [i[0] for i in tag_names] for request_string in request.form: if request_string in lit_of_tag_names: selected_tag_name.append(request_string) ids = [] for name in selected_tag_name: ids.append(common.id_of_tag_where_name_is(name)[0][0]) for tag_id in ids: common.delete_edit_tag(question_id) for tag_id in ids: common.update_tag(tag_id, question_id) return redirect("/question/" + question_id)
def delete_comment(comment_id): """ routed to url: /comments/<comment_id>/delete Deletes seleted comment from database """ question_id = get_question_for_comment(comment_id) sql_query = "DELETE FROM comment WHERE id={}".format(int(comment_id)) data_manager.run_query(sql_query) return redirect('/question/' + question_id)
def update(table, id, column, value): """ Build an update sql query in the format: UPDATE {table} SET {column}='{value}' WHERE id='{id}'; and call it. """ query = "UPDATE {0} SET {1}='{2}' WHERE id='{3}';".format(table, column, value, id) data_manager.run_query(query) return
def delete(table, id): """ Build a delete sql query in the format: DELETE FROM {table} WHERE id={id}; and call it. """ query = "DELETE FROM {0} WHERE id={1};".format(table, id) data_manager.run_query(query) return
def get_question_for_comment(comment_id): """ Utility function, gets the appropriate question_id for a comment_id """ sql_query = "SELECT question_id, answer_id FROM comment WHERE id={}".format( int(comment_id)) ids = data_manager.run_query(sql_query)[0] question_id = ids[0] answer_id = ids[1] if question_id is None: question_id = data_manager.run_query( "SELECT question_id FROM answer WHERE id={}".format( int(answer_id)))[0][0] return str(question_id)
def print_table(): """ Based on sorting priorities stored in the query string, lists Questions with list.html """ pairs = { 'ID': 'id', 'Question': 'title', 'Date': 'submission_time', 'Number of Views': 'view_number', 'Votes': 'vote_number' } sortingcols = request.args order = ','.join( [pairs[col] + ' ' + sortingcols[col] for col in sortingcols][::-1]) sql_query = """SELECT q.id, q.title, q.submission_time, q.view_number, q.vote_number, q.image, u.name FROM question q JOIN users u ON (q.users_id = u.id)""" if order: sql_query += '\nORDER BY q.' + order + ';' questions = data_manager.run_query(sql_query) headers = [ 'question_id', 'title', 'submission_time', 'view_number', 'vote_number', 'image', 'user_name' ] questions = data_manager.build_dict(questions, headers) url = '&'.join([key + '=' + sortingcols[key] for key in sortingcols]) return render_template('list.html', questions=questions, url=url)
def get_users(): """ Return the list of users from database """ query = """SELECT name FROM users;""" users = data_manager.run_query(query) simple_list = [x[0] for x in users] return simple_list
def get_question(id): """ Return a single question (dict) by its ID """ question = data_manager.run_query("SELECT * FROM question WHERE id={};".format(id)) question = data_manager.build_dict( question, ["question_id", "submission_time", "view_number", "vote_number", "title", "message", "image"]) return question[0]
def read_tags(question_id): list_of_keys_of_tag = ["tag_id", "name", "question_id", "color"] query_tag = """SELECT tag.id, tag.name, question_tag.question_id, tag.color FROM tag JOIN question_tag ON tag.id = question_tag.tag_id WHERE question_tag.question_id={0} ORDER BY tag_id""".format(question_id) data = data_manager.run_query(query_tag) tags = data_manager.build_dict(data, list_of_keys_of_tag) return tags
def update_comment_in_db(comment_id, comment): """ routed to url: /update_comment/<comment_id> Saves modifications of selected comment into the database """ counter = data_manager.run_query( "SELECT edited_count FROM comment WHERE id={}".format( int(comment_id)))[0][0] curr_time = str(datetime.datetime.now())[:16] counter = 1 if not counter else counter + 1 sql_query = ("""UPDATE comment SET submission_time='{}', message='{}', edited_count={} WHERE id={}""".format(curr_time, comment, counter, int(comment_id))) data_manager.run_query(sql_query) question_id = get_question_for_comment(comment_id) return redirect('/question/' + question_id)
def get_users_with_id(): """ Return the list of users with their id from database in the format of dictinoary (keywords: id, name) """ query = """SELECT id, name FROM users;""" user_list = data_manager.run_query(query) user_list = data_manager.build_dict(user_list, ["id", "name"]) return user_list
def group_by_question_with_tags(): query = """SELECT tag.name, COUNT(question_tag.tag_id), tag.color FROM tag LEFT JOIN question_tag ON tag.id=question_tag.tag_id GROUP BY tag.id ORDER BY COUNT(question_tag.tag_id) DESC;""" tags_number_of_question = data_manager.run_query(query) list_of_tag_number_key = ["name", "question_number", "color"] dict_of_tags = data_manager.build_dict(tags_number_of_question, list_of_tag_number_key) return dict_of_tags
def list_users(): sql_query = """ SELECT u.name, u.submission_time, (SELECT COUNT(question.users_id) FROM question WHERE question.users_id=u.id), (SELECT COUNT(answer.users_id) FROM answer WHERE answer.users_id=u.id) FROM users u ORDER BY u.submission_time; """ header = ('Username', 'Join Date', 'Number of Questions', 'Number of Answers') table = data_manager.run_query(sql_query) return render_template('print_users.html', title='List of Users', table=table, header=header)
def add_comment_to_db(q_or_a, id, comment, user): """ Routed to url: /add_comment_to_db/<q_or_a>/<id> Saves to new entry in the database """ sql_query = ("""SELECT id FROM users WHERE name='{}';""".format(user)) user_id = data_manager.run_query(sql_query)[0][0] if q_or_a == 'question': sql_query = ("""INSERT INTO comment (question_id, message, users_id) VALUES ({}, '{}', '{}');""".format(int(id), comment, user_id)) question_id = id else: sql_query = ("""INSERT INTO comment (answer_id, message, users_id) VALUES ({}, '{}', '{}' )""".format(int(id), comment, user_id)) question_id = str( data_manager.run_query( """SELECT question_id FROM answer WHERE id={}""".format(id))[0] [0]) data_manager.run_query(sql_query) return redirect('/question/' + question_id)
def add_new_answer_comment(answer_id): """ Routed to url: /answer/answer_id/new-comment Renders comment.html with arguments for adding new comment for an answer """ question_id = data_manager.run_query( """SELECT question_id FROM answer WHERE id={}""".format( answer_id))[0][0] sql_query = """SELECT message FROM answer WHERE id={}""".format(answer_id) answer_content = data_manager.run_query(sql_query)[0][0] users = common.get_users() return render_template('comment.html', basis='answer', mode='Add_new', button='Create', comment='', content=answer_content, question_id=str(question_id), answer_id=str(answer_id), title='Add a', users=users)
def search_questions(term): """search for answers and questions with the given search term and display it # search for answers with search term in their message""" searchterm = term.lower() # select questions question_query = """SELECT DISTINCT id, title, message, submission_time FROM question WHERE id = ANY (SELECT question_id FROM answer WHERE LOWER(title) LIKE '%{0}%' OR LOWER(message) LIKE '%{0}%') OR LOWER(message) LIKE '%{0}%' OR LOWER(title) LIKE '%{0}%';""".format(searchterm) table = data_manager.run_query(question_query) if table: keys = ("question_id", "title", "message", "submission_time") questions = data_manager.build_dict(table, keys) fancy_questions = make_fancy(term, questions, "title") fancy_questions = make_fancy(term, questions, "message") else: return render_template('search_results.html', questions=[], answers=[]) # select related answers answer_query = """SELECT DISTINCT id, message, question_id FROM answer WHERE question_id = ANY (SELECT id FROM question WHERE LOWER(title) LIKE '%{0}%' OR LOWER(message) LIKE '%{0}%') OR LOWER(message) LIKE '%{0}%';""".format(searchterm) table = data_manager.run_query(answer_query) keys = ("answer_id", "message", "question_id") answers = data_manager.build_dict(table, keys) fancy_answers = make_fancy(term, answers, "message") return render_template('search_results.html', questions=fancy_questions, answers=fancy_answers)
def contacts(): sql_query = """ SELECT s.name, m.first_name, m.last_name FROM schools s LEFT OUTER JOIN mentors m ON (s.contact_person = m.id) ORDER BY s.name;""" table = dm.run_query(sql_query) title = 'Contacts page' header = ('School', 'Contact First Name', 'Last Name') return render_template('print_table.html', title=title, header=header, table=table)
def all_school(): sql_query = """ SELECT m.first_name, m.last_name, s.name, s.country FROM mentors m RIGHT OUTER JOIN schools s ON (m.city = s.city) ORDER BY m.id;""" table = dm.run_query(sql_query) title = "All the schools page" header = ('First Name', 'Last Name', 'School Name', 'Country') return render_template('print_table.html', title=title, header=header, table=table)
def edit_comment(comment_id): """ Routed to url: /comments/<comment_id>/edit Renders comment.html with arguments for editing a comment """ sql_query = """SELECT name FROM users WHERE id=(SELECT users_id FROM comment WHERE id={})""".format( comment_id) name = data_manager.run_query(sql_query)[0][0] sql_query = """SELECT message FROM comment WHERE id={}""".format( comment_id) comment = data_manager.run_query(sql_query)[0][0] question_id = get_question_for_comment(comment_id) return render_template('comment.html', basis='comment', mode='Edit', button='Update', comment=comment, content='', question_id=question_id, answer_id='', comment_id=comment_id, title='Edit', name=name)
def applicants(): sql_query = """ SELECT a.first_name, a.application_code, a_m.creation_date FROM applicants a LEFT OUTER JOIN applicants_mentors a_m ON (a.id = a_m.applicant_id) WHERE a_m.creation_date > '2016-01-01' ORDER BY a_m.creation_date DESC;""" table = dm.run_query(sql_query) title = 'Applicants page' header = ('Applicant First Name', 'Application Code', 'Creation Date') return render_template('print_table.html', title=title, header=header, table=table)
def mentors_by_country(): sql_query = """ SELECT s.country, COUNT(*) AS mentor_count FROM mentors m JOIN schools s ON (m.city = s.city) GROUP BY s.country ORDER BY s.country;""" table = dm.run_query(sql_query) title = 'Mentors by country page' header = ('Country', 'Mentor Count') return render_template('print_table.html', title=title, header=header, table=table)
def get_answers_for_question(question_id, sort_by, direction): query = """ SELECT answer.*, users.name FROM answer LEFT JOIN users ON answer.users_id = users.id WHERE question_id={0} ORDER BY {1} {2}; """.format(question_id, sort_by, direction) rows = data_manager.run_query(query) list_of_names = [ "answer_id", "submission_time", "vote_number", "question_id", "message", "image", "accepted", "user_id", "user_name" ] answers = data_manager.build_dict(rows, list_of_names) return answers
def edit_question_route(question_id): """Find question details from id and redirect with the data to /new_question page""" title = "Modify question" action = "/modify/" + question_id query = """SELECT q.title, q.message, u.name FROM question q JOIN users u ON (q.users_id = u.id) WHERE q.id = '{}';""".format(question_id) table = data_manager.run_query(query) titles = "title", "message", "username" data = data_manager.build_dict(table, titles)[0] tags = common.show_tags_type() tag_query = """SELECT tag.name FROM tag LEFT JOIN question_tag ON tag.id=question_tag.tag_id WHERE question_id = {0};""".format(question_id) selected_tag = [i[0] for i in data_manager.run_query(tag_query)] return render_template("new-question.html", question_id=question_id, title=title, data=data, action=action, tags=tags, selected_tag=selected_tag)
def get_comments(comment_type, question_id): if comment_type == "question": query = """SELECT * FROM comment WHERE question_id = {}; """.format(question_id) elif comment_type == "answer": query = """SELECT comment.id, comment.question_id, comment.answer_id, comment.message, comment.submission_time, comment.edited_count FROM comment LEFT JOIN answer ON answer_id = answer.id WHERE answer.question_id = {}; """.format(question_id) rows = data_manager.run_query(query) columns = ["id", "question_id", "answer_id", "message", "submission_time", "edited_count"] comments = data_manager.build_dict(rows, columns) return comments
def applicants_and_mentors(): sql_query = """ SELECT a.first_name, a.application_code, COALESCE(m.first_name, 'No mentor yet'), COALESCE(m.last_name, 'No mentor yet') FROM ((applicants a LEFT OUTER JOIN applicants_mentors a_m ON (a.id = a_m.applicant_id)) LEFT OUTER JOIN mentors m ON (a_m.mentor_id = m.id)) ORDER BY a_m.applicant_id;""" table = dm.run_query(sql_query) title = 'Applicants and mentors page' header = ('Applicant First Name', 'Application Code', 'Mentor First Name', 'Last Name') return render_template('print_table.html', title=title, header=header, table=table)
def get_answer(id): """ Return a single answer (dict) by its ID with authors name """ print(id) answer = data_manager.run_query( """ SELECT answer.*, users.name FROM answer LEFT JOIN users ON answer.users_id = users.id WHERE answer.id={}; """.format(id)) answer = data_manager.build_dict(answer, [ "answer_id", "submission_time", "vote_number", "question_id", "message", "image", "accepted", "user_id", "user_name" ]) return answer[0]
def displays_a_single_question(question_id): list_of_key_of_question = [ "id", "submission_time", "view_number", "vote_number", "title", "message" ] title_of_question = [ "ID", "Submisson time", "View number", "Vote number", "Title", "Message" ] list_of_key_and_title_of_question = list( zip(list_of_key_of_question, title_of_question)) list_of_key_of_answer = [ "answer_id", "submission_time", "vote_number", "question_id", "message", "user_name" ] title_of_answer = [ "ID", "Submisson time", "Vote number", "Question id", "Message", "Author" ] list_of_key_and_title_of_answers = list( zip(list_of_key_of_answer, title_of_answer)) query = "SELECT * FROM question" rows = data_manager.run_query(query) list_of_names = [ "id", "submission_time", "view_number", "vote_number", "title", "message" ] all_question = data_manager.build_dict(rows, list_of_names) for question_ in all_question: if question_id == question_["id"]: question = question_ break query = "SELECT * FROM answer" rows = data_manager.run_query(query) list_of_names = [ "id", "submission_time", "vote_number", "question_id", "message" ] all_answers = data_manager.build_dict(rows, list_of_names) answers = [] for answer in all_answers: if question_id == answer["question_id"]: answers.append(answer) tags = common.read_tags(question_id) question_with_answers = { "tags": tags, "question_id": question_id, "question": question, "answers": answers, "list_of_key_of_question": list_of_key_of_question, "list_of_key_of_answer": list_of_key_of_answer, "title_of_question": title_of_question, "title_of_answer": title_of_answer, "list_of_key_and_title_of_question": list_of_key_and_title_of_question, "list_of_key_and_title_of_answers": list_of_key_and_title_of_answers } return question_with_answers
def show_tags_type(): list_of_keys_of_tag = ["id", "name", "color"] query_tag = "SELECT id,name,color FROM tag ORDER BY id" data = data_manager.run_query(query_tag) tags_type = data_manager.build_dict(data, list_of_keys_of_tag) return tags_type