Working with CSV, JSON and .env Files

Web applications deal with structured text data constantly — CSV files uploaded by users, JSON configuration files, API payloads, and environment variable files. Python’s standard library provides robust tools for each format: the csv module for tabular data, the json module for serialisation and deserialisation, and python-dotenv for loading .env files. In FastAPI, these appear together in upload endpoints that accept CSV files, configuration systems that load settings from .env, and response serialisation that produces JSON from Python objects.

Working with CSV #

import csv
from pathlib import Path
from io import StringIO

# ── Read CSV from file ─────────────────────────────────────────────────────────
with open("users.csv", "r", encoding="utf-8", newline="") as f:
    reader = csv.DictReader(f)   # reads into dicts using first row as header
    for row in reader:
        print(row)
        # {"name": "Alice", "email": "alice@example.com", "role": "admin"}

# Access headers
with open("users.csv", encoding="utf-8", newline="") as f:
    reader = csv.DictReader(f)
    headers = reader.fieldnames   # ["name", "email", "role"]
    rows    = list(reader)        # list of dicts

# ── Write CSV to file ─────────────────────────────────────────────────────────
users = [
    {"name": "Alice", "email": "alice@example.com", "role": "admin"},
    {"name": "Bob",   "email": "bob@example.com",   "role": "user"},
]

with open("output.csv", "w", encoding="utf-8", newline="") as f:
    writer = csv.DictWriter(f, fieldnames=["name", "email", "role"])
    writer.writeheader()
    writer.writerows(users)

# ── FastAPI file upload — process CSV bytes from UploadFile ───────────────────
from fastapi import UploadFile

async def process_csv_upload(file: UploadFile) -> list[dict]:
    content = await file.read()
    text    = content.decode("utf-8")
    reader  = csv.DictReader(StringIO(text))   # in-memory — no disk write
    return [
        {"name": row["name"].strip(), "email": row["email"].strip().lower()}
        for row in reader
        if row.get("email")   # skip rows with empty email
    ]

Working with JSON #

import json
from pathlib import Path
from datetime import datetime

# ── Parse JSON string → Python ────────────────────────────────────────────────
json_str = '{"name": "Alice", "age": 30, "active": true}'
data     = json.loads(json_str)   # loads = load string
print(data)         # {"name": "Alice", "age": 30, "active": True}
print(type(data))   # dict

# ── Convert Python → JSON string ─────────────────────────────────────────────
user = {"name": "Bob", "age": 25, "tags": ["python", "fastapi"]}
json_str = json.dumps(user)                      # compact
json_pretty = json.dumps(user, indent=2)         # formatted
json_sorted = json.dumps(user, sort_keys=True)   # sorted keys

print(json_pretty)
# {
#   "name": "Bob",
#   "age": 25,
#   "tags": ["python", "fastapi"]
# }

# ── Read/write JSON files ─────────────────────────────────────────────────────
# Write
with open("config.json", "w", encoding="utf-8") as f:
    json.dump(user, f, indent=2, ensure_ascii=False)

# Read
with open("config.json", "r", encoding="utf-8") as f:
    config = json.load(f)   # load = load file

# pathlib shortcut
Path("config.json").write_text(json.dumps(user, indent=2), encoding="utf-8")
config = json.loads(Path("config.json").read_text(encoding="utf-8"))

# ── Handling non-serialisable types ──────────────────────────────────────────
def json_serialiser(obj):
    """Custom serialiser for types that json module cannot handle."""
    if isinstance(obj, datetime):
        return obj.isoformat()
    raise TypeError(f"Type {type(obj)} not serialisable")

data = {"name": "Event", "timestamp": datetime.now()}
json_str = json.dumps(data, default=json_serialiser)
# {"name": "Event", "timestamp": "2025-08-06T14:30:00.000000"}

Working with .env Files #

# .env file format:
# DATABASE_URL=postgresql://localhost/blogdb
# SECRET_KEY=your-secret-key-here
# DEBUG=true
# ALLOWED_ORIGINS=http://localhost:5173,http://localhost:3000

