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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
# 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.
|
1 2 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
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!