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 test_delete_by_template_fail():
print("\n******************** " + "test_delete_by_template_fail" + " ********************")
try:
csv_tbl = CSVDataTable(table_name, connect_info, key_columns)
print('Delete by template {"birthCountry": "USA", "teamID": "BS1"}')
tmp = {"birthCountry": "USA", "teamID": "BS1"}
csv_tbl.delete_by_template(tmp)
except Exception as e:
print("Exception =", e)
print("Correct answer.")
print("******************** " + "end test_delete_by_template_fail" + " ********************\n")
def test_delete_by_template():
connect_info = {"directory": data_dir, "file_name": "People.csv"}
csv_tbl = CSVDataTable("people", connect_info, key_columns=["playerID"])
result1_delete_single = csv_tbl.delete_by_template(template={
"playerID": "aasedo01",
"birthYear": "1954"
})
result2_delete_multiple = csv_tbl.delete_by_template(
template={"birthYear": "1954"})
print(result1_delete_single)
print(result2_delete_multiple)
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_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_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 t_csv_delete_by_template():
connect_info = {"directory": data_dir, "file_name": "People.csv"}
csv_tbl = CSVDataTable("people", connect_info, ["playerID"])
r = {"playerID": "abbotgl01", "birthYear": "1951", "birthMonth": "2"}
print("Lines removed:", csv_tbl.delete_by_template(r), "should equal 1")
print("Lines removed:", csv_tbl.delete_by_template(r), "should equal 0")
print("Lines removed:", csv_tbl.delete_by_template({"birthYear": "1951"}),
"should > 1")
print("Lines removed:", csv_tbl.delete_by_template({"birthYear": "1951"}),
"should equal 0")
try:
csv_tbl.delete_by_template({"fdsfdafd": "123"})
print("Mismatch test failed")
except Exception:
print("Mismatch test passed")
def t_delete_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.delete_by_template(tmp)
print(result)
def test_delete_by_template_bad():
connect_info = {"directory": data_dir, "file_name": "People_test.csv"}
del_info = {"birthYear": "1980", "birthMonth": "0", "birthDay": "27"}
csv_tbl = CSVDataTable("people_test.csv", connect_info, None)
result = csv_tbl.delete_by_template(del_info)
print("Number of rows deleted by template(testing bad): ", result)
def t_csv_del_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_deletions = csv_tbl.delete_by_template(template)
print("Delete by template: " + str(n_deletions) + " deletions")
print(str(csv_tbl))
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_delete_by_template2():
connect_info = {
"directory": data_dir,
"file_name": "shire.csv"
}
tmp = {'last_name': 'Logan'}
csv_tbl = CSVDataTable('shire', connect_info, None)
result = csv_tbl.delete_by_template(tmp)
print("The number of rows to delete by searching template: {}".format(result))
print("The table now looks like: \n", csv_tbl)
def test_delete_by_template():
connect_info = {"directory": data_dir, "file_name": "_Small.csv"}
key_cols = ['playerID', 'yearID', 'stint', 'teamID']
template = {'teamID': 'CL1', 'yearID': '1871'}
csv_tbl = CSVDataTable("batting", connect_info, key_columns=key_cols)
output = csv_tbl.delete_by_template(template=template)
print("Query Result: \n", json.dumps(output, indent=2))
def t_delete_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'}
res = csv_tbl.delete_by_template(tmp)
print("Query result =", res)
def t_delete_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.delete_by_template(template=tmp)
# print("Created table = " + str(csv_tbl))
print("Query result = ", res)
def delete_by_template_test(template=None):
"""
:param data_table:
:param template:
:return:
"""
connect_info = {"directory": data_dir, "file_name": "Salaries.csv"}
csv_tbl = CSVDataTable("Salaries", connect_info, key_columns=['playerID'])
template = {'playerID': 'barkele01'}
affected_num = csv_tbl.delete_by_template(template)
print("performing delete_by_template_test")
print("deleted and affected_num is:")
print(affected_num)
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_delete_by_template():
connect_info = {
"directory": data_dir,
"file_name": "Batting.csv"
}
key_cols = ['playerID', 'teamID', 'yearID', 'stint']
tmp = {'yearID': '1900'}
csv_tbl = CSVDataTable("batting", connect_info, key_columns=key_cols)
cnt = csv_tbl.delete_by_template(template=tmp)
print("Number of rows deleted:", cnt)
def t_delete_by_template(data_dir, file_name, key_cols, 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.delete_by_template(template=tmp)
check = csv_tbl.find_by_template(template=tmp)
if res > 0 and check:
print("Record found - Delete by template on ", file_name.split('.csv')[0], "failed:\n", tmp, "\n")
elif res > 0:
print("Record not found after deletion - Delete By Template on", file_name.split('.csv')[0],
"with template\n", tmp, "\nwas successful with the deletion of {} rows".format(res), "\n")
elif res == 0:
print("No match found for deletion based on the given template", tmp, "\nTest Successful. 0 rows deleted")
def test_delete_by_template_good():
print("\n******************** " + "test_delete_by_template_good" + " ********************")
try:
csv_tbl = CSVDataTable(table_name, connect_info, key_columns)
print('Delete by template key {"birthMonth": "9", "birthDay": "22", "birthCountry": "USA",'
'"birthState": "MA", "deathCountry": "USA"}')
tmp = {"birthMonth": "9", "birthDay": "22", "birthCountry": "USA",
"birthState": "MA", "deathCountry": "USA"}
r1 = csv_tbl.find_by_template(tmp)
print('BEFORE deleting, all rows =\n', json.dumps(r1, indent=2))
print('Deleting...')
r2 = csv_tbl.delete_by_template(tmp)
print("Delete returned ", r2, "\n")
r3 = csv_tbl.find_by_template(tmp)
print('AFTER deleting, all rows =\n', json.dumps(r3, indent=2))
print("Correct answer.")
except Exception as e:
print("Exception =", e)
print("Wrong answer.")
print("******************** " + "end test_delete_by_template_good" + " ********************\n")
def test_delete_by_template():
print("\n******************** " + "test_delete_by_template" +
" ********************")
print("Compare time elapsed for delete_by_template.")
csv_tbl = CSVDataTable(table_name, csv_connect_info, key_columns)
rdb_tbl = RDBDataTable(table_name, rdb_connect_info, key_columns)
tmp = {"birthCountry": "USA", "birthState": "MA", "birthCity": "Boston"}
start1 = time.time()
r1 = csv_tbl.delete_by_template(tmp)
end1 = time.time()
elapsed1 = end1 - start1
print("Time elapsed for CSVDataTable is ", elapsed1, "seconds.")
start2 = time.time()
r2 = rdb_tbl.delete_by_template(tmp)
end2 = time.time()
elapsed2 = end2 - start2
print("Time elapsed for RDBDataTable is ", elapsed2, "seconds.")
print("******************** " + "end test_delete_by_template" +
" ********************\n")
def t_delete_by_template():
connect_info = {"directory": data_dir, "file_name": "Batting.csv"}
key_cols = ['playerID', 'teamID', 'yearID', 'stint']
tmp = {'playerID': 'aardsda01', 'yearID': '1999'}
file = "csv_table_test.txt"
with open(file, 'a+') as f:
f.write("\n\n******************** " + "test_delete_by_template" +
" ********************")
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 template = " + str(tmp))
r1 = csv_tbl.find_by_template(tmp)
with open(file, 'a+') as f:
f.write("\nReturned row = '\n'" + str(json.dumps(r1, indent=3)))
r2 = csv_tbl.delete_by_template(tmp)
with open(file, 'a+') as f:
f.write("\nDelete returned " + str(r2))
r3 = csv_tbl.find_by_template(tmp)
with open(file, 'a+') as f:
f.write("\nQuery result after delete='\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("\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_template" +
" ********************")
def t_delete_by_template():
connect_info = {
"directory": "../Data",
"file_name": "orderdetails.csv",
"delimiter": ";"
}
csv_tbl = CSVDataTable("orderdetails", connect_info,
key_columns=["orderNumber", "orderLineNumber"])
fields = ['orderNumber', 'productCode']
tmp = {'productCode': "S18_1749"}
r1 = csv_tbl.find_by_template(template=tmp, field_list=fields)
print("Details for order '10100' = \n", json.dumps(r1, indent=2))
print("\nDeleting productCode 'S18_1749':")
del_tmp = {'orderNumber': "10100", 'productCode': 'S18_1749'}
r2 = csv_tbl.delete_by_template(template=del_tmp)
print("Delete returned ", r2, "\n")
tmp2 = {'orderNumber': "10100"}
r3 = csv_tbl.find_by_template(template=tmp2, field_list=fields)
print("Details for order '10100' after delete = \n", json.dumps(r3, indent=2))
import csv
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)
