✅ Build a CLI To-Do App with SQLite

Connect to a SQLite database file. If the file does not exist, Python creates it automatically.

# todo.py
import sqlite3

def get_connection():
    conn = sqlite3.connect('todo.db')
    conn.execute('''
        CREATE TABLE IF NOT EXISTS tasks (
            id        INTEGER PRIMARY KEY AUTOINCREMENT,
            task      TEXT NOT NULL,
            done      INTEGER DEFAULT 0,
            created   TEXT DEFAULT (datetime('now'))
        )
    ''')
    conn.commit()
    return conn

Write functions to insert a new task and display all tasks.

def add_task(task_text):
    conn = get_connection()
    conn.execute('INSERT INTO tasks (task) VALUES (?)', (task_text,))
    conn.commit()
    conn.close()
    print(f'Added: "{task_text}"')

def list_tasks():
    conn = get_connection()
    rows = conn.execute('SELECT id, task, done FROM tasks ORDER BY id').fetchall()
    conn.close()

    if not rows:
        print('No tasks yet. Add one with: python todo.py add "your task"')
        return

    print(f'\n{"ID":<4} {"Status":<10} Task')
    print('-' * 50)
    for row_id, task, done in rows:
        status = '✅ Done  ' if done else '⬜ Todo  '
        print(f'{row_id:<4} {status:<10} {task}')
    print()

ID   Status     Task
--------------------------------------------------
1    ⬜ Todo    Buy groceries
2    ⬜ Todo    Finish Python project
3    ✅ Done    Read emails

def complete_task(task_id):
    conn = get_connection()
    cursor = conn.execute('UPDATE tasks SET done = 1 WHERE id = ?', (task_id,))
    conn.commit()
    conn.close()
    if cursor.rowcount:
        print(f'Task {task_id} marked as done!')
    else:
        print(f'No task with ID {task_id}')

def delete_task(task_id):
    conn = get_connection()
    cursor = conn.execute('DELETE FROM tasks WHERE id = ?', (task_id,))
    conn.commit()
    conn.close()
    if cursor.rowcount:
        print(f'Task {task_id} deleted.')
    else:
        print(f'No task with ID {task_id}')

import sys

def main():
    if len(sys.argv) < 2:
        print("Commands:")
        print("  python todo.py add \"task text\"   — add a task")
        print("  python todo.py list               — show all tasks")
        print("  python todo.py done <id>          — mark task done")
        print("  python todo.py delete <id>        — delete a task")
        return

    command = sys.argv[1].lower()

    if command == 'add' and len(sys.argv) >= 3:
        add_task(' '.join(sys.argv[2:]))
    elif command == 'list':
        list_tasks()
    elif command == 'done' and len(sys.argv) == 3:
        complete_task(int(sys.argv[2]))
    elif command == 'delete' and len(sys.argv) == 3:
        delete_task(int(sys.argv[2]))
    else:
        print(f'Unknown command: {command}')

if __name__ == '__main__':
    main()

$ python todo.py add "Learn SQLite"
Added: "Learn SQLite"

$ python todo.py add "Build a REST API"
Added: "Build a REST API"

$ python todo.py list
ID   Status     Task
--------------------------------------------------
1    ⬜ Todo    Learn SQLite
2    ⬜ Todo    Build a REST API

$ python todo.py done 1
Task 1 marked as done!

$ python todo.py list
ID   Status     Task
--------------------------------------------------
1    ✅ Done    Learn SQLite
2    ⬜ Todo    Build a REST API