from sqlalchemy import select
from .models import Order, db
def stream_orders(batch_size=1000):
stmt = (
select(
Order.id,
Order.customer_email,
Order.total_cents,
Order.status,
Order.created_at,
)
.order_by(Order.id)
.execution_options(stream_results=True)
)
result = db.session.execute(stmt).yield_per(batch_size)
for row in result:
yield (
row.id,
row.customer_email,
row.total_cents / 100.0,
row.status,
row.created_at.isoformat(),
)
import csv
import io
from flask import Blueprint, Response, stream_with_context
from .order_repository import stream_orders
bp = Blueprint("orders_export", __name__)
HEADER = ["id", "customer_email", "total", "status", "created_at"]
def generate_rows():
buffer = io.StringIO()
writer = csv.writer(buffer)
writer.writerow(HEADER)
yield buffer.getvalue()
buffer.seek(0)
buffer.truncate(0)
for order in stream_orders():
writer.writerow(order)
yield buffer.getvalue()
buffer.seek(0)
buffer.truncate(0)
@bp.route("/exports/orders.csv")
def export_orders():
headers = {
"Content-Disposition": "attachment; filename=orders.csv",
"X-Accel-Buffering": "no", # let nginx flush chunks immediately
}
return Response(
stream_with_context(generate_rows()),
mimetype="text/csv",
headers=headers,
)
from flask import Flask
from .models import db
from .orders_export import bp as exports_bp
def create_app():
app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = (
"postgresql+psycopg://app:app@localhost/shop"
)
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
db.init_app(app)
app.register_blueprint(exports_bp)
return app
app = create_app()
if __name__ == "__main__":
# threaded so streaming one client does not block others
app.run(threaded=True)
Exporting a large table as CSV is a classic place where a naive implementation quietly breaks in production: building the entire file in memory (or a list of rows) works fine for a thousand rows and then falls over at a few million, exhausting RAM and blocking the worker while the client waits for the whole payload. This snippet shows the streaming alternative, where Flask sends bytes to the client as they are produced instead of buffering the full response.
The key idea lives in orders_export.py. The route returns a Flask Response wrapping a Python generator, generate_rows, rather than a string. Flask (via Werkzeug) recognizes an iterable body and streams each yielded chunk down the socket, so memory stays flat regardless of table size. The generator uses csv.writer against a small in-memory StringIO buffer as a per-row line formatter: after writing a row it reads the buffer, yields the text, then truncates and rewinds the buffer so it never grows. Setting Content-Disposition: attachment and a filename turns the response into a browser download, and mimetype='text/csv' labels it correctly.
Crucially, the row source is itself lazy. stream_orders in order_repository.py uses SQLAlchemy's yield_per so the driver fetches rows in batches instead of materializing the full result set. This is what makes the whole chain O(1) in memory — a lazy DB cursor feeding a lazy generator feeding a streaming response. The repository yields plain tuples so the formatting layer stays decoupled from ORM objects.
The app setup in app.py wraps the generator in stream_with_context, which keeps the Flask application and request context alive for the duration of iteration. Without it, the database session bound to the request context could be torn down before the generator finishes producing rows, raising errors deep inside the stream.
A notable trade-off: once the first byte is sent, the HTTP status is already 200, so an error mid-stream cannot cleanly become a 500 — it can only truncate the download. Streaming also disables Content-Length, so clients see chunked transfer without a progress total. For very large, read-mostly exports those costs are usually worth the flat memory profile and the near-instant time-to-first-byte.
Related snips
export interface RetryOptions {
retries: number;
baseMs: number;
maxMs: number;
signal?: AbortSignal;
onRetry?: (attempt: number, delay: number, err: unknown) => void;Exponential backoff with jitter for retries
package dbutil
import (
"context"
"github.com/jackc/pgconn"Retry Postgres serialization failures with bounded attempts
require "csv"
class PeopleCsvStream
include Enumerable
HEADERS = %w[id full_name email signed_up_at plan].freezeResilient CSV Export as a Streamed Response
class AddSettingsToAccounts < ActiveRecord::Migration[7.1]
disable_ddl_transaction!
def change
add_column :accounts, :settings, :jsonb, null: false, default: {}Postgres JSONB Partial Index for Feature Flags
package deps
import (
"net"
"net/http"
"time"HTTP client tuned for production: timeouts, transport, and connection reuse
module EmailNormalization
extend ActiveSupport::Concern
included do
attr_accessor :soft_warningsSoft Validation: Normalize + Validate Email
Share this code
Here's the card — post it anywhere.
