sql_database.py

import sqlite3
import os.path

from sqlite3 import Cursor 
from sqlite3 import Connection

def remove_suffix(name_of_table:str,suffix:str):
    if name_of_table.find(suffix):
        return name_of_table.replace(suffix,"")
    return name_of_table 


def create_table(name_of_table:str):
    """
    Run once!!! to intialise or create a table
    """
    
    #Create sql file and connect to it:
    name_of_table = remove_suffix(name_of_table, ".db")

    connection = sqlite3.connect(f"{name_of_table}.db")
    print("     1. Opened database file successfully")

    # create the table with columns
    connection.execute(
                    f"""CREATE TABLE {name_of_table}
                    (
                        DATE        DATE    NOT NULL,
                        CATEGORY    TEXT    NOT NULL,
                        HOURS       INT     NOT NULL
                    )
                    """
                )
    print("     2. Table created successfully")

    return connection


def check_database_exists(name_of_table:str):
    name_of_table = remove_suffix(name_of_table, ".db")

    if os.path.exists(f"{name_of_table}.db"):
        print(f"\nSQL Table: '{name_of_table}.db' Loaded.")
        return True

    print(f"\nCreating new SQL Table '{name_of_table}.db'")
    return False


def load_table(name_of_table) -> list[Connection, Cursor]:
    name_of_table = remove_suffix(name_of_table, ".db")

    if check_database_exists(name_of_table):
        conn = sqlite3.connect(f"{name_of_table}.db")
        curs = conn.cursor()
    else:
        conn = create_table(name_of_table)
        curs = conn.cursor()
    
    return [conn, curs]


def add_insert_data(name_of_table:str, connection:Connection, cursor: Cursor, data:tuple):
    name_of_table = remove_suffix(name_of_table, ".db")

    cursor.execute(f"INSERT INTO {name_of_table} VALUES(?,?,?)", data)
    connection.commit()