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 test_update_by_key_fail():
print("\n******************** " + "test_update_by_key_fail" + " ********************")
try:
csv_tbl = CSVDataTable(table_name, connect_info, key_columns)
print('Update by primary key "abadijo01", new value is {"playerID": "howarma01"}')
csv_tbl.update_by_key(["abadijo01"], {"playerID": "howarma01"})
except Exception as e:
print("Exception =", e)
print("Correct answer.")
print("******************** " + "end test_update_by_key_fail" + " ********************\n")
def t_csv_update_by_key():
connect_info = {"directory": data_dir, "file_name": "People.csv"}
csv_tbl = CSVDataTable("people", connect_info, ["playerID"])
r = {"playerID": "abbotgl01"}
keys = ["abbotgl01"]
new_val = {"deathYear": "123", "deathMonth": "12", "deathDay": "1"}
print("Lines updated:", csv_tbl.update_by_key(keys, new_val),
"should equal 1")
print("Lines updated:", csv_tbl.update_by_key(keys, new_val),
"should equal 0")
print("Lines updated:",
csv_tbl.update_by_key(keys, {"playerID": "abbotgl01"}),
"should equal 0")
def t_update_by_key_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_key_fail" +
" ********************")
try:
csv_tbl = CSVDataTable("batting", connect_info, key_columns=key_cols)
key_fields = ['aardsda01', 'ATL', '2015', '1']
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)))
new_values = {'yearID': '2008', 'teamID': 'BOS'}
with open(file, 'a+') as f:
f.write("\nAttempt to update this row with bad new values = " +
str(json.dumps(new_values, indent=3)))
r2 = csv_tbl.update_by_key(key_fields, new_values)
with open(file, 'a+') as f:
f.write("\nUpdate returned " + str(r2))
r3 = csv_tbl.find_by_primary_key(key_fields)
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_key_fail" +
" ********************")
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_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_key(PRIMARY_KEY_SINGLE_VALUE,
UPDATE_NORMAL_SINGLE)
result_m = csv_tbl_m.update_by_key(PRIMARY_KEY_MULTIPLE_VALUE,
UPDATE_NORMAL_MULTIPLE)
self.assertEqual(1, result_s)
self.assertEqual(
UPDATE_NORMAL_RESULT_SINGLE,
csv_tbl_s.find_by_primary_key(PRIMARY_KEY_SINGLE_VALUE))
self.assertEqual(1, result_m)
self.assertEqual(
UPDATE_NORMAL_RESULT_MULTIPLE,
csv_tbl_m.find_by_primary_key(PRIMARY_KEY_MULTIPLE_VALUE))
def test_update_by_key_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_key(PRIMARY_KEY_SINGLE_VALUE,
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_key(PRIMARY_KEY_MULTIPLE_VALUE,
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 t_update_by_key():
connect_info = {"directory": data_dir, "file_name": "People.csv"}
csv_tbl = CSVDataTable("people", connect_info, key_columns=['playerID'])
result = csv_tbl.update_by_key(['aardsda01'], {
'birthCity': 'Beijing',
'deathYear': '2019'
})
print(result)
print(csv_tbl.find_by_primary_key(['aardsda01']))
def test_update_by_key_failure_normal(self):
csv_tbl = CSVDataTable("people", CONNECT_INFO,
PRIMARY_KEY_SINGLE_FILED)
with self.assertRaises(Exception) as context:
result = csv_tbl.update_by_key(PRIMARY_KEY_SINGLE_VALUE,
{"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_key([], UPDATE_NORMAL_SINGLE)
self.assertEqual('Invalid key fields!', str(context.exception))
csv_tbl = CSVDataTable("people", CONNECT_INFO, None)
with self.assertRaises(Exception) as context:
result = csv_tbl.update_by_key([], {})
self.assertEqual('Primary Key has not been setup yet!',
str(context.exception))
def t_update_by_key():
connect_info = {
"directory": data_dir,
"file_name": "shire.csv"
}
key_cols = ['last_name']
new_val = {'last_name': 'Cheros'}
csv_tbl = CSVDataTable('shire', connect_info,key_cols)
result = csv_tbl.update_by_key(['Baggins'], new_val)
print("The number of rows updated: {}".format(result))
print("This is what the table looks like now: ", csv_tbl)
def t_csv_update_by_key():
connect_info = {"directory": data_dir, "file_name": "People.csv"}
csv_tbl = CSVDataTable("people", connect_info, ["playerID"])
# key_fields = ["willite01"]
key_fields = ["aardsda01"]
n_updates = csv_tbl.update_by_key(key_fields,
new_values={"birthCity": "Los Angeles"})
print("Update by template: " + str(n_updates) + " updates")
print(str(csv_tbl))
def t_update_by_key():
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)
key_fields = ['aaa', '1997', '1997', '1']
new_values = {'yearID' : '2000'}
res = csv_tbl.update_by_key(key_fields, new_values)
print("Query result =", res)
def test_update_by_key():
connect_info = {"directory": data_dir, "file_name": "_Small.csv"}
key_cols = ['playerID', 'yearID', 'stint', 'teamID']
key_values = ["battijo01", '1871', '1', 'CL1']
new_values = {"playerID": "swapdog", 'G': '6', 'AB': '20'}
csv_tbl = CSVDataTable("batting", connect_info, key_columns=key_cols)
output = csv_tbl.update_by_key(key_fields=key_values,
new_values=new_values)
print("Query Result: \n", json.dumps(output, indent=2))
def test_update_by_key_success_primary_key(self):
csv_tbl_s = CSVDataTable("people", CONNECT_INFO,
PRIMARY_KEY_SINGLE_FILED)
self.assertTrue("abadan01" 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_key(PRIMARY_KEY_SINGLE_VALUE,
UPDATE_PK_SINGLE)
self.assertEqual(1, result_s)
self.assertEqual(UPDATE_PK_RESULT_SINGLE,
csv_tbl_s.find_by_primary_key(["aaa"]))
self.assertTrue("abadan01" 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("abadan01_Abad" 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_key(PRIMARY_KEY_MULTIPLE_VALUE,
UPDATE_PK_MULTIPLE)
self.assertEqual(1, result_m)
self.assertEqual(UPDATE_PK_RESULT_MULTIPLE,
csv_tbl_m.find_by_primary_key(["aaa", "bbb"]))
self.assertTrue("abadan01_Abad" not in csv_tbl_m._primary_keys_set)
self.assertTrue("aaa_bbb" in csv_tbl_m._primary_keys_set)
def t_update_by_key():
connect_info = {
"directory": data_dir,
"file_name": "Batting.csv"
}
key_cols = ['playerID', 'teamID', 'yearID', 'stint']
key_vals = ['willite01', 'BOS', '1950', '1']
new_vals = {'yearID': '2020'}
csv_tbl = CSVDataTable("batting", connect_info, key_columns=key_cols)
cnt = csv_tbl.update_by_key(key_fields=key_vals, new_values=new_vals)
print("Number of rows updated:", cnt)
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 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_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 t_update_by_key():
connect_info = {
"directory": "../Data",
"file_name": "orderdetails.csv",
"delimiter": ";"
}
csv_tbl = CSVDataTable("orderdetails", connect_info,
key_columns=['orderNumber', "orderLineNumber"])
fields = ['orderNumber', 'productCode']
key_vals = ['10100', '2']
r1 = csv_tbl.find_by_primary_key(key_vals, fields)
print("Find result= \n", json.dumps(r1, indent=2))
r = csv_tbl.update_by_key(key_vals, {"orderNumber": "10100", "productCode": 'S18_3171'})
print("Update returned ", r, "\n")
r2 = csv_tbl.find_by_primary_key(key_vals, fields)
print("Find result= \n", json.dumps(r2, indent=2))
def test_update_by_key():
print("\n******************** " + "test_update_by_key" +
" ********************")
print("Compare time elapsed for update_by_key.")
csv_tbl = CSVDataTable(table_name, csv_connect_info, key_columns)
rdb_tbl = RDBDataTable(table_name, rdb_connect_info, key_columns)
value = {"nameFirst": "Jackson", "nameLast": "Copper"}
start1 = time.time()
r1 = csv_tbl.update_by_key(["abadijo01"], value)
end1 = time.time()
elapsed1 = end1 - start1
print("Time elapsed for CSVDataTable is ", elapsed1, "seconds.")
start2 = time.time()
r2 = rdb_tbl.update_by_key(["abadijo01"], value)
end2 = time.time()
elapsed2 = end2 - start2
print("Time elapsed for RDBDataTable is ", elapsed2, "seconds.")
print("******************** " + "end test_update_by_key" +
" ********************\n")
def t_update_by_key():
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'}
key_vals = ['willite01', 'BOS', '1960', '1']
csv_tbl = CSVDataTable("batting", connect_info, key_cols)
res = csv_tbl.update_by_key(key_fields=key_vals,
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_key_bad():
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",
"extra": "column"
}
key_cols = ['playerID']
key_val = ['aardsda01']
csv_tbl = CSVDataTable("people_test.csv",
connect_info,
key_columns=key_cols)
result = csv_tbl.update_by_key(key_val, new_info)
print("Number of rows updated by key(testing bad): ", result)
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)
