def test3_querying_the_database_does_not_cause_exception(self): # Arrange exception_raised = False database = db._connect_to_database() # Act try: db._query_database('show tables;') except: exception_raised = True # Assert self.assertFalse(exception_raised) database.close()
def test1_database_should_have_5_tables(self): # Arrange database = db._connect_to_database() # Act response = db._query_database('show tables;') # Assert self.assertEqual(len(response), 5) db._close_database(database)
def fetch_orders_from_round(round_id: int): query = "SELECT * FROM orders WHERE round_id=" + str(round_id) results = db._query_database(query) orders = [] for result in results: order = _parse_order_row(result) orders.append(order) return orders
def get_person_by_names(first_name:str,surname:str,slack_id:str): try: query = f'SELECT * FROM people WHERE first_name="{first_name}" AND surname="{surname}" AND slack_id="{slack_id}"' results = db._query_database(query) if len(results) > 0: person = _parse_person_row(results[0]) return person else: print("There was no-one found that matched your query.") except: print("Connection Error.")
def get_drink(drink_name:str): try: query = f"SELECT * FROM drinks WHERE drink_name=\"{drink_name}\"" results = db._query_database(query) if len(results) == 1: drink = _parse_drink_row(results[0]) return drink else: print("There was no drink found with this name.") except: print("Connection Error.")
def get_preference(person_id:int): try: query = "SELECT drink_name FROM preferences WHERE person_id="+str(person_id) results = db._query_database(query) if len(results) == 1: drink_name = results[0][0] return drink_name else: print("There was no preference found for this person.") except: print("Connection Error.")
def get_round_by_id(round_id:int): try: query = "SELECT * FROM rounds WHERE round_id="+str(round_id) results = db._query_database(query) if len(results) == 1: active_round = _parse_round_row(results[0]) return active_round else: print("There was no active round found.") except: print("Connection Error.")
def get_person_by_id(person_id:int): try: query = "SELECT * FROM people WHERE person_id = "+str(person_id) results = db._query_database(query) if len(results) == 1: person = _parse_person_row(results[0]) return person else: print("There was no-one found that matched your query.") except: print("Connection Error.")
def get_active_round(): try: query = "SELECT * FROM rounds WHERE active=1" results = db._query_database(query) if len(results) > 0: active_round = _parse_round_row(results[0]) return active_round else: print("There was no active round found.") except: print("Connection Error.")
def test5_drinks_table_should_have_correct_columns(self): # Arrange database = db._connect_to_database() passed = True # Act response = db._query_database('describe drinks;') # Assert if response[0] != ('drink_name', 'varchar(100)', 'NO', 'PRI', None, ''): passed = False if response[1] != ('drink_type', 'varchar(100)', 'YES', '', None, ''): passed = False self.assertTrue(passed) db._close_database(database)
def test10_preferences_table_should_have_correct_columns(self): # Arrange database = db._connect_to_database() passed = True # Act response = db._query_database('describe preferences;') # Assert if response[0] != ('person_id', 'int(11)', 'NO', 'PRI', None, ''): passed = False if response[1] != ('drink_name', 'varchar(100)', 'YES', 'MUL', None, ''): passed = False self.assertTrue(passed) db._close_database(database)
def test7_rounds_table_should_have_correct_columns(self): # Arrange database = db._connect_to_database() passed = True # Act response = db._query_database('describe rounds;') # Assert if response[0] != ('round_id', 'int(11)', 'NO', 'PRI', None, 'auto_increment'): passed = False if response[1] != ('owner_id', 'int(11)', 'YES', 'MUL', None, ''): passed = False if response[2] != ('active', 'tinyint(1)', 'YES', '', '1', ''): passed = False self.assertTrue(passed) db._close_database(database)
def test3_people_table_should_have_correct_columns(self): # Arrange database = db._connect_to_database() passed = True # Act response = db._query_database('describe people;') # Assert if response[0] != ('person_id', 'int(11)', 'NO', 'PRI', None, 'auto_increment'): passed = False if response[1] != ('first_name', 'varchar(100)', 'YES', '', None, ''): passed = False if response[2] != ('surname', 'varchar(100)', 'YES', '', None, ''): passed = False if response[3] != ('slack_id', 'varchar(100)', 'YES', '', None, ''): passed = False self.assertTrue(passed) db._close_database(database)
def test8_orders_table_should_have_correct_columns(self): # Arrange database = db._connect_to_database() passed = True # Act response = db._query_database('describe orders;') # Assert if response[0] != ('order_id', 'int(11)', 'NO', 'PRI', None, 'auto_increment'): passed = False if response[1] != ('person_id', 'int(11)', 'YES', 'MUL', None, ''): passed = False if response[2] != ('drink_name', 'varchar(100)', 'YES', 'MUL', None, ''): passed = False if response[3] != ('round_id', 'int(11)', 'YES', 'MUL', None, ''): passed = False self.assertTrue(passed) db._close_database(database)
def alter_preference(person_id:int, drink_name:str): try: query = f"UPDATE preferences SET drink_name=\"{drink_name}\" WHERE person_id={str(person_id)}" db._query_database(query,False) except: print("Connection Error.")
def set_round_inactive(round_id: int): query = f"UPDATE rounds SET active=0 WHERE round_id={round_id}" db._query_database(query, False)
def create_test_table(): db._query_database("CREATE TABLE "+test_table_name+" ( "+test_table_field+" VARCHAR(100) );")
def delete_test_table(): db._query_database(f"DROP TABLE {test_table_name};")