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.
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)
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 , dtlist 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.
Inside the function add_task() we will fist read the data entered by user in the Entry boxe1 by using get() method.
As we are using Calendar library , we have imported the same at the top of the page.
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
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 ?.