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