Todolist_SQLite

create_db.py

'''
create_db.py - creates the table.
Note: This program should be run first to create the database and tables
'''
import sqlite3


def create_table():
    sql_query = """
    CREATE TABLE tasks(
    id INTEGER PRIMARY KEY,
    taskname TEXT,
    complete BOOLEAN,
    priority TEXT);
    """
    with sqlite3.connect("todo.db") as conn:
        cur = conn.cursor()
        cur.execute(sql_query)
        conn.commit()


if __name__ == "__main__":
    create_table()
    

db_ops.py

import sqlite3


def insert_into_tasks_table(task):
    sql_insert_query = """INSERT INTO tasks(taskname, complete)  VALUES ('%s', %s) """ % (task, 0)
    execute_query(sql_insert_query)


def execute_query(sql_query):
    with sqlite3.connect("todo.db") as conn:
        cur = conn.cursor()
        result = cur.execute(sql_query)
        conn.commit()
    return result


def select_all_records():
    sql_select_query = """SELECT taskname FROM tasks WHERE complete = 0"""
    result = execute_query(sql_select_query).fetchall()
    return [x[0] for x in result]


def mark_task_as_complete(task):
    sql_update_query = """UPDATE tasks SET complete = 1 WHERE taskname='%s' AND complete = 0""" % (task)
    execute_query(sql_update_query)


def update_task(old_task, new_task):
    pass


if __name__ == "__main__":
    select_all_records()

todo.py

import PySimpleGUI as sg
from db_ops import insert_into_tasks_table, select_all_records, mark_task_as_complete, update_task

tasks = select_all_records()

layout = [
    [sg.Text('ToDolist App',font=("Arial", 14))],
    [sg.InputText('', size=(40,1), font=("Arial", 14), key='todo_item', enable_events=True),
     sg.Button(button_text='Add', bind_return_key=True, font=("Arial", 14), key='add_save')],
    [sg.Listbox(values=tasks, size=(40, 10), font=("Arial", 14), key='items', enable_events=True),
     sg.Button('Delete', font=("Arial", 14), key='delete'),
     sg.Button('Edit', key='edit', font=("Arial", 14))],
    [sg.Button('View Incomplete Tasks', font=("Arial", 14), key='delete'),
     sg.Button('View Complete Tasks', font=("Arial", 14), key='delete')]
]

def add_tasks(values):
    if window.FindElement('add_save').GetText() == 'Add':
        insert_into_tasks_table(values['todo_item'])
    else:
        old_task = values['items'][0]
        new_task = values['todo_item']
        update_task(old_task, new_task)
    update_UI()


def delete_tasks(values):
    mark_task_as_complete(values['items'][0])
    update_UI()


def edit_tasks(values):
    window.FindElement('todo_item').Update(value=values['items'][0])
    window.FindElement('add_save').Update('Save')

def update_UI():
    """
    uodate the input elements after every event
    """
    tasks = select_all_records()
    window.FindElement('items').Update(values=tasks)
    window.FindElement('todo_item').Update(value="")


if __name__ == '__main__':
    window = sg.Window('ToDoList App', layout)
    while True:
        event, values = window.Read()
        if event == sg.WINDOW_CLOSED:
            break
        if event == "add_save":
            add_tasks(values)
        elif event == "delete":
            delete_tasks(values)
        elif event == "edit":
            edit_tasks(values)

    window.Close()