Python Forum
Flask export/upload database table in cvs/xlsx format
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Flask export/upload database table in cvs/xlsx format
#1
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')
Reply
#2
Use cw.writerows on the query.
If you iterate manually with a for-loop, you must add row by row with cw.writerow (without s at the end).
@admin_role.route('/download')
@login_required
def post():
    si = StringIO()
    cw = csv.writer(si)
    cw.writerows(Book.query.all())
    output = make_response(si.getvalue())
    output.headers["Content-Disposition"] = "attachment; filename=export.csv"
    output.headers["Content-type"] = "text/csv"
    return output
For safety reasons you should limit the query and use a paginate function.
Use a cache if you want to speed up requests.
Almost dead, but too lazy to die: https://sourceserver.info
All humans together. We don't need politicians!
Reply
#3
Thank you DeaD_EyE. I tried the solution you proposed and I still have an error.

This is the error I get

 cw.writerows(Book.query.all())
_csv.Error: iterable expected, not Book
Reply
#4
Then use a list to consume the iterator.

cw.writerows(list(Book.query.all()))
If you have many books, then chunking is better.
Then use sqlalchemy.orm.query.Query.yield_per.

Quote:Yield only count rows at a time.

The purpose of this method is when fetching very large result sets (> 10K rows), to batch results in sub-collections and yield them out partially, so that the Python interpreter doesn’t need to declare very large areas of memory which is both time consuming and leads to excessive memory use. The performance from fetching hundreds of thousands of rows can often double when a suitable yield-per setting (e.g. approximately 1000) is used, even with DBAPIs that buffer rows (which are most).

for chunks in Book.query.yield_per(100):
    cw.writerows(chunks)
Almost dead, but too lazy to die: https://sourceserver.info
All humans together. We don't need politicians!
Reply
#5
@DeaD_EyE Unfortunately, the same error appears

    cw.writerows(list(Book.query.all()))
_csv.Error: iterable expected, not Book
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Want to scrape a table data and export it into CSV format tahir1990 9 2,519 Oct-22-2019, 08:03 AM
Last Post: buran
  MySQL Database Flask maurosmartins 0 874 Oct-03-2019, 10:56 AM
Last Post: maurosmartins
  Using flask to add data to sqlite3 table with PRIMARY KEY catafest 1 2,654 Sep-09-2019, 07:00 AM
Last Post: buran
  Read Save RadioButtons from Database in Python Flask Webpage Gary8877 0 4,716 Apr-11-2019, 12:33 AM
Last Post: Gary8877
  How to format a datetime MySQL database field to local using strftime() nikos 6 1,834 Feb-24-2019, 06:32 PM
Last Post: nikos
  flask sqlite jinja accessing and updating database help pascale 5 2,102 Feb-11-2019, 03:49 PM
Last Post: pascale
  how i save the html form to flask database mebaysan 1 5,154 Feb-07-2019, 12:56 AM
Last Post: snippsat
  CRUD performing but output not shown in table format in flask lunchcook 2 2,701 Oct-23-2018, 03:42 PM
Last Post: lunchcook
  Buttons in table. Which row was selected to export. KirkmanJ 12 3,829 Jul-09-2018, 08:56 AM
Last Post: KirkmanJ
  General advice web application - flask - database - cms bashage 6 3,147 Jan-31-2018, 06:07 PM
Last Post: frostbite

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020