Managing Tasks by storing data in Database table in Tkinter Application

Checkbuttons Part I & II Task list Part IV Task list with Date

Task List using Checkbuttons in Tkinter

This script uses SQLite database to store tasks ( todo list ) with status ( True or False ). We can use MySQL database also to run this script by changing the connection part.

Tkinter todo List using SQLite or MySQL database to add remove & update tasks

Connection to Database and Creating table

Use the code below to connect to SQLite database ( or create the database ) and then create the table my_tasks.
The table my_tasks has three columns, id , tasks and status.
Some sample data is added while creating the table.
Update the path of the sqlite database file in this code.
To use MySQL database the connection string needs to be changed and the create table Query is also different due to the auto-increment column.

from sqlalchemy import create_engine 
from sqlalchemy.exc import SQLAlchemyError
my_path="D:\\testing\\sqlite\\my_db.db" #Change the path 
my_conn = create_engine("sqlite:///"+ my_path)
### for MySQL database , use the below line and remove the above line
#my_conn =create_engine("mysql+mysqldb://root:pw@localhost/my_tutorial")
try:
    #my_conn.execute('DROP TABLE my_tasks')
    my_conn.execute('''
        CREATE TABLE IF NOT EXISTS my_tasks(id integer primary key, 
                      tasks text, 
                      status Boolean 
                      );''')  
except SQLAlchemyError as e:
  #print(e)
  error = str(e.__dict__['orig'])
  print(error)
else:
  print("tasks  Table created successfully..")
try:
  r_set=my_conn.execute('''INSERT INTO `my_tasks` 
  (`id`, `tasks`, `status`) VALUES
  (1, 'My tasks 1 ', True),
  (2, 'My tasks 2', False),
  (3, 'My tasks 3', True);''')
except SQLAlchemyError as e:
  #print(e)
  error = str(e.__dict__['orig'])
  print(error)
else:
  print("No of Records added : ",r_set.rowcount)

from sqlalchemy import create_engine 
from sqlalchemy.exc import SQLAlchemyError
#my_path="D:\\testing\\sqlite\\my_db.db" #Change the path 
#my_conn = create_engine("sqlite:///"+ my_path)
my_conn =create_engine("mysql+mysqldb://root:pw@localhost/my_tutorial")
try:
    #my_conn.execute('DROP TABLE my_tasks')
    my_conn.execute('''
        CREATE TABLE IF NOT EXISTS `my_tasks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tasks` text,
  `status` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4''')  
except SQLAlchemyError as e:
  #print(e)
  error = str(e.__dict__['orig'])
  print(error)
else:
  print("tasks  Table created successfully..")
try:
  r_set=my_conn.execute('''INSERT INTO `my_tasks` 
  (`id`, `tasks`, `status`) VALUES
  (1, 'My tasks 1 ', True),
  (2, 'My tasks 2', False),
  (3, 'My tasks 3', True);''')
except SQLAlchemyError as e:
  #print(e)
  error = str(e.__dict__['orig'])
  print(error)
else:
  print("No of Records added : ",r_set.rowcount)

OR Use the SQL dump for MySQL given at end of this page.

Connection to Database in main script.

First we will connect to our SQLite or MySQL database. The variable my_conn we will use in our script.

from sqlalchemy import create_engine
from sqlalchemy.exc import SQLAlchemyError
try: # connection to database 
    my_path="D:\\testing\\sqlite\\my_db.db" #Change the path 
    my_conn = create_engine("sqlite:///" + my_path)
    ### for MySQL database , use the below line and remove the above line
    #my_conn =create_engine("mysql+mysqldb://root:pw@localhost/my_tutorial")
except SQLAlchemyError as e:
        error = str(e.__dict__['orig'])
        print(error)

Updating table with task status

The changes in font style and updating the font option is already discussed in Part 1 .
Here while updating the font option of the checkbuttons we will also update the database table my_tasks by making status column True or False. Based on the Checkbutton status the Query is prepared and status column is updated.

def my_upd(k):
    if(my_ref[k][1].get()==True):
        my_ref[k][0].config(font=f_done,fg='green')
        q='UPDATE my_tasks SET status=True WHERE id='+str(k)
    else:
        my_ref[k][0].config(font=f_normal,fg='blue')
        q='UPDATE my_tasks SET status=False WHERE id='+str(k)
    r_set=my_conn.execute(q)    
    msg="No. Updated:"+str(r_set.rowcount) # Number of rows updated
    my_msg(msg) # show message for 3 seconds

Displaying the tasks with status

