def posttest_answers():
params = request.args
#update order number
flask.session['order'] = flask.session['order'] + 1
#data
best1 = params['best1']
best2 = params['best2']
order = params['order']
#student_test_id = flask.session['student_test_id']
student_id = flask.session['userid']
answer_list = [(best1,1000), (best2,1001)]
#write to db
#update complete row in StudentsTest table
# r = conn.execute(StudentsTest.update().\
# where(StudentsTest.c.student_test_id == student_test_id).\
# values(complete='yes'))
conn.execute(Results.insert(), [{
'student_id':student_id,
'student_test_id':int(str(student_id) + str(answer[1])),
'answer':answer[0],
'graph_id':'na',
} for answer in answer_list])
#get next question
# question_json = first_question()
# return question_json
return flask.jsonify(result={"status": 200})
def training_answers():
params = request.args
#update order number
flask.session['order'] = flask.session['order'] + 1
#data
order = params['order']
graph1 = flask.session['graph1']
student_test_id = flask.session['student_test_id']
student_id = flask.session['userid']
if flask.session['question_type'] == 'rating':
answer_id = params['rating1']
else:
answer1 = params['best1']
#figure out answer
if answer1 == 'optionA':
answer_id = flask.session['answer1']
elif answer1 == 'optionB':
answer_id = flask.session['answer2']
elif answer1 == 'optionC':
answer_id = flask.session['answer3']
elif answer1 == 'optionD':
answer_id = flask.session['answer4']
elif answer1 == 'optionE':
answer_id = flask.session['answer5']
answer_list = [(answer_id,graph1)]
#write to db
#update complete row in StudentsTest table
r = conn.execute(StudentsTest.update().\
where(StudentsTest.c.student_test_id == student_test_id).\
values(complete='yes'))
conn.execute(Results.insert(), [{
'student_id':student_id,
'student_test_id':student_test_id,
'answer':answer[0],
'graph_id':answer[1],
} for answer in answer_list])
#get next question
# question_json = first_question()
# return question_json
return flask.jsonify(result={"status": 200})
def pretest_answers():
params = request.args
#update order number
flask.session['order'] = flask.session['order'] + 1
#data
best1 = params['best1']
best2 = params['best2']
best3 = params['best3']
order = params['order']
graph1 = flask.session['graph1']
graph2 = flask.session['graph2']
graph3 = flask.session['graph3']
student_test_id = flask.session['student_test_id']
student_id = flask.session['userid']
try:
best4 = params['best4']
answer_list = [(best1,graph1),(best2,graph2),(best3,graph3),(best4,'na')]
except:
answer_list = [(best1,graph1),(best2,graph2),(best3,graph3)]
#write to db
#update complete row in StudentsTest table
r = conn.execute(StudentsTest.update().\
where(StudentsTest.c.student_test_id == student_test_id).\
values(complete='yes'))
conn.execute(Results.insert(), [{
'student_id':student_id,
'student_test_id':student_test_id,
'answer':answer[0],
'graph_id':answer[1],
} for answer in answer_list])
#get next question
# question_json = first_question()
# return question_json
return flask.jsonify(result={"status": 200})
from sqlalchemy import create_engine
from sqlalchemy.sql import text, func, select, and_, or_, not_, desc, bindparam
from db_tables import metadata, Questions, Answers, Results, Students, StudentsTest, Graphs
import pandas as pd
engine = create_engine('sqlite:///quizDB.db?check_same_thread=False', echo=True)
conn = engine.connect()
metadata.create_all(engine)
#CLEAR RESULTS TABLE
if engine.dialect.has_table(engine.connect(), "results"):
conn.execute(Results.delete())
df_questions = pd.read_excel('quizApp/data/question_table.xlsx', 'Sheet1')
#check for table and if it is there clear before writing to
if engine.dialect.has_table(engine.connect(), "questions"):
conn.execute(Questions.delete())
conn.execute(Questions.insert(), [{'question_id':data.question_id,
'dataset':data.dataset,
'question':data.question,
'question_type':data.question_type}
for ix, data in df_questions.iterrows()])
df_answers = pd.read_excel('quizApp/data/answer_table.xlsx', 'Sheet1')
#check for table and if it is there clear before writing to
if engine.dialect.has_table(engine.connect(), "answers"):
conn.execute(Answers.delete())
students = conn.execute(select([Students])).fetchall()
results = conn.execute(
select(
[
Results.c.student_id,
Results.c.answer,
Results.c.graph_id,
Answers.c.answer,
Questions.c.question,
StudentsTest.c.dataset,
]
)
.select_from(
Results.join(Students, Students.c.student_id == Results.c.student_id)
.join(StudentsTest, StudentsTest.c.student_test_id == Results.c.student_test_id)
.outerjoin(Questions, Questions.c.question_id == StudentsTest.c.question_id)
.outerjoin(Answers, Answers.c.answer_id == Results.c.answer)
)
.where(Students.c.opt_in == "na")
).fetchall()
results = [[x.replace(",", "") if type(x) == type("s") else x for x in t] for t in results]
results = [["Student_id", "answer", "graph_id", "answer_text", "question", "dataset"]] + results
with open("student_table_list.csv", "w", newline="") as csvfile:
spamwriter = csv.writer(csvfile, delimiter=",", quotechar="|", quoting=csv.QUOTE_MINIMAL)
[spamwriter.writerow(s) for s in students]
with open("results_table_list.csv", "w", newline="") as csvfile:
spamwriter = csv.writer(csvfile, delimiter=",", quotechar="|", quoting=csv.QUOTE_MINIMAL)
[spamwriter.writerow(r) for r in results]
