How To Create ToDo-List In Python With MySQL Connectivity

Hello Pythonistas welcome back. Today we will continue with the third project in our series CodeCraft: Building Skills One Project at a Time.

So let’s get started, the third project in this series is a Todo List.

Yes, we will together Create ToDo-List In Python With MySQL Connectivity.

Create ToDo-List In Python With MySQL Connectivity. Create ToDo-List In Python With MySQL Connectivity.

Pre-requisites

Create ToDo-List In Python With MySQL Connectivity. Create ToDo-List In Python With MySQL Connectivity. Create ToDo-List In Python With MySQL Connectivity. Create ToDo-List In Python With MySQL Connectivity.

You need to go through the previous project once. Just check out the source code here. If you understand this one you are good to go.

Why A ToDo-List?

Well, because it is very good to understand the concept of working with database.

And, secondly, I provided a small list of challenges for you in the number guessing game, this one has database dealing.

Although, that challenge would work better with file handling.

Also, it has similar workings as the previous one.

Thus, it is a good build-up for the previous project.

Approach To Create ToDo-List In Python With MySQL Connectivity

Task: Create a simple to-do list where users can add, view, and delete tasks.

Create a Database and a Table
Establish Database Connection and Create a cursor
Add a Task
View Tasks
Delete a Task
Create a Menu for user to interact with
- Get task ids

Step 1: Create a Database and a Table

Either install MySQL on your system or use XAMPP instead, the choice is yours.

Create ToDo-List In Python With MySQL Connectivity Database creation.

Step 2: Establish Database Connection

First, install this module to work with the database.

pip install mysql-connector

Now, import this module and establish a database connection with MySQL.

After that, create a cursor.

A cursor is the variable that will enable you to execute queries from Python to MySQL.

# Step 2: Establish Database Connection
connection = connector.connect(host="localhost", user="root", password="maitry", database="todo_list")
# Step 3: Create a cursor
mycursor = connection.cursor()

Step 3: Add a Task

Let’s write the code to add a task in this table:

# Step 4: Add a Task
def fetch_last_task_id() -> int:
    """Returns the id of last task in the list. If there are no tasks in the list then returns 1"""
    mycursor.execute("SELECT task_id FROM todo ORDER BY task_id DESC LIMIT 1;")
    task_id = mycursor.fetchone()
    # task_id would be in this format (2,)
    if task_id == None:
        return None
    return task_id[0]

def add_task(task_name: str):
    """Adds the given task to the list of tasks."""
    task_id = fetch_last_task_id()
    if task_id != None:
        task_id += 1
    if task_id == None:
        task_id = 1
    mycursor.execute(f" insert into todo values({task_id}, \"{task_name}\");")
    connection.commit()
    print(f"Task '{task_name}' added successfully with ID {task_id}.\n")

Click For Explanation

First, to add a task to the table we need to fetch the task_id of the last task in the table.

For this, we created a function that executes a query to fetch that value and then returns it.

execute() method is used to execute a query in the database.

fetchone() method fetches the last result from the execution of the previous query.

task_id would be in the form (last_value, ) that is in the form of a tuple. That is why the function returns task_id[0]

Then in the add_task() method, I have just executed the insertion query and then committed the result to the database.

Step 4: View Tasks

This method would return a list of tuples. Like this one: [(1, “Create the Project”), (2, “Task 2”)]

# Step 5: View Tasks
def view_tasks() -> list[tuple]:
    """Returns the list of tasks."""
    mycursor.execute("Select * from todo")
    tasks = mycursor.fetchall()
    return tasks

Step 5: Delete a Task

# Step 6: Delete a Task
def delete_task(task_id: int):
    """Deletes a Task from the list of tasks."""
    mycursor.execute(f"delete from todo where task_id = {task_id};")
    connection.commit()
    print(f"Task with ID {task_id} deleted successfully.\n")

Step 6: Create a Menu For Users To Interact With

Code

