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

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]