Python Flask download/upload database table in cvs/xlsx format

Question

I have a download button on my flask app and i am trying to add functionality that will allow user to download all data from books table locally in csv or excel format.

Another thing i would like to do is to upload excel or csv file and import the data in books table.

For download i have this

@admin_role.route('/download')
@login_required
def post():
    si = StringIO()
    cw = csv.writer(si)
    for book in Book.query.all():
        cw.writerows(book)
    output = make_response(si.getvalue())
    output.headers["Content-Disposition"] = "attachment; filename=export.csv"
    output.headers["Content-type"] = "text/csv"
    return output

But i have error TypeError: writerows() argument must be iterable

this is the model:

class Book(db.Model):
    """
    Create a Books table
    """

    __tablename__ = 'books'

    id = db.Column(db.Integer, primary_key=True)
    book_name = db.Column(db.String(60), index=True,unique=True)
    author = db.Column(db.String(200), index=True)
    quantity = db.Column(db.Integer)
    department_id = db.Column(db.Integer, db.ForeignKey('departments.id'))
    employees_id = db.Column(db.Integer, db.ForeignKey('employees.id'))
    publisher = db.Column(db.String(200))
    no_of_pgs = db.Column(db.Integer)
    pbs_year = db.Column(db.Integer)
    genre_id = db.Column(db.Integer, db.ForeignKey('genres.id'), nullable=False)
    read = db.Column(db.Enum('NO', 'YES'), default='NO')

    borrows = db.relationship('Borrow', backref='book',
                                lazy='dynamic')

score 2 · Answer 1 · answered Jun 16 '20 at 18:17

writerows is expecting an iterable, which is to say, a list, tuple, or other iterable object. Looking over your code, it's clear to me that one problem is, you don't know what columns you're writing. For example, suppose you wanted to export id, book_name, and author:

cw = csv.writer(si)
cw.writerow(['id', 'book_name', 'author'])    # Write header row
for book in Book.query.all():
    cw.writerows([str(book.id), book.book_name, book.author])

Perhaps more elegantly, you can avoid repeating yourself:

export_columns = ['id', 'book_name', 'author']

cw = csv.writer(si)
cw.writerow(export_columns)
for book in Book.query.all():
    cw.writerows([str(getattr(book, col)) for col in export_columns])

Note that I'm explicitly casting all columns to a string. If you have Unicode in your database, you'll need to figure out how you want to encode those strings, but that's another topic.

In terms of actually sending the file to the user, you'll want to use the Flask send_file function.

from flask import send_file

si.seek(0)
return send_file(si, attachment_filename='export.csv', as_attachment=True)

I tried the solution but it returns empty CSV. Do you maybe know why — Stevan Stankovic, Jun 17 '20 at 09:28
Is books returning rows? Alternatively, maybe call si.flush() after you finish?? I'm not sure. — Ken Kinder, Jun 17 '20 at 13:52
books are returning rows but for some reason code you proposed is not working. I also tried using flush() — Stevan Stankovic, Jun 17 '20 at 17:59
@Stevan Stankovic... I just see this answer because I am having the same issue. It works if the send_file line is changed by return send_file(BytesIO(si.read().encode('utf8')), attachment_filename="export.csv", as_attachment=True, ) — Daniel Lema, Dec 06 '21 at 16:30

score 0 · Answer 2 · answered Jul 12 '21 at 08:13

The following solution works for me :

from io import StringIO
import csv
# Download shops as CSV
@bp_dashboard.route('/shops', methods=['GET'])
@login_required
def shops():
    export_columns = ['type', 'name', 'comment']
    si = StringIO()
    cw = csv.writer(si, delimiter=' ', quotechar='|', quoting=csv.QUOTE_MINIMAL)
    cw.writerow(export_columns)
    for shops in shops.query.all():
        cw.writerow([str(getattr(shops, col)) for col in export_columns])
    output = make_response(si.getvalue())
    output.headers["Content-Disposition"] = "attachment; filename=shops.csv"
    output.headers["Content-type"] = "text/csv"
    return output

Python Flask download/upload database table in cvs/xlsx format

2 Answers2