Project tutorials · 4 steps

Build a command-line app in Python with argparse and SQLite

A complete to-do tool with subcommands, a SQLite database, clear errors and exit codes, using only the standard library.

What you'll build

A small command-line tool teaches most of what makes software hold up: separating storage from interface, handling bad input, and writing errors to the right place. This one manages a to-do list in SQLite, with nothing to install.

What you'll learn

Turn a short brief into requirements, a data model and a layered design
Build a command-line interface with argparse, exit codes and clear errors
Store data with a repository class over SQLite
Test a whole program by calling its entry point with arguments

What you need

Python functions and classes, and a little SQL. It uses only the standard library, so it runs anywhere with Python 3.

Step 1: Keep storage in one class

TaskRepo is the only code that knows about SQL. Everything else calls its methods, so you could change the database without touching the commands. The ? placeholders let SQLite handle the values, which is what stops SQL injection. ORDER BY CASE sorts by priority in the order that makes sense to people, not alphabetically.

import argparse
import sqlite3
import sys

PRIORITIES = ["low", "normal", "high"]


class TaskRepo:
    def __init__(self, db):
        self.db = db
        self.db.execute("""
            CREATE TABLE IF NOT EXISTS tasks (
                id INTEGER PRIMARY KEY,
                title TEXT NOT NULL,
                priority TEXT NOT NULL,
                done INTEGER NOT NULL DEFAULT 0
            )
        """)

    def add(self, title, priority):
        cur = self.db.execute("INSERT INTO tasks (title, priority) VALUES (?, ?)", (title, priority))
        self.db.commit()
        return cur.lastrowid

    def list(self, include_done=False):
        where = "" if include_done else "WHERE done = 0"
        return self.db.execute(f"""
            SELECT id, title, priority, done FROM tasks {where}
            ORDER BY CASE priority WHEN 'high' THEN 0 WHEN 'normal' THEN 1 ELSE 2 END, id
        """).fetchall()

    def mark_done(self, task_id):
        cur = self.db.execute("UPDATE tasks SET done = 1 WHERE id = ?", (task_id,))
        self.db.commit()
        return cur.rowcount == 1

Step 2: Define the commands

argparse gives you subcommands, --help and error messages for free. choices rejects a bad priority before your code sees it. main takes its arguments and the repository as parameters, not from sys.argv and a global, which is what makes the whole program easy to test. parse_args exits on bad input, so the try turns that into a normal return value.

def main(argv, repo):
    parser = argparse.ArgumentParser(prog="tasks")
    sub = parser.add_subparsers(dest="command", required=True)
    add = sub.add_parser("add")
    add.add_argument("title")
    add.add_argument("--priority", choices=PRIORITIES, default="normal")
    lst = sub.add_parser("list")
    lst.add_argument("--all", action="store_true")
    done = sub.add_parser("done")
    done.add_argument("id", type=int)
    try:
        args = parser.parse_args(argv)
    except SystemExit as exc:
        return exc.code

Rather write it yourself?

In the lesson you write this from a starter, in your browser with nothing to install, and checks tell you what's wrong as you go. A free account opens it and all 244 lessons.

Create a free account

Step 3: Handle each command

Each branch does one job and returns an exit code: 0 for success and 1 for failure. Shell scripts rely on those. Errors go to stderr so they don't end up in a file when someone redirects the output. And strip catches a title that's only spaces, which argparse can't.

    if args.command == "add":
        title = args.title.strip()
        if not title:
            print("error: title can't be empty", file=sys.stderr)
            return 1
        task_id = repo.add(title, args.priority)
        print(f"Added task {task_id}: {title}")
        return 0
    if args.command == "list":
        rows = repo.list(include_done=args.all)
        if not rows:
            print("No tasks.")
        for task_id, title, priority, is_done in rows:
            mark = "x" if is_done else " "
            print(f"[{mark}] {task_id} ({priority}) {title}")
        return 0
    if args.command == "done":
        if not repo.mark_done(args.id):
            print(f"error: no task {args.id}", file=sys.stderr)
            return 1
        print(f"Done: {args.id}")
        return 0
    return 1

