How to stream CSV from Flask via sqlalchemy query?

Question

I'd like to stream a CSV from Flask using the technique they describe here:

from flask import Response

@app.route('/large.csv')
def generate_large_csv():
    def generate():
        for row in iter_all_rows():
            yield ','.join(row) + '\n'
    return Response(generate(), mimetype='text/csv')

I have a query from sqlalchemy that returns a list of Purchase objects. I'd like to write this to a CSV, ideally with customization over the attributes output to the file.

Unfortunately, I'm getting a blank CSV as output currently:

@app.route('/sales_export.csv')
@login_required
def sales_export():
    """ Export a CSV of all sales data """
    def generate():
        count = 0
        fieldnames = [
            'uuid',
            'recipient_name',
            'recipient_email',
            'shipping_street_address_1',
            'shipping_street_address_2',
            'shipping_city',
            'shipping_state',
            'shipping_zip',
            'purchaser_name',
            'purchaser_email',
            'personal_message',
            'sold_at'
        ]
        for i, row in enumerate(Purchase.query.all()):
            if i == 0:
                yield fieldnames
            csv = ','.join(row) + '\n'
            yield csv
    return Response(generate(), mimetype='text/csv')

Where am I going wrong?

Answer 1

Here's how I resolved this question. It doesn't use the streaming featured I was curious about originally. I would still be interested in a solution using that technique.

@app.route('/download')
@login_required
def download():
    "Export a CSV of all sales data"
    purchases = Purchase.query.all()
    csvfile = cStringIO.StringIO()
    headers = [
        'uuid',
        'recipient_name',
        'recipient_email',
        'shipping_street_address_1',
        'shipping_street_address_2',
        'shipping_city',
        'shipping_state',
        'shipping_zip',
        'purchaser_name',
        'purchaser_email',
        'personal_message',
        'sold_at',
        'coupon_used'
    ]
    rows = []
    for purchase in Purchase.query.all():
        rows.append(
            {
                'uuid': purchase.uuid,
                'recipient_name': purchase.recipient_name,
                'recipient_email': purchase.recipient_email,
                'shipping_street_address_1': purchase.shipping_street_address_1,
                'shipping_street_address_2': purchase.shipping_street_address_2,
                'shipping_city': purchase.shipping_city,
                'shipping_state': purchase.shipping_state,
                'shipping_zip': purchase.shipping_zip,
                'purchaser_name': purchase.purchaser_name,
                'purchaser_email': purchase.purchaser_email,
                'personal_message': purchase.personal_message,
                'sold_at': purchase.sold_at.strftime('%c'),
                'coupon_used': purchase.coupon_used
            }
        )
    writer = csv.DictWriter(csvfile, headers)
    writer.writeheader()
    for row in rows:
        writer.writerow(
            dict(
                (k, v.encode('utf-8') if type(v) is unicode else v) for k, v in row.iteritems()
            )
        )
    csvfile.seek(0)
    return send_file(csvfile, attachment_filename='sales_export.csv', as_attachment=True)