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 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 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 test_delete_by_template():
connect_info = {"directory": data_dir, "file_name": "People.csv"}
csv_tbl = CSVDataTable("people", connect_info, ["playerID"])
tmp = {"playerID": "aaronha01"}
print(csv_tbl.find_by_template(tmp))
a = csv_tbl.delete_by_template(tmp)
print(a)
print(csv_tbl.find_by_template(tmp))
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 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 test_find_by_template_fail():
print("\n******************** " + "test_find_by_template_fail" + " ********************")
try:
csv_tbl = CSVDataTable(table_name, connect_info, key_columns)
print('Find by template {"birthCountry": "USA", "teamID": "BS1"}')
tmp = {"birthCountry": "USA", "teamID": "BS1"}
csv_tbl.find_by_template(tmp)
except Exception as e:
print("Exception =", e)
print("Correct answer.")
print("******************** " + "end test_find_by_template_fail" + " ********************\n")
def test_find_by_template_failure(self):
csv_tbl = CSVDataTable("people", CONNECT_INFO,
PRIMARY_KEY_SINGLE_FILED)
with self.assertRaises(Exception) as context:
result = csv_tbl.find_by_template([""], PRIMARY_KEY_SELECT_FIELDS)
self.assertEqual("template column is not a subset of table columns!",
str(context.exception))
with self.assertRaises(Exception) as context:
result = csv_tbl.find_by_template(TEMPLATE, [""])
self.assertEqual("field column is not a subset of table columns!",
str(context.exception))
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 t_delete_by_tmp():
tmp = {'nameLast': 'Williams', 'nameFirst': 'Ted'}
field_list = ["playerID", 'nameLast', 'nameFirst']
connect_info = {
"directory": data_dir,
"file_name": "People.csv"
}
csv_tbl = CSVDataTable("people", connect_info, None)
print(csv_tbl.find_by_template(tmp,field_list))
result = csv_tbl.delete_by_template(tmp)
print(result)
print(csv_tbl.find_by_template(tmp, field_list))
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 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 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 test_match_all_by_template():
tmp = {'nameLast': 'Williams', 'birthCity': 'San Diego'}
connect_info = {"directory": data_dir, "file_name": "People.csv"}
csv_tbl = CSVDataTable("people", connect_info, None)
result = csv_tbl.find_by_template(tmp)
print("Matching all by templates result: \n", json.dumps(result, indent=2))
def t_find_by_tmp():
tmp = {'nameLast': 'Williams', 'nameFirst': 'Ted'}
connect_info = {"directory": data_dir, "file_name": "People.csv"}
csv_tbl = CSVDataTable("people", connect_info, None)
result = csv_tbl.find_by_template(tmp)
print(result)
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_csv_find_by_template():
connect_info = {"directory": data_dir, "file_name": "People.csv"}
csv_tbl = CSVDataTable("people", connect_info, None)
template = {"nameLast": "Williams", "birthCity": "San Diego"}
subset = csv_tbl.find_by_template(
template, field_list=["playerID", "nameLast", "nameFirst"])
print("Found by template")
print(str(pd.DataFrame(subset)))
def test_delelt_By_Tmp():
tem = {'nameLast': 'Williams', 'birthState': 'NC'}
connect_info = {"directory": data_dir, "file_name": "People.csv"}
csv_tbl = CSVDataTable("People", connect_info, None)
result = csv_tbl.delete_by_template(tem)
print(result)
print(
csv_tbl.find_by_template({
'nameLast': 'Williams',
'birthState': 'NC'
}))
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_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 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 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_find_by_template(data_dir, file_name, key_cols, fields, tmp):
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_template(template=tmp, field_list=fields)
if res is None:
print("Find By Template on", file_name.split('.csv')[0], "with template", tmp, ":\n", 'None\n')
else:
print("Find By Template on", file_name.split('.csv')[0], "with template", tmp, ":\n",
json.dumps(res, indent=2), '\n')
def test_find_by_template():
connect_info = {"directory": data_dir, "file_name": "_Small.csv"}
key_cols = ['playerID', 'teamID', 'yearID', 'stint']
fields = ['playerID', 'teamID', 'yearID', 'AB', 'H', 'HR', 'RBI']
template = {'teamID': 'CL1', 'yearID': '1871', "stint": "1"}
csv_tbl = CSVDataTable("batting", connect_info, key_columns=key_cols)
output = csv_tbl.find_by_template(template=template, field_list=None)
print("Query Result: \n", json.dumps(output, indent=2))
def test_match_all():
tmp = {'nameLast': 'Aardsma', 'birthCity': 'Denver'}
connect_info = {
"directory": data_dir,
"file_name": "People.csv"
}
csv_tbl = CSVDataTable("people", connect_info, None)
field_list = ["nameFirst", "nameLast", "birthCity", "playerID"]
result = csv_tbl.find_by_template(tmp, field_list=field_list)
print(json.dumps(result, indent=2))
def t_find_by_template():
connect_info = {
"directory": data_dir,
"file_name": "Batting.csv"
}
key_cols=['playerID', 'teamID', 'yearID', 'stint']
fields=['playerID', 'teamID', 'yearID', 'AB', 'H', 'HR', 'RBI']
tmp={'teamID': 'BOS', 'yearID': '1960'}
csv_tbl = CSVDataTable("batting", connect_info, key_columns=key_cols)
res=csv_tbl.find_by_template(template=tmp, field_list=fields)
print("Query result ='\n'", json.dumps(res, indent=3))
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_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))