We will keep all the steps required to display the tasks inside a function. This function we will call after every addition or deletion of tasks from the database table. Here we have kept the code inside the function my_show().
Inside the function first we will we will clear all the tasks ( previously displayed) by using grid_slaves(). Note that we have used one frame ( task_frame ) as container to hold all the rows of tasks.

for w in task_frame.grid_slaves(): #Loop through each row 
        w.grid_forget() # remove the row

We will read the tasks and the status of the task from the my_tasks table to display. In our table my_tasks, we have three columns, id, tasks and status. We will create one dictionary by using id as Key with tasks, status list as values.

q='SELECT *  FROM my_tasks ' 
    my_cursor=my_conn.execute(q)
    r_set=my_cursor.fetchall()
    my_dict = {i[0]: [i[1],i[2]] for i in r_set}# create dictionary

Based on the value of status column ( True or False ) we will apply the option font to each checkbutton.
While creating the Chckbuttons we used the frame task_frame as parent window ( instead of using my_w )
Here is the full code for the function my_disp()

my_ref={} # to store references to checkboxes 
def my_show():
    for w in task_frame.grid_slaves(): #Loop through each row 
        w.grid_forget() # remove the row 
    q='SELECT *  FROM my_tasks ' 
    my_cursor=my_conn.execute(q)
    r_set=my_cursor.fetchall()
    my_dict = {i[0]: [i[1],i[2]] for i in r_set}                

    i=1 # row number 
    for k in my_dict.keys(): # Number of checkbuttons 
        var=tk.BooleanVar() # variable 
        var.set(my_dict[k][1]) # set to value of status column
        if my_dict[k][1]==True: # set font based on status column
            font,fg=f_done,'green' # if True 
        else:
            font,fg=f_normal,'blue'
        ck = tk.Checkbutton(task_frame, text=my_dict[k][0], 
        variable=var,onvalue=True,offvalue=False,font=font,fg=fg,
        command=lambda k=k: my_upd(k))
        ck.grid(row=i,column=0,padx=20,pady=1,sticky='w') 
        my_ref[k]=[ck,var] # to hold the references 
        i=i+1 # increase the row number

Adding tasks

On clcik of the button b1 the function add_task() will be called.

b1=tk.Button(my_w,text='+',font=18,command=lambda:add_task())
b1.grid(row=0,column=2)

Inside the function add_task() we will fist read the data entered by user in the Entry box e1 by using get() method.
We will remove the user entered data in Entry box by using delete() method. Using the id value of just added task we will prepare a message string msg. This message will be passed to the function my_msg(msg) to display the id for 3 seconds.

def add_task():
    my_data=(e1.get(),False) # data to pass using query
    #r_set=my_conn.execute("INSERT INTO my_tasks (tasks, status) \
    #     VALUES(?,?)",my_data)
    ### for MySQL use the below line and remove the above line 
    r_set=my_conn.execute("INSERT INTO my_tasks (tasks, status) \
         VALUES(%s,%s)",my_data)

    msg="Task ID:"+str(r_set.lastrowid)
    e1.delete(0,'end') # remove the task from entry box
    my_msg(msg) # show message for 3 seconds 
    my_show() # refresh the view

Deleting all completed tasks

Once the chckbutton is checked then the status of the Task is updated to True( check the code above ). We can remove all the records or tasks for which the status is already changed True. After deleting we will display number of completed tasks deleted by using rowcount.

def delete_task(): # remove all completed tasks
    r_set=my_conn.execute("DELETE FROM my_tasks WHERE status=True")
    msg="No Deleted:"+str(r_set.rowcount) # Number of rows deleted
    my_msg(msg) # show message for 3 seconds 
    my_show()   # refresh the view

Showing messages

To communicate with user we will be displaying different message for some time period. Here we have kept the display duration as 3000 milliseconds ( 3 seconds ). This function my_msg() is called by different operations and pass the message to display as parameter. After 3000 milliseconds ( 3 seconds ) the message is removed by updating the text option of label l2 to blank string by using config() method.

def my_msg(msg):
    l2.config(text=msg) # show message 
    my_w.after(3000,lambda:l2.config(text='')) # remove after 3 seconds

