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]