I'm trying to make a code that when user press the button, it exports excel file-CodePudding

I don't know why, but it says that i can't iterate thru db model class 'Claim'. Does anyone have idea how to fix it?

Here is my code:

@auth.route('/download')
def excel_downlaod():
    # Function is defined somewhere else
    data = db.session.query(Claim).all()

    # Convert result set to pandas date frame and add columns
    df = pd.DataFrame((list(t) for t in data), columns=('ID', 'Email', 'Date', 'Name', 'Adress', 'Report'))

    # Creating output and writer (pandas excel writer)
    out = io.BytesIO()
    writer = pd.ExcelWriter(out, engine='xlsxwriter')

    # Export data frame to excel
    df.to_excel(excel_writer=writer, index=False, sheet_name='Claims')
    writer.save()
    writer.close()

    # Flask create response
    r = make_response(out.getvalue())

    # Defining correct excel headers
    r.headers["Content-Disposition"] = "attachment; filename=export.xlsx"
    r.headers["Content-type"] = "application/x-xls"

    # Return response
    return r

and class:

class Claim(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(50), nullable=False)
    date = db.Column(db.DateTime(timezone=True), default=func.now())
    name = db.Column(db.String(30), nullable=False)
    adress = db.Column(db.String(30), nullable=False)
    report = db.Column(db.String(1000), nullable=False)

CodePudding user response：

You can save the step of converting the results of the database query into a DataFrame by executing the query directly in pandas with read_sql.

The following example shows you how to generate the file and serve it with send_file.

from flask import send_file

# ...

@auth.route('/download')
def excel_download():
    df = pd.read_sql(Claim.query.statement, db.session.get_bind())

    buffer = io.BytesIO()
    with pd.ExcelWriter(buffer, engine='xlsxwriter') as writer:
        df.to_excel(writer, index=False, sheet_name='Claims')
    buffer.seek(0)

    return send_file(
        buffer, 
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', 
        download_name='export.xlsx'
    )