# ── python-dotenv (install: pip install python-dotenv) ────────────────────────
from dotenv import load_dotenv
import os

# Load .env into os.environ — call early, before other imports
load_dotenv()   # looks for .env in current directory
load_dotenv(".env.local")          # load from specific path
load_dotenv(override=True)         # override existing env vars

db_url = os.getenv("DATABASE_URL", "postgresql://localhost/dev")
debug  = os.getenv("DEBUG", "false").lower() == "true"
port   = int(os.getenv("PORT", "8000"))

# ── pydantic-settings (recommended for FastAPI) ───────────────────────────────
from pydantic_settings import BaseSettings

class Settings(BaseSettings):
    database_url:  str  = "postgresql://localhost/blogdb"
    secret_key:    str  = "changeme"
    debug:         bool = False
    port:          int  = 8000
    allowed_origins: list[str] = ["http://localhost:5173"]

    model_config = {"env_file": ".env", "env_file_encoding": "utf-8"}

settings = Settings()   # reads from .env AND environment variables automatically
print(settings.database_url)

# ── .env file for different environments ─────────────────────────────────────
# .env                  ← development defaults (NOT committed to git)
# .env.example          ← template with dummy values (committed to git)
# .env.test             ← test database settings
# .env.production       ← NEVER committed — only on the server

Complete FastAPI CSV Upload Endpoint #

from fastapi import FastAPI, UploadFile, File, HTTPException
from pydantic import BaseModel, EmailStr
from typing import List
from io import StringIO
import csv

app = FastAPI()

class UserImportRow(BaseModel):
    name:  str
    email: str
    role:  str = "user"

class ImportResult(BaseModel):
    imported: int
    errors:   List[dict]

@app.post("/users/import", response_model=ImportResult)
async def import_users(file: UploadFile = File(...)):
    # Validate file type
    if not file.filename.endswith(".csv"):
        raise HTTPException(400, "Only CSV files are accepted")

    # Read and decode
    content = await file.read()
    try:
        text = content.decode("utf-8")
    except UnicodeDecodeError:
        raise HTTPException(400, "File must be UTF-8 encoded")

    # Parse CSV
    reader    = csv.DictReader(StringIO(text))
    imported  = 0
    row_errors = []

    for row_num, row in enumerate(reader, start=2):   # start=2 (row 1 is header)
        try:
            # Validate using Pydantic
            user_data = UserImportRow(
                name  = row.get("name", "").strip(),
                email = row.get("email", "").strip().lower(),
                role  = row.get("role", "user").strip(),
            )
            # TODO: save to database
            imported += 1
        except Exception as e:
            row_errors.append({"row": row_num, "error": str(e), "data": dict(row)})

    return ImportResult(imported=imported, errors=row_errors)

Common Mistakes #

Mistake 1 — Missing newline=”” when opening CSV files #

❌ Wrong — universal newline mode interferes with csv module:

with open("data.csv", "r") as f:   # missing newline="" and encoding!
    reader = csv.reader(f)
    # May produce empty rows on Windows files

✅ Correct:

with open("data.csv", "r", encoding="utf-8", newline="") as f:   # ✓
    reader = csv.DictReader(f)

Mistake 2 — Serialising datetime with json.dumps without a custom serialiser #

❌ Wrong — TypeError on datetime objects:

data = {"created": datetime.now()}
json.dumps(data)   # TypeError: Object of type datetime is not JSON serializable

✅ Correct — use the default parameter:

json.dumps(data, default=lambda o: o.isoformat() if isinstance(o, datetime) else str(o))

Mistake 3 — Committing .env with real secrets to git #

❌ Wrong — .env with production credentials committed:

git add .env   # exposes SECRET_KEY, DATABASE_URL to anyone with repo access

✅ Correct — commit only .env.example with dummy values, add .env to .gitignore.

Quick Reference #