Task Management System

Now you will build the second core feature: task management.

Each user will be able to:

add tasks
view tasks
mark tasks as complete
delete tasks

All tasks will be stored in the database and linked to a specific user.

Goal of This Lesson

By the end of this lesson, you will:

create task-related database functions
link tasks to users
perform CRUD operations on tasks

Step 1: Create Task Service File

Create:

text

app/services/task_service.py

Step 2: Import Database Connection

python

from app.services.database import connect

Step 3: Add Task

python

def add_task(user_id, title):
    conn = connect()
    cursor = conn.cursor()

    cursor.execute(
        "INSERT INTO tasks (user_id, title, completed) VALUES (?, ?, ?)",
        (user_id, title, 0)
    )

    conn.commit()
    conn.close()

Step 4: Get All Tasks for User

python

def get_tasks(user_id):
    conn = connect()
    cursor = conn.cursor()

    cursor.execute(
        "SELECT id, title, completed FROM tasks WHERE user_id = ?",
        (user_id,)
    )

    tasks = cursor.fetchall()
    conn.close()

    return tasks

Step 5: Mark Task as Complete

python

def complete_task(task_id):
    conn = connect()
    cursor = conn.cursor()

    cursor.execute(
        "UPDATE tasks SET completed = 1 WHERE id = ?",
        (task_id,)
    )

    conn.commit()
    conn.close()

Step 6: Delete Task

python

def delete_task(task_id):
    conn = connect()
    cursor = conn.cursor()

    cursor.execute(
        "DELETE FROM tasks WHERE id = ?",
        (task_id,)
    )

    conn.commit()
    conn.close()

Step 7: Test in main.py

Update your main.py:

python

from app.services.database import create_tables
from app.services.user_service import register_user, login_user
from app.services.task_service import add_task, get_tasks, complete_task

def main():
    create_tables()

    register_user("tom", "123")
    user_id = login_user("tom", "123")

    add_task(user_id, "Study Python")
    add_task(user_id, "Build project")

    tasks = get_tasks(user_id)
    print("Tasks:", tasks)

    # mark first task complete
    if tasks:
        complete_task(tasks[0][0])

    print("Updated Tasks:", get_tasks(user_id))

if __name__ == "__main__":
    main()

Example Output

text

Tasks: [(1, 'Study Python', 0), (2, 'Build project', 0)]
Updated Tasks: [(1, 'Study Python', 1), (2, 'Build project', 0)]

Data Structure

Each task:

text

(id, title, completed)

Where:

completed = 0 → not done
completed = 1 → done

Important Notes

Always pass user_id when creating or fetching tasks
Never mix tasks between users
Use placeholders (?) for safety

Current Project Structure

text

app/
    services/
        database.py
        user_service.py
        task_service.py

Why This Step Matters

You now have a full feature:

persistent data
user-linked records
CRUD operations

This is the core of most real applications.

Summary

You built a task management system that allows users to add, view, update, and delete tasks stored in a database.