def load_questions(self, package_id):
if package_id:
with Connection() as con:
with con:
for row in con.execute(
'''
SELECT
id,
question,
correct,
incorrect1,
incorrect2,
incorrect3
FROM questions
WHERE package_id = ?
''', (str(package_id), )):
print(row[0])
self.qbank.append({
"id": row[0],
"text": row[1],
"correct": row[2],
"incorrect": [row[3], row[4], row[5]]
})
else:
with Connection() as con:
for id, question_text, correct, b, c, d, package_id in con.execute(
"SELECT * from questions"):
self.qbank.append({
"text": question_text,
"correct": correct,
"incorrect": [b, c, d],
"id": id
})
def ensure_table_exists(self):
with Connection() as con:
con.execute('''
CREATE TABLE IF NOT EXISTS statistics(
id INTEGER PRIMARY KEY,
question_id INTEGER,
quiz_format TEXT,
corrects INTEGER,
incorrects INTEGER,
skips INTEGER,
time INTEGER,
abandons INTEGER,
FOREIGN KEY(question_id) REFERENCES questions(id)
)''')
with Connection() as con:
con.execute('''
CREATE TABLE IF NOT EXISTS answer_stats(
id INTEGER PRIMARY KEY,
question_id INTEGER,
quiz_format TEXT,
status TEXT,
time INTEGER,
created_at TIMESTAMP,
FOREIGN KEY(question_id) REFERENCES questions(id)
)''')
def ensure_table_exists():
with Connection() as con:
con.execute('''
CREATE TABLE IF NOT EXISTS passwords(
id INTEGER PRIMARY KEY,
password TEXT
)''')
with Connection() as con:
with con:
con.execute('''
INSERT INTO passwords
(password)
VALUES
('password')
''')
def load_stats2(self):
Question = namedtuple("Question", [
"currently_assigned", "q_id", "text", "correct", "in1", "in2",
"in3", "time", "status", "created_at", "package_id",
"package_name", "quiz"
])
with Connection() as con:
with con:
for i, row in enumerate(
con.execute('''
SELECT questions.id,
question,
correct,
incorrect1,
incorrect2,
incorrect3,
time,
status,
created_at,
questions.package_id,
packages.name,
answer_stats.quiz_format,
packages.quiz_format
FROM ((answer_stats
INNER JOIN questions
ON answer_stats.question_id = questions.id)
INNER JOIN packages
ON questions.package_id = packages.package_id)
''')):
if not type(row[6]) == str and row[7] != "ongoing":
self.q_bank.append(
Question(row[-1] == row[-2], *row[:-1]))
elif row[7] == "ongoing":
print(row)
def load_stats(self):
Question = namedtuple("Question", [
"currently_assigned", "q_id", "text", "correct", "in1", "in2",
"in3", "successes", "failures", "skips", "abandons", "total_time",
"quiz"
])
with Connection() as con:
with con:
for i, row in enumerate(
con.execute('''
SELECT questions.id,
question,
correct,
incorrect1,
incorrect2,
incorrect3,
corrects,
incorrects,
skips,
abandons,
time,
statistics.quiz_format,
packages.quiz_format
FROM ((questions
INNER JOIN statistics
ON questions.id = statistics.question_id)
INNER JOIN packages
ON questions.package_id = packages.package_id)
''')):
q = Question(row[-1] == row[-2], *row[:-1])
self.q_bank.append(q)
print(q)
def get_password():
with Connection() as con:
return con.execute('''
SELECT password
FROM passwords
WHERE id = '1'
''').fetchone()[0]
def get_hangman_qs(random = False):
bank = []
with Connection() as con:
with con:
for row in con.execute('''
SELECT
id,
question,
correct,
incorrect1,
incorrect2,
incorrect3
FROM questions
INNER JOIN packages
ON questions.package_id = packages.package_id
WHERE packages.quiz_format = 'Hangman'
'''):
print(row[0])
bank.append(
{"id": row[0], "text": row[1], "correct": row[2], "incorrect": [row[3], row[4], row[5]]})
if random:
shuffle(bank)
for question in bank:
choices = [question["correct"]] + question["incorrect"]
shuffle(choices)
yield (question["id"], question["text"], choices, question["correct"])
def load_packages(self):
with Connection() as con:
for package_id, name, quiz_format in con.execute(
"SELECT * from packages"):
self.package_bank.append({
"name": name,
"package_id": package_id,
"quiz_format": quiz_format
})
def delete_package(package_id):
with Connection() as con:
with con:
con.execute(
'''DELETE
FROM questions
WHERE questions.package_id = ?''', (str(package_id), ))
con.execute("DELETE from packages WHERE package_id = ?",
(str(package_id), ))
def create_question(*, prompt, answer, incorrect1, incorrect2, incorrect3,
package_id):
with Connection() as con:
with con:
con.execute(
"INSERT INTO questions(question, correct, " +
"incorrect1, incorrect2, incorrect3, package_id) values (?,?,?,?,?,?)",
(prompt, answer, incorrect1, incorrect2, incorrect3,
package_id))
def set_password(password):
with Connection() as con:
with con:
con.execute(
'''
UPDATE passwords
SET password = ?
WHERE id = '1'
''', (password, ))
def save_answer_stats(*, id, quiz_format, status, time, created_at):
with Connection() as con:
with con:
con.execute(
'''
INSERT INTO answer_stats
(question_id, quiz_format, status, time, created_at)
VALUES
(?, ?, ?, ?, ?)
''', (str(id), quiz_format, status, str(time), created_at))
def create_stats(id):
with Connection() as con:
with con:
for format in ["Multi-Choice", "Hangman"]:
con.execute(
'''
INSERT INTO statistics
(question_id, quiz_format, corrects, incorrects, skips, abandons, time)
VALUES
(?, ?, 0, 0, 0, 0, 0)
''', (id, format))
def create_answer_stats(obj):
with Connection() as con:
with con:
con.execute(
'''
INSERT INTO answer_stats
(question_id, quiz_format, status, time, created_at)
VALUES
(?, ?, ?, ?, ?)
''',
(obj["id"], obj["quiz_format"], obj["status"], obj["time"],
datetime.datetime.now()))
def get_stats(id, quiz):
with Connection() as con:
with con:
return con.execute(
'''
SELECT question_id,
quiz_format,
corrects,
incorrects,
skips,
time,
abandons
FROM statistics
WHERE question_id = ?
AND quiz_format = ?
''', (str(id), str(quiz))).fetchone()
def get_package_questions(*, package_id):
bank = []
with Connection() as con:
with con:
for row in con.execute('''
SELECT
id,
question,
correct,
incorrect1,
incorrect2,
incorrect3
FROM questions
WHERE package_id = ?
''', (str(package_id),)):
bank.append(row)
return sample(bank, len(bank))
def update_stats(obj):
obj = {k: str(v) for k, v in obj.items()}
with Connection() as con:
with con:
con.execute(
'''
UPDATE statistics
SET corrects = ?,
incorrects = ?,
skips = ?,
time = ?,
abandons = ?
WHERE question_id = ?
AND quiz_format = ?
''', (obj["corrects"], obj["incorrects"], obj["skips"],
obj["time"], obj["abandons"], obj["id"],
obj["quiz_format"]))
def get_quiz_questions(*, quiz="Multi-Choice"):
bank = []
with Connection() as con:
with con:
for row in con.execute('''
SELECT
id,
question,
correct,
incorrect1,
incorrect2,
incorrect3
FROM questions
INNER JOIN packages
ON questions.package_id = packages.package_id
WHERE packages.quiz_format = ?
''', (str(quiz),)):
bank.append(row)
return sample(bank, len(bank))
def get_answer_stats(id):
with Connection() as con:
with con:
return con.execute(
'''
SELECT questions.id,
quiz_format,
status,
question,
correct,
incorrect1,
incorrect2,
incorrect3,
time,
created_at
FROM (answer_stats
INNER JOIN questions
ON answer_stats.question_id = questions.id)
WHERE question_id = ?
''', (str(id))).fetchall()
def delete_question(id):
with Connection() as con:
with con:
con.execute("DELETE from questions WHERE id = ?", (str(id), ))
def save_question(id, question, correct, inc1, inc2, inc3):
with Connection() as con:
with con:
con.execute(
"UPDATE questions SET question = ?, correct = ?, incorrect1 = ?, incorrect2 = ?, incorrect3 = ? WHERE id = ?",
(question, correct, inc1, inc2, inc3, str(id)))
def get_question(id):
with Connection() as con:
with con:
return con.execute(
"SELECT id, question, correct, incorrect1, incorrect2, incorrect3 from questions WHERE id = ?",
(str(id), )).fetchone()
def ensure_table_exists(self):
with Connection() as con:
with con:
con.execute(
"CREATE TABLE IF NOT EXISTS packages (package_id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, quiz_format text UNIQUE)"
)
def save_package(package_id, name, quiz_format):
with Connection() as con:
with con:
con.execute(
"UPDATE packages SET name = ?, quiz_format = ? WHERE package_id = ?",
(name, quiz_format, str(package_id)))
def ensure_table_exists(self):
with Connection() as con:
con.execute(
"CREATE TABLE IF NOT EXISTS questions (id INTEGER PRIMARY KEY, question TEXT, correct TEXT, incorrect1 TEXT, incorrect2 TEXT, incorrect3 TEXT, package_id INTEGER, FOREIGN KEY (package_id) REFERENCES packages (package_id))"
)
def search_format(package_id):
with Connection() as con:
with con:
return con.execute(
"SELECT package_id, name, quiz_format FROM packages WHERE package_id = ?",
(str(package_id))).fetchone()
def add_package(name):
with Connection() as con:
with con:
return con.execute("INSERT INTO packages(name) values (?)",
(name, )).lastrowid
from Quiz.multiplechoice import Multiplechoice
from Quiz.package import Package
from Quiz.statistics import Statistics
from data.connection import Connection
import random
# !!Nukes current data!!
for t_name in ["questions", "statistics", "packages"]:
with Connection() as con:
with con:
con.execute("DROP TABLE IF EXISTS " + t_name)
# Ensures that databases are created
Multiplechoice()
Statistics()
Package()
# helper function
def gen_num():
return str(random.randint(100, 999))
data = [{
"Harry Potter trivia":
[[
"What was Nearly Headless Nick's last name?", "de Mimsy-Porpington",
"von Grieve", "van Orton", "Delaney-Podmore"
],
[
"How old was Nicholas Flamel in the Sorcerer's Stone?", "655",