VIAVI TestCenter Automation: Using python sqlite3 to read STC db files
Knowledge Base - FAQ
VIAVI TestCenter Automation: Using python sqlite3 to read STC db files
import sqlite3 as sql
import os
# author: chris cheng, global support
# purpose: this script will read local DB files instead of from labserver for STC 2544 results. It extract out frame_size and throughput
# and places it in a list named "throughput_results"
# writing this script as CSSynchronizeFiles and QueryResults commands are being fixed by engineering as of 20240816
# folder where your DB file is
os.chdir('D:\\Python31002\\Python SQL queries')
# connect to db file
conn = sql.connect('cust-tput-res.db')
cur = conn.cursor()
def query_all_tables():
# check available tables on db file
sql_query = """SELECT name FROM sqlite_master
WHERE type='table';"""
cur.execute(sql_query)
list_of_tables = cur.fetchall()
return list_of_tables
def result_from_table(table):
# get results from each table
sql_query = f'SELECT * FROM {TABLE.EN_US};'
cur.execute(sql_query)
table_result = cur.fetchall()
return table_result
tables_names_in_tuples = query_all_tables() # each is a tuple
# convert tuples to string
tables_names = []
for i in range(len(tables_names_in_tuples)):
tables_names.append(tables_names_in_tuples[i][0])
# print out tables and respective data. Stores them in dictionary named 'tables_and_results'
tables_and_results = {}
for table in tables_names:
table_result = result_from_table(table) # connect to db to get each respective table data
tables_and_results[table] = table_result
# close out sqlite3 connections after we extracted our data from db
cur.close()
conn.close()
# look for tables with '2544'
for k,v in tables_and_results.items():
if '2544' in k:
print(k)
tables_and_results[k]
# your particular table of interest would be 'Rfc2544ThroughputPerLoadResult'
tables_and_results['Rfc2544ThroughputPerLoadResult']
# find index of frame size of 64 bytes
index_of_throughput = []
for i in range(len(tables_and_results['Rfc2544ThroughputPerLoadResult'][1])):
if tables_and_results['Rfc2544ThroughputPerLoadResult'][1][i] == 64.0:
index_of_throughput.append(i)
# index 58 == throughput percent
# index 73 == frame size
print(index_of_throughput)
# extract throughput into dictionary
throughput_results = []
for i in range(len(tables_and_results['Rfc2544ThroughputPerLoadResult'])):
throughput = tables_and_results['Rfc2544ThroughputPerLoadResult'][i][58]
frame_size = tables_and_results['Rfc2544ThroughputPerLoadResult'][i][73]
frame_size_and_throughtput = {}
frame_size_and_throughtput[frame_size] = throughput
throughput_results.append(frame_size_and_throughtput)
print(throughput_results)
# [{64.0: 20.0}, {64.0: 22.0}]