SQL querying and updating a global variable in callback function isn't working - Broken pipe

[eloiup]

Hi,

I am having an issue with dash when trying to do live updates

The example at https://plot.ly/dash/live-updates updates simple text components and the graph directly.

What i would like to do is to update a dataframe, on which i produce the graphs/texts through other callbacks such as radio buttons and dropdowns.

The live update component works perfectly well for simple updates (changing text/components), just as in the example.

But when I try to set up a new dataframe, through retrieving some data from a sql database periodically, it crashes. They way I do it is the following:

Make a callback:

• input: interval component
• output: some text update

Within the function that feeds on the callback:

• call global variable, dataframe
• run connector/cursor retrieve data from sql database
• update dataframe
• update text/return html.span text

note: the connector/cursor code is fine, it works perfectly outside the function
note2: the dash works perfectly without the connector/cursor/dataframe update

error message:

----------------------------------------
Exception happened during processing of request from ('127.0.0.1', 44154)
----------------------------------------
----------------------------------------
Traceback (most recent call last):
  File "/home/ubuntu/anaconda2/lib/python2.7/SocketServer.py", line 290, in _handle_request_noblock
    self.process_request(request, client_address)
  File "/home/ubuntu/anaconda2/lib/python2.7/SocketServer.py", line 318, in process_request
    self.finish_request(request, client_address)
  File "/home/ubuntu/anaconda2/lib/python2.7/SocketServer.py", line 331, in finish_request
    self.RequestHandlerClass(request, client_address, self)
  File "/home/ubuntu/anaconda2/lib/python2.7/SocketServer.py", line 654, in __init__
    self.finish()
  File "/home/ubuntu/anaconda2/lib/python2.7/SocketServer.py", line 713, in finish
    self.wfile.close()
  File "/home/ubuntu/anaconda2/lib/python2.7/socket.py", line 283, in close
    self.flush()
  File "/home/ubuntu/anaconda2/lib/python2.7/socket.py", line 307, in flush
    self._sock.sendall(view[write_offset:write_offset+buffer_size])
error: [Errno 32] Broken pipe
Traceback (most recent call last):

any hints/advices? is what i am trying to do feasable?
or shld i have a graph update function/callback instead of trying to update the dataframe?

ty

[wtluke]

+1. I described a similar issue here

[chriddyp]

Thanks for reporting @eloiup and @wtluke ! I just tried to replicate this but was unable to. Here is a simple, self-contained SQL example that uses SQLite (it generates the SQLite database on start)

import dash
import dash_core_components as dcc
import dash_html_components as html
import pandas as pd

from sqlalchemy import create_engine

# Create a simple database
engine = create_engine('sqlite:///sample.db')
df = pd.DataFrame({
    'a': [1, 2, 3, 4, 5, 6],
    'b': ['x', 'y', 'x', 'x', 'z', 'y']
})
df.to_sql('dataframe', engine, if_exists='replace')

# Dash
def generate_table(dataframe, max_rows=10):
    return html.Table(
        # Header
        [html.Tr([html.Th(col) for col in dataframe.columns])] +

        # Body
        [html.Tr([
            html.Td(dataframe.iloc[i][col]) for col in dataframe.columns
        ]) for i in range(min(len(dataframe), max_rows))]
    )


app = dash.Dash()
app.layout = html.Div([
    dcc.Dropdown(
        id='dropdown',
        options=[{'label': i, 'value': i} for i in df.b.unique()],
        value='x',
        clearable=False
    ),
    html.Div(id='table-container')
])


@app.callback(
    dash.dependencies.Output('table-container', 'children'),
    [dash.dependencies.Input('dropdown', 'value')])
def sql(value):
    dff = pd.read_sql_query(
        'SELECT a, b FROM dataframe WHERE b = "{}"'.format(value),
        engine
    )
    return generate_table(dff)

if __name__ == '__main__':
    app.run_server(debug=True)

[chriddyp]

It would be helpful to get a little bit more info:

• Are you on mac or windows? I've seen these "broken pipe" issues but only on windows. See Serving large files locally (JS bundles) on windows machines in flask sometimes doesn't work #56
• Is the file / query particularly big?
• Can you create an example that I can run on my machine? i.e. with a public database (or, as above, initialize a sample database using pandas).

Thanks for the help! This is a weird issue for sure

[eloiup]

hi!

1.i am on windows.
2. its not that big, it shld take about 1 minute to run.
3. yes i might (will try it out soon)

its kind of different from the example you gave here.
you are updating a graph (table) from a dropdown select with the query. which is similar to the satelite example.

since my query takes a while to run (1-2 minutes), and it only needs to be run every 1-2 days, what i was trying to achieve was to update a global variable (dataframe) every X interval instead. hence the issue might be related to the different approach.

[chriddyp]

Ahhh, thanks for clarifying @eloiup ! Yes, global variables aren't safe in Dash as not all of the processes share the same memory. As a rule of thumb, it's not safe to change any variables outside of the scope of a callback function. You can read from global variables, but you can't write to them.

In the meantime, I recommend using Flask-Caching which will safely share data across processes. You can also set a time-based expiry so that the query will re-run every day or every 2 days. There is a tutorial on caching here: https://plot.ly/dash/plugins.

Let me know if that works!

[eloiup]

yeah i am running/killing the script periodically to update the data, not the optimal solutions but works. tried reading the plugins link (thanks) but i am not much of a dev myself (data scientist / BI analyst).

[ankitgoel888]

Even I tried to update a global variable from callback but it is failing consistently; I will try out flask caching

[chriddyp]

Is anyone still seeing this? I'm going to close due to inactivity, but feel free to reopen