Bottom Page

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')
Quote
#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.
My code examples are always for Python >=3.6.0
Almost dead, but too lazy to die: https://sourceserver.info
All humans together. We don't need politicians!
Quote
#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
Quote
#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)
My code examples are always for Python >=3.6.0
Almost dead, but too lazy to die: https://sourceserver.info
All humans together. We don't need politicians!
Quote
#5
@DeaD_EyE Unfortunately, the same error appears

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

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  python 3.7 on windows using flask and flask-sqlalchemy. Alpy 1 461 Jul-23-2020, 10:17 PM
Last Post: mlieqo
  Want to scrape a table data and export it into CSV format tahir1990 9 840 Oct-22-2019, 08:03 AM
Last Post: buran
  MySQL Database Flask maurosmartins 0 393 Oct-03-2019, 10:56 AM
Last Post: maurosmartins
  Using flask to add data to sqlite3 table with PRIMARY KEY catafest 1 2,037 Sep-09-2019, 07:00 AM
Last Post: buran
  Read Save RadioButtons from Database in Python Flask Webpage Gary8877 0 2,370 Apr-11-2019, 12:33 AM
Last Post: Gary8877
  How to format a datetime MySQL database field to local using strftime() nikos 6 783 Feb-24-2019, 06:32 PM
Last Post: nikos
  flask sqlite jinja accessing and updating database help pascale 5 988 Feb-11-2019, 03:49 PM
Last Post: pascale
  how i save the html form to flask database mebaysan 1 2,704 Feb-07-2019, 12:56 AM
Last Post: snippsat
  sqlalchemy DataTables::"No data available in table" when using self-joined table Asma 0 895 Nov-22-2018, 02:46 PM
Last Post: Asma
  CRUD performing but output not shown in table format in flask lunchcook 2 1,566 Oct-23-2018, 03:42 PM
Last Post: lunchcook

Forum Jump:


Users browsing this thread: 1 Guest(s)