# Step 7: Create a Menu for user to interact with
def main():
    while True:
        print("1. Add a Task")
        print("2. View All Tasks")
        print("3. Delete a Task")
        print("4. Quit")
        choice = input("Enter Your Choice: ")
        try:
            choice = int(choice)
        except ValueError:
            print("Please enter a valid choice!")
            continue
        if choice == 1:
            task = input("Enter a Task: ")
            add_task(task)
            
        elif choice == 2:
            tasks = view_tasks()
            if tasks == []:
                print("\nNo Tasks\n")
                continue
            print()
            print("Task Id, Task Name")
            for task in tasks:
                print(f"{task[0]}, {task[1]}")
            print()
        elif choice == 3:
            tasks = view_tasks()
            if tasks == []:
                print("\nNo Tasks To Delete\n")
                continue
            print()
            print("Task Id, Task Name")
            for task in tasks:
                print(f"{task[0]}, {task[1]}")
            print()
            task_id = input("Enter the task's id: ")
            try:
                task_id = int(task_id)
            except ValueError:
                print("Enter a valid task id!")
                continue
            # Step 8: get task ids
            ids = get_task_ids()
            if task_id in ids:
                delete_task(task_id)
            else:
                print("Enter a valid task id!")
        elif choice == 4:
            print("Thanks For Using!!")
            break
        else:
            print("Please enter a valid choice!")

if __name__ == "__main__":
    main()

Get Task Ids

# Step 8: get task ids
def get_task_ids() -> list[int]:
    """Returns a list of task ids"""
    mycursor.execute("Select task_id from todo")
    task_ids = mycursor.fetchall()
    final_task_ids = []
    for task_id in task_ids:
        final_task_ids.append(task_id[0])
    return final_task_ids

Full Source Code

Click For Code

# Step 1: Create Database and Table
# [image]
import mysql.connector as connector
# Step 2: Establish Database Connection
connection = connector.connect(host="localhost", user="root", password="maitry", database="todo_list")
# Step 3: Create a cursor
mycursor = connection.cursor()

# Step 4: Add a Task
def fetch_last_task_id() -> int:
    """Returns the id of last task in the list. If there are no tasks in the list then returns 1"""
    mycursor.execute("SELECT task_id FROM todo ORDER BY task_id DESC LIMIT 1;")
    task_id = mycursor.fetchone()
    if task_id == None:
        return None
    return task_id[0]

def add_task(task_name: str):
    """Adds the given task to the list of tasks."""
    task_id = fetch_last_task_id()
    if task_id != None:
        task_id += 1
    if task_id == None:
        task_id = 1
    mycursor.execute(f" insert into todo values({task_id}, \"{task_name}\");")
    connection.commit()
    print(f"Task '{task_name}' added successfully with ID {task_id}.\n")

# Step 5: View Tasks
def view_tasks() -> list[tuple]:
    """Returns the list of tasks."""
    mycursor.execute("Select * from todo")
    tasks = mycursor.fetchall()
    return tasks

# Step 6: Delete a Task
def delete_task(task_id: int):
    """Deletes a Task from the list of tasks."""
    mycursor.execute(f"delete from todo where task_id = {task_id};")
    connection.commit()
    print(f"Task with ID {task_id} deleted successfully.\n")

# Step 8: get task ids
def get_task_ids() -> list[int]:
    """Returns a list of task ids"""
    mycursor.execute("Select task_id from todo")
    task_ids = mycursor.fetchall()
    final_task_ids = []
    for task_id in task_ids:
        final_task_ids.append(task_id[0])
    return final_task_ids

# Step 7: Create a Menu for user to interact with
def main():
    while True:
        print("1. Add a Task")
        print("2. View All Tasks")
        print("3. Delete a Task")
        print("4. Quit")
        choice = input("Enter Your Choice: ")
        try:
            choice = int(choice)
        except ValueError:
            print("Please enter a valid choice!")
            continue
        if choice == 1:
            task = input("Enter a Task: ")
            add_task(task)
            
        elif choice == 2:
            tasks = view_tasks()
            if tasks == []:
                print("\nNo Tasks\n")
                continue
            print()
            print("Task Id, Task Name")
            for task in tasks:
                print(f"{task[0]}, {task[1]}")
            print()
        elif choice == 3:
            tasks = view_tasks()
            if tasks == []:
                print("\nNo Tasks To Delete\n")
                continue
            print()
            print("Task Id, Task Name")
            for task in tasks:
                print(f"{task[0]}, {task[1]}")
            print()
            task_id = input("Enter the task's id: ")
            try:
                task_id = int(task_id)
            except ValueError:
                print("Enter a valid task id!")
                continue
            # Step 8: get task ids
            ids = get_task_ids()
            if task_id in ids:
                delete_task(task_id)
            else:
                print("Enter a valid task id!")
        elif choice == 4:
            print("Thanks For Using!!")
            break
        else:
            print("Please enter a valid choice!")

if __name__ == "__main__":
    main()

Challenge For You!

Provide priorities to the tasks. Display them priority-wise. Also, provide a feature to edit the task.