阅读中文版本

SQLite Todo CLI in Python

A Python CLI that stores todos in a SQLite database with add, list, and done commands, using the standard library sqlite3 module only.

python --version

python todo.py add "write docs"

python todo.py list

Prints the id of the new todo, then lists pending todos with a done marker when listed.

What it does

Persists todos in a SQLite file using the standard library, so nothing needs to be installed and the data survives between runs.

import sqlite3
import sys

DB = "todo.db"

def connect():
    conn = sqlite3.connect(DB)
    conn.execute("CREATE TABLE IF NOT EXISTS todos (id INTEGER PRIMARY KEY, title TEXT NOT NULL, done INTEGER NOT NULL DEFAULT 0)")
    return conn

def add(conn, title):
    cursor = conn.execute("INSERT INTO todos (title) VALUES (?)", (title,))
    conn.commit()
    print(cursor.lastrowid)

def listing(conn, only_open=True):
    query = "SELECT id, title, done FROM todos" + (" WHERE done = 0" if only_open else "") + " ORDER BY id"
    for row in conn.execute(query):
        print(f"{'x' if row[2] else ' '} {row[0]} {row[1]}")

def done(conn, todo_id):
    conn.execute("UPDATE todos SET done = 1 WHERE id = ?", (todo_id,))
    conn.commit()
    print("done " + str(todo_id))

def main():
    command = sys.argv[1] if len(sys.argv) > 1 else "list"
    conn = connect()
    if command == "add":
        add(conn, sys.argv[2])
    elif command == "list":
        listing(conn)
    elif command == "done":
        done(conn, int(sys.argv[2]))
    else:
        print("usage: todo <add|list|done> [title|id]")
        sys.exit(1)

if __name__ == "__main__":
    main()

Parameterised statements are used throughout, and the table is created on first use so the CLI works from a cold start.

Run and verify

python todo.py add "write docs"
python todo.py list

Add a todo, then list; the database file is created in the working directory on first run.

Expected Output

Adding prints the new id; the list command should print the pending todo with a blank done marker. The output is:

1
  1 write docs

After running done 1 the same list becomes empty, and the id sequence is preserved by SQLite across restarts.

Limitations

A single database file is fine for one user; concurrent writers need a locking strategy or a client-server database.