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?

You should determine where the problem originates from. It could be sqlalchmey not returning data, or flask not handling it correclty. Try printing the output of Purchase.query.all in console to check that sqlalchemy is doing what its supposed to — Marcom
– Marcom, Commented Dec 2, 2014 at 11:30
My query returns a list of objects as expected from the console. — Patrick Beeson
– Patrick Beeson, Commented Dec 2, 2014 at 11:53
Kinda, but not using the technique described in the question. — Patrick Beeson
– Patrick Beeson, Commented Jan 24, 2015 at 18:22

Patrick Beeson · Accepted Answer · 2015-01-26 16:57:41Z

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)

user78110 · Accepted Answer · 2015-05-05 17:33:37Z

0

http://flask.pocoo.org/docs/0.10/patterns/streaming/#streaming-with-context

pretty much explains how to keep the context around

answered May 5, 2015 at 17:33

user78110

Comments

Manh Tai · Accepted Answer · 2015-05-23 11:26:54Z

0

Explain Ronny's answer: You should add stream_with_context() function to Response() for keeping the Context.

answered May 23, 2015 at 11:26

Manh Tai

3965 silver badges8 bronze badges

Collectives™ on Stack Overflow

How to stream CSV from Flask via sqlalchemy query?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related