Build a Local Task Tracker with SQLite and Python

Build a Local Task Tracker with SQLite and Python

This tutorial shows how to build a small but solid command-line task tracker using Python, SQLite, and argparse. You will learn how to model data, write CRUD commands, and structure the code so it stays maintainable as the app grows. The sqlite3 module gives you a lightweight embedded database, and argparse is the standard-library way to build a proper CLI.

What you will build

You will create a task tracker that can add tasks, list tasks, mark tasks as done, and delete tasks. The app stores data in a local SQLite file, so it works without any server or extra dependency. SQLite is a good fit for small tools because it is self-contained and built for simple local persistence.

By the end, you will have a project that looks like a real utility rather than a script. That means it will have a database layer, a command-line interface, and a clear separation between commands and storage logic.

Project layout

Use a structure like this:

task-tracker/
├─ tracker.py
└─ tasks.db

You can keep it in one file at first, then split it later if you want. Starting simple is useful because it helps you understand the flow before introducing more files and abstractions. pathlib is handy here because it gives you an object-oriented way to work with filesystem paths.

Step 1: Define the schema

We need one table to store tasks. Each task should have an ID, title, optional notes, completion status, and timestamps. SQLite works well for this because it stores everything in a single file and supports standard SQL operations through Python.

Here is a practical schema:

CREATE TABLE IF NOT EXISTS tasks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    notes TEXT DEFAULT '',
    done INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

This design keeps the app flexible without being overengineered. The done column uses 0 and 1 instead of booleans because that maps cleanly to SQLite storage.

Step 2: Create the database helper

Start with a small helper that opens the database and creates the table when needed.

from pathlib import Path
from datetime import datetime
import sqlite3

DB_PATH = Path("tasks.db")

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

def init_db():
    with connect_db() as conn:
        conn.execute("""
            CREATE TABLE IF NOT EXISTS tasks (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                notes TEXT DEFAULT '',
                done INTEGER NOT NULL DEFAULT 0,
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL
            );
        """)

sqlite3.Row lets you access columns by name, which makes the code easier to read. The context manager pattern also helps ensure changes are committed cleanly when the block exits.

Step 3: Add tasks

Now add a function for inserting new rows.

def add_task(title, notes=""):
    now = datetime.utcnow().isoformat(timespec="seconds")
    with connect_db() as conn:
        conn.execute(
            """
            INSERT INTO tasks (title, notes, done, created_at, updated_at)
            VALUES (?, ?, 0, ?, ?)
            """,
            (title, notes, now, now),
        )

The ? placeholders keep the query parameterized, which is safer than string formatting and also cleaner to maintain. This is the standard way to pass values into SQLite queries from Python.

Example usage:

add_task("Write project outline", "Draft the tutorial structure first")
add_task("Record screenshots")

Step 4: List tasks

A useful CLI should make it easy to see both open and completed work.

def list_tasks(show_all=False):
    query = "SELECT * FROM tasks"
    if not show_all:
        query += " WHERE done = 0"
    query += " ORDER BY id DESC"

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

You can then format the results like this:

def print_tasks(rows):
    if not rows:
        print("No tasks found.")
        return

    for row in rows:
        status = "✓" if row["done"] else " "
        print(f'{row["id"]:>3} [{status}] {row["title"]}')
        if row["notes"]:
            print(f'      {row["notes"]}')

This keeps the display logic separate from the database logic, which makes later changes much easier. For example, you could swap the terminal output for JSON without rewriting the query code.

Step 5: Mark tasks done

Updating a task should be explicit and small.

def mark_done(task_id):
    now = datetime.utcnow().isoformat(timespec="seconds")
    with connect_db() as conn:
        conn.execute(
            "UPDATE tasks SET done = 1, updated_at = ? WHERE id = ?",
            (now, task_id),
        )

This is a good example of an idempotent operation: running it multiple times leaves the task in the same completed state. That makes retries safe and simplifies your mental model.

Step 6: Delete tasks

Deleting by ID is straightforward.

def delete_task(task_id):
    with connect_db() as conn:
        conn.execute("DELETE FROM tasks WHERE id = ?", (task_id,))

If you want a safer workflow, you can later add a confirmation prompt or a soft-delete flag. For a first version, direct deletion keeps the app easy to use and test.

Step 7: Build the CLI

Now wire everything together with argparse. The module is the recommended standard-library way to parse command-line arguments in Python.

import argparse

def build_parser():
    parser = argparse.ArgumentParser(description="Local task tracker")
    sub = parser.add_subparsers(dest="command", required=True)

    add = sub.add_parser("add", help="Add a task")
    add.add_argument("title")
    add.add_argument("-n", "notes", default="")

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

    done = sub.add_parser("done", help="Mark a task as done")
    done.add_argument("id", type=int)

    delete = sub.add_parser("delete", help="Delete a task")
    delete.add_argument("id", type=int)

    return parser

Then add the command dispatcher:

def main():
    init_db()
    parser = build_parser()
    args = parser.parse_args()

    if args.command == "add":
        add_task(args.title, args.notes)
        print("Task added.")
    elif args.command == "list":
        print_tasks(list_tasks(show_all=False))
    elif args.command == "all":
        print_tasks(list_tasks(show_all=True))
    elif args.command == "done":
        mark_done(args.id)
        print("Task marked done.")
    elif args.command == "delete":
        delete_task(args.id)
        print("Task deleted.")

if __name__ == "__main__":
    main()

This gives you a clean command structure and a predictable user experience. The CLI now behaves like a real tool instead of a one-off script.

Step 8: Try it out

Run these commands from your terminal:

python tracker.py add "Write blog post" -n "Include code examples"
python tracker.py add "Review output"
python tracker.py list
python tracker.py done 1
python tracker.py all
python tracker.py delete 2

A sample output might look like this:

  2 [ ] Review output
  1 [ ] Write blog post
      Include code examples

Then, after marking task 1 done:

  2 [ ] Review output
  1 [✓] Write blog post
      Include code examples

The simple output is intentional. It makes the tool quick to scan in a terminal and easy to extend later with filters, priorities, or tags.

Making it better

Once the basic version works, there are several practical improvements you can make. You can add due dates, priorities, tags, or a search command. You can also add validation so empty titles are rejected before they hit the database. SQLite supports richer querying than this tutorial uses, so adding sorting and filtering later is a natural next step.

A useful upgrade is to add a json flag for automation. That lets other scripts consume your task list without parsing terminal text, which is especially helpful if you later build integrations or reports. Another good improvement is to split the code into db.py, cli.py, and models.py once the single-file version feels comfortable.

Common mistakes

One common mistake is mixing SQL and output formatting in the same function. That makes the code hard to test and harder to evolve. Another is building SQL with string concatenation instead of parameters, which is both messier and less safe.

A second mistake is letting the database schema drift without intent. If you want to add fields later, use a migration step rather than editing old data by hand. For a small local tool, a simple versioning table or migration script is usually enough.

Next steps

A good follow-up project is to add an edit command, then introduce tag filtering and a search command. After that, you could package the app so it can be installed as a global CLI tool. Because the foundation uses standard-library modules, the project stays portable and easy to maintain.

This tutorial gives you a realistic pattern you can reuse in other internal tools: database helper, command parser, focused business functions, and clean terminal output. That structure scales much better than writing everything in one function.

-

Rizwan Saleem | https://rizwansaleem.co