Flask sessions and ByteIO excel_export

Dash Python
1

Hi there,
In this code snippet I’m trying to create multiple user sessions, save an excel file using io.ByteIO() so multiple user can generate create multiple files and save into their machines.
I don’t know much about, flask sessions and how to automatically initialize session variables when entering the home page, so I’m using a dumb callback function.

import warnings
from packs_simulador_v13 import *
import time
from datetime import datetime
import numpy as np
import pandas as pd
import io
import os
import flask
from flask import session
import dash
import dash_auth
import dash_core_components as dcc
import dash_html_components as html
from dash.dependencies import Input, Output, State
from dash.exceptions import PreventUpdate
import dash_table
from dash_google_auth import GoogleOAuth
def create_output():
    output_excel = io.BytesIO()
    d = {"col1": [1, 2,3], "col2": [3, 4,5]}
    df = pd.DataFrame(data=d)
    writer = pd.ExcelWriter(output_excel, engine='xlsxwriter')
    df.to_excel(writer, startrow = 0, merge_cells = False, sheet_name = "Sheet_1")
    workbook  = writer.book
    worksheet = writer.sheets['Sheet_1']

    # Create a chart object.
    chart = workbook.add_chart({'type': 'column'})

    # Configure the series of the chart from the dataframe data.
    chart.add_series({'values': '=Sheet_1!$B$2:$B$8'})
    # Insert the chart into the worksheet.
    worksheet.insert_chart('D2', chart)
    writer.save()

    #the writer has done its job
    writer.close()

    #go back to the beginning of the stream
    output_excel.seek(0)
    return output_excel

server = flask.Flask(__name__)
external_stylesheets = ["./static/stylesheet.css"]
external_css = ["https://cdnjs.cloudflare.com/ajax/libs/skeleton/2.0.4/skeleton.min.css",
                "https://fonts.googleapis.com/css?family=Raleway:400,400i,700,700i",
                "https://fonts.googleapis.com/css?family=Product+Sans:400,400i,700,700i"]
app = dash.Dash(__name__,
                 external_stylesheets=external_stylesheets,
                 url_base_pathname='/',
                  server=server)
server.config['SECRET_KEY'] = os.getenv('SECRET_KEY') or \
    'e5ac358c-f0bf-11e5-9e39-d3b532c10a28'
server.config["SESSION_TYPE"] = 'null'
@server.route("/")
def MyDashApp():
    """
    Rota do Flask para passar pelo Google Auth
    """
    print("sessão iniciada")
    session["text"] = "text"
    #init_session()
    return app.index()

app.layout = html.Div([
        html.A(
                "Download mapa: {}".format("simulacao"),
                href="/download_output_excel",
                style={"display": "block"}
                ),
        html.Div(children="",id="hidden1"),
        html.Div(children="",id="hidden2")
        ])
#session["excel_output"] = create_output()
@app.callback( Output("hidden2","children"),
              [Input("hidden1","children")]
)
def test(banana):
    session["excel_output"] = create_output()
    return ""

@app.server.route("/download_output_excel")
def xslx_output():
    #global output_excel
    # finally return the file
    return flask.send_file(
        session["excel_output"] ,
        attachment_filename="test" + ".xlsx",
        as_attachment=True
    )


if __name__ == "__main__":
    app.run_server(port=8001,debug=True)

I’m getting the error :

