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 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_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_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_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 t_csv_insert_with_key_failure():
connect_info = {"directory": data_dir, "file_name": "People.csv"}
csv_tbl = CSVDataTable("people", connect_info, ["playerID"])
print("Number of records before insert: " + str(len(csv_tbl.get_rows())))
new_record = {"playerID": "aardsda01", "birthCity": "Dallas", "age": "26"}
csv_tbl.insert(new_record)
print("Number of records after insert: " + str(len(csv_tbl.get_rows())))
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 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_csv_insert_no_key():
connect_info = {"directory": data_dir, "file_name": "People.csv"}
csv_tbl = CSVDataTable("people", connect_info, None)
print("Number of records before insert: " + str(len(csv_tbl.get_rows())))
new_record = {
"playerID": "lawlesszach01",
"birthCity": "Dallas",
"age": "26"
}
csv_tbl.insert(new_record)
print("Number of records after insert: " + str(len(csv_tbl.get_rows())))
def t_insert2():
connect_info = {
"directory": data_dir,
"file_name": "shire.csv"
}
key_cols = ['last_name','first_name','email']
csv_tbl = CSVDataTable('shire', connect_info, key_columns=key_cols)
new_row = {
'last_name': 'Logan', 'first_name': 'Vildred', 'email': '*****@*****.**'
}
csv_tbl.insert(new_row)
print("The table now looks like: \n", csv_tbl)
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_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 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_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_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 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 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 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_row = {
'playerID': 'willite01', 'teamID': 'BOS', 'yearID': '1960', 'stint': '2', 'H': '21', 'AB': '23'
}
return csv_tbl.insert(new_row)
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 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_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_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 test_insert_bad():
connect_info = {"directory": data_dir, "file_name": "People_test.csv"}
new_info = {
"playerID": "ss5644",
"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",
"Unknown": "column"
}
csv_tbl = CSVDataTable("people_test.csv", connect_info, None)
result = csv_tbl.insert(new_info)
print("Number of inserted row = ", result)
result1 = csv_tbl.find_by_template(new_info)
print("The new row inserted: \n", json.dumps(result1, indent=2))
def test_insert_good():
connect_info = {"directory": data_dir, "file_name": "People_test.csv"}
new_info = {
"playerID": "willitr01",
"birthYear": "1992",
"birthMonth": "4",
"birthDay": "25",
"birthCountry": "USA",
"birthState": "CA",
"birthCity": "San Diego",
"deathYear": "",
"deathMonth": "",
"deathDay": "",
"deathCountry": "",
"deathState": "",
"deathCity": "",
"nameFirst": "Trevor",
"nameLast": "Williams",
"nameGiven": "Trevor Anthony",
"weight": "230",
"height": "75",
"bats": "R",
"throws": "R",
"debut": "2016-09-07",
"finalGame": "2018-09-27",
"retroID": "willt002",
"bbrefID": "willitr01"
}
csv_tbl = CSVDataTable("people_test.csv", connect_info, None)
result = csv_tbl.insert(new_info)
print("Number of inserted row = ", result)
result1 = csv_tbl.find_by_template(new_info)
print("The new row inserted: \n", json.dumps(result1, indent=2))
def t_csv_insert():
connect_info = {"directory": data_dir, "file_name": "People.csv"}
csv_tbl = CSVDataTable("people", connect_info, ["playerID"])
r = {"playerID": "abbotgl01"}
csv_tbl.insert({"playerID": "abbotgl99"})
print(csv_tbl.find_by_primary_key(["abbotgl99"]))
try:
csv_tbl.insert({"playerID": "abbotgl99"})
print("Duplicate row insert test, failed")
except Exception:
print("Duplicate row insert test, passed")
try:
csv_tbl.insert({"birthYear": "abbotgl99"})
print("Empty key insert test, failed")
except Exception:
print("Empty key insert test, passed")
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_insert_failure_dup_keys(self):
csv_tbl = CSVDataTable("people", CONNECT_INFO,
PRIMARY_KEY_SINGLE_FILED)
with self.assertRaises(Exception) as context:
csv_tbl.insert(INSERT_ROW_DUP_PK)
self.assertEqual(PK_UNIQUE_ERROR, str(context.exception))
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])
# m1 = matches_template(row, {"birthDay": "25", "birthCountry": "USA"})
# print("Match = ", m1)
# cols = get_columns(row, ['playerID', 'nameLast'])
# print("The requested columns are = ", cols)
