Tools/tool_lib/custom_data_tables.py

import sqlite3


def create_custom_datatable(database_path, columns: list or dict, table_name=''):
    if table_name == '':
        return
    # Create a String
    query_str = ''#
    if isinstance(columns, list):
        for name_count in range(0, len(columns)):
            query_str += columns[name_count] + ' VARCHAR, '
    else:
        for key,v in columns.items():
            query_str += key + ' VARCHAR, '


    query_str= 'CREATE TABLE IF NOT EXISTS ' + table_name + '(' \
               + query_str[:-2] + ')'

    # Connect to database
    connection = sqlite3.connect(database_path)
    cursor = connection.cursor()
    cursor.execute(query_str)
    connection.commit()
    connection.close()

def add_row(database_path, columns:dict, table_name =''):
    if table_name == '':
        return
    # Create a String
    column_names_str = ''
    values_list = []

    for key,value in columns.items():
        column_names_str += key + ', '
        values_list += [str(value)]

    add_questionmarks = '?,' * len(values_list)
    query_str = 'INSERT OR IGNORE INTO ' + table_name + '(' + column_names_str[:-2]+  ') VALUES(' + add_questionmarks[:-1] + ')'

    # Connect to database
    connection = sqlite3.connect(database_path)
    cursor = connection.cursor()
    cursor.execute(query_str, values_list)
    connection.commit()
    connection.close()

def delete_rows_based_on_high_value(database_path,value, column:str, table_name =''):
    if table_name == '':
        return

    # Create a String
    query_str = 'DELETE FROM ' + table_name + ' WHERE ' + 'CAST('+ column+ ' as decimal)' + ' > ' + str(value)

    # Connect to database
    connection = sqlite3.connect(database_path)
    cursor = connection.cursor()
    cursor.execute(query_str)
    connection.commit()
    connection.close()