Full code is here ( Change the path to your database or connection string.

from sqlalchemy import create_engine
from sqlalchemy.exc import SQLAlchemyError
try: # connection to database 
    #my_path="D:\\testing\\sqlite\\my_db.db" #Change the path 
    #my_conn = create_engine("sqlite:///" + my_path)
    ### for MySQL database , use the below line and remove the above line
    my_conn =create_engine("mysql+mysqldb://root:pw@localhost/my_db")
except SQLAlchemyError as e:
        error = str(e.__dict__['orig'])
        print(error)

import tkinter as tk
from tkinter import *
my_w = tk.Tk()
my_w.geometry("500x400")

f_done=('Times',16,'overstrike') # font style for tasks completed
f_normal=('Times',16,'normal')  # for pending 
def my_upd(k):
    if(my_ref[k][1].get()==True):
        my_ref[k][0].config(font=f_done,fg='green')
        q='UPDATE my_tasks SET status=True WHERE id='+str(k)
    else:
        my_ref[k][0].config(font=f_normal,fg='blue')
        q='UPDATE my_tasks SET status=False WHERE id='+str(k)
    r_set=my_conn.execute(q)    
    msg="No. Updated:"+str(r_set.rowcount) # Number of rows updated
    my_msg(msg) # show message for 3 seconds 
    
l1=tk.Label(my_w,text='Task List',font=('Times',22,('bold','underline')),fg='green')
l1.grid(row=0,column=0,padx=1,pady=10)    
e1=tk.Entry(my_w,width=12,bg='yellow',font=18)
e1.grid(row=0,column=1)
b1=tk.Button(my_w,text='+',font=18,command=lambda:add_task())
b1.grid(row=0,column=2)
b2=tk.Button(my_w,text='-',font=18,bg='red',command=lambda:delete_task())
b2.grid(row=0,column=3,padx=3)
l2=tk.Label(my_w,text='Message here',bg='lightgreen')    
l2.grid(row=0,column=4,padx=2)

task_frame=Frame(my_w,width=390, height=390,background='lightyellow',
highlightbackground='lightblue',highlightthicknes=3)
task_frame.grid(row=1,column=0,columnspan=5,
    padx=15,pady=5,ipadx=5,ipady=5,sticky='w')
my_ref={} # to store references to checkboxes 
def my_show():
    for w in task_frame.grid_slaves(): #Loop through each row 
        w.grid_forget() # remove the row 
    q='SELECT *  FROM my_tasks ' 
    my_cursor=my_conn.execute(q)
    r_set=my_cursor.fetchall()
    my_dict = {i[0]: [i[1],i[2]] for i in r_set}

i=1 # row number 
    for k in my_dict.keys(): # Number of checkbuttons 
        var=tk.BooleanVar() # variable 
        var.set(my_dict[k][1]) # set to value of status column
        if my_dict[k][1]==True: # set font based on status column
            font,fg=f_done,'green' # if True 
        else:
            font,fg=f_normal,'blue'
        ck = tk.Checkbutton(task_frame, text=my_dict[k][0], 
        variable=var,onvalue=True,offvalue=False,font=font,fg=fg,
        command=lambda k=k: my_upd(k))
        ck.grid(row=i,column=0,padx=20,pady=1,sticky='w') 
        my_ref[k]=[ck,var] # to hold the references 
        i=i+1 # increase the row number 
def add_task():
    my_data=(e1.get(),False) # data to pass using query
    #r_set=my_conn.execute("INSERT INTO my_tasks (tasks, status) \
    #     VALUES(?,?)",my_data)
    ### for MySQL use the below line and remove the above line 
    r_set=my_conn.execute("INSERT INTO my_tasks (tasks, status) \
         VALUES(%s,%s)",my_data)

msg="Task ID:"+str(r_set.lastrowid)
    e1.delete(0,'end') # remove the task from entry box
    my_msg(msg) # show message for 3 seconds 
    my_show() # refresh the view 
def delete_task(): # remove all completed tasks
    r_set=my_conn.execute("DELETE FROM my_tasks WHERE status=True")
    msg="No Deleted:"+str(r_set.rowcount) # Number of rows deleted
    my_msg(msg) # show message for 3 seconds 
    my_show()   # refresh the view

def my_msg(msg):
    l2.config(text=msg) # show message 
    my_w.after(3000,lambda:l2.config(text='')) # remove after 3 seconds
    
my_show()
my_w.mainloop() # keep the window open

SQL Dump for my_tasks table ( for MySQL )

CREATE TABLE IF NOT EXISTS `my_tasks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tasks` text,
  `status` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `my_tasks`
--

INSERT INTO `my_tasks` (`id`, `tasks`, `status`) VALUES
(1, 'My tasks 1 ', 1),
(2, 'My tasks 2', 0),
(3, 'My tasks 3', 1);

Difference between SQLite and MySQL

While creating the table take care of Auto-increment task id column.
To Pass the parameters the placeholders are different. For MySQL it is %s and for SQLite it is ?.

Task List with Date Part IV

We will integrate calendar in our application for selection of date by user while adding any task.
Adding Task with Date from Calendar ( Part IV ) Checkbutton Part 1 of Task List

Python Tkinter Dictionary Python- Tutorials

Subscribe to our YouTube Channel here

Task List using Database table