def change_answer_accepted(answer_id, value):
query = '''
UPDATE answer
SET accepted = %s
WHERE id = %s'''
list_of_var = [value, answer_id]
connection.db_mod_list_without_return(query=query, list_of_var=list_of_var)
def delete_image(table, image):
query = """
UPDATE {}
SET image = '{}'
WHERE image = '{}'""".format(table, 'None', image)
list_of_var = []
connection.db_mod_list_without_return(query=query, list_of_var=list_of_var)
def add_comment_to_answer(message, q_id_num, user_id):
sub_time = datetime.datetime.now().replace(microsecond=0).isoformat()
query = '''
INSERT INTO comment(answer_id, message, submission_time, edit_submission_time, user_id)
VALUES (%s, %s, %s, %s, %s)'''
list_of_var = [q_id_num, message, sub_time, sub_time, user_id]
connection.db_mod_list_without_return(query=query, list_of_var=list_of_var)
def vote(table, id_num, num, column):
query = '''
UPDATE {}
SET {} = {} + %s
WHERE id = %s'''.format(table, column, column)
list_of_var = [num, id_num]
connection.db_mod_list_without_return(query=query, list_of_var=list_of_var)
def edit_comment(id_num, message):
sub_time = datetime.datetime.now().replace(microsecond=0).isoformat()
query = '''
UPDATE comment
SET message = %s, edit_submission_time = %s, edited_count = edited_count + 1
WHERE id = %s'''
list_of_var = [message, sub_time, id_num]
connection.db_mod_list_without_return(query=query, list_of_var=list_of_var)
def modify_tags(question_id, new_tags):
for tag in new_tags:
query = '''
INSERT INTO tag (name)
SELECT %s
WHERE NOT EXISTS (SELECT name FROM tag WHERE name = %s);
INSERT INTO question_tag(question_id, tag_id)
SELECT %s, (SELECT id FROM tag WHERE name = %s)
WHERE NOT EXISTS (SELECT question_id, tag_id FROM question_tag
WHERE question_id = %s and tag_id = (SELECT id FROM tag WHERE name = %s))'''
list_of_var = [tag, tag, question_id, tag, question_id, tag]
connection.db_mod_list_without_return(query=query, list_of_var=list_of_var)
def add_answer(file, id_num):
sub_time = datetime.datetime.now().replace(microsecond=0).isoformat()
try:
message, image, user_id = file
query = '''
INSERT INTO answer(submission_time, vote_number, question_id, message, image, edit_submission_time, user_id)
VALUES (%s, 0, %s, %s, %s, %s, %s)'''
list_of_var = [sub_time, id_num, message, image, sub_time, user_id]
except ValueError:
message = file[0]
user_id = file[1]
query = '''
INSERT INTO answer(submission_time, vote_number, question_id, message, edit_submission_time, user_id)
VALUES (%s, 0, %s, %s, %s, %s)'''
list_of_var = [sub_time, id_num, message, sub_time, user_id]
connection.db_mod_list_without_return(query=query, list_of_var=list_of_var)
def add_new_user(list_of_data):
username, email_address, password = list_of_data
registration_date = datetime.datetime.now().replace(microsecond=0).isoformat()
query = '''
INSERT INTO users(username, email_address, registration_date, password)
VALUES (%s, %s, %s, %s)'''.format(registration_date)
list_of_var = [username, email_address, registration_date, password]
return connection.db_mod_list_without_return(query=query, list_of_var=list_of_var)
def edit_answer(file, id_num):
sub_time = datetime.datetime.now().replace(microsecond=0).isoformat()
try:
message, image = file
query = '''
UPDATE answer
SET message = %s, image = %s, edit_submission_time = %s
WHERE id = %s'''
list_of_var = [message, image, sub_time, id_num]
except ValueError:
message = file[0]
query = '''
UPDATE answer
SET message = %s, edit_submission_time = %s
WHERE id = %s'''
list_of_var = [message, sub_time, id_num]
connection.db_mod_list_without_return(query=query, list_of_var=list_of_var)
def tag_delete():
query = '''
DELETE FROM tag t
WHERE t.id not in (SELECT tag_id FROM question_tag)'''
list_of_var = []
connection.db_mod_list_without_return(query=query, list_of_var=list_of_var)
def delete_vote(id_type, id_num, u_id):
query = '''
DELETE FROM votes
WHERE {} = %s AND user_id = %s'''.format(id_type)
list_of_var = [id_num, u_id]
connection.db_mod_list_without_return(query=query, list_of_var=list_of_var)
def user_vote_saving(column, vote_id, u_id, value):
query = '''
INSERT INTO votes ({}, user_id, updown)
VALUES (%s, %s, %s)'''.format(column)
list_of_var = [vote_id, u_id, value]
connection.db_mod_list_without_return(query=query, list_of_var=list_of_var)
def delete_by_id(table, id_type, id_num):
query = '''
DELETE FROM {}
WHERE {} = %s'''.format(table, id_type)
list_of_var = [id_num]
connection.db_mod_list_without_return(query=query, list_of_var=list_of_var)