import os
from sqlalchemy import create_engine, ForeignKey
from sqlalchemy import Column, Date, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import text, func, select, and_, or_, not_, desc, bindparam
from db_tables import metadata, Questions, Answers, Results, Students, StudentsTest, Graphs
# project_root = os.path.dirname(os.path.realpath('quizApp_Project/'))
# DATABASE = os.path.join(project_root, 'quizDB.db')
engine = create_engine('sqlite:///quizDB.db?check_same_thread=False', echo=True)
conn = engine.connect()
metadata.create_all(engine)
ids = [x[0] for x in conn.execute(select([Students.c.student_id])).fetchall()]
print (ids)
for sid in ids:
r = conn.execute(Students.update().\
where(Students.c.student_id == sid).\
values(progress='post_test'))
def first_question():
userid = flask.session['userid']
try:
order = flask.session['order']
except:
order = 1
try:
progress, graph_id, question, question_type, answers, complete, dataset, student_test_id,question_id = get_question(order)
except:
complete = 'yes'
progress = conn.execute(select([Students.c.progress]).\
where(Students.c.student_id == flask.session['userid'])).fetchone()
progress = progress[0]
#check to make sure they have not done the question before
if complete == 'yes':
#this means the question has already been completed
order_list = conn.execute(select([StudentsTest.c.order]).\
select_from(StudentsTest.join(Students,
Students.c.student_id == StudentsTest.c.student_id)).\
where(and_(StudentsTest.c.student_id == flask.session['userid'],
StudentsTest.c.complete == 'no',
StudentsTest.c.test == Students.c.progress))).fetchall()
order_list = sorted(order_list)
if len(order_list) >= 1:
progress, graph_id, question, question_type, answers, complete, dataset, student_test_id, question_id = get_question(order_list[0][0])
else:
#the section has been completed, update progress and return to home page
#update order back to start
flask.session['order'] = 1
progress_list = ['pre_test', 'training', 'post_test']
ix = progress_list.index(progress) + 1
if ix == len(progress_list):
new_progress = 'complete'
else:
new_progress = progress_list[ix]
r = conn.execute(Students.update().\
where(Students.c.student_id == flask.session['userid']).\
values(progress=new_progress))
#return to homepage
if new_progress == 'complete':
return flask.jsonify(progress='done')
else:
return flask.jsonify(progress='next')
#put the student_test_id in session
flask.session['student_test_id'] = student_test_id
flask.session['order'] = order
flask.session['question_type'] = question_type
#check which test
if progress == 'pre_test' or progress == 'post_test':
# query three graphs
graph_list = conn.execute(select([Graphs.c.graph_location,
Graphs.c.graph_id]).\
where(Graphs.c.dataset == dataset)).fetchall()
#randomly shuffle order of graphs
shuffle(graph_list)
#put graph id's in session
flask.session['graph1'] = graph_list[0][1]
flask.session['graph2'] = graph_list[1][1]
flask.session['graph3'] = graph_list[2][1]
return flask.jsonify(graph1='<img class=graph src=' + url_for('static',filename='graphs/'+str(graph_list[0][0])) + '>',
graph2='<img class=graph src=' + url_for('static',filename='graphs/'+str(graph_list[1][0])) + '>',
graph3='<img class=graph src=' + url_for('static',filename='graphs/'+str(graph_list[2][0])) + '>',
question=question,
question_type=question_type,
order=order,
progress=progress)
elif progress == 'training':
#get graph location
graph_location = conn.execute(select([Graphs.c.graph_location]).\
where(Graphs.c.graph_id == graph_id)).fetchall()
#if it is a rating question just return graph
if question_type == 'rating':
flask.session['graph1'] = graph_id
return flask.jsonify(graph1='<img class=graph src=' + url_for('static',filename='graphs/'+str(graph_location[0][0])) + '>',
question=question,
question_type=question_type,
order=order,
progress=progress)
else:
#get answers query
answer_list = conn.execute(select([Answers.c.answer,
Answers.c.answer_id]).\
where(Answers.c.question_id == question_id)).fetchall()
try:
answer_list[0][1]
except:
if len(answer_list) >1:
longg
elif len(answer_list) <= 1:
shortt
if question_type == 'heuristic':
#put graph id's in session
flask.session['graph1'] = graph_id
flask.session['answer1'] = answer_list[0][1]
flask.session['answer2'] = answer_list[1][1]
flask.session['answer3'] = answer_list[2][1]
flask.session['answer4'] = answer_list[3][1]
flask.session['answer5'] = answer_list[4][1]
return flask.jsonify(graph1='<img src=' + url_for('static',filename='graphs/'+str(graph_location[0][0])) + ' height="500" width="500">',
question=question,
question_type=question_type,
order=order,
progress=progress,
answer1=answer_list[0][0],
answer2=answer_list[1][0],
answer3=answer_list[2][0],
answer4=answer_list[3][0],
answer5=answer_list[4][0])
else:
#put graph id's in session
flask.session['graph1'] = graph_id
flask.session['answer1'] = answer_list[0][1]
flask.session['answer2'] = answer_list[1][1]
flask.session['answer3'] = answer_list[2][1]
return flask.jsonify(graph1='<img src=' + url_for('static',filename='graphs/'+str(graph_location[0][0])) + ' height="500" width="500">',
question=question,
question_type=question_type,
order=order,
progress=progress,
answer1=answer_list[0][0],
answer2=answer_list[1][0],
answer3=answer_list[2][0])
import os
import pandas as pd
from sqlalchemy import create_engine, ForeignKey
from sqlalchemy import Column, Date, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import text, func, select, and_, or_, not_, desc, bindparam
from db_tables import metadata, Questions, Answers, Results, Students, StudentsTest, Graphs
# project_root = os.path.dirname(os.path.realpath('quizApp_Project/'))
# DATABASE = os.path.join(project_root, 'quizDB.db')
engine = create_engine('sqlite:///quizDB.db?check_same_thread=False', echo=True)
conn = engine.connect()
metadata.create_all(engine)
df_q = pd.read_excel('student_id_list_final.xlsx', 'questions consented')
df_h = pd.read_excel('student_id_list_final.xlsx', 'critiques consented')
for ix, data in df_q.iterrows():
r = conn.execute(Students.update().\
where(Students.c.student_id == data.questions).\
values(opt_in=data.consented))
for ix, data in df_h.iterrows():
r = conn.execute(Students.update().\
where(Students.c.student_id == data.Heuristics).\
values(opt_in=data.consented))