def test_delete_by_key_failure(self):
csv_tbl = CSVDataTable("people", CONNECT_INFO, None)
with self.assertRaises(Exception) as context:
result = csv_tbl.delete_by_key([""])
self.assertEqual("Primary Key has not been setup yet!",
str(context.exception))
csv_tbl = CSVDataTable("people", CONNECT_INFO,
PRIMARY_KEY_SINGLE_FILED)
with self.assertRaises(Exception) as context:
result = csv_tbl.delete_by_key(["", ""])
self.assertEqual("Invalid key fields!", str(context.exception))
def test_delete_by_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_1 = csv_tbl_s.delete_by_key(PRIMARY_KEY_SINGLE_VALUE)
result_s_0 = csv_tbl_s.delete_by_key(["aaa"])
self.assertEqual(result_s_0, 0)
self.assertEqual(result_s_1, 1)
result_m_1 = csv_tbl_m.delete_by_key(PRIMARY_KEY_MULTIPLE_VALUE)
result_m_0 = csv_tbl_m.delete_by_key(["aaa", "bbb"])
self.assertEqual(result_m_0, 0)
self.assertEqual(result_m_1, 1)
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 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_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_key():
connect_info = {"directory": data_dir, "file_name": "People.csv"}
csv_tbl = CSVDataTable("people", connect_info, key_columns=['playerID'])
tmp = csv_tbl.key_to_template(['aardsda01'])
print(tmp)
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 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 t_load_good():
connect_info = {"directory": data_dir, "file_name": "Batting.csv"}
file = "csv_table_test.txt"
with open(file, 'a+') as f:
f.write("\n\n******************** " + "test_load_good" +
" ********************")
f.write(
"\nTable is " + connect_info["file_name"] +
" and key_columns are ['playerID','teamID', 'yearID', 'stint']")
try:
csv_tbl = CSVDataTable(
"batting",
connect_info,
key_columns=['playerID', 'teamID', 'yearID', 'stint'])
with open(file, 'a+') as f:
f.write("\nLoaded table = \n" + str(csv_tbl))
f.write("\nThis is the correct answer.")
except Exception as de:
with open(file, 'a+') as f:
f.write("\nLoad failed. Exception = " + str(de))
f.write("\nThis is the wrong answer.")
with open(file, 'a+') as f:
f.write("\n******************** " + "end test_load_good" +
" ********************")
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 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 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 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 test_update_by_template_good():
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"
}
csv_tbl = CSVDataTable("people_test.csv", connect_info, None)
result = csv_tbl.update_by_template(tmp, new_info)
print("Number of rows updated by template(testing good): ", result)
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 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_template():
connect_info = {
"directory": data_dir,
"file_name": "shire.csv"
}
tmp = {'last_name' : 'Baggins'}
new_val = 'Cheros'
csv_tbl = CSVDataTable('shire', connect_info, key_columns=['last_name'])
result = csv_tbl.update_by_template(tmp,new_val)
print("The number of rows updated: {}".format(result))
print("This is what the table looks like now: ", csv_tbl)
# t_load()
# t_find_by_primary_key_and_template()
# t_find_by_template()
# t_matches_template()
# t_delete_by_key2()
# t_insert2()
# t_delete_by_template2()
# t_update_by_key()
# t_update_by_template()
# t_duplicates()
# t_delete_by_key()
# t_insert()
# t_delete_by_key()
# t_delete_by_template()
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 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 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_delete_by_template_success(self):
csv_tbl = CSVDataTable("people", CONNECT_INFO,
PRIMARY_KEY_SINGLE_FILED)
result = csv_tbl.delete_by_template(TEMPLATE)
self.assertEqual(result, 1)
result = csv_tbl.delete_by_template({"birthYear": "1"})
self.assertEqual(result, 0)
def t_delete_by_key():
connect_info = {"directory": data_dir, "file_name": "People.csv"}
csv_tbl = CSVDataTable("people", connect_info, key_columns=['playerID'])
result = csv_tbl.delete_by_key(['aaronha01'])
#csv_tbl.save()
print(result)
def t_load():
connect_info = {"directory": data_dir, "file_name": "People.csv"}
csv_tbl = CSVDataTable("people", connect_info, None)
print("Created table = " + str(csv_tbl))
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_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_delete_by_template_failure(self):
csv_tbl = CSVDataTable("people", CONNECT_INFO,
PRIMARY_KEY_SINGLE_FILED)
with self.assertRaises(Exception) as context:
result = csv_tbl.delete_by_template({"": ""})
self.assertEqual("template column is not a subset of table columns!",
str(context.exception))
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))
