def test_insert(): print("\n******************** " + "test_insert" + " ********************") print("Compare time elapsed for insert.") csv_tbl = CSVDataTable(table_name, csv_connect_info, key_columns) rdb_tbl = RDBDataTable(table_name, rdb_connect_info, key_columns) row = { "playerID": "coms4111f19", "birthYear": "2000", "birthMonth": "1", "birthDay": "1" } start1 = time.time() csv_tbl.insert(row) end1 = time.time() elapsed1 = end1 - start1 print("Time elapsed for CSVDataTable is ", elapsed1, "seconds.") start2 = time.time() rdb_tbl.insert(row) end2 = time.time() elapsed2 = end2 - start2 print("Time elapsed for RDBDataTable is ", elapsed2, "seconds.") print("******************** " + "end test_insert" + " ********************\n")
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_insert_failure_normal(self): csv_tbl = CSVDataTable("people", CONNECT_INFO, PRIMARY_KEY_SINGLE_FILED) with self.assertRaises(Exception) as context: csv_tbl.insert(INSERT_ROW_FAILURE) self.assertEqual("field column is not a subset of table columns!", str(context.exception))
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 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_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 t_csv_insert_with_key_failure(): connect_info = {"directory": data_dir, "file_name": "People.csv"} csv_tbl = CSVDataTable("people", connect_info, ["playerID"]) print("Number of records before insert: " + str(len(csv_tbl.get_rows()))) new_record = {"playerID": "aardsda01", "birthCity": "Dallas", "age": "26"} csv_tbl.insert(new_record) print("Number of records after insert: " + str(len(csv_tbl.get_rows())))
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 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_csv_insert_no_key(): connect_info = {"directory": data_dir, "file_name": "People.csv"} csv_tbl = CSVDataTable("people", connect_info, None) print("Number of records before insert: " + str(len(csv_tbl.get_rows()))) new_record = { "playerID": "lawlesszach01", "birthCity": "Dallas", "age": "26" } csv_tbl.insert(new_record) print("Number of records after insert: " + str(len(csv_tbl.get_rows())))
def t_insert2(): connect_info = { "directory": data_dir, "file_name": "shire.csv" } key_cols = ['last_name','first_name','email'] csv_tbl = CSVDataTable('shire', connect_info, key_columns=key_cols) new_row = { 'last_name': 'Logan', 'first_name': 'Vildred', 'email': '*****@*****.**' } csv_tbl.insert(new_row) print("The table now looks like: \n", csv_tbl)
def test_insert(): connect_info = {"directory": data_dir, "file_name": "_Small.csv"} key_cols = ['playerID', 'yearID', 'stint', 'teamID'] row = { "playerID": "swapdog", "yearID": '2019', "stint": '1', 'teamID': 'TEX', 'G': '6', 'AB': '20', 'R': '20', 'H': '20', '2B': '20', '3B': '20', 'HR': '20', 'RBI': '20', 'SB': '20', 'CS': '20', 'BB': '20', 'SO': '20', 'IBB': '20', 'HBP': '20', 'SH': '20', 'SF': '20', 'GIDP': '20' } csv_tbl = CSVDataTable("batting", connect_info, key_columns=key_cols) output = csv_tbl.insert(new_record=row) print("Query Result: \n", json.dumps(output, indent=2))
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 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 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_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)) # print("Query result = \n", json.dumps(res, indent=2)) # t_load() # t_tmp_r() # t_find_by_pk() # t_find_by_template() # t_delete_by_key() # t_delete_by_template() # t_update_by_key() # t_update_by_template() # t_insert()
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 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 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_row = { 'playerID': 'willite01', 'teamID': 'BOS', 'yearID': '1960', 'stint': '2', 'H': '21', 'AB': '23' } return csv_tbl.insert(new_row)
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 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_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_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 test_insert_bad(): connect_info = {"directory": data_dir, "file_name": "People_test.csv"} new_info = { "playerID": "ss5644", "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", "Unknown": "column" } csv_tbl = CSVDataTable("people_test.csv", connect_info, None) result = csv_tbl.insert(new_info) print("Number of inserted row = ", result) result1 = csv_tbl.find_by_template(new_info) print("The new row inserted: \n", json.dumps(result1, indent=2))
def test_insert_good(): connect_info = {"directory": data_dir, "file_name": "People_test.csv"} new_info = { "playerID": "willitr01", "birthYear": "1992", "birthMonth": "4", "birthDay": "25", "birthCountry": "USA", "birthState": "CA", "birthCity": "San Diego", "deathYear": "", "deathMonth": "", "deathDay": "", "deathCountry": "", "deathState": "", "deathCity": "", "nameFirst": "Trevor", "nameLast": "Williams", "nameGiven": "Trevor Anthony", "weight": "230", "height": "75", "bats": "R", "throws": "R", "debut": "2016-09-07", "finalGame": "2018-09-27", "retroID": "willt002", "bbrefID": "willitr01" } csv_tbl = CSVDataTable("people_test.csv", connect_info, None) result = csv_tbl.insert(new_info) print("Number of inserted row = ", result) result1 = csv_tbl.find_by_template(new_info) print("The new row inserted: \n", json.dumps(result1, indent=2))
def t_csv_insert(): connect_info = {"directory": data_dir, "file_name": "People.csv"} csv_tbl = CSVDataTable("people", connect_info, ["playerID"]) r = {"playerID": "abbotgl01"} csv_tbl.insert({"playerID": "abbotgl99"}) print(csv_tbl.find_by_primary_key(["abbotgl99"])) try: csv_tbl.insert({"playerID": "abbotgl99"}) print("Duplicate row insert test, failed") except Exception: print("Duplicate row insert test, passed") try: csv_tbl.insert({"birthYear": "abbotgl99"}) print("Empty key insert test, failed") except Exception: print("Empty key insert test, passed")
def t3(): connect_info = {"directory": data_dir, "file_name": "Salaries.csv"} csv_tbl = CSVDataTable("Salaries", connect_info, None) new_record = { "yearID": "1985", "teamID": "ATM", "lgID": "nl", "playerID": "BARKELE07", "salary": "f870000", } csv_tbl.insert(new_record) new_record["yearID"] = "1998" csv_tbl.insert(new_record) new_record["yearID"] = "1997" csv_tbl.insert(new_record) template = {"yearID": "1997"} new_values = {"yearID": "2011"} affected_num = csv_tbl.update_by_template(template=template, new_values=new_values) print(affected_num)
def test_insert_failure_dup_keys(self): csv_tbl = CSVDataTable("people", CONNECT_INFO, PRIMARY_KEY_SINGLE_FILED) with self.assertRaises(Exception) as context: csv_tbl.insert(INSERT_ROW_DUP_PK) self.assertEqual(PK_UNIQUE_ERROR, str(context.exception))
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) # row = dict(csv_data[0]) # m1 = matches_template(row, {"birthDay": "25", "birthCountry": "USA"}) # print("Match = ", m1) # cols = get_columns(row, ['playerID', 'nameLast']) # print("The requested columns are = ", cols)