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 test_update_by_key_fail(): print("\n******************** " + "test_update_by_key_fail" + " ********************") try: csv_tbl = CSVDataTable(table_name, connect_info, key_columns) print('Update by primary key "abadijo01", new value is {"playerID": "howarma01"}') csv_tbl.update_by_key(["abadijo01"], {"playerID": "howarma01"}) except Exception as e: print("Exception =", e) print("Correct answer.") print("******************** " + "end test_update_by_key_fail" + " ********************\n")
def t_csv_update_by_key(): connect_info = {"directory": data_dir, "file_name": "People.csv"} csv_tbl = CSVDataTable("people", connect_info, ["playerID"]) r = {"playerID": "abbotgl01"} keys = ["abbotgl01"] new_val = {"deathYear": "123", "deathMonth": "12", "deathDay": "1"} print("Lines updated:", csv_tbl.update_by_key(keys, new_val), "should equal 1") print("Lines updated:", csv_tbl.update_by_key(keys, new_val), "should equal 0") print("Lines updated:", csv_tbl.update_by_key(keys, {"playerID": "abbotgl01"}), "should equal 0")
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 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_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_update_by_key_failure_dup_keys(self): csv_tbl_s = CSVDataTable("people", CONNECT_INFO, PRIMARY_KEY_SINGLE_FILED) with self.assertRaises(Exception) as context: result_s = csv_tbl_s.update_by_key(PRIMARY_KEY_SINGLE_VALUE, DUP_PK_SINGLE) self.assertEqual(PK_UNIQUE_ERROR, str(context.exception)) self.assertTrue("abadan01" in csv_tbl_s._primary_keys_set) self.assertTrue("aardsda01" in csv_tbl_s._primary_keys_set) csv_tbl_m = CSVDataTable("people", CONNECT_INFO, PRIMARY_KEY_MULTIPLE_FIELD) with self.assertRaises(Exception) as context: result_m = csv_tbl_m.update_by_key(PRIMARY_KEY_MULTIPLE_VALUE, DUP_PK_MULTIPLE) self.assertEqual(PK_UNIQUE_ERROR, str(context.exception)) self.assertTrue("abadan01_Abad" in csv_tbl_m._primary_keys_set) self.assertTrue("aardsda01_Aardsma" in csv_tbl_m._primary_keys_set)
def t_update_by_key(): connect_info = {"directory": data_dir, "file_name": "People.csv"} csv_tbl = CSVDataTable("people", connect_info, key_columns=['playerID']) result = csv_tbl.update_by_key(['aardsda01'], { 'birthCity': 'Beijing', 'deathYear': '2019' }) print(result) print(csv_tbl.find_by_primary_key(['aardsda01']))
def test_update_by_key_failure_normal(self): csv_tbl = CSVDataTable("people", CONNECT_INFO, PRIMARY_KEY_SINGLE_FILED) with self.assertRaises(Exception) as context: result = csv_tbl.update_by_key(PRIMARY_KEY_SINGLE_VALUE, {"a": "b"}) self.assertEqual("field column is not a subset of table columns!", str(context.exception)) with self.assertRaises(Exception) as context: result = csv_tbl.update_by_key([], UPDATE_NORMAL_SINGLE) self.assertEqual('Invalid key fields!', str(context.exception)) csv_tbl = CSVDataTable("people", CONNECT_INFO, None) with self.assertRaises(Exception) as context: result = csv_tbl.update_by_key([], {}) self.assertEqual('Primary Key has not been setup yet!', str(context.exception))
def t_update_by_key(): connect_info = { "directory": data_dir, "file_name": "shire.csv" } key_cols = ['last_name'] new_val = {'last_name': 'Cheros'} csv_tbl = CSVDataTable('shire', connect_info,key_cols) result = csv_tbl.update_by_key(['Baggins'], new_val) print("The number of rows updated: {}".format(result)) print("This is what the table looks like now: ", csv_tbl)
def t_csv_update_by_key(): connect_info = {"directory": data_dir, "file_name": "People.csv"} csv_tbl = CSVDataTable("people", connect_info, ["playerID"]) # key_fields = ["willite01"] key_fields = ["aardsda01"] n_updates = csv_tbl.update_by_key(key_fields, new_values={"birthCity": "Los Angeles"}) print("Update by template: " + str(n_updates) + " updates") print(str(csv_tbl))
def t_update_by_key(): 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) key_fields = ['aaa', '1997', '1997', '1'] new_values = {'yearID' : '2000'} res = csv_tbl.update_by_key(key_fields, new_values) print("Query result =", res)
def test_update_by_key(): connect_info = {"directory": data_dir, "file_name": "_Small.csv"} key_cols = ['playerID', 'yearID', 'stint', 'teamID'] key_values = ["battijo01", '1871', '1', 'CL1'] new_values = {"playerID": "swapdog", 'G': '6', 'AB': '20'} csv_tbl = CSVDataTable("batting", connect_info, key_columns=key_cols) output = csv_tbl.update_by_key(key_fields=key_values, new_values=new_values) print("Query Result: \n", json.dumps(output, indent=2))
def test_update_by_key_success_primary_key(self): csv_tbl_s = CSVDataTable("people", CONNECT_INFO, PRIMARY_KEY_SINGLE_FILED) self.assertTrue("abadan01" 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_key(PRIMARY_KEY_SINGLE_VALUE, UPDATE_PK_SINGLE) self.assertEqual(1, result_s) self.assertEqual(UPDATE_PK_RESULT_SINGLE, csv_tbl_s.find_by_primary_key(["aaa"])) self.assertTrue("abadan01" 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("abadan01_Abad" 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_key(PRIMARY_KEY_MULTIPLE_VALUE, UPDATE_PK_MULTIPLE) self.assertEqual(1, result_m) self.assertEqual(UPDATE_PK_RESULT_MULTIPLE, csv_tbl_m.find_by_primary_key(["aaa", "bbb"])) self.assertTrue("abadan01_Abad" not in csv_tbl_m._primary_keys_set) self.assertTrue("aaa_bbb" in csv_tbl_m._primary_keys_set)
def t_update_by_key(): connect_info = { "directory": data_dir, "file_name": "Batting.csv" } key_cols = ['playerID', 'teamID', 'yearID', 'stint'] key_vals = ['willite01', 'BOS', '1950', '1'] new_vals = {'yearID': '2020'} csv_tbl = CSVDataTable("batting", connect_info, key_columns=key_cols) cnt = csv_tbl.update_by_key(key_fields=key_vals, new_values=new_vals) print("Number of rows updated:", cnt)
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 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_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 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_update_by_key(): print("\n******************** " + "test_update_by_key" + " ********************") print("Compare time elapsed for update_by_key.") csv_tbl = CSVDataTable(table_name, csv_connect_info, key_columns) rdb_tbl = RDBDataTable(table_name, rdb_connect_info, key_columns) value = {"nameFirst": "Jackson", "nameLast": "Copper"} start1 = time.time() r1 = csv_tbl.update_by_key(["abadijo01"], value) end1 = time.time() elapsed1 = end1 - start1 print("Time elapsed for CSVDataTable is ", elapsed1, "seconds.") start2 = time.time() r2 = rdb_tbl.update_by_key(["abadijo01"], value) end2 = time.time() elapsed2 = end2 - start2 print("Time elapsed for RDBDataTable is ", elapsed2, "seconds.") print("******************** " + "end test_update_by_key" + " ********************\n")
def t_update_by_key(): 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'} key_vals = ['willite01', 'BOS', '1960', '1'] csv_tbl = CSVDataTable("batting", connect_info, key_cols) res = csv_tbl.update_by_key(key_fields=key_vals, new_values={ "playerID": "worthal01", "teamID": "BOS", "yearID": "1960", "stint": "1", "AB": "1", "H": "100", "HR": "100", "RBI": "100" }) # print("Created table = " + str(csv_tbl)) print("Query result = ", res)
def test_update_by_key_bad(): connect_info = {"directory": data_dir, "file_name": "People_test.csv"} tmp = { "playerID": "aardsda01", "birthYear": "1981", "birthMonth": "12", "birthDay": "27", "birthCountry": "USA", "birthState": "CO", "birthCity": "Denver", "deathYear": "", "deathMonth": "", "deathDay": "", "deathCountry": "", "deathState": "", "deathCity": "", "nameFirst": "David", "nameLast": "Aardsma", "nameGiven": "David Allan", "weight": "215", "height": "75", "bats": "R", "throws": "R", "debut": "2004-04-06", "finalGame": "2015-08-23", "retroID": "aardd001", "bbrefID": "aardsda01" } new_info = { "playerID": "ss5644", "birthYear": "1993", "birthMonth": "5", "birthDay": "19", "birthCountry": "Korea", "birthState": "Seoul", "birthCity": "Somewhere", "deathYear": "", "deathMonth": "", "deathDay": "", "deathCountry": "", "deathState": "", "deathCity": "", "nameFirst": "Soo", "nameLast": "Shin", "nameGiven": "Soo Young Shin", "weight": "I dont know", "height": "I dont know", "bats": "R", "throws": "R", "debut": "2004-04-06", "finalGame": "2015-08-23", "retroID": "ss5644", "bbrefID": "ss5644", "extra": "column" } key_cols = ['playerID'] key_val = ['aardsda01'] csv_tbl = CSVDataTable("people_test.csv", connect_info, key_columns=key_cols) result = csv_tbl.update_by_key(key_val, new_info) print("Number of rows updated by key(testing bad): ", result)
import csv from src.CSVDataTable import CSVDataTable connect_info = { "directory": '../Data/baseballdatabank-2019.2/core/', "file_name": "Appearances.csv" } csv_tbl = CSVDataTable("Appearances", connect_info, ['yearID','teamID','playerID','']) result = csv_tbl.find_by_primary_key(['1871','CL1','bassjo01']) result1 = csv_tbl.find_by_template({'teamID':'TRO','playerID':'abercda01'}) result2 = csv_tbl.delete_by_key(['1871','CL1','bassjo01']) result3 = csv_tbl.delete_by_template({'teamID':'TRO','playerID':'abercda01'}) result4 = csv_tbl.update_by_key(['1871','RC1','barkeal01'],['1996','JGS','janane1']) result5 = csv_tbl.update_by_template({'teamID':'JGS','playerID':'janane1'},{'teamID':'JJJ','playerID':'jjjjjjjjjj'}) result6 = csv_tbl.insert({'G_ss': '15', 'teamID': 'BS1', 'G_lf': '0', 'lgID': 'NA', 'yearID': '1871', 'G_3b': '0', 'G_defense': '31', 'G_rf': '0', 'G_all': '31', 'G_pr': '0', 'GS': '31', 'G_batting': '31', 'G_1b': '0', 'G_p': '0', 'playerID': 'barnero01', 'G_c': '0', 'G_cf': '0', 'G_2b': '16', 'G_ph': '0', 'G_of': '0', 'G_dh': '0'}) print('find_by_primary_key',result) print('find_by_template',result1) print('delete_by_key',result2) print('delete_by_template',result3) print('update_by_key',result4) print('update_by_template',result5) print('insert',result6) # csv_data = load_csv(dir_path, file_name)