Create CSV on-the-fly with Flask, stream_with_context and SQLAlchemy using generator

views.py

from datetime import datetime

from flask import current_app, stream_with_context, Response
from flask_blueprint_acquisition import current_blueprint
from sqlalchemy.sql import compiler

from .blueprints import base_blueprint
from .models import Acquisition, Package, Product, User


def compile_query(query, param_list):
    '''
    Replace the query params with values
    '''
    dialect = query.session.bind.dialect
    statement = query.statement
    comp = compiler.SQLCompiler(dialect, statement)
    comp.compile()
    enc = dialect.encoding

    return (comp.string.encode(enc) % param_list).decode(enc)


def parse_field(field, translate=False):
    if isinstance(field, datetime):
        return field.isoformat()

    if field is None:
        return '-'

    if translate:
        return _(field)

    return field.encode('utf-8')


def generate_csv():
    config = current_blueprint.config

    query = Acquisition.query.filter(Acquisition.package_id, Package.id).join(User).join(Package).join(Product)
    query = query.add_columns('user.msisdn AS msisdn',
                              'user.email AS email',
                              'product.name AS product_name',
                              'package.name AS package_name',
                              'package.periodicity AS periodicity',
                              'package.price AS price')
    query = query.filter(Product.carrier == config['CARRIER'])

    result = current_app.db.engine.execute(compile_query(query, list(config['CARRIER'])))

    yield ', '.join(['MSISDN', 'Nombre del servicio', 'Nombre del Paquete', 'Vigencia', 'Correo electronico',
                     'Fecha de Activacion', 'Tipo de Proceso', 'Costo del servicio', 'Canal de Activacion',
                     'Estado del servicio', 'Ultima fecha de renovacion',
                     'Fecha de cancelacion', 'Canal de desafiliacion']) + '\n'

    for row in result:
      yield ', '.join([parse_field(row['msisdn']),
                      parse_field(row['product_name']),
                      parse_field(row['package_name']),
                      parse_field(row['periodicity']),
                      parse_field(row['email']),
                      parse_field(row['created']),
                      parse_field(row['state']),
                      parse_field(row['price']),
                      "%s-%s" % (parse_field(row['activation_source']), parse_field(row['activation_medium'])),
                      parse_field(row['state']),
                      parse_field(row['last_successful_charge']),
                      parse_field(row['cancelation_date']),
                      "%s-%s" % (parse_field(row['cancelation_source']), parse_field(row['cancelation_medium']))]) + '\n'


@base_blueprint.route('/download-report')
def download_report():
    return Response(stream_with_context(generate_csv()),
                    mimetype='text/csv',
                    headers={'Content-Disposition': 'attachment; filename=report_peru.csv'})