Step 4: Run it

Calling main with a list of arguments is exactly how a test would run it, and it uses a database in memory. High priority comes first, and the finished task shows as done.

repo = TaskRepo(sqlite3.connect(":memory:"))
main(["add", "Write report", "--priority", "high"], repo)
main(["add", "Buy milk"], repo)
main(["done", "2"], repo)
main(["list", "--all"], repo)

What it prints

Added task 1: Write report
Added task 2: Buy milk
Done: 2
[ ] 1 (high) Write report
[x] 2 (normal) Buy milk

To use it as a real tool, open a file instead and pass the command line in:

if __name__ == "__main__":
    sys.exit(main(sys.argv[1:], TaskRepo(sqlite3.connect("tasks.db"))))

Where to take it next

Write tests that call main with arguments and check the exit code and output.
Add a delete command, or due dates, which means changing the table.
Package it with pyproject.toml so tasks works as a command.

Mistakes to watch for

Printing errors to stdout and always exiting with 0.
Reading sys.argv deep inside the program, which makes it untestable.
Putting SQL in the command-line code instead of one repository class.
Skipping requirements and discovering them as bugs.

The complete program

import argparse
import sqlite3
import sys

PRIORITIES = ["low", "normal", "high"]


class TaskRepo:
    def __init__(self, db):
        self.db = db
        self.db.execute("""
            CREATE TABLE IF NOT EXISTS tasks (
                id INTEGER PRIMARY KEY,
                title TEXT NOT NULL,
                priority TEXT NOT NULL,
                done INTEGER NOT NULL DEFAULT 0
            )
        """)

    def add(self, title, priority):
        cur = self.db.execute("INSERT INTO tasks (title, priority) VALUES (?, ?)", (title, priority))
        self.db.commit()
        return cur.lastrowid

    def list(self, include_done=False):
        where = "" if include_done else "WHERE done = 0"
        return self.db.execute(f"""
            SELECT id, title, priority, done FROM tasks {where}
            ORDER BY CASE priority WHEN 'high' THEN 0 WHEN 'normal' THEN 1 ELSE 2 END, id
        """).fetchall()

    def mark_done(self, task_id):
        cur = self.db.execute("UPDATE tasks SET done = 1 WHERE id = ?", (task_id,))
        self.db.commit()
        return cur.rowcount == 1


def main(argv, repo):
    parser = argparse.ArgumentParser(prog="tasks")
    sub = parser.add_subparsers(dest="command", required=True)
    add = sub.add_parser("add")
    add.add_argument("title")
    add.add_argument("--priority", choices=PRIORITIES, default="normal")
    lst = sub.add_parser("list")
    lst.add_argument("--all", action="store_true")
    done = sub.add_parser("done")
    done.add_argument("id", type=int)
    try:
        args = parser.parse_args(argv)
    except SystemExit as exc:
        return exc.code

    if args.command == "add":
        title = args.title.strip()
        if not title:
            print("error: title can't be empty", file=sys.stderr)
            return 1
        task_id = repo.add(title, args.priority)
        print(f"Added task {task_id}: {title}")
        return 0
    if args.command == "list":
        rows = repo.list(include_done=args.all)
        if not rows:
            print("No tasks.")
        for task_id, title, priority, is_done in rows:
            mark = "x" if is_done else " "
            print(f"[{mark}] {task_id} ({priority}) {title}")
        return 0
    if args.command == "done":
        if not repo.mark_done(args.id):
            print(f"error: no task {args.id}", file=sys.stderr)
            return 1
        print(f"Done: {args.id}")
        return 0
    return 1


repo = TaskRepo(sqlite3.connect(":memory:"))
main(["add", "Write report", "--priority", "high"], repo)
main(["add", "Buy milk"], repo)
main(["done", "2"], repo)
main(["list", "--all"], repo)

Write it yourself

In the lesson you write this from a starter, in your browser with nothing to install, and checks tell you what's wrong as you go. A free account opens it and all 244 lessons.

Create a free account