Docker + Python CRUD API + Excel VBA – All for beginners

If you are just starting with Docker and want a practical project, here is a fun one: let’s build a tiny CRUD app in Python, run it inside a Docker container, and then connect to it from Excel with VBA. It would not be as fun as it sounds, but it is interesting to see it in action.

The Python App

We use FastAPI and SQLite to build a minimal backend with five endpoints:

• POST /todos – create
• GET /todos – list
• GET /todos/{id} – get one
• PATCH /todos/{id} – update
• DELETE /todos/{id} – delete
• FastAPI automatically gives us a Swagger UI at /docs, where we can test everything without writing any frontend code.

import os, sqlite3
from typing import List, Optional
from fastapi import FastAPI, HTTPException
from pydantic import BaseModel

DB_PATH = os.getenv("DB_PATH", "/data/app.db")  

app = FastAPI(title="Minimal Todo CRUD", description="Beginner-friendly, zero frontend.")

class TodoIn(BaseModel):
    title: str
    completed: bool = False

class TodoUpdate(BaseModel):
    title: Optional[str] = None
    completed: Optional[bool] = None

class TodoOut(TodoIn):
    id: int

def row_to_todo(row) -> TodoOut:
    return TodoOut(id=row["id"], title=row["title"], completed=bool(row["completed"]))

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

@app.on_event("startup")
def init_db():
    os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
    conn = get_conn()
    conn.execute("""
        CREATE TABLE IF NOT EXISTS todos(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            completed INTEGER NOT NULL DEFAULT 0
        )
    """)
    conn.commit(); conn.close()

@app.post("/todos", response_model=TodoOut, status_code=201)
def create_todo(payload: TodoIn):
    conn = get_conn()
    cur = conn.execute(
        "INSERT INTO todos(title, completed) VALUES(?, ?)",
        (payload.title, int(payload.completed))
    )
    conn.commit()
    row = conn.execute("SELECT * FROM todos WHERE id=?", (cur.lastrowid,)).fetchone()
    conn.close()
    return row_to_todo(row)

@app.get("/todos", response_model=List[TodoOut])
def list_todos():
    conn = get_conn()
    rows = conn.execute("SELECT * FROM todos ORDER BY id DESC").fetchall()
    conn.close()
    return [row_to_todo(r) for r in rows]

@app.get("/todos/{todo_id}", response_model=TodoOut)
def get_todo(todo_id: int):
    conn = get_conn()
    row = conn.execute("SELECT * FROM todos WHERE id=?", (todo_id,)).fetchone()
    conn.close()
    if not row:
        raise HTTPException(404, "Todo not found")
    return row_to_todo(row)

@app.patch("/todos/{todo_id}", response_model=TodoOut)
def update_todo(todo_id: int, payload: TodoUpdate):
    data = payload.model_dump(exclude_unset=True)
    if not data:
        return get_todo(todo_id)  # nothing to change

    fields, values = [], []
    if "title" in data:
        fields.append("title=?"); values.append(data["title"])
    if "completed" in data:
        fields.append("completed=?"); values.append(int(data["completed"]))
    if not fields:
        return get_todo(todo_id)

    conn = get_conn()
    cur = conn.execute(f"UPDATE todos SET {', '.join(fields)} WHERE id=?", (*values, todo_id))
    if cur.rowcount == 0:
        conn.close(); raise HTTPException(404, "Todo not found")
    conn.commit()
    row = conn.execute("SELECT * FROM todos WHERE id=?", (todo_id,)).fetchone()
    conn.close()
    return row_to_todo(row)

@app.delete("/todos/{todo_id}", status_code=204)
def delete_todo(todo_id: int):
    conn = get_conn()
    cur = conn.execute("DELETE FROM todos WHERE id=?", (todo_id,))
    conn.commit(); conn.close()
    if cur.rowcount == 0:
        raise HTTPException(404, "Todo not found")
    return  # 204 No Content

The Dockerfile

# Dockerfile
FROM python:3.11-slim

ENV PYTHONDONTWRITEBYTECODE=1 \
    PYTHONUNBUFFERED=1

WORKDIR /app
COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt

COPY main.py .
EXPOSE 8000

# Persist DB to a mounted volume /data (see docker run below)
ENV DB_PATH=/data/app.db

CMD ["uvicorn", "main:app", "--host", "0.0.0.0", "--port", "8000"]

With a single Dockerfile we package the app and its dependencies. We expose port 8000 and mount a host folder as /data, so the SQLite database persists even if the container is removed.

docker build -t todo-app .
docker run --rm -p 8000:8000 -v ${PWD}/data:/data todo-min

Now you can open http://localhost:8000/docs and play with the CRUD operations.

Four ways to display the db data are illustrated in the GitHub repository.

Excel and VBA

To make it even more fun, we fetch the todos directly into Excel. With a few lines of VBA using XMLHTTP and a JSON parser, the /todos endpoint is read into the sheet. That way, Excel becomes a simple client for our Dockerized API:

Public Sub Main()

    Dim http As Object
    Dim JSON As Object
    Dim item As Object
    Dim i As Long
    
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", "http://localhost:8000/todos", False
    http.Send
    ThisWorkbook.Sheets(1).Cells.Delete
    If http.Status = 200 Then
        ' Requires VBA JSON parser (e.g. VBA JSON from GitHub: https://github.com/VBA-tools/VBA-JSON)
        Set JSON = JsonConverter.ParseJson(http.ResponseText)
        
        i = 2
        ThisWorkbook.Sheets(1).Cells(1, 1).Value = "ID"
        ThisWorkbook.Sheets(1).Cells(1, 2).Value = "Title"
        ThisWorkbook.Sheets(1).Cells(1, 3).Value = "Completed"
        
        For Each item In JSON
            Cells(i, 1).Value = item("id")
            Cells(i, 2).Value = item("title")
            Cells(i, 3).Value = item("completed")
            i = i + 1
        Next
    Else
        MsgBox "Error: " & http.Status
    End If
End Sub

The GitHub repository is here: https://github.com/Vitosh/Python_personal/blob/master/YouTube/039_Docker-Basics

Enjoy!