Managing Tasks with Data column in Tkinter Application

Checkbuttons Part I & II Task list Task with Database Part III

Task List with Calendar to select date

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 task list with Calendar for user selection of date and storing in Database tables

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 Four columns, id , tasks, status,dt.
Here we have added one extra column dt to store Date.
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` int(11) NOT NULL AUTO_INCREMENT,
    `tasks` text,
    `status` tinyint(1) DEFAULT NULL,
    `dt` date NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;''')  
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`,dt) VALUES
  (1, 'My tasks 1 ', True,'2023-05-29'),
  (2, 'My tasks 2', False,'2023-05-30'),
  (3, 'My tasks 3', True,'2023-05-31');''')
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 main script. We will call the connection object from main script.

from tk_checkbutton4_connect import my_conn # database connection

Inside the file tk_checkbutton4_connect.py we will keep this code.

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 four columns, id, tasks , status and dt. We will create one dictionary by using id as Key with tasks, status , dt list as values.

Collecting date and displaying with task

We are collecting date column ( dt ) value and then displaying the along with the checkbutton.

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

We are using the date column value and converting to string by using strftime(). Here we are using the list of Date formats to show date.

#dt=datetime.strptime(my_dict[k][2],'%Y-%m-%d').strftime('%d-%b-%Y') # sqlite
dt=datetime.strftime(my_dict[k][2],'%d-%b-%Y') # MySQL
ld=tk.Label(task_frame,text=dt)
ld.grid(row=i,column=1,padx=2)

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 = {row[0]: [row[1],row[2],row[3]] for row in r_set}                

    i=2 # 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') 
        #dt=datetime.strptime(my_dict[k][2],'%Y-%m-%d').strftime('%d-%b-%Y') # sqlite
        dt=datetime.strftime(my_dict[k][2],'%d-%b-%Y') # MySQL
        ld=tk.Label(task_frame,text=dt)
        ld.grid(row=i,column=1,padx=2)
        my_ref[k]=[ck,var] # to hold the references 
        i=i+1 # increase the row number

Adding tasks

On click 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.
As we are using Calendar library , we have imported the same at the top of the page.

from tkcalendar import DateEntry

To display the calendar with other components

cal=DateEntry(my_w,selectmode='day',font=18)
cal.grid(row=1,column=1)

To display and collect user selected calendar date use this

dt=cal.get_date()

Calendar for Date selection
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():
    dt=cal.get_date()
    #dt = date.today() # todays date 
    my_data=(e1.get(),False,dt) # data to pass using query
    ### for SQLite use the below line and remove MySQL line 
    #r_set=my_conn.execute("INSERT INTO my_tasks (tasks, status,dt) \
    #     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,dt) \
         VALUES(%s,%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.
tk_checkbutton4_connect.py

tk_checkbutton4_main.py

from tk_checkbutton4_connect import my_conn
import tkinter as tk
from tkinter import *
from datetime import datetime,date
my_w = tk.Tk()
my_w.geometry("500x400") 
from tkcalendar import DateEntry
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 with Date',font=('Times',22,('bold','underline')),fg='green')
l1.grid(row=0,column=0,padx=30,pady=10,columnspan=5,sticky='ew')    
e1=tk.Entry(my_w,width=12,bg='yellow',font=18)
e1.grid(row=1,column=0,padx=10)
cal=DateEntry(my_w,selectmode='day',font=18)
cal.grid(row=1,column=1)
b1=tk.Button(my_w,text='+',font=18,command=lambda:add_task())
b1.grid(row=1,column=2)
b2=tk.Button(my_w,text='-',font=18,bg='red',command=lambda:delete_task())
b2.grid(row=1,column=3,padx=3)
l2=tk.Label(my_w,text='Message here',bg='lightgreen',width=15)    
l2.grid(row=1,column=4,padx=2)

task_frame=Frame(my_w,width=390, height=390,background='lightyellow',
highlightbackground='lightblue',highlightthicknes=3)
task_frame.grid(row=2,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 '  # query to collect data from table
    my_cursor=my_conn.execute(q)
    r_set=my_cursor.fetchall()
    my_dict = {row[0]: [row[1],row[2],row[3]] for row in r_set}

i=2 # 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')
        #dt=datetime.strptime(my_dict[k][2],'%Y-%m-%d').strftime('%d-%b-%Y') # sqlite
        dt=datetime.strftime(my_dict[k][2],'%d-%b-%Y')
        ld=tk.Label(task_frame,text=dt) # Label to display date
        ld.grid(row=i,column=1,padx=2)
        my_ref[k]=[ck,var] # to hold the references 
        i=i+1 # increase the row number 
def add_task():
    dt=cal.get_date() # Selected date from the calendar 
    #dt=date.today()  # Use today's date if required 
    my_data=(e1.get(),False,dt) # data to pass using query
    #r_set=my_conn.execute("INSERT INTO my_tasks (tasks, status,dt) \
    #     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,dt) \
         VALUES(%s,%s,%s)",my_data)

msg="Task ID:"+str(r_set.lastrowid) # id of the row added 
    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,
  `dt` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

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

INSERT INTO `my_tasks` (`id`, `tasks`, `status`, `dt`) VALUES
(1, 'My tasks 1 ', True,'2023-05-29'),
(2, 'My tasks 2', False,'2023-05-30'),
(3, 'My tasks 3', True,'2023-05-31');

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 ?.

Checkbutton Part 1 & II of Task List Part III of Task List

Python Tkinter Dictionary Python- Tutorials

Subscribe to our YouTube Channel here

Todo List with Date column in Database table