def t_update_by_key_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_key_fail" + " ********************") try: csv_tbl = CSVDataTable("batting", connect_info, key_columns=key_cols) key_fields = ['aardsda01', 'ATL', '2015', '1'] with open(file, 'a+') as f: f.write("\nLooking up with key = " + str(key_fields)) r1 = csv_tbl.find_by_primary_key(key_fields) with open(file, 'a+') as f: f.write("\nReturned row = '\n'" + str(json.dumps(r1, indent=3))) new_values = {'yearID': '2008', 'teamID': 'BOS'} with open(file, 'a+') as f: f.write("\nAttempt to update this row with bad new values = " + str(json.dumps(new_values, indent=3))) r2 = csv_tbl.update_by_key(key_fields, new_values) with open(file, 'a+') as f: f.write("\nUpdate returned " + str(r2)) r3 = csv_tbl.find_by_primary_key(key_fields) 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_key_fail" + " ********************")
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_key(): connect_info = { "directory": data_dir, "file_name": "People.csv" } csv_tbl = CSVDataTable("people", connect_info, key_columns=['playerID']) print(csv_tbl.find_by_primary_key(['aardsda01'])) result = csv_tbl.update_by_key(['aardsda01'], {'birthCity': 'Beijing', 'deathYear': '2019'}) print(result) print(csv_tbl.find_by_primary_key(['aardsda01']))
def t_delete_by_key(): connect_info = { "directory": data_dir, "file_name": "People.csv" } csv_tbl = CSVDataTable("people", connect_info, key_columns=['playerID']) print(csv_tbl.find_by_primary_key(['aaronha01'])) result = csv_tbl.delete_by_key(['aaronha01']) print(result) print(csv_tbl.find_by_primary_key(['aaronha01']))
def test_find_by_primary_key_fail2(): print("\n******************** " + "test_find_by_primary_key_fail2" + " ********************") try: csv_tbl = CSVDataTable(table_name, connect_info, key_columns) print("Find by primary key with None key fields") csv_tbl.find_by_primary_key(None) except Exception as e: print("Exception =", e) print("Correct answer.") print("******************** " + "end test_find_by_primary_key_fail2" + " ********************\n")
def t_update_by_tmp(): tmp = {'nameLast': 'Williams', 'nameFirst': 'Ted'} new_values = {'birthCity': 'Tokyo', 'deathYear': '2020'} connect_info = { "directory": data_dir, "file_name": "People.csv" } csv_tbl = CSVDataTable("people", connect_info, key_columns=['playerID']) print(csv_tbl.find_by_primary_key(['willite01'])) result = csv_tbl.update_by_template(tmp, new_values) print(result) print(csv_tbl.find_by_primary_key(['willite01']))
def test_update_by_template_success_normal(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 = csv_tbl_s.update_by_template(TEMPLATE, UPDATE_NORMAL_SINGLE) result_m = csv_tbl_m.update_by_template(TEMPLATE, UPDATE_NORMAL_MULTIPLE) self.assertEqual(1, result_s) self.assertEqual(UPDATE_TEMPLATE_SINGLE_RESULT, csv_tbl_s.find_by_primary_key(["aardsda01"])) self.assertEqual(1, result_m) self.assertEqual( UPDATE_TEMPLATE_MULTIPLE_RESULT, csv_tbl_m.find_by_primary_key(["aardsda01", "Aardsma"]))
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_insert_success(self): csv_tbl = CSVDataTable("people", CONNECT_INFO, PRIMARY_KEY_SINGLE_FILED) self.assertTrue("abcdefg" not in csv_tbl._primary_keys_set) csv_tbl.insert(INSERT_ROW) self.assertTrue("abcdefg" in csv_tbl._primary_keys_set) self.assertEqual(INSERT_ROW, csv_tbl.find_by_primary_key(["abcdefg"]))
def t_find_by_key(): connect_info = {"directory": data_dir, "file_name": "People.csv"} csv_tbl = CSVDataTable("people", connect_info, key_columns=['playerID']) result = csv_tbl.find_by_primary_key(['aaronto01'], ['nameFirst', 'nameLast']) print(result)
def t_find_by_pk(): connect_info = {"directory": data_dir, "file_name": "Batting.csv"} key_cols = ['playerID', 'teamID', 'yearID', 'stint'] fields = ['playerID', 'teamID', 'yearID', 'AB', 'H', 'HR', 'RBI'] key_vals = ['willite01', 'BOS', '1960', '1'] file = "csv_table_test.txt" with open(file, 'a+') as f: f.write("\n\n******************** " + "test_find_by_primary_key" + " ********************") f.write("\nThe key columns of the table are " + str(key_cols)) f.write( "\nThe values for the key_columns to use to find a record are " + str(key_vals)) f.write("\nThe subset of the fields of the record to return is " + str(fields)) try: csv_tbl = CSVDataTable("batting", connect_info, key_columns=key_cols) res = csv_tbl.find_by_primary_key(key_vals, fields) with open(file, 'a+') as f: f.write("\nQuery result ='\n'" + str(json.dumps(res, indent=3))) f.write("\nThis is the right answer.") except Exception as err: with open(file, 'a+') as f: f.write("\nFind failed. Exception = " + str(err)) f.write("\nThis is the wrong answer.") with open(file, 'a+') as f: f.write("\n******************** " + "end_test_find_by_primary_key" + " ********************")
def test_update_by_key_success_normal(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 = csv_tbl_s.update_by_key(PRIMARY_KEY_SINGLE_VALUE, UPDATE_NORMAL_SINGLE) result_m = csv_tbl_m.update_by_key(PRIMARY_KEY_MULTIPLE_VALUE, UPDATE_NORMAL_MULTIPLE) self.assertEqual(1, result_s) self.assertEqual( UPDATE_NORMAL_RESULT_SINGLE, csv_tbl_s.find_by_primary_key(PRIMARY_KEY_SINGLE_VALUE)) self.assertEqual(1, result_m) self.assertEqual( UPDATE_NORMAL_RESULT_MULTIPLE, csv_tbl_m.find_by_primary_key(PRIMARY_KEY_MULTIPLE_VALUE))
def test_insert(): connect_info = {"directory": data_dir, "file_name": "People.csv"} csv_tbl = CSVDataTable("people", connect_info, key_columns=["playerID"]) csv_tbl.insert({"playerID": "rx2166", "birthYear": 1996}) print(csv_tbl.find_by_primary_key(["rx2166"], ["playerID", "birthYear"]))
def test_match_all_by_key(): connect_info = {"directory": data_dir, "file_name": "Batting.csv"} key_cols = ['playerID', 'yearID', 'stint', 'teamID'] fields = ['playerID', 'teamID', 'yearID', 'AB', 'H', 'HR', 'RBI'] key_vals = ['willite01', '1960', '1', 'BOS'] csv_tbl = CSVDataTable("Batting.csv", connect_info, key_columns=key_cols) result = csv_tbl.find_by_primary_key(key_vals) print("Maching all by key result: \n", json.dumps(result, indent=2))
def test_find_by_primary_key_failure(self): csv_tbl = CSVDataTable("people", CONNECT_INFO, PRIMARY_KEY_SINGLE_FILED) with self.assertRaises(Exception) as context: result = csv_tbl.find_by_primary_key([""], [""]) self.assertEqual("field column is not a subset of table columns!", str(context.exception)) with self.assertRaises(Exception) as context: result = csv_tbl.find_by_primary_key([]) self.assertEqual('Invalid key fields!', str(context.exception)) csv_tbl = CSVDataTable("people", CONNECT_INFO, None) with self.assertRaises(Exception) as context: result = csv_tbl.find_by_primary_key([]) self.assertEqual('Primary Key has not been setup yet!', str(context.exception))
def t_csv_find_by_key(): connect_info = {"directory": data_dir, "file_name": "People.csv"} csv_tbl = CSVDataTable("people", connect_info, ["playerID"]) key_fields = ["willite01"] subset = csv_tbl.find_by_primary_key( key_fields, field_list=["playerID", "nameLast", "nameFirst"]) print("Found by key") print(str(pd.DataFrame(subset)))
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 t_insert(): new_record = {'playerID': 'newplayer01', 'nameFirst': 'Lexi', 'nameLast': 'Ma'} connect_info = { "directory": data_dir, "file_name": "People.csv" } csv_tbl = CSVDataTable("people", connect_info, key_columns=['playerID']) csv_tbl.insert(new_record) print(csv_tbl.find_by_primary_key(['newplayer01']))
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_find_by_primary_key(): connect_info = {"directory": data_dir, "file_name": "People.csv"} csv_tbl = CSVDataTable("people", connect_info, key_columns=["playerID"]) result = csv_tbl.find_by_primary_key( key_fields=["aasedo01"], field_list=["playerID", "birthYear", "birthMonth"]) print(result)
def test_find_by_primary_key(): connect_info = {"directory": data_dir, "file_name": "_Small.csv"} key_cols = ['yearID', 'stint', 'teamID'] key_values = ["1871", "1", "CL1"] csv_tbl = CSVDataTable("batting", connect_info, key_columns=key_cols) output = csv_tbl.find_by_primary_key(key_fields=key_values) print("Query Result: \n", json.dumps(output, indent=2))
def t_find_by_pk(): connect_info = { "directory": data_dir, "file_name": "Batting.csv" } key_cols = ['playerID', 'teamID', 'yearID', 'stint'] fields = ['playerID', 'teamID', 'yearID', 'AB', 'H', 'HR', 'RBI'] key_vals = ['willite01', 'BOS', '1960', '1'] csv_tbl = CSVDataTable("batting", connect_info, key_columns=key_cols) res = csv_tbl.find_by_primary_key(key_vals, fields) print("Query result= \n", json.dumps(res, indent=2))
def t_find_by_primary_key(data_dir, file_name, key_cols, fields, keys): connect_info = { "directory": data_dir, "file_name": file_name } csv_tbl = CSVDataTable(file_name.split('.csv')[0], connect_info, key_cols) res = csv_tbl.find_by_primary_key(key_fields=keys, field_list=fields) if res is None: print("Find By Primary Key on", file_name.split('.csv')[0], "with keys", keys, ":\n", "None\n") else: print("Find By Primary Key on", file_name.split('.csv')[0], "with keys ", keys, ":\n", json.dumps(res, indent=2), '\n')
def test_find_by_primary_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 = csv_tbl_s.find_by_primary_key(PRIMARY_KEY_SINGLE_VALUE) result_m = csv_tbl_m.find_by_primary_key(PRIMARY_KEY_MULTIPLE_VALUE) self.assertEqual(PRIMARY_KEY_RESULT_ALL_FIELDS, result_s) self.assertEqual(PRIMARY_KEY_RESULT_ALL_FIELDS, result_m) result_s = csv_tbl_s.find_by_primary_key(PRIMARY_KEY_SINGLE_VALUE, PRIMARY_KEY_SELECT_FIELDS) result_m = csv_tbl_m.find_by_primary_key(PRIMARY_KEY_MULTIPLE_VALUE, PRIMARY_KEY_SELECT_FIELDS) self.assertEqual(PRIMARY_KEY_RESULT_SELECT_FIELDS, result_s) self.assertEqual(PRIMARY_KEY_RESULT_SELECT_FIELDS, result_m) csv_tbl_n = CSVDataTable("people", CONNECT_INFO, PRIMARY_KEY_SINGLE_FILED) result = csv_tbl_n.find_by_primary_key(["a"]) self.assertEqual(None, result)
def test_update_by_template_success_primary_key(self): csv_tbl_s = CSVDataTable("people", CONNECT_INFO, PRIMARY_KEY_SINGLE_FILED) self.assertTrue("aardsda01" in csv_tbl_s._primary_keys_set) self.assertTrue("aaa" not in csv_tbl_s._primary_keys_set) result_s = csv_tbl_s.update_by_template(TEMPLATE, UPDATE_PK_SINGLE) self.assertEqual(1, result_s) self.assertEqual(UPDATE_TEMPLATE_PK_SINGLE_RESULT, csv_tbl_s.find_by_primary_key(["aaa"])) self.assertTrue("aardsda01" not in csv_tbl_s._primary_keys_set) self.assertTrue("aaa" in csv_tbl_s._primary_keys_set) csv_tbl_m = CSVDataTable("people", CONNECT_INFO, PRIMARY_KEY_MULTIPLE_FIELD) self.assertTrue("aardsda01_Aardsma" in csv_tbl_m._primary_keys_set) self.assertTrue("aaa_bbb" not in csv_tbl_m._primary_keys_set) result_m = csv_tbl_m.update_by_template(TEMPLATE, UPDATE_PK_MULTIPLE) self.assertEqual(1, result_m) self.assertEqual(UPDATE_TEMPLATE_PK_MULTIPLE_RESULT, csv_tbl_m.find_by_primary_key(["aaa", "bbb"])) self.assertTrue("aardsda01_Aardsma" not in csv_tbl_m._primary_keys_set) self.assertTrue("aaa_bbb" in csv_tbl_m._primary_keys_set)
def t_find_by_primary_key_and_template(): connect_info = { "directory": data_dir, "file_name": "People.csv" } csv_tbl = CSVDataTable("people",connect_info,key_columns=['playerID']) r = csv_tbl.find_by_primary_key(['willite01'], field_list=['playerID','nameLast','throws','bats','birthCountry']) print("Find by key returned = " + str(r)) t = {"playerID":"willite01"} rr = csv_tbl.find_by_template(t, field_list=['playerID','nameLast','throws','bats','birthCountry']) print("Find by template returned = " + str(rr))
def t_delete_by_key(): connect_info = {"directory": data_dir, "file_name": "Batting.csv"} key_cols = ['playerID', 'teamID', 'yearID', 'stint'] key_fields = ['aardsda01', 'ATL', '1999', '0'] file = "csv_table_test.txt" with open(file, 'a+') as f: f.write("\n\n******************** " + "test_delete_by_key" + " ********************") try: csv_tbl = CSVDataTable("batting", connect_info, key_columns=key_cols) new_record = { 'playerID': 'aardsda01', 'yearID': '1999', 'teamID': 'ATL', 'stint': '0' } csv_tbl.insert(new_record) with open(file, 'a+') as f: f.write("\nLooking up with key = " + str(key_fields)) r1 = csv_tbl.find_by_primary_key(key_fields) with open(file, 'a+') as f: f.write("\nReturned row = '\n'" + str(json.dumps(r1, indent=3))) r2 = csv_tbl.delete_by_key(key_fields) with open(file, 'a+') as f: f.write("\nDelete returned " + str(r2)) r3 = csv_tbl.find_by_primary_key(key_fields) with open(file, 'a+') as f: f.write("\nQuery result after delete = " + str(json.dumps(r3, indent=2))) f.write("\nThis is the correct answer.") except Exception as e: with open(file, 'a+') as f: f.write("\nDelete failed. Exception = " + str(e)) f.write("\nThis is the wrong answer.") with open(file, 'a+') as f: f.write("\n******************** " + "end test_delete_by_key" + " ********************")
def t_update_by_key(): connect_info = { "directory": "../Data", "file_name": "orderdetails.csv", "delimiter": ";" } csv_tbl = CSVDataTable("orderdetails", connect_info, key_columns=['orderNumber', "orderLineNumber"]) fields = ['orderNumber', 'productCode'] key_vals = ['10100', '2'] r1 = csv_tbl.find_by_primary_key(key_vals, fields) print("Find result= \n", json.dumps(r1, indent=2)) r = csv_tbl.update_by_key(key_vals, {"orderNumber": "10100", "productCode": 'S18_3171'}) print("Update returned ", r, "\n") r2 = csv_tbl.find_by_primary_key(key_vals, fields) print("Find result= \n", json.dumps(r2, indent=2))
def test_delete_by_key_good(): print("\n******************** " + "test_delete_by_key_good" + " ********************") try: csv_tbl = CSVDataTable(table_name, connect_info, key_columns) print("Delete by primary key 'AARDSDA01'") res = csv_tbl.delete_by_key(['AARDSDA01']) print("Wrong primary key and nothing to delete. The number of the rows deleted =", res) print("\nDelete by primary key 'aardsda01'") r1 = csv_tbl.find_by_primary_key(['aardsda01']) print("BEFORE deleting, the row =\n", json.dumps(r1, indent=2)) print("Deleting...") r2 = csv_tbl.delete_by_key(['aardsda01']) print("Delete returned ", r2, "\n") r3 = csv_tbl.find_by_primary_key(['aardsda01']) print("AFTER deleting, the row =\n", json.dumps(r3, indent=2)) print("Correct answer.") except Exception as e: print("Exception =", e) print("Wrong answer.") print("******************** " + "end test_delete_by_key_good" + " ********************\n")
def t_find_by_primary_key_and_template(): connect_info = {"directory": data_dir, "file_name": "Salaries.csv"} csv_tbl = CSVDataTable("Salaries", connect_info, key_columns=['playerID']) r = csv_tbl.find_by_primary_key( ['barkele01'], field_list=['yearID', 'teamID', 'lgID', 'playerID', 'salary']) print("find by key return:" + str(r)) # t={"playerID":"willite01"} t = {"yearID": "1985", "playerID": "barkele01", "salary": "870000"} r = csv_tbl.find_by_template( t, field_list=['yearID', 'teamID', 'lgID', 'playerID', 'salary']) print("find by template return:" + str(r))