def video(question_id): sql = 'SELECT video from questions_question where id = {}'.format( question_id) cursor = execute_sql(sql) video = cursor.fetchone()['video'] cursor.close() return video
def question_from_sub_topic(sub_topic): sql = 'SELECT * from questions_question where sub_topic = "{}" and ' \ 'correct = TRUE limit 1'.format(sub_topic) cursor = execute_sql(sql) response = cursor.fetchone() cursor.close() return response
def has_video(question_id): sql = 'select video_added from questions_question where id = {}'.format( question_id) cursor = execute_sql(sql) val = cursor.fetchone()['video_added'] cursor.close() if val == 1: return True return False
def question_from_topic(topic): sql = 'SELECT question_text, id from questions_question where ' \ 'topic = "{}" ' \ 'and deleted = 0 and correct = TRUE order by rand() limit 1'.format( topic) cursor = execute_sql(sql) response = cursor.fetchone() cursor.close() return response
def questions_answered(sender_id, num): try: sql = "select count(*) from answer_provided where time_asked \ BETWEEN (select CURRENT_TIMESTAMP + interval '-%s' day) \ AND (select CURRENT_TIMESTAMP) \ AND sender_id = %s" cursor = execute_sql(sql, num, sender_id) response = cursor.fetchone()['count(*)'] cursor.close() return response except: log('error! questions answered') return None
def questions_grouped_by_date_eternity(sender_id): try: sql = "select DATE(time_asked) AS ForDate, count(*) \ from answer_provided \ where sender_id = %s \ GROUP BY ForDate" cursor = execute_sql(sql, sender_id) response = cursor.fetchall() cursor.close() return response except: log('error! questions group by date') return None
def score_in_given_topic(sender_id, topic): try: sql = "select count(*) \ from answer_provided a join questions_question q \ on q.id = a.question_id \ where a.is_correct = 1 \ AND q.topic = %s AND a.sender_id = %s" cursor = execute_sql(sql, topic, sender_id) response = cursor.fetchone()['count(*)'] cursor.close() return response except: log('error! score in a given topic') return None
def options_and_answer(question_id): sql = 'SELECT o.id, o.option_text, o.correct from questions_option ' \ 'o join questions_question q on o.qid_id = q.id ' \ 'where q.id = {}'.format(question_id) cursor = execute_sql(sql) options = cursor.fetchall() cursor.close() while len(options) > 3: item = random.choice(options) if item['correct'] == 1: continue options.remove(item) return answer( options=[{'id': opt['id'], 'text': opt['option_text']} for opt in options], correct=correct_answer(options))
def scores_in_topics(sender_id): try: sql = "select count(*), q.topic \ from answer_provided a join questions_question q \ on q.id = a.question_id \ where a.is_correct = 1 AND a.sender_id = %s \ GROUP BY q.topic \ ORDER BY count(*) DESC" cursor = execute_sql(sql, sender_id) response = cursor.fetchall() cursor.close() return response except: log('error! scores in topics') return None
def correct_questions_grouped_by_date_last_month(sender_id): try: sql = "select DATE(time_asked) AS ForDate, count(*) \ from answer_provided \ where time_asked \ BETWEEN (select CURRENT_TIMESTAMP + interval '-30' day) \ AND (select CURRENT_TIMESTAMP) AND is_correct = 1 AND \ sender_id = %s\ GROUP BY ForDate" cursor = execute_sql(sql, sender_id) response = cursor.fetchall() cursor.close() return response except: log('error! correct questions group by date') return None
def bottom_two_scoring_topics(sender_id): try: sql = "select count(*), q.topic \ from answer_provided a join questions_question q \ on q.id = a.question_id \ where a.is_correct = 1 AND a.sender_id = %s \ GROUP BY q.topic \ ORDER BY count(*) \ LIMIT 2" cursor = execute_sql(sql, sender_id) res = cursor.fetchall() res = [r['topic'] for r in res] cursor.close() return res except: log('error! bottom two scoring topics') return None
def subtopics(): sql = 'select distinct sub_topic from questions_question' cursor = execute_sql(sql) response = cursor.fetchall() cursor.close() return response