Sign in Get started

Code review · Python

Review a CSV Export Endpoint

Medium Code review

Finance asked for a CSV export of all orders. The endpoint below works on the staging database (2,000 orders). Production has 8 million orders.

Review it: list the problems, why each matters at production scale, and how to fix it.

The code to review

There are 5 issues to find.

from fastapi import APIRouter, Depends
from fastapi.responses import Response
from sqlalchemy.orm import Session

from app.db import get_db
from app.models import Customer, Order

router = APIRouter()


@router.get("/reports/orders.csv")
def export_orders(db: Session = Depends(get_db)):
    orders = db.query(Order).all()
    csv = "id,customer,total,created_at\n"
    for order in orders:
        customer = db.query(Customer).get(order.customer_id)
        csv += f"{order.id},{customer.name},{order.total},{order.created_at}\n"
    return Response(csv, media_type="text/csv")

Write your review

Loading the editor… (JavaScript is required to run code in your browser.)

The issues and how to fix them

Show the issues

1. Loads the whole table into memory

Critical, line 13-18

.all() materialises 8 million ORM objects, and the full CSV string is built in memory too. That is gigabytes of RAM in one web worker, which will be killed or will stall every other request.

Fix: Stream: iterate in batches (yield_per(5000) or keyset pagination) and return a StreamingResponse that writes rows as they are read. For very large exports, generate the file in a background job and email a download link.

2. N+1 queries

Major, line 16

One extra query per order to fetch the customer: 8 million round trips.

Fix: Join in the query (select(Order.id, Customer.name, ...).join(Customer)) so each row arrives complete.

3. Hand-built CSV breaks on commas and quotes

Major, line 17

A customer name like Smith, Jones & Co or one containing a quote shifts the columns. Values starting with =, +, - or @ are also executed as formulas when the file is opened in Excel (CSV injection).

Fix: Use the csv module's writer, which quotes fields correctly, and prefix formula-like values with a single quote.

4. No authorization

Major, line 11-12

Anyone who can reach the endpoint downloads every order with customer names.

Fix: Require an authenticated user with a finance or admin role.

5. Quadratic string building and a long request

Minor, line 14-17

csv += in a loop can copy the growing string repeatedly. Even when streamed, an export this size will outlive typical proxy timeouts (often 60 s).

Fix: Write to a buffer or stream, and move large exports to an asynchronous job.

This exercise tests whether you think about data size. The strongest answers ask "how many rows in production?" first, then fix memory (stream or batch), query count (join), and correctness of the format, and suggest an asynchronous export for very large data.

Fixed version

import csv
import io

from fastapi import APIRouter, Depends
from fastapi.responses import StreamingResponse
from sqlalchemy import select
from sqlalchemy.orm import Session

from app.auth import require_role
from app.db import get_db
from app.models import Customer, Order

router = APIRouter()


def _safe(value) -> str:
    text = str(value)
    return "'" + text if text[:1] in ("=", "+", "-", "@") else text  # CSV injection guard


@router.get("/reports/orders.csv", dependencies=[Depends(require_role("finance"))])
def export_orders(db: Session = Depends(get_db)):
    query = (select(Order.id, Customer.name, Order.total, Order.created_at)
             .join(Customer, Customer.id == Order.customer_id)
             .order_by(Order.id)
             .execution_options(yield_per=5000))

    def rows():
        buf = io.StringIO()
        writer = csv.writer(buf)
        writer.writerow(["id", "customer", "total", "created_at"])
        for row in db.execute(query):
            writer.writerow([row.id, _safe(row.name), row.total, row.created_at.isoformat()])
            if buf.tell() > 64_000:
                yield buf.getvalue()
                buf.seek(0)
                buf.truncate()
        yield buf.getvalue()

    return StreamingResponse(rows(), media_type="text/csv",
                             headers={"Content-Disposition": "attachment; filename=orders.csv"})

Follow-up questions

  • How would you let finance export 50 million rows without affecting the production database? (read replica, warehouse, async job)