def test_update_by_template_fail():
print("\n******************** " + "test_update_by_template_fail" + " ********************")
try:
csv_tbl = CSVDataTable(table_name, connect_info, key_columns)
print('Update by template {"birthYear": "1995"}, new value is {"playerID": "abbotod01"}')
csv_tbl.update_by_template({"birthYear": "1995"}, {"playerID": "abbotod01"})
except Exception as e:
print("Exception =", e)
print("Correct answer.")
print("******************** " + "end test_update_by_template_fail" + " ********************\n")
def t_csv_update_by_template():
connect_info = {"directory": data_dir, "file_name": "People.csv"}
csv_tbl = CSVDataTable("people", connect_info, ["playerID"])
r = {"playerID": "abbotgl01"}
new_val = {"deathYear": "123", "deathMonth": "12", "deathDay": "1"}
print("Lines updated:", csv_tbl.update_by_template(r, new_val),
"should equal 1")
print("Lines updated:", csv_tbl.update_by_template(r, new_val),
"should equal 0")
print("Lines updated:",
csv_tbl.update_by_template(r, {"playerID": "abbotgl01"}),
"should equal 0")
def test_update_by_template_failure_normal(self):
csv_tbl = CSVDataTable("people", CONNECT_INFO,
PRIMARY_KEY_SINGLE_FILED)
with self.assertRaises(Exception) as context:
result = csv_tbl.update_by_template(TEMPLATE, {"a": "b"})
self.assertEqual("field column is not a subset of table columns!",
str(context.exception))
with self.assertRaises(Exception) as context:
result = csv_tbl.update_by_template({"a": "b"},
UPDATE_NORMAL_SINGLE)
self.assertEqual("template column is not a subset of table columns!",
str(context.exception))
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_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 t_update_by_template(data_dir, file_name, key_cols, tmp, 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_template(template=tmp)
if existing_tmp is None:
existing_tmp_len = 0
else: existing_tmp_len = len(existing_tmp)
res = csv_tbl.update_by_template(template=tmp, 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 template", tmp, "\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 template", tmp, "\nTest Successful. 0 rows updated")
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_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_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_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, None)
result = csv_tbl.update_by_template(tmp, new_values)
print(result)
def test_update_by_template_failure_dup_keys(self):
csv_tbl_s = CSVDataTable("people", CONNECT_INFO,
PRIMARY_KEY_SINGLE_FILED)
with self.assertRaises(Exception) as context:
result_s = csv_tbl_s.update_by_template(TEMPLATE,
UPDATE_DUP_PK_SINGLE)
self.assertEqual(PK_UNIQUE_ERROR, str(context.exception))
self.assertTrue("abadan01" in csv_tbl_s._primary_keys_set)
self.assertTrue("aardsda01" in csv_tbl_s._primary_keys_set)
csv_tbl_m = CSVDataTable("people", CONNECT_INFO,
PRIMARY_KEY_MULTIPLE_FIELD)
with self.assertRaises(Exception) as context:
result_m = csv_tbl_m.update_by_template(TEMPLATE,
UPDATE_DUP_PK_MULTIPLE)
self.assertEqual(PK_UNIQUE_ERROR, str(context.exception))
self.assertTrue("abadan01_Abad" in csv_tbl_m._primary_keys_set)
self.assertTrue("aardsda01_Aardsma" in csv_tbl_m._primary_keys_set)
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 t_update_by_tmp_error():
tmp = {'nameLast': 'Williams', 'nameFirst': 'Ted'}
new_values = {'playerID': 'aardsda01', 'birthCity': 'Tokyo', 'deathYear': '2020'}
connect_info = {
"directory": data_dir,
"file_name": "People.csv"
}
csv_tbl = CSVDataTable("people", connect_info, key_columns=['playerID'])
result = csv_tbl.update_by_template(tmp, new_values)
print(result)
def t_csv_update_by_template():
connect_info = {"directory": data_dir, "file_name": "People.csv"}
csv_tbl = CSVDataTable("people", connect_info, None)
#template = {"nameLast": "Williams", "birthCity": "San Diego"}
template = {"playerID": "aardsda01"}
n_updates = csv_tbl.update_by_template(
template, new_values={"birthCity": "Los Angeles"})
print("Update by template: " + str(n_updates) + " updates")
print(str(csv_tbl))
def t_update_by_template():
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)
tmp = {'playerID': 'aaa', 'teamID': '1997'}
new_values = {'yearID' : '2005'}
res = csv_tbl.update_by_template(tmp, new_values)
print("Query result =", res)
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 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 test_update_by_template():
connect_info = {"directory": data_dir, "file_name": "_Small.csv"}
key_cols = ['playerID', 'yearID', 'stint', 'teamID']
template = {'teamID': 'CL1', 'yearID': '1871'}
new_values = {"playerID": "battijo01", 'G': '6', 'AB': '20'}
csv_tbl = CSVDataTable("batting", connect_info, key_columns=key_cols)
output = csv_tbl.update_by_template(template=template,
new_values=new_values)
print("Query Result: \n", json.dumps(output, indent=2))
def t_update_by_template():
connect_info = {
"directory": data_dir,
"file_name": "Batting.csv"
}
key_cols = ['playerID', 'teamID', 'yearID', 'stint']
tmp = {'teamID': 'HOU', 'yearID': '1990'}
new_vals = {'stint': '2'}
csv_tbl = CSVDataTable("batting", connect_info, key_columns=key_cols)
cnt = csv_tbl.update_by_template(template=tmp, new_values=new_vals)
print("Number of rows updated:", cnt)
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 test_update_by_template():
connect_info = {"directory": data_dir, "file_name": "People.csv"}
csv_tbl = CSVDataTable("people", connect_info, key_columns=["playerID"])
n = csv_tbl.update_by_template(template={"birthYear": "1954"},
new_values={
"birthYear": "3000",
"birthMonth": "1"
})
print(n, "recolds updated.")
print(
csv_tbl.find_by_template(
template={"birthYear": "3000"},
field_list=["playerID", "birthYear", "birthMonth"]))
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 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_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_update_by_template():
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'}
csv_tbl = CSVDataTable("batting", connect_info, key_cols)
res = csv_tbl.update_by_template(template=tmp,
new_values={
"playerID": "worthal01",
"teamID": "BOS",
"yearID": "1960",
"stint": "1",
"AB": "1",
"H": "100",
"HR": "100",
"RBI": "100"
})
# print("Created table = " + str(csv_tbl))
print("Query result = ", res)
def test_update_by_template():
print("\n******************** " + "test_update_by_template" +
" ********************")
print("Compare time elapsed for update_by_template.")
csv_tbl = CSVDataTable(table_name, csv_connect_info, key_columns)
rdb_tbl = RDBDataTable(table_name, rdb_connect_info, key_columns)
tmp = {"birthYear": "1990", "birthCountry": "USA"}
value = {"nameFirst": "Jackson", "nameLast": "Copper"}
start1 = time.time()
r1 = csv_tbl.update_by_template(tmp, value)
end1 = time.time()
elapsed1 = end1 - start1
print("Time elapsed for CSVDataTable is ", elapsed1, "seconds.")
start2 = time.time()
r2 = rdb_tbl.update_by_template(tmp, value)
end2 = time.time()
elapsed2 = end2 - start2
print("Time elapsed for RDBDataTable is ", elapsed2, "seconds.")
print("******************** " + "end test_update_by_template" +
" ********************\n")
from src.CSVDataTable import CSVDataTable
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])
