def update_by_template_test(template=None, new_values=None): """ :param data_table: :param template: :param new_values: :return: """ connect_info = {"directory": data_dir, "file_name": "Salaries.csv"} csv_tbl = CSVDataTable("Salaries", connect_info, key_columns=['playerID']) template = {"playerID": "barkele01", "yearID": "1985"} new_values = {"yearID": "1777"} affected_num = csv_tbl.update_by_template(template=template, new_values=new_values) print("performing update_by_template_test") print("updated and the affected_num is:") print(affected_num)
def t_load_good(): connect_info = {"directory": data_dir, "file_name": "Batting.csv"} file = "csv_table_test.txt" with open(file, 'a+') as f: f.write("\n\n******************** " + "test_load_good" + " ********************") f.write( "\nTable is " + connect_info["file_name"] + " and key_columns are ['playerID','teamID', 'yearID', 'stint']") try: csv_tbl = CSVDataTable( "batting", connect_info, key_columns=['playerID', 'teamID', 'yearID', 'stint']) with open(file, 'a+') as f: f.write("\nLoaded table = \n" + str(csv_tbl)) f.write("\nThis is the correct answer.") except Exception as de: with open(file, 'a+') as f: f.write("\nLoad failed. Exception = " + str(de)) f.write("\nThis is the wrong answer.") with open(file, 'a+') as f: f.write("\n******************** " + "end test_load_good" + " ********************")
def update_by_key_test(key_fields=None, new_values=None): """ :param data_table: :param key_fields: :param new_values: :return: """ connect_info = {"directory": data_dir, "file_name": "Salaries.csv"} csv_tbl = CSVDataTable("Salaries", connect_info, key_columns=['playerID']) key_fields = ["barkele01"] new_values = {"playerID": "barkele01"} affected_num = csv_tbl.update_by_key(key_fields=key_fields, new_values=new_values) print("performing update_by_key_test") print("updated and the affected_num is:") print(affected_num)
def test_update_by_key(): connect_info = {"directory": data_dir, "file_name": "People.csv"} csv_tbl = CSVDataTable("people", connect_info, key_columns=["playerID"]) n = csv_tbl.update_by_key(key_fields=["aasedo01"], new_values={ "birthYear": "3000", "birthMonth": "1" }) print(n, "recolds updated.") print( csv_tbl.find_by_template( template={"playerID": "aasedo01"}, field_list=["playerID", "birthYear", "birthMonth"]))
def t_load(): connect_info = {"directory": data_dir, "file_name": "People.csv"} csv_tbl = CSVDataTable("people", connect_info, None) print("Created table = " + str(csv_tbl))
def t_insert(): connect_info = { "directory": data_dir, "file_name": "Batting.csv" } key_cols = ['playerID', 'teamID', 'yearID', 'stint'] fields = ['playerID', 'teamID', 'yearID'] new_rec = {'playerID': 'cl3910', 'teamID': 'CU', 'yearID': '2019'} csv_tbl = CSVDataTable("batting", connect_info, key_columns=key_cols) csv_tbl.insert(new_record=new_rec) res = csv_tbl.find_by_template(template=new_rec, field_list=fields) print("New record inserted: \n", json.dumps(res, indent=2))
def test_insert_fail(): print("\n******************** " + "test_insert_fail" + " ********************") try: bad_row = { "playerID": "arroych01", "birthYear": "2000", "birthMonth": "1", "birthDay": "1" } csv_tbl = CSVDataTable(table_name, connect_info, key_columns) print("Attempting to insert bad row = ", json.dumps(bad_row, indent=2)) csv_tbl.insert(bad_row) except Exception as e: print("Insert failed. Exception = ", e) print("Correct answer.") print("******************** " + "end test_insert_fail" + " ********************\n")
def t_insert(): connect_info = { "directory": data_dir, "file_name": "Batting.csv" } key_cols = ['playerID', 'teamID', 'yearID', 'stint'] csv_tbl = CSVDataTable("batting", connect_info, key_columns=key_cols) new_record = {'playerID': 'aaa', 'yearID': '1997', 'teamID': '1997', 'stint': '1'}
def t_load_csv(data_dir, file_name, key_cols): connect_info = { "directory": data_dir, "file_name": file_name } csv_tbl = CSVDataTable(file_name.split('.csv')[0], connect_info, key_cols) print("Created table = " + str(csv_tbl))
def t_load(): connect_info = { "directory": data_dir, "file_name": "People.csv" } csv_tbl = CSVDataTable("people", connect_info, None) print('Loaded table: \n', csv_tbl)
def test_find_by_template(): connect_info = {"directory": data_dir, "file_name": "People.csv"} csv_tbl = CSVDataTable("people", connect_info, None) result1_single_record = csv_tbl.find_by_template( template={ "playerID": "aasedo01", "birthYear": "1954" }, field_list=["playerID", "birthYear", "birthMonth"]) print(result1_single_record) result2_multi_records = csv_tbl.find_by_template( template={"birthYear": "1954"}, field_list=["playerID", "birthYear", "birthMonth"]) print(result2_multi_records)
def t_load(): connect_info = { "directory": data_dir, "file_name": "Batting.csv" } csv_tbl = CSVDataTable("batting", connect_info, key_columns=['playerID', 'teamID', 'yearID', 'stint']) print("Created table = " + str(csv_tbl))
def test_update_by_key_good(): print("\n******************** " + "test_update_by_key_good" + " ********************") try: csv_tbl = CSVDataTable(table_name, connect_info, key_columns) print('Update by primary key "abadijo01", new value is {"nameFirst": "Jackson", "nameLast": "Copper"}') r1 = csv_tbl.find_by_primary_key(["abadijo01"]) print("BEFORE updating, the row =\n", json.dumps(r1, indent=2)) print("Updating...") r2 = csv_tbl.update_by_key(["abadijo01"], {"nameFirst": "Jackson", "nameLast": "Copper"}) print("Update returned ", r2, "\n") r3 = csv_tbl.find_by_primary_key(["abadijo01"]) print('AFTER Updating, the row =\n', json.dumps(r3, indent=2)) print("Correct answer.") except Exception as e: print("Exception =", e) print("Wrong answer.") print("******************** " + "end test_update_by_key_good" + " ********************\n")
def test_load_failure(self): with self.assertRaises(Exception) as context: csv_tbl_s = CSVDataTable("people", connect_fail("3_people_dup.csv"), PRIMARY_KEY_SINGLE_FILED) self.assertEqual(PK_UNIQUE_ERROR, str(context.exception)) with self.assertRaises(Exception) as context: csv_tbl_m = CSVDataTable("people", connect_fail("3_people_dup.csv"), PRIMARY_KEY_MULTIPLE_FIELD) self.assertEqual(PK_UNIQUE_ERROR, str(context.exception)) with self.assertRaises(Exception) as context: csv_tbl_m = CSVDataTable("people", connect_fail("2_people_missing_key.csv"), PRIMARY_KEY_MULTIPLE_FIELD) self.assertEqual("Some row does not have primary key info!", str(context.exception))
def test_insert_good(): print("\n******************** " + "test_insert_good" + " ********************") try: good_row = { "playerID": "coms4111f19", "birthYear": "2000", "birthMonth": "1", "birthDay": "1" } csv_tbl = CSVDataTable(table_name, connect_info, key_columns) print("Attempting to insert good row = ", json.dumps(good_row, indent=2)) csv_tbl.insert(good_row) print("Correct answer.") except Exception as e: print("Insert failed. Exception = ", e) print("Wrong answer.") print("******************** " + "end test_insert_good" + " ********************\n")
def test_find_function(): print("\n This is a test for find_by_tmp function and find_by_primary_key of CSVTable") t1 = CSVDataTable("offices", connect_info={ "directory": "/Users/chenchenwei/Desktop/COMS4111 Databases/HW1/Data", "file_name": "offices.csv" }, key_columns=['officeCode'], debug=True) print("t1 = " , t1) print("\n test find_by_template: 'city: Boston' ") print(t1.find_by_template(template={'city': 'Boston'}, field_list= ['officeCode', 'city', 'state'])) print("\n test find_by_primary_key: '1'") print(t1.find_by_primary_key([ '1' ], field_list=None)) print("\n test find_by_primary_key: 'zychto0111', should return None" ) print(t1.find_by_primary_key(['zychto0111'], field_list=None) )
def test_matches_template(): connect_info = {"directory": data_dir, "file_name": "People.csv"} csv_tbl = CSVDataTable("people", connect_info, None) match = csv_tbl.matches_template(csv_tbl.rows[0], template={ "playerID": "aasedo01", "birthYear": "1954" }) print(match) match = csv_tbl.matches_template(csv_tbl.rows[3], template={ "playerID": "aasedo01", "birthYear": "1954" }) print(match)
def test_init_fail(): print("\n******************** " + "test_init_fail" + " ********************") try: print("Table is People and key_columns are ['playerID', 'playerID']") CSVDataTable(table_name, connect_info, ['playerID', 'playerID']) except Exception as e: print("Init failed. Exception =", e) print("Correct answer.") print("******************** " + "end test_init_fail" + " ********************\n")
def test_create_initial(): print("\nThis is a test for initialiazation of CSVTable") t1 = CSVDataTable("People", connect_info={ "directory": "/Users/chenchenwei/Desktop/COMS4111 Databases/HW1/Data", "file_name": "People.csv" }, key_columns=['playerID'], debug=True) print("t1 = ", t1)
def t_insert(): connect_info = { "directory": "../Data", "file_name": "orderdetails.csv" } new_item = { "orderNumber": "10025", "productCode": "s19_3171" } fields = ['orderNumber', 'productCode'] csv_tbl = CSVDataTable("orderdetails", connect_info, key_columns=['productCode']) tmp = {'orderNumber': "10025"} res_bef = csv_tbl.find_by_template(template=tmp, field_list=fields) print("Query result= \n", json.dumps(res_bef, indent=2)) res = csv_tbl.insert(new_item) res_aft = csv_tbl.find_by_template(template=tmp, field_list=fields) print("Query result= \n", json.dumps(res_aft, indent=2))
def test_load_fail2(): print("\n******************** " + "test_load_fail2" + " ********************") try: print("Table is People and key_columns are ['birthYear']") CSVDataTable(table_name, connect_info, ['birthYear']) except Exception as e: print("Load failed. Exception =", e) print("Correct answer.") print("******************** " + "end test_load_fail2" + " ********************\n")
def test_delete_by_key_success(self): csv_tbl_s = CSVDataTable("people", CONNECT_INFO, PRIMARY_KEY_SINGLE_FILED) csv_tbl_m = CSVDataTable("people", CONNECT_INFO, PRIMARY_KEY_MULTIPLE_FIELD) result_s_1 = csv_tbl_s.delete_by_key(PRIMARY_KEY_SINGLE_VALUE) result_s_0 = csv_tbl_s.delete_by_key(["aaa"]) self.assertEqual(result_s_0, 0) self.assertEqual(result_s_1, 1) result_m_1 = csv_tbl_m.delete_by_key(PRIMARY_KEY_MULTIPLE_VALUE) result_m_0 = csv_tbl_m.delete_by_key(["aaa", "bbb"]) self.assertEqual(result_m_0, 0) self.assertEqual(result_m_1, 1)
def t_duplicates(): connect_info = { "directory": data_dir, "file_name": "Batting.csv" } key_cols = ['playerID','teamID','yearID','stint'] csv_tbl = CSVDataTable('batting',connect_info,key_columns=key_cols) new_row = { 'playerID': 'willite01', 'teamID': 'BOS', 'yearID':'1960', 'stint':'1', 'H':'21','AB':'22' } res =
def t_update_by_key(data_dir, file_name, key_cols, keys, updates): connect_info = { "directory": data_dir, "file_name": file_name } csv_tbl = CSVDataTable(file_name.split('.csv')[0], connect_info, key_cols) existing = csv_tbl.find_by_template(template=updates) if existing is None: existing_len = 0 else: existing_len = len(existing) existing_tmp = csv_tbl.find_by_primary_key(key_fields=keys) if existing_tmp is None: existing_tmp_len = 0 else: existing_tmp_len = len(existing_tmp) res = csv_tbl.update_by_key(key_fields=keys, new_values=updates) check_updates = csv_tbl.find_by_template(template=updates) if check_updates is None: check_updates_len = 0 else: check_updates_len = len(check_updates) if res > 0 and check_updates_len >= existing_len: print(existing_len, "record(s) found before updates and", check_updates_len, "record(s) found after updates. Update on", file_name.split('.csv')[0], "with keys", keys, "\nand updates", updates, "was successful.\n", "Data found based on updated values:\n", json.dumps(check_updates, indent=2)) elif res == 0 and existing_tmp_len == 0: print("No match found for update based on the given keys", keys, "\nTest Successful. 0 rows updated")
def test_update_by_key(): connect_info = {"directory": data_dir, "file_name": "People.csv"} csv_tbl = CSVDataTable("people", connect_info, ["playerID"]) print(csv_tbl.find_by_primary_key(["abernte01"])) new_values = {"birthYear": "2000"} csv_tbl.update_by_key(["abernte01"], new_values) print(csv_tbl.find_by_primary_key(["abernte01"]))
def t_update_by_template_fail(): connect_info = {"directory": data_dir, "file_name": "Batting.csv"} key_cols = ['playerID', 'teamID', 'yearID', 'stint'] file = "csv_table_test.txt" with open(file, 'a+') as f: f.write("\n\n******************** " + "test_update_by_tamplate_fail" + " ********************") try: csv_tbl = CSVDataTable("batting", connect_info, key_columns=key_cols) tmp = { 'playerID': 'aardsda01', 'yearID': '2015', 'stint': '1', 'teamID': 'ATL' } with open(file, 'a+') as f: f.write("\nLooking up with template = " + str(tmp)) r1 = csv_tbl.find_by_template(tmp) new_values = {'yearID': '2008', 'teamID': 'BOS'} with open(file, 'a+') as f: f.write("\nReturned row = '\n'" + str(json.dumps(r1, indent=3))) f.write("\nAttempt to update this row with bad new values = " + str(json.dumps(new_values, indent=3))) r2 = csv_tbl.update_by_template(tmp, new_values) with open(file, 'a+') as f: f.write("\nUpdate returned" + str(r2)) r3 = csv_tbl.find_by_template(tmp) with open(file, 'a+') as f: f.write("\nQuery result after update='\n'" + str(json.dumps(r3, indent=3))) f.write("\nThis is the wrong answer") except Exception as e: with open(file, 'a+') as f: f.write("\nUpdate failed. Exception = " + str(e)) f.write("\nThis is the correct answer.") with open(file, 'a+') as f: f.write("\n******************** " + "end_test_update_by_tamplate_fail" + " ********************")
def test_delete_by_key(): print("\n******************** " + "test_delete_by_key" + " ********************") print("Compare time elapsed for delete_by_key.") csv_tbl = CSVDataTable(table_name, csv_connect_info, key_columns) rdb_tbl = RDBDataTable(table_name, rdb_connect_info, key_columns) start1 = time.time() r1 = csv_tbl.delete_by_key(['aardsda01']) end1 = time.time() elapsed1 = end1 - start1 print("Time elapsed for CSVDataTable is ", elapsed1, "seconds.") start2 = time.time() r2 = rdb_tbl.delete_by_key(['aardsda01']) end2 = time.time() elapsed2 = end2 - start2 print("Time elapsed for RDBDataTable is ", elapsed2, "seconds.") print("******************** " + "end test_delete_by_key" + " ********************\n")
def t_insert(): connect_info = {"directory": data_dir, "file_name": "Batting.csv"} key_cols = ['playerID', 'teamID', 'yearID', 'stint'] fields = ['playerID', 'teamID', 'yearID', 'stint', 'AB', 'H', 'HR', 'RBI'] tmp = {'teamID': 'BOS', 'yearID': '1960'} new_r = { "playerID": "a", "teamID": "BOS", "yearID": "1960", "stint": "1", "AB": "1", "H": "100", "HR": "100", "RBI": "100" } csv_tbl = CSVDataTable("batting", connect_info, key_cols) res = csv_tbl.insert(new_record=new_r) print("Created table = " + str(csv_tbl))
def t_tmp_r(): r = { "playerID": "webstra02", "teamID": "BOS", "yearID": "1960", "AB": "3", "H": "0", "HR": "0", "RBI": "1" } fields = ['playerID', 'yearID', 'HR'] test = CSVDataTable.find_tmp_row(r, fields) print("Matches = ", test)
def t_matches_template(): r = { "playerID": "worthal01", "teamID": "BOS", "yearID": "1960", "AB": "1", "H": "0", "HR": "0", "RBI": "0" } tmp = {"playerID": "willite01"} test = CSVDataTable.matches_template(r, tmp) print("Matches = ", test)