Looking for help with strange problem (tag I'd add if I could: mysql connector)

Dash Python
1

I’m trying to use the following:
python
dash
mysql
relayoutData (to modify y-axis zoom since dash doesn’t seem to do that for me)

I made a dash app that displays a single plot using some data I have stored in a mysql database. I have actually done this several times and so far, it has worked fine. But this one time, I’d like to try using relayoutData, as per this one of many examples I’ve found on the web: auto ranging y axis in plotly python - Stack Overflow

The problem I run into is that even if I don’t include any code beyond this (from the example):

@app.callback(Output('dashFig', 'figure'),
            [Input('dashFig', 'relayoutData')])
def rangesliderchg(relayoutData):
        return <some empty plot or something>

it will error out in three really weird ways:

mysql.connector.errors.DatabaseError: 2027 (HY000): Malformed packet

or:

double free or corruption (!prev) (…and the program exits)

or:

mysql.connector.errors.InternalError: Unread result found (…and sometimes it’ll draw my graph, usually it won’t)

I can’t figure this out. At all. Is there any other way I can get the present state of the x-axes without having to use…this? Whatever this relayoutData thing is, it seems to trap me in weird errors I can’t solve. I have been googling this all day and the only way I can fix my corruption problem is by not ever passing relayoutData to anything, and that doesn’t make any sense to me.

Here is the simplest and slightly anonymized example of what I’m doing:

cursor = db.cursor(buffered=True)
sql = "use <db>"
cursor.execute(sql)

stdev = 2
period = 20
time_span = "4H"

def BB_plot():
    cursor.execute("select <data> from <table> where <condition>")
    results = cursor.fetchall()
    df = pd.DataFrame (results, columns = <columns, one of which is "datetime">)
    df_sorted = df.sort_values(by=["datetime"])
    resampled_price = df_sorted.set_index("datetime").resample("1T").ffill()

    # add OHLC data:
    OHLC = resampled_price["price"].resample(time_span).ohlc()
    OHLC["SMA"] = OHLC["close"].rolling(period).mean()
    OHLC["stdev"] = OHLC["close"].rolling(period).std(ddof = 0)

    # advance the data by the period so we don't have NaNs
    OHLC = OHLC[period:]

    fig = go.Figure(data=[go.Candlestick(x = OHLC.index,
                             open = OHLC["open"],
                             high = OHLC["high"],
                             low = OHLC["low"],
                             close = OHLC["close"],
                             showlegend = False,
                             name = "candlestick")])

    fig.add_trace(go.Scatter(x = OHLC.index,
                         y = OHLC["SMA"],
                         showlegend = False,
                         line = {"color": "rgba(0, 0, 255, 0.75)", "width": 1},
                         name = "SMA" + str(period)
                         ))

    fig.add_trace(go.Scatter(x = OHLC.index,
                         y = OHLC["SMA"] + (OHLC["stdev"] * stdev),
                         line = {"color": "rgba(0, 0, 255, 0.5)", "width": 1},
                         name = "BB Upper",
                         showlegend = False))

    fig.add_trace(go.Scatter(x = OHLC.index,
                         y = OHLC["SMA"] - (OHLC["stdev"] * stdev),
                         fillcolor = "rgba(0, 0, 255, 0.1)",
                         line = {"color": "rgba(0, 100, 255, 0.5)", "width": 1},
                         fill = "tonexty",
                         name = "BB Lower",
                         showlegend = False,
                        )) 

    fig.update_layout(uirevision = "no change")
    return fig

app = dash.Dash(__name__, external_stylesheets=[dbc.themes.BOOTSTRAP], meta_tags=[{'name': 'viewport', 'content': 'width=device-width, initial-scale=1.0'}])
interval1 = dcc.Interval(
        id = "updater",
        disabled = False,
        interval = 60 * 1000, # 1 minute
        n_intervals = 0,
        max_intervals = -1,
    )
graphrow1 = dbc.Row(
        dbc.Col([
            dcc.Graph(id= "figure1", figure = {}),
        ])
    )

app.layout = dbc.Container([
    interval1,
    graphrow1,
])

# Callbacks:

# works:
#@app.callback(
#    Output("figure1", component_property = "figure"),
#    Input("updater", "n_intervals"),
#)
#def update_figure1(n_intervals):
#    return BB_plot()

# does not work:
@app.callback(
    Output("figure1", component_property = "figure"),
    Input("updater", "n_intervals"),
    [Input("figure1", "relayoutData")],
)
def update_figure1(n_intervals, relayoutData):
    return BB_plot()

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

All I want is to have my y-axis automatically resize itself if I move the x-axis slider around (and I want it to keep its state even after n_intervals fires). What am I doing wrong? Or, if I’m not doing anything wrong and this is just some bug, what other way can I try to accomplish this? Eventually I was hoping to implement that whole example, or at least my version of it, but I can’t get past these errors.

One more thing. I’ve noticed that sometimes inputs are in square brackets and sometimes they are not. I’ve tried every permutation of brackets, including none. The result is always the same: just bringing this into existence in my Python program makes mysql connector die somehow.

2

OK so I’ve managed to create my own workaround. I’ve created a dcc.Slider object and have managed to map that in the ways I need. The only problem is that data is one-way – ie I need to force the user (myself, lol) to always use the slider and never the graph because I can’t get zoom data from the graph without facing these weird errors. But at least I can create an auto y-zoom functionality. So I can slide the slider around at will and the y-axis automatically adjusts, and so far there hasn’t been a weird memory leak kind of error.

Relevant bits of code using my own example:

OHLC["rownum"] = np.arrange(len(OHLC)) # sadly, it won't use pandas timestamps. Needs a number.

# and then somewhere in the layout you'll need:
dcc.RangeSlider(
    min = OHLC["rownum"].min(),
    max = OHLC["rownum"]max(), # er, this can be simplified, but I'll leave that up to you
    marks = None,
    id = "range_slider1")

# and then the simplest callback:
@app.callback(
    Output("figure1", "figure"),
    Input("updater", "n_intervals"),
    [Input("range_slider1", "value")],
)
def update_figure1(n_intervals, value):
    fig = <call your plotter here or whatever>
    if (value is not None):
        low_y = min(OHLC[value[0]:value[1]]["low"])
        high_y = max(OHLC[value[0]:value[1]]["high"])
        fig.update_yaxes(range = [low_y = 0.1 * low_y, high_y + 0.1 * high_y] # gotta bump them a bit or the bollinger bands don't fit
        # x_axis is a little easier:
        low_x = min(OHLC[value[0]:value[1]]].index)
        high_x = max(OHLC[value[0]:value[1]]].index)
        fig.update_xaxes(range = [low_x, high_x])
    return fig

It’s not ideal but at least it doesn’t crash. edit: as quickly. There is definitely a memory leak somewhere.

3

Hi,

Welcome to the community! :slightly_smiling_face:
Your error is likely related to the way you are managing the DB connection. A quick fix would be to create the connection in a context statement, so it is closed automatically after each call:

with db.cursor(buffered=True) as cursor:
    cursor.execute("select <data> from <table> where <condition>")
    results = cursor.fetchall()
...

Otherwise you might want to take a look on connection pools, if there’s too much overhead of creating/closing db connections.

That said, I don’t think you need to fetch the db or recreate the figure every time the callback runs, as you can pass State("figure1", "figure") to the callback and just update the axis. I can give you an example if neeeded.

Hope this helps! :slightly_smiling_face:

4

Oh nice I will give that a try. Thanks!