def get_conf_mtx_stats(matrixTable): """Get Classification Accuracy, Precision, Recall from a confusion matrix. Input: matrixTable - Confusion matrix table generated by IDAX.CONFUSION_MATRIX Output: Classification Accuracy, Precision, Recall """ ibm_db_conn, conn = connect_to_schema(schema, conn_str) sql = "CALL IDAX.CMATRIX_STATS('matrixTable=" + matrixTable + "')" stmt = ibm_db.exec_immediate(ibm_db_conn, sql) df = pd.DataFrame(columns=['Class', 'Precision', 'Recall']) row = ibm_db.fetch_assoc(stmt) while row != False: print("Classification Accuracy: ", "{:.1%}".format(row["ACC"])) row = ibm_db.fetch_assoc(stmt) stmt1 = ibm_db.next_result(stmt) while stmt1 != False: row = ibm_db.fetch_assoc(stmt1) while row != False: to_append = [ row["CLASS"], "{:.1%}".format(row["PPV"]), "{:.1%}".format(row["TPR"]) ] df_length = len(df) df.loc[df_length] = to_append row = ibm_db.fetch_assoc(stmt1) stmt1 = ibm_db.next_result(stmt) display(df) rc = ibm_db.close(ibm_db_conn) print('Connection Closed:', rc)
def print_multi_result_set(ibm_db_conn, sql): stmt = ibm_db.exec_immediate(ibm_db_conn, sql) row = ibm_db.fetch_assoc(stmt) while row != False: print(row) row = ibm_db.fetch_assoc(stmt) stmt1 = ibm_db.next_result(stmt) while stmt1 != False: row = ibm_db.fetch_assoc(stmt1) while row != False: print(row) row = ibm_db.fetch_assoc(stmt1) stmt1 = ibm_db.next_result(stmt) return None
def run_test_201(self): conn = ibm_db.connect(config.database, config.user, config.password) serverinfo = ibm_db.server_info( conn ) server = serverinfo.DBMS_NAME[0:3] if (server == 'IDS'): procedure = """CREATE FUNCTION multiResults () RETURNING CHAR(16), INT, VARCHAR(32), NUMERIC(7,2); DEFINE p_name CHAR(16); DEFINE p_id INT; DEFINE p_breed VARCHAR(32); DEFINE p_weight NUMERIC(7,2); FOREACH c1 FOR SELECT name, id, breed, weight INTO p_name, p_id, p_breed, p_weight FROM animals ORDER BY name DESC RETURN p_name, p_id, p_breed, p_weight WITH RESUME; END FOREACH; END FUNCTION;""" else: procedure = """CREATE PROCEDURE multiResults () RESULT SETS 3 LANGUAGE SQL BEGIN DECLARE c1 CURSOR WITH RETURN FOR SELECT name, id FROM animals ORDER BY name; DECLARE c2 CURSOR WITH RETURN FOR SELECT name, id, breed, weight FROM animals ORDER BY name DESC; DECLARE c3 CURSOR WITH RETURN FOR SELECT name FROM animals ORDER BY name; OPEN c1; OPEN c2; OPEN c3; END""" if conn: try: ibm_db.exec_immediate(conn, 'DROP PROCEDURE multiResults') except: pass ibm_db.exec_immediate(conn, procedure) stmt = ibm_db.exec_immediate(conn, 'CALL multiResults()') print("Fetching first result set") row = ibm_db.fetch_tuple(stmt) while ( row ): for i in row: print(str(i).strip()) row = ibm_db.fetch_tuple(stmt) if (server == 'IDS') : print("Fetching second result set (should fail -- IDS does not support multiple result sets)") else: print("Fetching second result set") res = ibm_db.next_result(stmt) if res: row = ibm_db.fetch_tuple(res) while ( row ): for i in row: print(str(i).strip()) row = ibm_db.fetch_tuple(res) if (server == 'IDS'): print("Fetching third result set (should fail -- IDS does not support multiple result sets)") else: print("Fetching third result set") res2 = ibm_db.next_result(stmt) if res2: row = ibm_db.fetch_tuple(res2) while ( row ): for i in row: print(str(i).strip()) row = ibm_db.fetch_tuple(res2) ibm_db.close(conn) else: print("Connection failed.")
def run_test_201(self): conn = ibm_db.connect(config.database, config.user, config.password) serverinfo = ibm_db.server_info(conn) server = serverinfo.DBMS_NAME[0:3] if (server == 'IDS'): procedure = """CREATE FUNCTION multiResults () RETURNING CHAR(16), INT, VARCHAR(32), NUMERIC(7,2); DEFINE p_name CHAR(16); DEFINE p_id INT; DEFINE p_breed VARCHAR(32); DEFINE p_weight NUMERIC(7,2); FOREACH c1 FOR SELECT name, id, breed, weight INTO p_name, p_id, p_breed, p_weight FROM animals ORDER BY name DESC RETURN p_name, p_id, p_breed, p_weight WITH RESUME; END FOREACH; END FUNCTION;""" else: procedure = """CREATE PROCEDURE multiResults () RESULT SETS 3 LANGUAGE SQL BEGIN DECLARE c1 CURSOR WITH RETURN FOR SELECT name, id FROM animals ORDER BY name; DECLARE c2 CURSOR WITH RETURN FOR SELECT name, id, breed, weight FROM animals ORDER BY name DESC; DECLARE c3 CURSOR WITH RETURN FOR SELECT name FROM animals ORDER BY name; OPEN c1; OPEN c2; OPEN c3; END""" if conn: try: ibm_db.exec_immediate(conn, 'DROP PROCEDURE multiResults') except: pass ibm_db.exec_immediate(conn, procedure) if sys.platform == 'zos': stmt = ibm_db.exec_immediate(conn, 'CALL MULTIRESULTS()') else: stmt = ibm_db.exec_immediate(conn, 'CALL multiresults()') print("Fetching first result set") row = ibm_db.fetch_tuple(stmt) while (row): for i in row: print(str(i).strip()) row = ibm_db.fetch_tuple(stmt) if (server == 'IDS'): print( "Fetching second result set (should fail -- IDS does not support multiple result sets)" ) else: print("Fetching second result set") res = ibm_db.next_result(stmt) if res: row = ibm_db.fetch_tuple(res) while (row): for i in row: print(str(i).strip()) row = ibm_db.fetch_tuple(res) if (server == 'IDS'): print( "Fetching third result set (should fail -- IDS does not support multiple result sets)" ) else: print("Fetching third result set") res2 = ibm_db.next_result(stmt) if res2: row = ibm_db.fetch_tuple(res2) while (row): for i in row: print(str(i).strip()) row = ibm_db.fetch_tuple(res2) ibm_db.close(conn) else: print("Connection failed.")
def run_test_200(self): conn = ibm_db.connect(config.database, config.user, config.password) serverinfo = ibm_db.server_info(conn) server = serverinfo.DBMS_NAME[0:3] if (server == 'IDS'): procedure = """ CREATE FUNCTION multiResults() RETURNING CHAR(16), INT; DEFINE p_name CHAR(16); DEFINE p_id INT; FOREACH c1 FOR SELECT name, id INTO p_name, p_id FROM animals ORDER BY name RETURN p_name, p_id WITH RESUME; END FOREACH; END FUNCTION; """ else: procedure = """ CREATE PROCEDURE multiResults () RESULT SETS 3 LANGUAGE SQL BEGIN DECLARE c1 CURSOR WITH RETURN FOR SELECT name, id FROM animals ORDER BY name; DECLARE c2 CURSOR WITH RETURN FOR SELECT name, id FROM animals WHERE id < 4 ORDER BY name DESC; DECLARE c3 CURSOR WITH RETURN FOR SELECT name, id FROM animals WHERE weight < 5.0 ORDER BY name; OPEN c1; OPEN c2; OPEN c3; END """ if conn: try: ibm_db.exec_immediate(conn, 'DROP PROCEDURE multiResults') except: pass ibm_db.exec_immediate(conn, procedure) stmt = ibm_db.exec_immediate(conn, 'CALL multiResults()') #print(stmt) print("Fetching first result set") row = ibm_db.fetch_tuple(stmt) while (row): for i in row: print(i) row = ibm_db.fetch_tuple(stmt) if (server == 'IDS'): print( "Fetching second result set (should fail -- IDS does not support multiple result sets)" ) else: print("Fetching second result set") #print(stmt) res = ibm_db.next_result(stmt) if res: row = ibm_db.fetch_tuple(res) while (row): for i in row: print(i) row = ibm_db.fetch_tuple(res) if (server == 'IDS'): print( "Fetching third result set (should fail -- IDS does not support multiple result sets)" ) else: print("Fetching third result set") res2 = ibm_db.next_result(stmt) if res2: row = ibm_db.fetch_tuple(res2) while (row): for i in row: print(i) row = ibm_db.fetch_tuple(res2) print("Fetching fourth result set (should fail)") res3 = ibm_db.next_result(stmt) if res3: row = ibm_db.fetch_tuple(res3) while (row): for i in row: print(i) row = ibm_db.fetch_tuple(res3) ibm_db.close(conn) else: print("Connection failed.")