def insert_answer(message, question_id, user):
answer_id = get_max_id_answer()
dt = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
user_id = fetch_user_id(user)[0][0]
query = """INSERT INTO answer
VALUES (%s, '%s', 0, '%s', '%s', 0, '%d') """ % (answer_id, dt, question_id, message, user_id)
return config.run_query(query)
def update_vote(table, data_id, votenumber):
query = """
UPDATE %s
SET vote_number = %s
WHERE id = %s;
""" % (table, votenumber, data_id)
return config.run_query(query)
def insert_question_comment(message, question_id, user):
comment_id = get_max_id_comment()
dt = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
user_id = fetch_user_id(user)[0][0]
query = """INSERT INTO comment
VALUES (%s, %s, null, '%s', '%s', '%d') """ % (comment_id, question_id, message, dt, user_id)
return config.run_query(query)
def answer_comment(question_id):
query = """SELECT comment.submission_time, comment.message, comment.answer_id, answer.question_id FROM comment
JOIN answer ON comment.answer_id= answer.id
WHERE answer.question_id = %s
ORDER BY comment.submission_time DESC""" % (question_id)
rows = config.run_query(query)
return rows
def list_all_user():
query = """SELECT username FROM users"""
rows = config.run_query(query)
user_names = []
for name in rows:
user_names.append(name[0])
return user_names
def insert_data(title, message, user):
question_id = get_max_id()
dt = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
user_id = fetch_user_id(user)[0][0]
query = """INSERT INTO question
VALUES (%s, '%s', 0, 0, '%s', '%s', null, '%d') """ % (question_id, dt, title, message, user_id)
return config.run_query(query)
def update_reputation(operator, value, user_id):
query = """
UPDATE users
SET reputation = reputation %s %s
WHERE id = %s;
""" % (operator, value, user_id)
return config.run_query(query)
def insert_username(user):
try:
query = """INSERT INTO users (username)
VALUES ('%s') """ % user
return config.run_query(query)
except psycopg2.IntegrityError as error:
message = "The username is invalid or already exist."
print(message)
def get_question(question_id):
query = """SELECT title, message from question WHERE id = %s """ % question_id
rows = config.run_query(query)
return rows
def get_max_id_comment():
query = """SELECT id from comment ORDER BY id DESC LIMIT 1;"""
rows = config.run_query(query)
return int(rows[0][0]) + 1
def five_latest_questions():
query = """SELECT * FROM question ORDER BY submission_time DESC
LIMIT 5;"""
return config.run_query(query)
def get_max_id():
query = """SELECT id from question ORDER BY id DESC LIMIT 1;"""
rows = config.run_query(query)
return int(rows[0][0]) + 1
def answer(question_idd):
query = """SELECT * from answer WHERE question_id = %s ORDER BY id;""" % question_idd
return config.run_query(query)
def all_question():
query = """SELECT * FROM question ORDER BY submission_time DESC;"""
return config.run_query(query)
def fetch_user_id(user):
query = """ SELECT id FROM users WHERE username = '******';""" % (user)
return config.run_query(query)
def all_user():
query = """SELECT username, to_char(registration_time, 'YYYY-MM-DD HH24:MI'), reputation
FROM users
ORDER BY reputation DESC;"""
return config.run_query(query)
def question_comment(question_id):
query = """SELECT submission_time, message from comment WHERE question_id = %s
ORDER BY submission_time DESC """ % question_id
rows = config.run_query(query)
return rows
def question(question_idd):
query = """SELECT * from question WHERE id = %s;""" % question_idd
return config.run_query(query)
def get_answer(answer_id):
query = """SELECT message from answer WHERE id = %s """ % answer_id
rows = config.run_query(query)
return rows
def question_id_from_answer(answer_id):
query = """SELECT question_id from answer WHERE id = %s """ % answer_id
rows = config.run_query(query)
return int(rows[0][0])