Python and SQLite: build a to-do app with CRUD (tested)

Almost every application stores data, and SQLite is the easiest real database to start with. It lives in a single file, needs no server, and Python ships with the sqlite3 module. In this project you build a to-do list and practise CRUD: Create, Read, Update and Delete. You will also learn the one habit that prevents SQL injection. The code and outputs below were run with Python 3 before publishing.

What we are building

A command-line to-do list with four commands: add, list (optionally only open tasks), done and delete. Tasks are saved in a file called todo.db, so they are still there next time.

The code

Save as todo.py:

#!/usr/bin/env python3
"""A to-do list stored in SQLite: add, list, done and delete tasks."""
import argparse
import sqlite3

DB_FILE = "todo.db"


def connect(path=DB_FILE):
    conn = sqlite3.connect(path)
    conn.row_factory = sqlite3.Row
    conn.execute(
        """CREATE TABLE IF NOT EXISTS tasks (
               id INTEGER PRIMARY KEY AUTOINCREMENT,
               title TEXT NOT NULL CHECK (length(title) BETWEEN 1 AND 200),
               done INTEGER NOT NULL DEFAULT 0,
               created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
           )"""
    )
    return conn


def add_task(conn, title):
    with conn:
        cur = conn.execute("INSERT INTO tasks (title) VALUES (?)", (title,))
    return cur.lastrowid


def list_tasks(conn, only_open=False):
    sql = "SELECT id, title, done FROM tasks"
    if only_open:
        sql += " WHERE done = 0"
    return conn.execute(sql + " ORDER BY id").fetchall()


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


def delete_task(conn, task_id):
    with conn:
        cur = conn.execute("DELETE FROM tasks WHERE id = ?", (task_id,))
    return cur.rowcount == 1


def main(argv=None):
    parser = argparse.ArgumentParser(description="SQLite to-do list")
    sub = parser.add_subparsers(dest="command", required=True)
    sub.add_parser("add").add_argument("title")
    sub.add_parser("list").add_argument("--open", action="store_true", help="hide finished tasks")
    sub.add_parser("done").add_argument("id", type=int)
    sub.add_parser("delete").add_argument("id", type=int)
    args = parser.parse_args(argv)

    conn = connect()
    if args.command == "add":
        try:
            print(f"Added task {add_task(conn, args.title)}")
        except sqlite3.IntegrityError:
            parser.error("title must be 1 to 200 characters")
    elif args.command == "list":
        for row in list_tasks(conn, args.open):
            print(f'{row["id"]:>3} [{"x" if row["done"] else " "}] {row["title"]}')
    else:
        action = mark_done if args.command == "done" else delete_task
        if not action(conn, args.id):
            parser.exit(1, f"No task with id {args.id}\n")
        print("OK")


if __name__ == "__main__":
    main()

Try it

$ python3 todo.py add "Learn SQL"
Added task 1
$ python3 todo.py add "Write tests"
Added task 2
$ python3 todo.py add "Deploy app"
Added task 3
$ python3 todo.py done 1
OK
$ python3 todo.py list
  1 [x] Learn SQL
  2 [ ] Write tests
  3 [ ] Deploy app
$ python3 todo.py list --open
  2 [ ] Write tests
  3 [ ] Deploy app
$ python3 todo.py delete 3
OK
$ python3 todo.py done 42
No task with id 42
$ python3 todo.py add ""
todo.py: error: title must be 1 to 200 characters

How it works

The table. CREATE TABLE IF NOT EXISTS makes the table on first run and does nothing afterwards. The id is generated by the database. NOT NULL, DEFAULT and CHECK put rules in the database, so bad data is rejected even if some other code forgets to validate.

Parameterised queries. Look at VALUES (?) followed by (title,). The value is passed separately from the SQL text, so the database never treats it as code. Never build SQL with f-strings or + from user input. That is how SQL injection happens. The tests prove it: a title like x'); DROP TABLE tasks; -- is stored as harmless text.

Transactions. with conn: commits if the block succeeds and rolls back if it raises an error. Without a commit, your insert would vanish when the program exits.

rowcount. After an UPDATE or DELETE, cur.rowcount says how many rows changed. If it is not 1, the id did not exist, and the command tells the user instead of pretending it worked.

row_factory = sqlite3.Row lets us read columns by name (row["title"]) instead of position.

Separate functions. Each database action is a small function that takes the connection as a parameter. That is what makes testing easy.

Add tests

For tests we use :memory:, a database that exists only in RAM and disappears afterwards, so tests are fast and never touch your real data. Save as test_todo.py:

import sqlite3
import unittest

import todo


class TodoTest(unittest.TestCase):
    def setUp(self):
        self.conn = todo.connect(":memory:")

    def test_add_list_done_delete(self):
        first = todo.add_task(self.conn, "Learn SQL")
        todo.add_task(self.conn, "Write tests")
        self.assertTrue(todo.mark_done(self.conn, first))
        self.assertEqual([r["title"] for r in todo.list_tasks(self.conn, only_open=True)], ["Write tests"])
        self.assertTrue(todo.delete_task(self.conn, first))
        self.assertEqual(len(todo.list_tasks(self.conn)), 1)

    def test_missing_id_returns_false(self):
        self.assertFalse(todo.mark_done(self.conn, 999))

    def test_sql_injection_text_is_stored_as_plain_text(self):
        evil = "x'); DROP TABLE tasks; --"
        todo.add_task(self.conn, evil)
        self.assertEqual(todo.list_tasks(self.conn)[0]["title"], evil)

    def test_empty_title_rejected_by_database(self):
        with self.assertRaises(sqlite3.IntegrityError):
            todo.add_task(self.conn, "")


if __name__ == "__main__":
    unittest.main()

$ python3 -m unittest -v
test_add_list_done_delete ... ok
test_empty_title_rejected_by_database ... ok
test_missing_id_returns_false ... ok
test_sql_injection_text_is_stored_as_plain_text ... ok

Ran 4 tests in 0.004s

OK

Known limits

• Everything is in one file with a hard-coded name todo.db in the current folder.
• SQLite allows one writer at a time. That is fine for a personal tool or a small site, but a busy multi-user service usually needs PostgreSQL or MySQL.
• There is no editing of a task title yet, and no due dates.
• Real projects use migrations to change a table safely after launch, instead of only CREATE TABLE IF NOT EXISTS.

Challenges

1. Add an edit command that changes a title.
2. Add a due column and a list --overdue filter.
3. Add a priority column with a CHECK of 1 to 3, and sort by it.
4. Add a search command using WHERE title LIKE ? (keep the placeholder).
5. Build a tiny web page on top of these same functions, such as with the Node or Python API from earlier projects.

Takeaway: CRUD is four SQL statements. Use parameterised queries every time, put rules in the database, wrap writes in transactions, and keep database code in small testable functions.

---

Build-along projects series, Part 1 (small tested projects): 4 of 5. Previous: Build a Python file organiser that sorts your Downloads folder.