simple todo list

todo-list.py

import sqlite3
import os

from platform import system
from typing import Union

from art import tprint
from prettytable import PrettyTable


class DataBase:
    __FILE: str = os.getenv("HOME") + "/.todo.db"

    def __init__(self) -> None:
        if not os.path.isfile(self.__FILE):
            self.__create_tables()

    def __create_tables(self) -> None:
        self.__connect()
        self.__cursor.execute("""create table job(
            id INTEGER NOT NULL PRIMARY KEY,
            name TEXT NOT NULL
        )""")
        self.__cursor.execute("""create table item(
            id INTEGER NOT NULL PRIMARY KEY,
            jobID INTEGER NOT NULL,
            name TEXT NOT NULL,
            is_done bool default false,
            create_date TEXT default NULL,
            done_date TEXT default NULL,
            FOREIGN KEY (jobID) REFERENCES job(id) ON DELETE CASCADE
        )""")
        self.__disconnect()

    def __connect(self) -> None:
        self.__conn = sqlite3.connect(self.__FILE)
        self.__cursor = self.__conn.cursor()

    def __disconnect(self) -> None:
        self.__conn.commit()
        self.__cursor.close()
        self.__conn.close()

    def add_job(self, name: str) -> None:
        self.__connect()
        self.__cursor.execute("""insert into job (name) values (?)""", (name,))
        self.__disconnect()

    def add_item(self, job_id: int, item_name: str) -> None:
        self.__connect()
        self.__cursor.execute(
            """insert into item (jobID,name,create_date) values (?,?,DATETIME('now', 'localtime'))""", (job_id, item_name,))
        self.__disconnect()

    def make_item_done(self, item_id: int) -> None:
        self.__connect()
        self.__cursor.execute(
            """update item set is_done = true, done_date = DATETIME('now', 'localtime') where id = ?""", (item_id,))
        self.__disconnect()

    def delete_item(self, item_id: int) -> None:
        self.__connect()
        self.__cursor.execute("""delete from item where id = ?""", (item_id,))
        self.__disconnect()

    def delete_job(self, job_id: int) -> bool:
        self.__connect()
        try:
            self.__cursor.execute(
                """delete from job where id = ?""", (job_id,))
            self.__disconnect()
            return True
        except Exception as err:
            print(err)
        finally:
            self.__disconnect()
            return False

    def show__all_jobs(self) -> list:
        self.__connect()
        self.__cursor.execute("""select * from job""")
        job_list = self.__cursor.fetchall()
        self.__disconnect()
        return job_list

    def show__all_items(self) -> list:
        self.__connect()
        self.__cursor.execute(
            """select id,name,is_done,create_date,done_date from item where is_done = 0""")
        item_list = self.__cursor.fetchall()
        self.__disconnect()
        return item_list

    def show_items(self, job_id: int) -> list:
        self.__connect()
        self.__cursor.execute(
            """select id,name,is_done,create_date,done_date from item where jobID = ? and is_done = 0""", (job_id,))
        items = self.__cursor.fetchall()
        self.__disconnect()
        return items

    def show_item(self, item_id: int) -> list:
        self.__connect()
        self.__cursor.execute(
            """select id,name,is_done,create_date,done_date from item where id = ?""", (item_id,))
        item = self.__cursor.fetchone()
        self.__disconnect()
        return item

    def show_done_item(self) -> list:
        self.__connect()
        self.__cursor.execute(
            """select id,name,is_done,create_date,done_date from item where is_done = 1""")
        item_list = self.__cursor.fetchall()
        self.__disconnect()
        return item_list

    def clear_db(self) -> None:
        self.__connect()
        self.__cursor.execute("""delete from item""")
        self.__cursor.execute("""delete from job""")
        self.__disconnect()


def clear() -> None:
    if system() in ["Linux", "Darwin"]:
        os.system("clear")
    else:
        os.system("cls")


def list_to_table(items: list, mode: chr = 'J') -> PrettyTable:
    list_table = PrettyTable()
    if mode == 'J':
        list_table.field_names = ["id", "job name"]
        for item in items:
            list_table.add_row([item[0], item[1]])
    else:
        list_table.field_names = ["id", "item name",
                                  "is done", "create(TIME)", "done(TIME)"]
        for item in items:
            list_table.add_row([item[0], item[1], item[2], item[3], item[4]])
    return list_table


def menu(db: DataBase) -> int:
    tprint("Todo List", font="random")
    menu_table = PrettyTable()
    menu_table.field_names = ["id", "action"]
    menu_table.add_rows([
        [1, "add job"],
        [2, "add item"],
        [3, "done item"],
        [4, "show jobs"],
        [5, "show items"],
        [6, "show jobs item"],
        [7, "show done items"],
        [8, "!!! CLEAR DATABASE !!!"],
        [0, "exit"]
    ])

    message: str = ''
    output: Union[str, PrettyTable] = ''

    while True:
        if message:
            print("!" * 5, end=' ')
            print(message, end=' ')
            print("!" * 5, end='\n')
        print(menu_table)
        if output:
            print("*" * 40)
            print(output)
            print("*" * 40)
        option: int = int(input("$ "))
        if option == 0:
            return
        elif option == 1:
            message = ''
            output = ''
            name: str = input("job name > ")
            db.add_job(name)
            clear()
        elif option == 2:
            message = ''
            output = ''
            print(list_to_table(db.show__all_jobs()))
            id: int = int(input("job id > "))
            name: str = input("item name > ")
            db.add_item(id, name)
            clear()
        elif option == 3:
            message = ''
            output = ''
            id: int = int(input("item id > "))
            db.make_item_done(id)
            clear()
        elif option == 4:
            output = list_to_table(db.show__all_jobs())
            message = ''
            clear()
        elif option == 5:
            output = list_to_table(db.show__all_items(), mode='I')
            message = ''
            clear()
        elif option == 6:
            print(list_to_table(db.show__all_jobs()))
            id: int = int(input("job id > "))
            output = list_to_table(db.show_items(id), mode='I')
            message = ''
            clear()
        elif option == 7:
            output = list_to_table(db.show_done_item(), mode='I')
            message = ''
            clear()
        elif option == 8:
            db.clear_db()
            clear()
            message = "Database Cleared"
            output = ''


if __name__ == '__main__':
    db = DataBase()
    menu(db)