def update_by_template_test(template=None, new_values=None):
"""
:param data_table:
:param template:
:param new_values:
:return:
"""
connect_info = {"directory": data_dir, "file_name": "Salaries.csv"}
csv_tbl = CSVDataTable("Salaries", connect_info, key_columns=['playerID'])
template = {"playerID": "barkele01", "yearID": "1985"}
new_values = {"yearID": "1777"}
affected_num = csv_tbl.update_by_template(template=template,
new_values=new_values)
print("performing update_by_template_test")
print("updated and the affected_num is:")
print(affected_num)
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 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():
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 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 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 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_record = {'playerID': 'aaa', 'yearID': '1997', 'teamID': '1997', 'stint': '1'}
def t_load_csv(data_dir, file_name, key_cols):
connect_info = {
"directory": data_dir,
"file_name": file_name
}
csv_tbl = CSVDataTable(file_name.split('.csv')[0], connect_info, key_cols)
print("Created table = " + str(csv_tbl))
def t_load():
connect_info = {
"directory": data_dir,
"file_name": "People.csv"
}
csv_tbl = CSVDataTable("people", connect_info, None)
print('Loaded table: \n', csv_tbl)
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_load():
connect_info = {
"directory": data_dir,
"file_name": "Batting.csv"
}
csv_tbl = CSVDataTable("batting", connect_info, key_columns=['playerID', 'teamID', 'yearID', 'stint'])
print("Created table = " + str(csv_tbl))
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_load_failure(self):
with self.assertRaises(Exception) as context:
csv_tbl_s = CSVDataTable("people",
connect_fail("3_people_dup.csv"),
PRIMARY_KEY_SINGLE_FILED)
self.assertEqual(PK_UNIQUE_ERROR, str(context.exception))
with self.assertRaises(Exception) as context:
csv_tbl_m = CSVDataTable("people",
connect_fail("3_people_dup.csv"),
PRIMARY_KEY_MULTIPLE_FIELD)
self.assertEqual(PK_UNIQUE_ERROR, str(context.exception))
with self.assertRaises(Exception) as context:
csv_tbl_m = CSVDataTable("people",
connect_fail("2_people_missing_key.csv"),
PRIMARY_KEY_MULTIPLE_FIELD)
self.assertEqual("Some row does not have primary key info!",
str(context.exception))
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 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 test_matches_template():
connect_info = {"directory": data_dir, "file_name": "People.csv"}
csv_tbl = CSVDataTable("people", connect_info, None)
match = csv_tbl.matches_template(csv_tbl.rows[0],
template={
"playerID": "aasedo01",
"birthYear": "1954"
})
print(match)
match = csv_tbl.matches_template(csv_tbl.rows[3],
template={
"playerID": "aasedo01",
"birthYear": "1954"
})
print(match)
def test_init_fail():
print("\n******************** " + "test_init_fail" + " ********************")
try:
print("Table is People and key_columns are ['playerID', 'playerID']")
CSVDataTable(table_name, connect_info, ['playerID', 'playerID'])
except Exception as e:
print("Init failed. Exception =", e)
print("Correct answer.")
print("******************** " + "end test_init_fail" + " ********************\n")
def test_create_initial():
print("\nThis is a test for initialiazation of CSVTable")
t1 = CSVDataTable("People",
connect_info={
"directory": "/Users/chenchenwei/Desktop/COMS4111 Databases/HW1/Data",
"file_name": "People.csv"
},
key_columns=['playerID'], debug=True)
print("t1 = ", t1)
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 test_load_fail2():
print("\n******************** " + "test_load_fail2" + " ********************")
try:
print("Table is People and key_columns are ['birthYear']")
CSVDataTable(table_name, connect_info, ['birthYear'])
except Exception as e:
print("Load failed. Exception =", e)
print("Correct answer.")
print("******************** " + "end test_load_fail2" + " ********************\n")
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 t_duplicates():
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':'1', 'H':'21','AB':'22'
}
res =
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():
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_template_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_tamplate_fail" +
" ********************")
try:
csv_tbl = CSVDataTable("batting", connect_info, key_columns=key_cols)
tmp = {
'playerID': 'aardsda01',
'yearID': '2015',
'stint': '1',
'teamID': 'ATL'
}
with open(file, 'a+') as f:
f.write("\nLooking up with template = " + str(tmp))
r1 = csv_tbl.find_by_template(tmp)
new_values = {'yearID': '2008', 'teamID': 'BOS'}
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 bad 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 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_tamplate_fail" + " ********************")
def test_delete_by_key():
print("\n******************** " + "test_delete_by_key" +
" ********************")
print("Compare time elapsed for delete_by_key.")
csv_tbl = CSVDataTable(table_name, csv_connect_info, key_columns)
rdb_tbl = RDBDataTable(table_name, rdb_connect_info, key_columns)
start1 = time.time()
r1 = csv_tbl.delete_by_key(['aardsda01'])
end1 = time.time()
elapsed1 = end1 - start1
print("Time elapsed for CSVDataTable is ", elapsed1, "seconds.")
start2 = time.time()
r2 = rdb_tbl.delete_by_key(['aardsda01'])
end2 = time.time()
elapsed2 = end2 - start2
print("Time elapsed for RDBDataTable is ", elapsed2, "seconds.")
print("******************** " + "end test_delete_by_key" +
" ********************\n")
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 t_tmp_r():
r = {
"playerID": "webstra02",
"teamID": "BOS",
"yearID": "1960",
"AB": "3",
"H": "0",
"HR": "0",
"RBI": "1"
}
fields = ['playerID', 'yearID', 'HR']
test = CSVDataTable.find_tmp_row(r, fields)
print("Matches = ", test)
def t_matches_template():
r = {
"playerID": "worthal01",
"teamID": "BOS",
"yearID": "1960",
"AB": "1",
"H": "0",
"HR": "0",
"RBI": "0"
}
tmp = {"playerID": "willite01"}
test = CSVDataTable.matches_template(r, tmp)
print("Matches = ", test)
