def save_new_question(self, *args): """Passes the user information input (Question object) from GUI to SqlManager to save data in selected_database table. """ question, answer1, answer2, answer3, answer4, answer5, state_var1, state_var2, state_var3, state_var4, state_var5 = args right_answers_list = [ state_var1, state_var2, state_var3, state_var4, state_var5 ] settings_obj = SettingVariables() selected_database = settings_obj.selected_database row_id = SqlManager().sql_get_last_row_id(selected_database) + 1 new_question = Question(row_id, question.replace("\n", "."), answer1.replace("\n", "."), answer2.replace("\n", "."), answer3.replace("\n", "."), answer4.replace("\n", "."), answer5.replace("\n", "."), right_answers_list, 2) new_question_list = [new_question] sql_m = SqlManager() sql_m.sql_insert_new_questions(new_question_list, selected_database) try: self.insert_top.destroy() except AttributeError as ae: print("Exception occurred in save_new_question - due to " + str(ae)) finally: self.gui_insert_menu()
def insert_new_questions_from_file(*args): """Imports data from txt file into the selected_database table. *args: only needed to delete the gui entry_field after passing of data """ with open("import_questions.txt", encoding="utf8") as f: r = f.read() test_questions = r.split("Exercise") test_questions = test_questions[1:] questions_list = [] for i in range(0, len(test_questions)): questions_list.append(test_questions[i].split("\n\n")) for ii in range(0, len(questions_list)): states = [] answer_states = questions_list[ii][1] for i in range(0, len(answer_states)): answer_state = answer_states[i] if answer_state == "*": state = 1 states.append(state) elif answer_state == "-": state = 0 states.append(state) question = questions_list[ii][0].replace("\n", ".") answer1 = questions_list[ii][2].replace("\n", "."), answer2 = questions_list[ii][3].replace("\n", "."), answer3 = questions_list[ii][4].replace("\n", "."), answer4 = questions_list[ii][5].replace("\n", "."), answer5 = questions_list[ii][6].replace("\n", "."), right_answers = [ states[0], states[1], states[2], states[3], states[4] ] sql_m = SqlManager() table_content = sql_m.sql_get_database_content( SettingVariables().selected_database) if len(table_content) > 0: row_id = table_content[-1].row_id + 1 else: row_id = 1 variables = Question(row_id, question, answer1, answer2, answer3, answer4, answer5, right_answers) variables = [variables] sql_m = SqlManager() sql_m.sql_insert_new_questions( variables, SettingVariables().selected_database)
def delete_table(table_name, *args): """Deletes a whole table with the passed table name. *args is used only in the button widget to delete input inside the entry widget. *args: only needed to delete the gui entry_field after passing of data """ sql_m = SqlManager() sql_m.sql_delete_table(table_name)
def delete_row(table_name, row_id, *args): """Deletes the passed row in the passed table. *args is used only in the button widget to delete input inside the entry widget. *args: only needed to delete the gui entry_field after passing of data """ sql_m = SqlManager() sql_m.sql_delete_row(table_name, row_id)
def test_sql_add_firsts(self): """Creates a database table, inserts a row into it and creates a database copy of the database table. Queries last row_id, adds 1 to it and creates a Results object to pass it to the sql_insert_new_results. Adds 1 to the firsts variable with the sql_add_firsts function. Then queries the row with that row_id and checks that firsts value is plus 1. """ test_obj = TestVariables() selected_database = test_obj.selected_database selected_database_copy = test_obj.selected_database_copy self.setUpDatabase() self.setUpInsertRow(selected_database) self.setUpDatabaseCopy() row_id = self.setUpRowID(selected_database_copy) - 1 sql_m = SqlManager() new_question_obj_list = sql_m.sql_get_database_content(selected_database_copy) new_question_obj = new_question_obj_list[-1] firsts_before_update = new_question_obj.firsts sql_m.sql_add_firsts(row_id, selected_database_copy) new_question_obj_list = sql_m.sql_get_database_content(selected_database_copy) new_question_obj = new_question_obj_list[-1] firsts_after_update = new_question_obj.firsts firsts_should_right = 3 self.assertEqual(firsts_should_right, firsts_after_update) self.assertNotEqual(firsts_before_update, firsts_after_update) print("test_sql_subtract_firsts: successful") self.tearDownTable(selected_database) self.tearDownTable(selected_database_copy)
def show_all_tables(self): """Function passes the order to SqlManager to query all table names in database.""" sql_m = SqlManager() all_tables = sql_m.sql_get_all_tables() tables_list = [tables.table_name for tables in all_tables] self.gui_show_all_tables(tables_list) return tables_list
def setUpRowID(table_name): """Queries the last row_id from passed table and returns that value + 1 as an integer value.""" try: sql_m = SqlManager() row_id = sql_m.sql_get_last_row_id(table_name) + 1 except sqlite3.OperationalError: print("First row_id was set to 1") row_id = 1 return row_id
def __init__(self, sql_file, minsup): self.sql_manager = SqlManager(sql_file) self.sql_file = sql_file self.minsup = minsup tr_id = self.sql_manager.crs.execute( "select DISTINCT tr_id from transactions") self.total_item = len(list(tr_id)) print("TOTAL ", self.total_item) self.minsup_count = self.minsup * self.total_item
def read_data(sql_file): sql_manager = SqlManager(sql_file) data = sql_manager.crs.execute( "select description,count(description) from transactions group by description order by count(description) desc" ).fetchall() x_axis_data = [] y_axis_data = [] for item in data: x_axis_data.append(item[0]) y_axis_data.append(item[1]) return x_axis_data, y_axis_data
def test_create_exam_database(self): """Creates a database table with certain name, queries all tables and checks that the queried last table_name equals the initial name. """ sql_m = SqlManager() test_obj = TestVariables() table_name = test_obj.entry_field_content sql_m.sql_create_exam_database(table_name) queried_table_name = sql_m.sql_get_all_tables() queried_table_name = [tables.table_name for tables in queried_table_name][-1][-1] self.assertEqual(table_name + "_db", queried_table_name) print("test_create_exam_database: successful") self.tearDownTable(table_name + "_db")
def next_question(self, row_id, question, right_answers, user_answers, start_time, firsts): """Sets the score variable to 1 if right_answers and user_answers are equally. Creates the time_track_variable to save how long user took to answer a question. Adds or subtracts firsts variable accordingly to the score(add if score=0 add subtract if score=1). Checks if any firsts variable is lower than one and deletes that row if true. Passes all variables to the selected_results table. Then call the start_testing function again. """ try: if right_answers == user_answers: score = 1 else: score = 0 end_time = time.time() time_track = round(((end_time - start_time) / 60), 2) results = Results(row_id, question, right_answers, user_answers, score, time_track, firsts) results_list = [results] sql_m = SqlManager() sql_m.sql_insert_new_results( results_list, SettingVariables().selected_results_table) if score == 1: sql_m.sql_subtract_firsts( row_id, SettingVariables().selected_database_copy) elif score == 0: sql_m.sql_add_firsts(row_id, SettingVariables().selected_database_copy) if score == 1: firsts = sql_m.sql_get_row( SettingVariables().selected_database_copy, row_id)[0].firsts if firsts < 1: sql_m.sql_delete_row(SettingVariables().selected_database_copy, row_id) except IndexError as ie: print(str(ie) + " in next_question") print( "Possible reason might be that the database itself is empty or you choose an empty database_copy" ) try: self.testing_top.destroy() self.start_testing(SettingVariables().selected_database_copy) except Exception: print("Another exception in next_question occurred")
def create_results_tables(table_name, *args): """Creates a results table with the passed table name. *args is used only in the button widget to delete input inside the entry widget. *args: only needed to delete the gui entry_field after passing of data """ try: # creates 2 tables: 1. for saving results and 2. a copy of the selected database settings_obj = SettingVariables() selected_database = settings_obj.selected_database sql_m = SqlManager() sql_m.sql_create_results_table(table_name) sql_m.sql_create_database_copy(selected_database) except sqlite3.OperationalError as oe: print("Exception occurred in create_results_tables: " + str(oe))
def test_create_database_copy(self): """Creates a database copy table with certain name, queries all tables and checks that the queried last table_name equals the initial name. """ test_obj = TestVariables() selected_database = test_obj.selected_database self.setUpDatabase() sql_m = SqlManager() sql_m.sql_create_database_copy(selected_database) queried_table_name = sql_m.sql_get_all_tables() queried_table_name = [tables.table_name for tables in queried_table_name][-1][-1] self.assertEqual(selected_database + "_copy", queried_table_name) print("test_create_database_copy: successful") self.tearDownTable(selected_database) self.tearDownTable(selected_database + "_copy")
def set_up_settings(self): """Function selects each one of the three table types: database, results, database_copy.""" sql_m = SqlManager() all_tables = sql_m.sql_get_all_tables() database_list = DatabaseTables().get_settings_variables(all_tables) results_list = ResultsTables().get_settings_variables(all_tables) database_copy_list = DatabaseCopyTables().get_settings_variables( all_tables) database_list = [database.database_name for database in database_list] results_list = [results.results_name for results in results_list] database_copy_list = [ database_copy.database_copy_name for database_copy in database_copy_list ] self.gui_setting_menu(database_list, results_list, database_copy_list) return database_list, results_list, database_copy_list
def results_overview(self, top): """Calculates the average score and passes it as well as the last score to the gui_results_overview.""" try: sql_m = SqlManager() results_list = sql_m.sql_get_results_content( SettingVariables().selected_results_table) last_score = results_list[-1].score score_list = [score.score for score in results_list] score_average = 0 for score in score_list: score_average += score score_average = str(round(score_average / len(score_list) * 100, 2)) + " %" self.gui_results_overview(top, score_average, last_score) except IndexError as ie: print(str(ie) + " in results_overview") print( "This is OK, if our results table is empty. Otherwise you should inform your admin." )
def prepare_data(sql_file, rules): sql_manager = SqlManager(sql_file) data = {} for rule in rules: rule = rule + (rule[0] + rule[1], ) for side in rule: query = 'select count(Descriptions) from transactions2 where ' temp = '' for sub in side: query += 'Descriptions like ' + '"%' + str(sub).replace( '"', "'") + '%" and ' temp += str(sub) + ',' query = query[:-4] result = sql_manager.crs.execute(query).fetchall()[0][0] temp = temp[:-1] data[temp] = result tr_number = sql_manager.crs.execute( 'select COUNT(tr_id) from transactions2').fetchall()[0][0] return data, tr_number
def setUpInsertRow(self, table_name): """Inserts a row into the passed table(can be database or database copy table).""" row_id = self.setUpRowID(table_name) new_question = Question(row_id, "q", "a1", "a2", "a3", "a4", "a5", [0, 1, 0, 1, 0], 2) new_question = [new_question] sql_m = SqlManager() sql_m.sql_insert_new_questions(new_question, table_name) query_should = [row_id, "q", "a1", "a2", "a3", "a4", "a5", "[0, 1, 0, 1, 0]", 2] query_question_objects = sql_m.sql_get_database_content(table_name) q_obj = query_question_objects[-1] query_real = [q_obj.row_id, q_obj.question, q_obj.answer1, q_obj.answer2, q_obj.answer3, q_obj.answer4, q_obj.answer5, q_obj.right_answers, q_obj.firsts] return query_real, query_should
def start_testing(self, table_name=SettingVariables().selected_database_copy): """Queries content of the selected_database_copy table and selects randomly some row. Creates a random sequence of 5 digits in range 0 and 4 to sort newly the possible answers as well as right_answers sequence. Then pass all variables to the testing gui. """ sql_m = SqlManager() all_questions = sql_m.sql_get_database_content(table_name) random_row_id = random.randint(1, len(all_questions)) random_answer_seq = random.sample([0, 1, 2, 3, 4], 5) question_vars = all_questions[random_row_id - 1] random_question = [ question_vars.row_id, question_vars.question, question_vars.answer1, question_vars.answer2, question_vars.answer3, question_vars.answer4, question_vars.answer5, question_vars.right_answers, question_vars.firsts ] row_id = random_question[0] question_var = random_question[1] answer_vars = random_question[2:7] answer_vars = [answer_vars[i] for i in random_answer_seq] # transform right_answer variable from str to list with 5 integers right_answers = random_question[7] right_answers = [ int(right_answers[i]) for i in range(1, len(right_answers), 3) ] # save the right_answer variable in same order as the random_answer_seq right_answers_new = [right_answers[i] for i in random_answer_seq] firsts = random_question[8] question_obj = Question(row_id, question_var, answer_vars[0], answer_vars[1], answer_vars[2], answer_vars[3], answer_vars[4], right_answers_new, firsts) self.gui_testing_menu(question_obj) return random_answer_seq, random_row_id, question_obj
def test_sql_insert_new_results(self): """Creates a database and results tables, inserts a row into the database table and creates a database copy of the database table. Queries last row_id + 1 and creates a Results object to pass it to the sql_insert_new_results. Then queries last row of the results table and compares it to the variables initially passed. """ test_obj = TestVariables() selected_database = test_obj.selected_database selected_results_table = test_obj.selected_results_table selected_database_copy = test_obj.selected_database_copy self.setUpDatabase() self.setUpResultsTable() self.setUpInsertRow(selected_database) self.setUpDatabaseCopy() answers_right = test_obj.right_answers user_answers_right = test_obj.user_answers_right user_answers_wrong = test_obj.user_answers_wrong row_id = self.setUpRowID("database_table_db_db") results = self.setUpResultsObject(row_id, answers_right, user_answers_right, 1, firsts=2) sql_m = SqlManager() sql_m.sql_insert_new_results(results, selected_results_table) results_objects_list = sql_m.sql_get_results_content(selected_results_table) results_vars_list = results_objects_list[-1] results_vars_query = [results_vars_list.row_id, results_vars_list.question, results_vars_list.right_answers, results_vars_list.user_answers, results_vars_list.score, results_vars_list.time_track, results_vars_list.firsts] results_should_right = [row_id, "question", answers_right, user_answers_right, 1, 0.5, 2] results_should_wrong = [row_id, "question", answers_right, user_answers_wrong, 1, 0.5, 2] self.assertEqual(results_should_right, results_vars_query) self.assertNotEqual(results_should_wrong, results_vars_query) print("test_sql_insert_new_results: successful") self.tearDownTable(selected_database) self.tearDownTable(selected_results_table) self.tearDownTable(selected_database_copy)
def tearDownInsertRow(table_name, row_id): """Deletes a row from the passed table with the passed row_id.""" sql_m = SqlManager() sql_m.sql_delete_row(table_name, row_id)
self.update_selection_problems_probability_list() parents_pool = [ self.population[self.fps()] for i in range(self.population_size) ] random.shuffle(parents_pool) offsprings = self.generate_offsprings2(parents_pool=parents_pool) self.population = offsprings self.generation += 1 return max(self.population, key=lambda item: self.fitness_function(item)) if __name__ == '__main__': time1 = time.time() sql_manager = SqlManager(file="information.sqlite") sql_manager.create_database() for function in [one_max, peak, trap]: for max_gen in [100, 200, 300]: for problem_size in [10, 30, 50, 70, 100]: for pop_size in [50, 100, 200, 300]: for i in range(10): print( "\n_________________________________________________" ) print( f"problem_size={problem_size}\npop_size={pop_size}\nmax_gen={max_gen}\nfitness ={function.__name__}" ) sga = SGA(population_size=pop_size, problem_size=problem_size, fitness_function=function,
ax.set_xlabel("POP_SIZE") ax.set_ylabel('MAX_GEN') ax.set_zlabel('TIME') x_labels = [] for x in pop_sizes.tolist(): x_labels.append(" ") x_labels.append(x) y_labels = [] for y in max_gens.tolist(): y_labels.append(" ") y_labels.append(y) ax.set_xticklabels(x_labels) ax.set_yticklabels(y_labels) plt.show() if __name__ == '__main__': sql_manager = SqlManager("information.sqlite") df = pd.read_sql(sql="select * from information", con=sql_manager.conn) for func in [one_max, peak, trap]: func_df = query_by_fitness_function(func.__name__, df) print("____________________") # group_df = func_df.groupby(["problem_size", "pop_size", "max_gen"]).agg( # {'fitness_value': ['mean', 'std'], 'time': ['mean', 'std']}).reset_index() group_df = func_df.apply(calculate_color, axis=1) print(group_df) draw_plot1(group_df, func.__name__) draw_plot2(group_df, func.__name__) draw_plot3(group_df, func.__name__)
def create_exam_database(table_name, *args): """Creates a database table with the passed table name. *args: only needed to delete the gui entry_field after passing of data """ sql_m = SqlManager() sql_m.sql_create_exam_database(table_name)
def show_remaining_questions(self): """Opens a window which shows contents of selected_database_copy table.""" sql_m = SqlManager() database_copy = sql_m.sql_get_database_content( SettingVariables().selected_database_copy) self.gui_show_remaining_questions(database_copy)
def show_testing_results(self): """Opens a window which shows contents of selected_results table.""" sql_m = SqlManager() results_list = sql_m.sql_get_results_content( SettingVariables().selected_results_table) self.gui_show_testing_results(results_list)
from tkinter import * import sqlite3 from app_interface import AppInterface from sql_manager import SqlManager # todo errors should be saved to logfile # Create an example database table sql_m = SqlManager() try: sql_m.sql_create_exam_database("example_exam_database") print("Example Table Was Created") except sqlite3.OperationalError as oe: print("sqlite3.OperationalError occurred: " + str(oe)) # ===== Main ===== root = Tk() root.title("SQL Exam Simulator") gui_w = AppInterface(root) gui_w.gui_main_menu(root) AppInterface().set_up_settings() root.mainloop()
def tearDownTable(table_name): """Deletes a table with the passed name.""" sql_m = SqlManager() sql_m.sql_delete_table(table_name)
def setUpDatabase(): """Creates new database table.""" test_obj = TestVariables() sql_m = SqlManager() sql_m.sql_create_exam_database(test_obj.entry_field_content)
def setUpResultsTable(): """Creates new results table.""" test_obj = TestVariables() results_table = test_obj.selected_results_table sql_m = SqlManager() sql_m.sql_create_results_table(results_table)
def setUpDatabaseCopy(): """Creates new copy of the selected database table.""" test_obj = TestVariables() selected_database = test_obj.selected_database sql_m = SqlManager() sql_m.sql_create_database_copy(selected_database)