def load_questions(
) -> List[Tuple[QID, SQuestion, List[Tuple[AID, SChoice, List[int]]]]]:
data = (
TBLCol.question_id,
TBLCol.question,
TBLCol.answer_id,
TBLCol.choice,
TBLCol.vector,
TBL.Questions,
TBL.AnswerChoices,
TBLCol.question_id,
TBLCol.question_id,
)
cursor.execute("""
SELECT a.%s, a.%s, b.%s, b.%s, b.%s
FROM %s a JOIN %s b ON a.%s = b.%s
""" % data)
result = {}
for qid, question, aid, choice, vector in cursor.fetchall():
question_list = result.get((qid, question), [])
question_list.append(
(aid, choice, convert_vector_text_to_int_list(vector)))
result[(qid, question)] = question_list
return [(qid, question, result[(qid, question)])
for (qid, question) in result]
def create_unique_response_salt() -> str:
cursor.execute("SELECT %s FROM %s" %
(TBLCol.response_salt, TBL.Responses))
return generator_util.generate_unique_id(
set(el[0] for el in cursor.fetchall()),
generator_util.generate_response_salt,
)
def store_question(
question: str, q_type: SQuestionType, qa_type: SQuestionAnswerType,
choices: List[Tuple[str, str]]
) -> Tuple[QID, SQuestion, List[Tuple[SChoice, SVector]]]:
if _DB.question_exists_in_db(question):
raise ValueError("this question already exists in the database")
data = (
TBL.Questions,
TBLCol.question,
TBLCol.question_type,
TBLCol.question_answer_type,
quote(question),
quote(q_type),
quote(qa_type),
)
cursor.execute("INSERT INTO %s (%s, %s, %s) VALUES (%s, %s, %s)" %
data)
question_id = _DB.question_id(question)
data = (
TBL.AnswerChoices,
TBLCol.question_id,
TBLCol.choice,
TBLCol.vector,
', '.join([
"(" + ",".join(
[str(question_id),
quote(str(choice)),
quote(str(vector))]) + ")" for choice, vector in choices
]),
)
cursor.execute("INSERT INTO %s (%s, %s, %s) VALUES %s;" % data)
answers = \
[(SChoice(choice), SVector(vector)) for choice, vector in choices]
return QID(question_id), SQuestion(question), answers
def load_labelled_responses(
) -> Dict[Tuple[RID, SCourseNumber], Dict[QID, AID]]:
data = (
TBLCol.response_id,
TBLCol.course_number,
TBLCol.question_id,
TBLCol.answer_id,
TBL.Responses,
TBL.ResponseMappings,
TBLCol.response_id,
TBLCol.response_id,
TBLCol.course_number,
)
cursor.execute("""
SELECT a.%s, b.%s, b.%s, b.%s
FROM %s a
JOIN %s b ON a.%s = b.%s
WHERE
a.%s IS NOT NULL
""" % data)
result = {}
for rid_, cn_, qid_, aid_ in cursor.fetchall():
rid, cn, qid, aid = \
RID(rid_), SCourseNumber(cn_), QID(qid_), AID(aid_)
dic = result.get((rid, cn), {})
dic[qid] = aid
result[(rid, cn)] = dic
return result
def load_courses() -> List[Tuple[CID, SCourseNumber, SCourse]]:
data = (
TBLCol.course_id,
TBLCol.course_number,
TBLCol.course_name,
TBL.Courses,
)
cursor.execute("SELECT %s, %s, %s FROM %s" % data)
return cursor.fetchall()
def load_response(rid: RID) -> Dict[QID, AID] or None:
data = (
TBLCol.question_id,
TBLCol.answer_id,
TBL.ResponseMappings,
TBLCol.response_id,
str(rid),
)
cursor.execute("SELECT %s, %s FROM %s WHERE %s = %s" % data)
rows = cursor.fetchall()
if len(rows) == 0:
return None
return {QID(qid): AID(aid) for qid, aid in rows}
def get_unique_field(
tbl: str,
unique_col_name: str,
target_col_name: str,
target_value: str,
) -> str or int or None:
"""
this method expects that the column target_col_name column has unique
values such that the id_col_name column only has one id that can match
the given target_value. Given that, this returns that unique id.
:param tbl: table where this is coming from
:param unique_col_name: the column name of the id
:param target_col_name: the target column name
:param target_value: the target column value
:return:
unique_col_name from the database for the row with the unique
target_col_name equal to the target value
"""
cursor.execute("SELECT %s FROM %s WHERE %s = %s" %
(unique_col_name, tbl, target_col_name, target_value))
row = cursor.fetchall()
if len(row) == 0:
return None
return row[0][0]
def store_response(
response: Dict[QID, AID],
cn: SCourseNumber = None,
) -> RID:
salt = _DB.create_unique_response_salt()
data = (
TBL.Responses,
TBLCol.course_number,
TBLCol.response_salt,
"NULL" if cn is None else cn,
salt,
)
cursor.execute("INSERT INTO %s (%s, %s) VALUES (%s, %s)" % data)
rid = RID(_DB.response_id(salt))
values = [(rid, qid, response[qid]) for qid in response]
data = (
TBL.ResponseMappings,
TBLCol.response_id,
TBLCol.question_id,
TBLCol.answer_id,
convert_to_query_values(values),
)
cursor.execute("INSERT INTO %s (%s, %s, %s) VALUES %s;" % data)
return rid