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
