87 lines
2.1 KiB
Python
87 lines
2.1 KiB
Python
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)
|
|
|
|
db.execute('''CREATE TABLE QUESTIONS(question TEXT UNIQUE, answer TEXT, correct INT)''')
|
|
db.commit()
|
|
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()
|
|
try:
|
|
cursor.execute("UPDATE QUESTIONS SET correct=correct + ? WHERE question = ?", (correct, question))
|
|
except sqlite3.IntegrityError:
|
|
pass
|
|
db.commit()
|
|
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))
|
|
db.commit()
|
|
db.close()
|
|
|
|
|
|
def add_words(input_data):
|
|
db = open_database()
|
|
cursor = db.cursor()
|
|
|
|
cursor.executemany("INSERT INTO QUESTIONS(question, answer, correct) VALUES(?, ?, 0)", list(input_data.items()))
|
|
db.commit()
|
|
db.close()
|
|
|
|
|
|
def fetch_question():
|
|
"""
|
|
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.
|
|
|
|
"""
|
|
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
|
|
|
|
cursor.execute("SELECT question, answer, correct FROM QUESTIONS ORDER BY correct ASC")
|
|
|
|
result = cursor.fetchmany(fetch_questions)
|
|
|
|
my_result = random.choice(result)
|
|
return my_result[:2]
|
|
|