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")