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))
