def t_update_by_key():
connect_info = {
"host": "localhost",
"port": 3306,
"user": "******",
"password": "******",
"db": "test"
}
rdb_tbl = RDBDataTable("orderdetails",
connect_info,
key_columns=['orderNumber', "orderLineNumber"])
fields = ['orderNumber', 'productCode']
key_vals = ['10101', '2']
r1 = rdb_tbl.find_by_primary_key(key_vals, fields)
print("Find result= \n", json.dumps(r1, indent=2))
r = rdb_tbl.update_by_key(key_vals, {
"orderNumber": "10101",
"productCode": 'S18_3171'
})
print("Update returned ", r, "\n")
r2 = rdb_tbl.find_by_primary_key(key_vals, fields)
print("Find result= \n", json.dumps(r2, indent=2))
def t_delete_by_template():
connect_info = {
"host": "localhost",
"port": 3306,
"user": "******",
"password": "******",
"db": "test"
}
rdb_tbl = RDBDataTable("orderdetails",
connect_info,
key_columns=["orderNumber", "orderLineNumber"])
fields = ['orderNumber', 'productCode']
tmp = {'productCode': "S18_1749"}
r1 = rdb_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 = rdb_tbl.delete_by_template(template=del_tmp)
print("Delete returned ", r2, "\n")
tmp2 = {'orderNumber': "10100"}
r3 = rdb_tbl.find_by_template(template=tmp2, field_list=fields)
print("Details for order '10100' after delete = \n",
json.dumps(r3, indent=2))
def t_find_by_pk():
connect_info = {
"user": "******",
"password": "******",
"db":"lahman2019raw"
}
key_cols = ['playerID', 'teamID', 'yearID', 'stint']
fields = ['playerID', 'teamID', 'yearID', 'AB', 'H', 'HR', 'RBI']
key_vals = ['willite01', 'BOS', '1960', '1']
file = "rdb_table_test.txt"
with open(file, 'a+') as f:
f.write("\n\n******************** " + "test_find_by_primary_key" + " ********************")
f.write("\nThe key columns of the table are "+str(key_cols))
f.write("\nThe values for the key_columns to use to find a record are "+str(key_vals))
f.write("\nThe subset of the fields of the record to return is "+str(fields))
try:
rdb_tbl = RDBDataTable("batting", connect_info, key_columns=key_cols)
res=rdb_tbl.find_by_primary_key(key_vals,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_primary_key" + " ********************")
def t_update_by_template_fail():
connect_info = {
"user": "******",
"password": "******",
"db": "lahman2019raw"
}
key_cols = ['playerID', 'teamID', 'yearID', 'stint']
file = "rdb_table_test.txt"
with open(file, 'a+') as f:
f.write("\n\n******************** " + "test_update_by_tamplate_fail" + " ********************")
try:
rdb_tbl = RDBDataTable("batting", connect_info, key_columns=key_cols)
tmp = {'playerID': 'aardsda01', 'yearID':'2015', 'stint':'0', 'teamID':'ATL'}
with open(file, 'a+') as f:
f.write("\nLooking up with template = "+str(tmp))
r1 = rdb_tbl.find_by_template(tmp)
new_values = {'stint':'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 bad new values = "+str(json.dumps(new_values, indent=3)))
r2 = rdb_tbl.update_by_template(tmp, new_values)
with open(file, 'a+') as f:
f.write("\nUpdate returned"+str( r2))
r3 = rdb_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 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_tamplate_fail" + " ********************")
def t_delete_by_template():
connect_info = {
"user": "******",
"password": "******",
"db": "lahman2019raw"
}
key_cols = ['playerID', 'teamID', 'yearID', 'stint']
tmp = {'playerID': 'aardsda01', 'yearID': '1997'}
file = "rdb_table_test.txt"
with open(file, 'a+') as f:
f.write("\n\n******************** " + "test_delete_by_template" + " ********************")
try:
rdb_tbl = RDBDataTable("batting", connect_info, key_columns=key_cols)
with open(file, 'a+') as f:
f.write("\nLooking up with template = "+str(tmp))
r1 = rdb_tbl.find_by_template(tmp)
with open(file, 'a+') as f:
f.write("\nReturned row = '\n'"+str (json.dumps(r1, indent=3)))
r2 = rdb_tbl.delete_by_template(tmp)
with open(file, 'a+') as f:
f.write("\nDelete returned "+str (r2))
r3 = rdb_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 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_rdb_delete_by_key():
connect_info = SQLHelper._get_default_connection()
rdb_tbl = RDBDataTable("people", connect_info, ["playerID"], commit=False)
print("Lines removed:", rdb_tbl.delete_by_key(["abbotgl01"]), "should equal 1")
print("Lines removed:", rdb_tbl.delete_by_key(["abbotgl01"]), "should equal 0")
print("Lines removed:", rdb_tbl.delete_by_key(["abbeych01"]), "should equal 1")
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 = {
"playerID": "allisar01",
"teamID": "CL1",
"yearID": "1871",
"stint": "2"
}
csv_tbl = RDBDataTable("batting", connect_info, key_cols)
res = csv_tbl.update_by_template(template=tmp,
new_values={
"playerID": "allisar01",
"teamID": "CL1",
"yearID": "1871",
"stint": "3",
"AB": "1",
"H": "100",
"HR": "100",
"RBI": "100"
})
# print("Created table = " + str(csv_tbl))
print("Query result = ", res)
def test4():
t = RDBDataTable("People", ["playerID"])
#tmp = {"nameLast": "Williams"}
result = t.find_by_primary_key(
["willite01"], field_list=["playerID", "nameLast", "nameFirst"])
print("result = ", result)
def test_update_by_key_good():
print("\n******************** " + "test_update_by_key_good" +
" ********************")
try:
rdb_tbl = RDBDataTable(table_name, connect_info, key_columns)
print(
'Update by primary key "abadijo01", new value is {"nameFirst": "Jackson", "nameLast": "Copper"}'
)
r1 = rdb_tbl.find_by_primary_key(["abadijo01"])
print("BEFORE updating, the row =\n", json.dumps(r1, indent=2))
print("Updating...")
r2 = rdb_tbl.update_by_key(["abadijo01"], {
"nameFirst": "Jackson",
"nameLast": "Copper"
})
print("Update returned ", r2, "\n")
r3 = rdb_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_insert(table_name, key_cols, insert_record):
rdb_tbl = RDBDataTable(table_name, connect_info, key_cols)
existing = rdb_tbl.find_by_template(template=insert_record)
if existing == ():
existing_len = 0
else:
existing_len = len(existing)
try:
res = rdb_tbl.insert(new_record=insert_record)
check = rdb_tbl.find_by_template(template=insert_record)
if check == ():
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",
table_name, "was successful\n", insert_record, "\n")
else:
print("Record not found - Insert into", table_name, "failed\n")
except Exception as e:
print("Insertion of record", insert_record, "unsuccessful due to:\n",
e)
print("Test successful as the insert failed as it should")
def test_find_by_template_good():
print("\n******************** " + "test_find_by_template_good" +
" ********************")
try:
rdb_tbl = RDBDataTable(table_name, connect_info, key_columns)
print(
'Find by template {"birthMonth": "9", "birthDay": "22", "birthCountry": "USA", "birthState": "MA"}'
)
tmp = {
"birthMonth": "9",
"birthDay": "22",
"birthCountry": "USA",
"birthState": "MA"
}
res = rdb_tbl.find_by_template(
tmp, ["playerID", "nameFirst", "nameLast", "nameGiven"])
print("Result =\n", json.dumps(res, indent=2))
print("Correct answer.")
except Exception as e:
print("Exception =", e)
print("Wrong answer.")
print("******************** " + "end test_find_by_template_good" +
" ********************\n")
def test_insert_failure_normal(self):
rdb_tbl = RDBDataTable("people", CONNECT_INFO,
PRIMARY_KEY_SINGLE_FILED)
with self.assertRaises(Exception) as context:
rdb_tbl.insert(INSERT_ROW_FAILURE)
self.assertEqual("field column is not a subset of table columns!",
str(context.exception))
def test_delete_by_template_success(self):
rdb_tbl = RDBDataTable("people", CONNECT_INFO,
PRIMARY_KEY_SINGLE_FILED)
result = rdb_tbl.delete_by_template(TEMPLATE)
self.assertEqual(result, 1)
result = rdb_tbl.delete_by_template({"birthYear": "1"})
self.assertEqual(result, 0)
def test_update_by_template_success_primary_key(self):
rdb_tbl_s = RDBDataTable("people", CONNECT_INFO,
PRIMARY_KEY_SINGLE_FILED)
result_s = rdb_tbl_s.update_by_template(TEMPLATE, UPDATE_PK_SINGLE)
self.assertEqual(1, result_s)
self.assertEqual(UPDATE_TEMPLATE_PK_SINGLE_RESULT,
rdb_tbl_s.find_by_primary_key(["aaa"]))
def t_insert_fail():
connect_info = {
"user": "******",
"password": "******",
"db": "lahman2019raw"
}
key_cols = ['playerID', 'teamID', 'yearID', 'stint']
new_record = {'playerID': 'aardsda01', 'yearID': '1999', 'teamID': 'ATL', 'stint': '0'}
file = "rdb_table_test.txt"
with open(file, 'a+') as f:
f.write("\n\n******************** " + "test_insert_fail" + " ********************")
try:
rdb_tbl = RDBDataTable("batting", connect_info, key_columns=key_cols)
key = ['aardsda01', 'ATL', '1999', '0']
with open(file, 'a+') as f:
f.write("\nLooking up with key = " + str(key))
r1 = rdb_tbl.find_by_primary_key(key)
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)))
rdb_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_delete_by_template_good():
print("\n******************** " + "test_delete_by_template_good" +
" ********************")
try:
rdb_tbl = RDBDataTable(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 = rdb_tbl.find_by_template(tmp)
print('BEFORE deleting, all rows =\n', json.dumps(r1, indent=2))
print('Deleting...')
r2 = rdb_tbl.delete_by_template(tmp)
print("Delete returned ", r2, "\n")
r3 = rdb_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_failure(self):
rdb_tbl = RDBDataTable("people", CONNECT_INFO,
PRIMARY_KEY_SINGLE_FILED)
with self.assertRaises(Exception) as context:
result = rdb_tbl.delete_by_template({"": ""})
self.assertEqual("template column is not a subset of table columns!",
str(context.exception))
def t_rdb_insert():
connect_info = SQLHelper._get_default_connection()
rdb_tbl = RDBDataTable("people", connect_info, ["playerID"], commit=False)
rdb_tbl.insert({"playerID": "abbotgl99"})
if not rdb_tbl.find_by_primary_key(["abbotgl99"]):
print("insert test, failed")
else:
print("insert test, passed")
def test_insert_failure_dup_keys(self):
rdb_tbl = RDBDataTable("people", CONNECT_INFO,
PRIMARY_KEY_SINGLE_FILED)
with self.assertRaises(Exception) as context:
rdb_tbl.insert(INSERT_ROW_DUP_PK)
self.assertEqual(
'(1062, "Duplicate entry \'aardsda01\' for key \'PRIMARY\'")',
str(context.exception))
def t_rdb_find_by_primary_key():
connect_info = SQLHelper._get_default_connection()
rdb_tbl = RDBDataTable("people", connect_info, ["playerID"], commit=False)
r = {
"playerID": "abbotgl01"
}
print(rdb_tbl.find_by_primary_key(r.values()))
print(rdb_tbl.find_by_primary_key(r.values(), ["birthCity", "birthState"]))
def test_update_by_template_failure_dup_keys(self):
rdb_tbl_s = RDBDataTable("people", CONNECT_INFO,
PRIMARY_KEY_SINGLE_FILED)
with self.assertRaises(Exception) as context:
result_s = rdb_tbl_s.update_by_template(TEMPLATE,
UPDATE_DUP_PK_SINGLE)
self.assertEqual(
'(1062, "Duplicate entry \'abadan01\' for key \'PRIMARY\'")',
str(context.exception))
def test_update_by_key_success_normal(self):
rdb_tbl_s = RDBDataTable("people", CONNECT_INFO,
PRIMARY_KEY_SINGLE_FILED)
result_s = rdb_tbl_s.update_by_key(PRIMARY_KEY_SINGLE_VALUE,
UPDATE_NORMAL_SINGLE)
self.assertEqual(1, result_s)
self.assertEqual(
UPDATE_NORMAL_RESULT_SINGLE,
rdb_tbl_s.find_by_primary_key(PRIMARY_KEY_SINGLE_VALUE))
def t_find_by_primary_key(table_name, key_cols, fields, keys):
rdb_tbl = RDBDataTable(table_name, connect_info, key_cols)
res = rdb_tbl.find_by_primary_key(key_fields=keys, field_list=fields)
if res == ():
print("Find By Primary Key on", table_name, "with keys", keys, ":\n",
"None\n")
else:
print("Find By Primary Key on", table_name, "with keys ", keys, ":\n",
json.dumps(res, indent=2), '\n')
def t_rdb_update_by_key():
connect_info = SQLHelper._get_default_connection()
rdb_tbl = RDBDataTable("people", connect_info, ["playerID"], commit=False)
keys = ["abbotgl01"]
new_val = {"deathYear": "123", "deathMonth": "12", "deathDay": "1"}
print("Lines updated:", rdb_tbl.update_by_key(keys, new_val), "should equal 1")
print("Lines updated:", rdb_tbl.update_by_key(keys, new_val), "should equal 0")
print("Lines updated:", rdb_tbl.update_by_key(keys, {"playerID": "abbotgl01"}), "should equal 0")
def t_delete_by_template():
key_cols = ['playerID', 'teamID', 'yearID', 'stint']
tmp = {'yearID': '1900'}
rdb_tbl = RDBDataTable(c_info["db"] + ".batting",
connect_info=c_info,
key_columns=key_cols)
cnt = rdb_tbl.delete_by_template(template=tmp)
print("Number of rows deleted:", cnt)
def t_find_by_template(table_name, key_cols, fields, tmp):
rdb_tbl = RDBDataTable(table_name=table_name,
connect_info=connect_info,
key_columns=key_cols)
res = rdb_tbl.find_by_template(template=tmp, field_list=fields)
if res == ():
print("Find By Template on", table_name, "with template", tmp, ":\n",
'None\n')
else:
print("Find By Template on", table_name, "with template", tmp, ":\n",
json.dumps(res, indent=2), '\n')
def t_rdb_find_by_template():
connect_info = SQLHelper._get_default_connection()
rdb_tbl = RDBDataTable("people", connect_info, ["playerID"], commit=False)
r = {
"playerID": "aasedo01",
"birthYear": "1954",
"birthCity": "Orange"
}
# print(rdb_tbl.find_by_template({}))
print(rdb_tbl.find_by_template(r))
print(rdb_tbl.find_by_template(r, ["weight", "height"]))
print(rdb_tbl.find_by_template(r, ["deathYear"]))
def test7():
t = RDBDataTable("People", key_columns=None, 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"}
r1 = t.find_by_template(tmp)
print("After insert", r1)
r2 = t.delete_by_template(tmp)
print("After delete ", r2)
def t_rdb_delete_by_template():
connect_info = SQLHelper._get_default_connection()
rdb_tbl = RDBDataTable("people", connect_info, ["playerID"], commit=False)
r = {
"playerID": "abbotgl01",
"birthYear": "1951",
"birthMonth": "2"
}
print("Lines removed:", rdb_tbl.delete_by_template(r), "should equal 1")
print("Lines removed:", rdb_tbl.delete_by_template(r), "should equal 0")
print("Lines removed:", rdb_tbl.delete_by_template({"birthYear": "1951"}), "should > 1")
print("Lines removed:", rdb_tbl.delete_by_template({"birthYear": "1951"}), "should equal 0")
