def insert_results_into_table(table, database, *args):
connection_config_dict = get_db_info.get_values_from_json(
False, database.db_type)
connection = None
cursor = None
try:
connect_string = database.connection
connection = connect_string.connect(**connection_config_dict)
cursor = connection.cursor()
sql_insert_query = table.insert_string
tuple_list = []
for arg in args:
tuple_list.append(arg)
sql_tuple = tuple(tuple_list)
cursor.execute(sql_insert_query, sql_tuple)
connection.commit()
print("Record inserted successfully into the " + table.table_name +
" table")
except Exception as error:
connection.rollback()
print("Failed to insert into " + table.table_name +
" table {}".format(error))
finally:
# closing database connection.
if connection is not None:
cursor.close()
connection.close()
def create_database():
database = get_db_type.PostgreSQL
connection_config_dict = dbci.get_values_from_json(True, database.db_type)
connection = psycopg2.connect(**connection_config_dict)
cursor = connection.cursor()
cursor.execute("CREATE DATABASE spyda")
def drop_database():
database = get_db_type.PostgreSQL
connection_config_dict = dbci.get_values_from_json(True, database.db_type)
connection = psycopg2.connect(**connection_config_dict)
cursor = connection.cursor()
cursor.execute("DROP DATABASE [IF EXISTS] spyda")
def drop_database():
database = get_db_type.MySQL
connection_config_dict = dbci.get_values_from_json(True, database.db_type)
connection = mysql.connector.connect(**connection_config_dict)
cursor = connection.cursor()
cursor.execute("DROP DATABASE spyda")
def get_display_latest_call_statistics(database):
connection_config_dict = get_db_info.get_values_from_json(False, database.db_type)
connection = None
cursor = None
try:
connect_string = database.connection
connection = connect_string.connect(**connection_config_dict)
cursor = connection.cursor()
display_query = table.CallStatisticsTable.display_query
cursor.execute(display_query)
rows = cursor.fetchall()
df = pd.DataFrame([[ij for ij in i] for i in rows])
df.rename(columns={0: 'label', 1: 'average', 2: 'response_time', 3: 'error_count', 4: 'error_rate',
5: 'suite_run_history_id'},
inplace=True)
df = df.sort_values(['error_rate'], ascending=[1])
trace1 = go.Bar(
x=df['label'],
y=df['error_rate'],
name='Bar'
)
layout = go.Layout(
title='Call Statistics History',
xaxis=dict(title='Call Label'),
yaxis=dict(title='Error Rate'),
)
data = [trace1]
fig = go.Figure(data=data, layout=layout)
# connection.commit()
plotly.offline.plot(fig, filename='call_statistics_chart.html')
except Error as error:
connection.rollback()
finally:
# closing database connection.
if connection is not None:
cursor.close()
connection.close()
def get_display_suite_run_history(database):
connection_config_dict = get_db_info.get_values_from_json(False, database.db_type)
connection = None
cursor = None
try:
connect_string = database.connection
connection = connect_string.connect(**connection_config_dict)
cursor = connection.cursor()
display_query = table.SuiteHistoryTable.display_query
cursor.execute(display_query)
rows = cursor.fetchall()
df = pd.DataFrame([[ij for ij in i] for i in rows])
df.rename(columns={0: 'key', 1: 'id', 2: 'date', 3: 'avg', 4: 'total', 5: 'error_rate', 6: 'error_count'},
inplace=True)
df = df.sort_values(['date'], ascending=[1])
trace1 = go.Scatter(
x=df['date'],
y=df['avg'],
mode='lines+markers',
name='Scatter'
)
layout = go.Layout(
title='Suite History',
xaxis=dict(title='Date'),
yaxis=dict(title='Average Response Time'),
)
data = [trace1]
fig = go.Figure(data=data, layout=layout)
# connection.commit()
plotly.offline.plot(fig, filename='suite_run_history_chart.html')
except Error as error:
connection.rollback()
finally:
# closing database connection.
if connection is not None:
cursor.close()
connection.close()
def create_tables():
""" create tables in the PostgreSQL database"""
commands = (""" CREATE TABLE suite_run_history (
suite_run_history_key SERIAL PRIMARY KEY,
suite_run_history_id character varying(50) COLLATE pg_catalog."default" NOT NULL,
run_date timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
average_response_time integer,
total_samples integer,
error_rate double precision,
error_count integer,
CONSTRAINT "suite_run_history_id UNIQUE" UNIQUE (suite_run_history_id)
)
""", """
CREATE TABLE call_statistics (
call_statistics_key SERIAL PRIMARY KEY,
date timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
label text COLLATE pg_catalog."default",
samples integer,
average_response_time integer,
ninety_percent integer,
ninety_five_percent integer,
ninety_nine_percent integer,
minimum_response_time integer,
maximum_response_time integer,
average_latency double precision,
geo_mean_response_time smallint,
standard_deviation smallint,
duration_ms integer,
average_bytes double precision,
average_throughput double precision,
median_response_time integer,
error_count integer,
error_rate double precision,
has_passed_thresholds bit(1) DEFAULT NULL::"bit",
suite_run_history_id character varying(50) COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT suite_run_history_id FOREIGN KEY (suite_run_history_id)
REFERENCES public.suite_run_history (suite_run_history_id) MATCH SIMPLE
ON UPDATE RESTRICT
ON DELETE RESTRICT
)
""")
conn = None
try:
# gathers the connection parameters
database = get_db_type.PostgreSQL
connection_config_dict = dbci.get_values_from_json(
False, database.db_type)
# connect to the PostgreSQL server
conn = psycopg2.connect(**connection_config_dict)
conn.autocommit = True
cur = conn.cursor()
# create table one by one
for command in commands:
cur.execute(command)
conn.commit()
# close communication with the PostgreSQL database server
cur.close()
# commit the changes
conn.commit()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
def create_tables():
""" create tables in the MySQL database"""
commands = (""" CREATE TABLE `suite_run_history` (
`suite_run_history_key` INT(11) NOT NULL AUTO_INCREMENT,
`suite_run_history_id` VARCHAR(50) NOT NULL,
`run_date` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`average_response_time` INT(64) NULL DEFAULT NULL,
`total_samples` INT(64) NULL DEFAULT NULL,
`error_rate` FLOAT NULL DEFAULT NULL,
`error_count` INT(64) NULL DEFAULT NULL,
PRIMARY KEY (`suite_run_history_key`),
UNIQUE INDEX `ID` (`suite_run_history_id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1;
""", """
CREATE TABLE `call_statistics` (
`call_statistics_key` INT(11) NOT NULL AUTO_INCREMENT,
`date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`label_id` TEXT NULL,
`label` TEXT NULL,
`samples` INT(11) NULL DEFAULT NULL,
`average_response_time` INT(11) NULL DEFAULT NULL,
`ninety_percent` INT(11) NULL DEFAULT NULL,
`ninety_five_percent` INT(11) NULL DEFAULT NULL,
`ninety_nine_percent` INT(11) NULL DEFAULT NULL,
`minimum_response_time` INT(11) NULL DEFAULT NULL,
`maximum_response_time` INT(11) NULL DEFAULT NULL,
`average_latency` FLOAT NULL DEFAULT NULL,
`geo_mean_response_time` SMALLINT(6) NULL DEFAULT NULL,
`standard_deviation` SMALLINT(6) NULL DEFAULT NULL,
`duration_ms` INT(11) NULL DEFAULT NULL,
`average_bytes` FLOAT NULL DEFAULT NULL,
`average_throughput` FLOAT NULL DEFAULT NULL,
`median_response_time` INT(11) NULL DEFAULT NULL,
`error_count` INT(11) NULL DEFAULT NULL,
`error_rate` FLOAT NULL DEFAULT NULL,
`has_passed_thresholds` BIT(1) NULL DEFAULT NULL,
`suite_run_history_id` VARCHAR(50) NOT NULL,
PRIMARY KEY (`call_statistics_key`),
INDEX `suite_run_history_id` (`suite_run_history_id`),
CONSTRAINT `call_statistics_ibfk_1` FOREIGN KEY (`suite_run_history_id`) REFERENCES `suite_run_history`
(`suite_run_history_id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1;
""")
conn = None
try:
# gathers connection parameters
database = get_db_type.MySQL
connection_config_dict = dbci.get_values_from_json(
False, database.db_type)
# connect to the MySQL server
conn = mysql.connector.connect(**connection_config_dict)
cur = conn.cursor()
# create table one by one
for command in commands:
cur.execute(command)
conn.commit()
# close communication with the database server
cur.close()
# commit any final changes
conn.commit()
except (Exception, mysql.connector.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()