Traceback (most recent call last):
  File " \Python\Python37\lib\site-packages\flask\app.py", line 2309, in __call__
    return self.wsgi_app(environ, start_response)
  File " \Python\Python37\lib\site-packages\flask\app.py", line 2295, in wsgi_app
    response = self.handle_exception(e)
  File " \Python\Python37\lib\site-packages\flask\app.py", line 1741, in handle_exception
    reraise(exc_type, exc_value, tb)
  File " \Python\Python37\lib\site-packages\flask\_compat.py", line 35, in reraise
    raise value
  File " \Python\Python37\lib\site-packages\flask\app.py", line 2292, in wsgi_app
    response = self.full_dispatch_request()
  File " \Python\Python37\lib\site-packages\flask\app.py", line 1816, in full_dispatch_request
    return self.finalize_request(rv)
  File " \Python\Python37\lib\site-packages\flask\app.py", line 1833, in finalize_request
    response = self.process_response(response)
  File " \Python\Python37\lib\site-packages\flask\app.py", line 2114, in process_response
    self.session_interface.save_session(self, ctx.session, response)
  File " \Python\Python37\lib\site-packages\flask\sessions.py", line 375, in save_session
    val = self.get_signing_serializer(app).dumps(dict(session))
  File " \Python\Python37\lib\site-packages\itsdangerous\serializer.py", line 166, in dumps
    payload = want_bytes(self.dump_payload(obj))
  File " \Python\Python37\lib\site-packages\itsdangerous\url_safe.py", line 42, in dump_payload
    json = super(URLSafeSerializerMixin, self).dump_payload(obj)
  File " \Python\Python37\lib\site-packages\itsdangerous\serializer.py", line 133, in dump_payload
    return want_bytes(self.serializer.dumps(obj, **self.serializer_kwargs))
  File " \Python\Python37\lib\site-packages\flask\json\tag.py", line 296, in dumps
    return dumps(self.tag(value), separators=(',', ':'))
  File " \Python\Python37\lib\site-packages\flask\json\__init__.py", line 179, in dumps
    rv = _json.dumps(obj, **kwargs)
  File " \Python\Python37\lib\json\__init__.py", line 238, in dumps
    **kw).encode(obj)
  File " \Python\Python37\lib\json\encoder.py", line 199, in encode
    chunks = self.iterencode(o, _one_shot=True)
  File " \Python\Python37\lib\json\encoder.py", line 257, in iterencode
    return _iterencode(o, 0)
  File " \Python\Python37\lib\site-packages\flask\json\__init__.py", line 81, in default
    return _json.JSONEncoder.default(self, o)
  File " \Python\Python37\lib\json\encoder.py", line 179, in default
    raise TypeError(f'Object of type {o.__class__.__name__} '
TypeError: Object of type BytesIO is not JSON serializable
2

For my excel file url’s to work properly, I had a callback that would update the urls such that I could pass in variables. Something like the following (modified for your example)

retval = '/download_output_excel/?excel_output={}'.format( session['excel_output'] )

Then, in the app.server.route, utiilze flask to extract the args:

excel_arg = flask.request.args.get('excel_output')

For returning the excel file, here is a snippet that works for me (it would be contained within the app.server.route: Note that the wb variable is an excel workbook created via the openpyxl.workbook function (i had more luck working with openpyxl vs xlsxwriter).

# Save/Stream WB
with NamedTemporaryFile() as tmp:
    wb.save(tmp.name)
    str_io = io.BytesIO(tmp.read())

return send_file(str_io,
                 attachment_filename='your_filename.xlsx',
                 as_attachment=True)

For the above example to work, you will need the following imports at the top of your code:

from flask import send_file
from tempfile import NamedTemporaryFile
3

Hi there,
I already can export an excel, but I need to generate multiple excel files output for each session, but I can’t store it in a Global Variable or even in the Flask.session.

4

Gotcha…I got confused by your error and probably went down the wrong path. Unfortunately, based on my current understanding, you can only return one file the the app.server.route flask functionality. If you needed to provide more than one file (and not combine them into one w/ each being their own worksheet), then you need to upload the files to a datastore (AWS S3 bucket)…your app could then periodically poll this data store to present the files for downloading.

5

No, what I want to do is the following:

  • User enter the dash, choose some parameters and do a simulation
  • Then it’s displayed some analytics about the simulation and the user is able to download that simulation results in excel format to his Computer.

What happens is that if if I store the io.BytesIO() of the excel in a Global Variable, when multiple users acess the dash at the same time, they get the last simulation calculated.

What I tried is to use from flask import session as a session “global variable” but I can’t store “io.BytesIO()” or “io.StringIO()” in session, getting the error:

  File " \Python\Python37\lib\json\encoder.py", line 179, in default
    raise TypeError(f'Object of type {o.__class__.__name__} '
TypeError: Object of type BytesIO is not JSON serializable
6

You don’t store the entire session, you should store a key that can be used to look up the excel file that you generated.