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
  Flask: sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) database is locked pythonpaul32 1 2,032 Apr-04-2023, 07:44 AM
Last Post: Larz60+
  Flask and SQLAlchemy question: Database is being created but tables aren't adding pythonpaul32 3 4,418 Feb-07-2023, 10:48 AM
Last Post: pythonpaul32
  Flask/non-flask database sharing MorganSamage 2 1,136 Feb-03-2023, 12:05 PM
Last Post: MorganSamage
  Want to scrape a table data and export it into CSV format tahir1990 9 5,134 Oct-22-2019, 08:03 AM
Last Post: buran
  MySQL Database Flask maurosmartins 0 1,790 Oct-03-2019, 10:56 AM
Last Post: maurosmartins
  Using flask to add data to sqlite3 table with PRIMARY KEY catafest 1 3,705 Sep-09-2019, 07:00 AM
Last Post: buran
  Read Save RadioButtons from Database in Python Flask Webpage Gary8877 0 7,114 Apr-11-2019, 12:33 AM
Last Post: Gary8877
  How to format a datetime MySQL database field to local using strftime() nikos 6 3,720 Feb-24-2019, 06:32 PM
Last Post: nikos
  flask sqlite jinja accessing and updating database help pascale 5 4,087 Feb-11-2019, 03:49 PM
Last Post: pascale
  how i save the html form to flask database mebaysan 1 7,246 Feb-07-2019, 12:56 AM
Last Post: snippsat

Forum Jump:

User Panel Messages

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