Building a Python command-line task tracker with SQLite

#frontend #webdev

Building a Python command-line task tracker with SQLite

This tutorial walks through a practical CLI app that stores tasks in SQLite, supports adding/listing/completing tasks, and keeps the code structured so it is easy to extend. SQLite is a lightweight, file-based relational database built into Python, which makes it a strong choice for small tools and prototypes.

What you will build

You will build a terminal task tracker with these features:

Add a task with a title and optional due date.
List open and completed tasks.
Mark tasks as completed.
Store data in a real database instead of a JSON file or in-memory list.

SQLite is a good fit here because it gives you persistence without needing a separate database server, and it works well for small local applications.

Project structure

A simple structure keeps the app understandable:

task_tracker/
├── app.py
├── db.py
├── models.py
└── task_tracker.db

For a small CLI, you can keep everything in one file at first, but splitting database access from command handling makes the code much easier to maintain. That separation also makes testing simpler because you can verify logic without depending on the terminal interface.

Step 1: Define the schema

Start by creating a tasks table. Each task gets an id, title, completion status, and timestamps.

### db.py
import sqlite3

DB_PATH = "task_tracker.db"

def get_connection():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    with get_connection() as conn:
        conn.execute("""
            CREATE TABLE IF NOT EXISTS tasks (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                due_date TEXT,
                completed INTEGER NOT NULL DEFAULT 0,
                created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                completed_at TEXT
            )
        """)

This schema keeps the database simple while still recording enough information to show task history and completion state. Storing dates as ISO-style text is often enough for a small local app and keeps the example portable.

Step 2: Add database functions

Next, write small functions for the core data operations.

### db.py
def add_task(title, due_date=None):
    with get_connection() as conn:
        conn.execute(
            "INSERT INTO tasks (title, due_date) VALUES (?, ?)",
            (title, due_date),
        )

def list_tasks(show_completed=False):
    query = """
        SELECT id, title, due_date, completed, created_at, completed_at
        FROM tasks
    """
    if not show_completed:
        query += " WHERE completed = 0"
    query += " ORDER BY created_at DESC"

    with get_connection() as conn:
        return conn.execute(query).fetchall()

def complete_task(task_id):
    with get_connection() as conn:
        conn.execute(
            """
            UPDATE tasks
            SET completed = 1,
                completed_at = CURRENT_TIMESTAMP
            WHERE id = ?
            """,
            (task_id,),
        )

def get_task(task_id):
    with get_connection() as conn:
        return conn.execute(
            "SELECT * FROM tasks WHERE id = ?",
            (task_id,),
        ).fetchone()

These functions use parameterized SQL, which protects against SQL injection and keeps the data layer straightforward. The list_tasks function also shows a useful pattern: build one query and adjust it based on the caller’s intent.

Step 3: Build the CLI

Now create a command-line interface with argparse.

### app.py
import argparse
from db import init_db, add_task, list_tasks, complete_task, get_task

def parse_args():
    parser = argparse.ArgumentParser(description="Task tracker")
    subparsers = parser.add_subparsers(dest="command", required=True)

    add = subparsers.add_parser("add", help="Add a new task")
    add.add_argument("title", help="Task title")
    add.add_argument("due", help="Optional due date, e.g. 2026-06-10")

    subparsers.add_parser("list", help="List open tasks")
    subparsers.add_parser("list-all", help="List all tasks")

    done = subparsers.add_parser("done", help="Mark task complete")
    done.add_argument("id", type=int, help="Task id")

    show = subparsers.add_parser("show", help="Show one task")
    show.add_argument("id", type=int, help="Task id")

    return parser.parse_args()

def print_task(row):
    status = "✓" if row["completed"] else " "
    due = row["due_date"] or "-"
    completed_at = row["completed_at"] or "-"
    print(f"[{status}] {row['id']:>3} | {row['title']} | due: {due} | completed: {completed_at}")

def main():
    init_db()
    args = parse_args()

    if args.command == "add":
        add_task(args.title, args.due)
        print("Task added.")
    elif args.command == "list":
        for row in list_tasks(show_completed=False):
            print_task(row)
    elif args.command == "list-all":
        for row in list_tasks(show_completed=True):
            print_task(row)
    elif args.command == "done":
        task = get_task(args.id)
        if not task:
            print("Task not found.")
            return
        complete_task(args.id)
        print("Task completed.")
    elif args.command == "show":
        task = get_task(args.id)
        if not task:
            print("Task not found.")
            return
        print_task(task)

if __name__ == "__main__":
    main()

This structure makes the CLI readable because each command does one job. It also keeps the database code reusable if you later add a web API or desktop UI.

Step 4: Try it out

Run the app from the terminal:

python app.py add "Write tutorial" due 2026-06-05
python app.py add "Review pull request"
python app.py list
python app.py done 1
python app.py list-all

A typical output might look like this:

[ ]   2 | Review pull request | due: - | completed: -
[ ]   1 | Write tutorial | due: 2026-06-05 | completed: -

After marking task 1 as done:

[✓]   1 | Write tutorial | due: 2026-06-05 | completed: 2026-05-31 21:40:00
[ ]   2 | Review pull request | due: - | completed: -

A small app like this is a good example of “start simple, then harden the edges.” You get persistence, clear commands, and a real database without introducing unnecessary infrastructure.

Add validation

Once the basic version works, add input checks so bad data fails fast.

def validate_title(title):
    title = title.strip()
    if not title:
        raise ValueError("Title cannot be empty.")
    if len(title) > 200:
        raise ValueError("Title is too long.")
    return title

Use it in the add branch:

if args.command == "add":
    try:
        title = validate_title(args.title)
        add_task(title, args.due)
        print("Task added.")
    except ValueError as e:
        print(f"Error: {e}")

Validation belongs close to the command boundary because that is where user input enters the system. Keeping invalid data out early reduces surprises later in storage and reporting.

Make it more useful

A few low-effort upgrades make this tool much better:

Add edit to change a title or due date.
Add tags with a separate table.
Add priority and sort by priority first.
Add overdue filtering by comparing due_date to today.
Add tests using a temporary SQLite database file.

For a production-ready local utility, tests matter almost as much as features. They let you refactor the schema or command parser without worrying about breaking the behavior users rely on.

Common mistakes

Avoid these pitfalls:

Putting SQL directly inside the CLI layer, which makes the code harder to test and extend.
Storing dates in inconsistent formats, which makes sorting and filtering unreliable.
Skipping NOT NULL constraints on fields that should always exist.
Forgetting to handle missing task ids when updating or showing a task.

A good rule is to keep command parsing, validation, and persistence separate. That separation creates cleaner code even in a tiny project, and it scales better than a single all-in-one script.

Next steps

You now have a working SQLite-backed CLI task tracker with a clean structure. The same pattern can power bookmark managers, habit trackers, expense logs, or internal team tools. SQLite’s simplicity makes it a practical default when you need durable local storage without operational overhead.

Rizwan Saleem | https://rizwansaleem.co