scientific-programming-exer.../exam/ex03/database.py

87 lines
2.1 KiB
Python
Raw Permalink Normal View History

2019-01-31 16:51:05 +00:00
import os
import sqlite3
import random
from cfg import config
database_path = os.path.join(config["APP_PATH"], "app.sqlite")
def create_database():
# truncate the file if it exists
with open(database_path, "w") as database_file:
pass
db = sqlite3.connect(database_path)
2019-01-31 18:53:49 +00:00
db.execute('''CREATE TABLE QUESTIONS(question TEXT UNIQUE, answer TEXT, correct INT)''')
db.commit()
2019-01-31 16:51:05 +00:00
db.close()
def open_database():
if(not os.path.exists(database_path)):
create_database()
return sqlite3.connect(database_path)
def update_correct(question, correct):
"""
Correct has to be +1 (for a correct answer) or -1 (for a wrong answer).
"""
db = open_database()
cursor = db.cursor()
2019-01-31 18:53:49 +00:00
try:
cursor.execute("UPDATE QUESTIONS SET correct=correct + ? WHERE question = ?", (correct, question))
except sqlite3.IntegrityError:
pass
db.commit()
2019-01-31 16:51:05 +00:00
db.close()
def add_word(question, answer):
db = open_database()
cursor = db.cursor()
cursor.execute("INSERT INTO QUESTIONS(question, answer, correct) VALUES(?, ?, 0)", (question, answer))
2019-01-31 18:53:49 +00:00
db.commit()
2019-01-31 16:51:05 +00:00
db.close()
2019-01-31 18:53:49 +00:00
2019-01-31 16:51:05 +00:00
def add_words(input_data):
db = open_database()
cursor = db.cursor()
2019-01-31 18:53:49 +00:00
cursor.executemany("INSERT INTO QUESTIONS(question, answer, correct) VALUES(?, ?, 0)", list(input_data.items()))
db.commit()
2019-01-31 16:51:05 +00:00
db.close()
2019-01-31 18:53:49 +00:00
2019-01-31 16:51:05 +00:00
def fetch_question():
2019-01-31 18:53:49 +00:00
"""
Fetches a new question from the database.
The list of question is sorted ascending by number of
times the question has been answered correctly
and from the least n elements one is chosen randomly.
n is either the number of questions in the database divided
by ``cfg.config["WORD_BUCKET_SIZE"]`` or (if this number is zero)
or one.
"""
2019-01-31 16:51:05 +00:00
db = open_database()
cursor = db.cursor()
cursor.execute("SELECT COUNT(question) FROM questions")
number_of_questions = cursor.fetchone()[0]
if(number_of_questions == 0):
raise Exception("No questions in the database")
fetch_questions = number_of_questions // config["WORD_BUCKET_SIZE"]
if(fetch_questions == 0):
fetch_questions = 1
2019-01-31 18:53:49 +00:00
cursor.execute("SELECT question, answer, correct FROM QUESTIONS ORDER BY correct ASC")
2019-01-31 16:51:05 +00:00
2019-01-31 18:53:49 +00:00
result = cursor.fetchmany(fetch_questions)
2019-01-31 16:51:05 +00:00
my_result = random.choice(result)
2019-01-31 18:53:49 +00:00
return my_result[:2]
2019-01-31 16:51:05 +00:00