def t_update_by_template(): connect_info = { "directory": "../Data", "file_name": "orderdetails.csv", "delimiter": ";" } csv_tbl = CSVDataTable("orderdetails", connect_info, key_columns=['orderNumber', "orderLineNumber"]) fields = ['orderNumber', 'productCode'] r1 = csv_tbl.find_by_template({"orderNumber": "10025"}, field_list=fields) print("Details for order '10025' = \n", json.dumps(r1, indent=2)) print("\nUpdate productCode 'S18_1749':") r2 = csv_tbl.update_by_template({"orderNumber": "10100", "productCode": 'S18_4409'},{"orderNumber": "10025", "productCode": 'S18_3171'}) print("update returned ", r2, "\n") r3 = csv_tbl.find_by_template({"orderNumber": "10025"}, field_list=fields) print("Details for order '10025' after update = \n", json.dumps(r3, indent=2)) # print("Loaded table = \n", csv_tbl) print("This is the correct answer")
def t_insert(data_dir, file_name, key_cols, insert_record): connect_info = { "directory": data_dir, "file_name": file_name } csv_tbl = CSVDataTable(file_name.split('.csv')[0], connect_info, key_cols) insert_record_original = copy.deepcopy(insert_record) existing = csv_tbl.find_by_template(template=insert_record) if existing is None: existing_len = 0 else: existing_len = len(existing) try: res = csv_tbl.insert(new_record=insert_record) check = csv_tbl.find_by_template(template=insert_record) if check is None: check_len = 0 else: check_len = len(check) if check_len and check_len > existing_len: print(existing_len, "record(s) found before insertion and", check_len, "record(s) found after insertion. Insert into", file_name.split('.csv')[0], "was successful\n", insert_record_original, "\n") else: print("Record not found - Insert into", file_name.split('.csv')[0], "failed\n") except Exception as e: print("Insert of record", insert_record_original, "unsuccessful due to:\n", e) print("Test successful as the insert failed as it should")
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_insert(): t = CSVDataTable("People", connect_info={ "directory": "/Users/chenchenwei/Desktop/COMS4111 Databases/HW1/Data", "file_name": "People.csv" }, key_columns=[ 'playerID' ], debug=True) new_person = { "playerID": "dff201", "nameLast": "w", "nameFirst": "c" } result = t.insert(new_person) print("result = ", json.dumps(result, indent=2)) tmp = {"playerID": "dff201"} new_c = { "nameFirst": "DD", "birthMonth": "11" } r1 = t.find_by_template(tmp) print("After insert", r1) r2 = t.update_by_template(tmp, new_c) print("After update", r2) r3 = t.find_by_template(tmp) print("Find", r3) r4 = t.delete_by_template(tmp) print("After delete ", r4) r1 = t.find_by_template(tmp) print("Find again", r1)
def test_delete_by_template(): connect_info = {"directory": data_dir, "file_name": "People.csv"} csv_tbl = CSVDataTable("people", connect_info, ["playerID"]) tmp = {"playerID": "aaronha01"} print(csv_tbl.find_by_template(tmp)) a = csv_tbl.delete_by_template(tmp) print(a) print(csv_tbl.find_by_template(tmp))
def test_insert(): connect_info = {"directory": data_dir, "file_name": "People.csv"} csv_tbl = CSVDataTable("people", connect_info, ["playerID"]) tmp1 = {"playerID": "abernte02"} new_record = {"playerID": "abernte02", "birthYear": "2019"} print(csv_tbl.find_by_template(tmp1)) csv_tbl.insert(new_record) print(csv_tbl.find_by_template(tmp1))
def test_update_by_template(): connect_info = {"directory": data_dir, "file_name": "People.csv"} csv_tbl = CSVDataTable("people", connect_info, ["playerID"]) tmp1 = {"birthYear": "1886", "birthDay": "12", "deathMonth": "12"} print(csv_tbl.find_by_template(tmp1)) new = {"playerID": "aberal01"} a = csv_tbl.update_by_template(tmp1, new) print(a) print(csv_tbl.find_by_template(tmp1))
def test_find_by_template_fail(): print("\n******************** " + "test_find_by_template_fail" + " ********************") try: csv_tbl = CSVDataTable(table_name, connect_info, key_columns) print('Find by template {"birthCountry": "USA", "teamID": "BS1"}') tmp = {"birthCountry": "USA", "teamID": "BS1"} csv_tbl.find_by_template(tmp) except Exception as e: print("Exception =", e) print("Correct answer.") print("******************** " + "end test_find_by_template_fail" + " ********************\n")
def test_find_by_template_failure(self): csv_tbl = CSVDataTable("people", CONNECT_INFO, PRIMARY_KEY_SINGLE_FILED) with self.assertRaises(Exception) as context: result = csv_tbl.find_by_template([""], PRIMARY_KEY_SELECT_FIELDS) self.assertEqual("template column is not a subset of table columns!", str(context.exception)) with self.assertRaises(Exception) as context: result = csv_tbl.find_by_template(TEMPLATE, [""]) self.assertEqual("field column is not a subset of table columns!", str(context.exception))
def t_csv_find_by_template(): connect_info = {"directory": data_dir, "file_name": "People.csv"} csv_tbl = CSVDataTable("people", connect_info, ["playerID"]) r = {"playerID": "aasedo01", "birthYear": "1954", "birthCity": "Orange"} print(csv_tbl.find_by_template({})[:10]) print(csv_tbl.find_by_template(r)) print(csv_tbl.find_by_template(r, ["weight", "height"])) print(csv_tbl.find_by_template(r, ["deathYear"])) try: csv_tbl.find_by_template(r, ["weight", "height", "luhuhyg"]) print("field list mismatch, failed") except Exception: print("field list mismatch, passed") try: temp_r = { "fdsafds": "aasedo01", "birthYear": "1954", "birthCity": "Orange" } csv_tbl.find_by_template(temp_r) print("Column mismatch, failed") except Exception: print("Column mismatch, passed")
def t_delete_by_tmp(): tmp = {'nameLast': 'Williams', 'nameFirst': 'Ted'} field_list = ["playerID", 'nameLast', 'nameFirst'] connect_info = { "directory": data_dir, "file_name": "People.csv" } csv_tbl = CSVDataTable("people", connect_info, None) print(csv_tbl.find_by_template(tmp,field_list)) result = csv_tbl.delete_by_template(tmp) print(result) print(csv_tbl.find_by_template(tmp, field_list))
def t_find_by_template(): connect_info = {"directory": data_dir, "file_name": "Batting.csv"} key_cols = ['playerID', 'teamID', 'yearID', 'stint'] tmp = {'teamID': 'BOS', 'yearID': '1960'} fields = ['playerID', 'teamID', 'yearID', 'AB', 'H', 'HR', 'RBI'] file = "csv_table_test.txt" with open(file, 'a+') as f: f.write("\n\n******************** " + "test_find_by_template" + " ********************") f.write("\nThe template to use to find a record is " + str(tmp)) 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_template(template=tmp, field_list=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_template" + " ********************")
def test_insertrecord(): new = {'nameLast': 'Sara', 'nameFirst': 'Ted'} connect_info = {"directory": data_dir, "file_name": "People.csv"} csv_tbl = CSVDataTable("People", connect_info, None) result = csv_tbl.insert(new) print(result) print(csv_tbl.find_by_template({'nameLast': 'Sara', 'nameFirst': 'Ted'}))
def t_insert_fail(): connect_info = {"directory": data_dir, "file_name": "Batting.csv"} key_cols = ['playerID', 'teamID', 'yearID', 'stint'] new_record = { 'playerID': 'aardsda01', 'yearID': '2015', 'teamID': 'ATL', 'stint': '1' } file = "csv_table_test.txt" with open(file, 'a+') as f: f.write("\n\n******************** " + "test_insert_fail" + " ********************") try: csv_tbl = CSVDataTable("batting", connect_info, key_columns=key_cols) key = ['aardsda01', 'ATL', '2015', '1'] with open(file, 'a+') as f: f.write("\nLooking up with key = " + str(key)) r1 = csv_tbl.find_by_template(new_record) with open(file, 'a+') as f: f.write("\nReturned row = '\n'" + str(json.dumps(r1, indent=3))) f.write("\nAttempt to insert bad row = " + str(json.dumps(new_record, indent=2))) csv_tbl.insert(new_record) with open(file, 'a+') as f: f.write("\nThis is the wrong answer.") except Exception as err: with open(file, 'a+') as f: f.write("\nInsert failed. Exception = " + str(err)) f.write("\nThis is the right answer.") with open(file, 'a+') as f: f.write("\n******************** " + "end_test_insert_fail" + " ********************")
def test_find_by_template(): tem = {'nameLast': 'Williams', 'nameFirst': 'Ted'} connect_info = {"directory": data_dir, "file_name": "People.csv"} csv_tbl = CSVDataTable("People", connect_info, key_columns=['playerID']) result = csv_tbl.find_by_template(tem) print(result)
def test_match_all_by_template(): tmp = {'nameLast': 'Williams', 'birthCity': 'San Diego'} connect_info = {"directory": data_dir, "file_name": "People.csv"} csv_tbl = CSVDataTable("people", connect_info, None) result = csv_tbl.find_by_template(tmp) print("Matching all by templates result: \n", json.dumps(result, indent=2))
def t_find_by_tmp(): tmp = {'nameLast': 'Williams', 'nameFirst': 'Ted'} connect_info = {"directory": data_dir, "file_name": "People.csv"} csv_tbl = CSVDataTable("people", connect_info, None) result = csv_tbl.find_by_template(tmp) print(result)
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_csv_find_by_template(): connect_info = {"directory": data_dir, "file_name": "People.csv"} csv_tbl = CSVDataTable("people", connect_info, None) template = {"nameLast": "Williams", "birthCity": "San Diego"} subset = csv_tbl.find_by_template( template, field_list=["playerID", "nameLast", "nameFirst"]) print("Found by template") print(str(pd.DataFrame(subset)))
def test_delelt_By_Tmp(): tem = {'nameLast': 'Williams', 'birthState': 'NC'} connect_info = {"directory": data_dir, "file_name": "People.csv"} csv_tbl = CSVDataTable("People", connect_info, None) result = csv_tbl.delete_by_template(tem) print(result) print( csv_tbl.find_by_template({ 'nameLast': 'Williams', 'birthState': 'NC' }))
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 t_update_by_template_good(): 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_good" + " ********************") 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) tmp = {'playerID': 'aardsda01', 'yearID': '1999'} with open(file, 'a+') as f: f.write("\nLooking up with template = " + str(tmp)) r1 = csv_tbl.find_by_template(tmp) new_values = {'AB': '1'} 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 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 correct answer") except Exception as e: with open(file, 'a+') as f: f.write("\nUpdate failed. Exception = " + str(e)) f.write("\nThis is the wrong answer.") with open(file, 'a+') as f: f.write("\n******************** " + "end_test_update_by_tamplate_good" + " ********************")
def test_update_by_tmp(): tem = {'nameLast': 'Williams', 'nameFirst': 'Ted'} connect_info = {"directory": data_dir, "file_name": "People.csv"} new_value = {'birthCity': 'Orange'} csv_tbl = CSVDataTable("People", connect_info, None) result = csv_tbl.update_by_template(tem, new_value) print(result) print( csv_tbl.find_by_template({ 'nameLast': 'Williams', 'nameFirst': 'Ted' }))
def insert_test(): connect_info = {"directory": data_dir, "file_name": "Salaries.csv"} csv_tbl = CSVDataTable("Salaries", connect_info, key_columns=['playerID']) new_record = {"yearID": "1977", "playerID": "EEEE"} csv_tbl.insert(new_record) # new_record["yearID"] = "1998" # csv_tbl.insert(new_record) # new_record["yearID"] = "1997" # csv_tbl.insert(new_record) tmp = {"yearID": "1977", "playerID": "EEEE"} r = csv_tbl.find_by_template(template=tmp) print("inserted:" + str(r))
def t_find_by_template(data_dir, file_name, key_cols, fields, tmp): 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_template(template=tmp, field_list=fields) if res is None: print("Find By Template on", file_name.split('.csv')[0], "with template", tmp, ":\n", 'None\n') else: print("Find By Template on", file_name.split('.csv')[0], "with template", tmp, ":\n", json.dumps(res, indent=2), '\n')
def test_find_by_template(): connect_info = {"directory": data_dir, "file_name": "_Small.csv"} key_cols = ['playerID', 'teamID', 'yearID', 'stint'] fields = ['playerID', 'teamID', 'yearID', 'AB', 'H', 'HR', 'RBI'] template = {'teamID': 'CL1', 'yearID': '1871', "stint": "1"} csv_tbl = CSVDataTable("batting", connect_info, key_columns=key_cols) output = csv_tbl.find_by_template(template=template, field_list=None) print("Query Result: \n", json.dumps(output, indent=2))
def test_match_all(): tmp = {'nameLast': 'Aardsma', 'birthCity': 'Denver'} connect_info = { "directory": data_dir, "file_name": "People.csv" } csv_tbl = CSVDataTable("people", connect_info, None) field_list = ["nameFirst", "nameLast", "birthCity", "playerID"] result = csv_tbl.find_by_template(tmp, field_list=field_list) print(json.dumps(result, indent=2))
def t_find_by_template(): connect_info = { "directory": data_dir, "file_name": "Batting.csv" } key_cols=['playerID', 'teamID', 'yearID', 'stint'] fields=['playerID', 'teamID', 'yearID', 'AB', 'H', 'HR', 'RBI'] tmp={'teamID': 'BOS', 'yearID': '1960'} csv_tbl = CSVDataTable("batting", connect_info, key_columns=key_cols) res=csv_tbl.find_by_template(template=tmp, field_list=fields) print("Query result ='\n'", json.dumps(res, indent=3))
def test_update_by_template_good(): print("\n******************** " + "test_update_by_template_good" + " ********************") try: csv_tbl = CSVDataTable(table_name, connect_info, key_columns) print('Update by template {"birthYear": "1995", "birthMonth": "11"}, ' 'new value is {"nameFirst": "Jackson", "nameLast": "Copper"}') tmp = {"birthYear": "1995", "birthMonth": "11"} value = {"nameFirst": "Jackson", "nameLast": "Copper"} r1 = csv_tbl.find_by_template(tmp) print("BEFORE updating, all rows =\n", json.dumps(r1, indent=2)) print("Updating...") r2 = csv_tbl.update_by_template(tmp, value) print("Update returned ", r2, "\n") r3 = csv_tbl.find_by_template(tmp) 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_template_good" + " ********************\n")
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))