mysql database error

[brecht83]

I'm writing a program with Dash where I update a graph according to the data the users selects in a dropdown menu. The graph data comes from a mysql db and I have an input field where the user can change the maximum of the y-graph (id='max_value')
If the user writes for example 5 in the max input field, everything goes well. If the users writes 50, he first types 5, the function 'update graph' is called and the program reads the data from the db. Before this is finished, the users types 0 and the function 'update graph' is called again and the program tries to read the data from the db again while he's still reading from the previous function (writing the 5). At least, I think that is the problem.
I know I don't have to read the data when the maximum value is changed and I could write a new function for changing the maximum value, but is there an other way to solve the problem? A sort of interlock?

Thanks

Brecht

import mysql.connector

import pandas as pd

import dash
from dash.dependencies import Input, Output
import dash_core_components as dcc
import dash_html_components as html

import datetime

import plotly.graph_objs as go


app = dash.Dash()

#create mySQL connection
''' with ...'''
con = mysql.connector.connect(
    host="*****",
    user="*****",
    passwd="*****",
    database="*****",
)

print(con)

#read Id and name of EWon's
db_ewon = con.cursor()
db_ewon.execute("SELECT Id,name FROM esync_stations")
installations = db_ewon.fetchall()
installations_dict = {}
total_dict = {}


for item in installations:
    # item[0]: installationID
    # item[1]: name installation

    #first we read all the parameters of the current installationID
    parameters_dict = {}
    db_parameters = con.cursor()
    db_parameters.execute("SELECT Id,name FROM esync_tags WHERE stationID=%i" % item[0])
    parameters = db_parameters.fetchall()
    parameters_dict = {}

    for par in parameters:
        parameters_dict[par[0]] = par[1]

    total_dict[item[0]] = parameters_dict

    installations_dict[item[0]] = item[1]


#read Id and name from tag
db_tag = con.cursor()
db_tag.execute("SELECT Id,name FROM esync_tags WHERE stationID=10")
tags = db_tag.fetchall()
tags_dict = {}

for item in tags:
    tags_dict[item[0]] = item[1]


# create layout
app.layout = html.Div(children=[
    dcc.Input(id='max_value', value='100', type='text'),
    html.Div(children='Select installation:'),
    dcc.Dropdown(
        id='dropdown-machine',
        options=[{'label': machine, 'value': machineID} for machineID, machine in installations_dict.items()],
        value=10
    ),
    dcc.Dropdown(
        id='dropdown-tag',
        value=642
    ),
    html.Div(id='output-graph'),
])

# update dropdown tags
@app.callback(
    Output(component_id='dropdown-tag', component_property='options'),
    [Input(component_id='dropdown-machine', component_property='value')]
)
def update_dropdown(input_data):
    print('input data dropdown:', input_data)

    # return [{'label': tag, 'value': tagID} for tagID, tag in tags_dict.items()]
    return [{'label': tag, 'value': tagID} for tagID, tag in total_dict[input_data].items()]


# update graph
@app.callback(
    Output(component_id='output-graph', component_property='children'),
    [Input(component_id='dropdown-tag', component_property='value'),
     Input(component_id='max_value', component_property='value')]
)
def update_graph(input_data, max):
    print('input data graph:', input_data)
    start = datetime.datetime(2015, 1, 1)
    end = datetime.datetime.now()
    print('before reading')
    df = pd.read_sql_query('SELECT _date,Val FROM esync_tagshistory WHERE TagId=%i AND DATE(_date)>"2017-01-01"' % (int(input_data)), con)
    print('after reading')

    return dcc.Graph(
        id='example-graph2',
        figure={
            'data': [
                {'x': df._date, 'y': df.Val, 'type': 'line', 'name': input_data},
            ],
            'layout': go.Layout(xaxis=dict(range=[start, end]),
                                yaxis=dict(range=[0, int(max)]),)
        }
    )


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

<mysql.connector.connection.MySQLConnection object at 0x0A9FBEF0>
 * Serving Flask app "dash_graph_new" (lazy loading)
 * Environment: production
   WARNING: Do not use the development server in a production environment.
   Use a production WSGI server instead.
 * Debug mode: on
 * Restarting with stat
<mysql.connector.connection.MySQLConnection object at 0x0A9FCFD0>
 * Debugger is active!
 * Debugger PIN: 413-847-908
 * Running on http://127.0.0.1:8050/ (Press CTRL+C to quit)
127.0.0.1 - - [04/Dec/2018 12:37:57] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [04/Dec/2018 12:37:58] "GET /_dash-layout HTTP/1.1" 200 -
127.0.0.1 - - [04/Dec/2018 12:37:58] "GET /_dash-dependencies HTTP/1.1" 200 -
127.0.0.1 - - [04/Dec/2018 12:37:58] "GET /favicon.ico HTTP/1.1" 200 -
input data graph: 642
before reading
input data dropdown: 10
127.0.0.1 - - [04/Dec/2018 12:37:58] "POST /_dash-update-component HTTP/1.1" 200 -
after reading
127.0.0.1 - - [04/Dec/2018 12:38:01] "POST /_dash-update-component HTTP/1.1" 200 -
input data graph: 642
before reading
input data graph: 642
before reading
127.0.0.1 - - [04/Dec/2018 12:38:07] "POST /_dash-update-component HTTP/1.1" 500 -
Traceback (most recent call last):
  File "C:\Python37\lib\site-packages\flask\app.py", line 2309, in __call__
    return self.wsgi_app(environ, start_response)
  File "C:\Python37\lib\site-packages\flask\app.py", line 2295, in wsgi_app
    response = self.handle_exception(e)
  File "C:\Python37\lib\site-packages\flask\app.py", line 1741, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "C:\Python37\lib\site-packages\flask\_compat.py", line 35, in reraise
    raise value
  File "C:\Python37\lib\site-packages\flask\app.py", line 2292, in wsgi_app
    response = self.full_dispatch_request()
  File "C:\Python37\lib\site-packages\flask\app.py", line 1815, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "C:\Python37\lib\site-packages\flask\app.py", line 1718, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "C:\Python37\lib\site-packages\flask\_compat.py", line 35, in reraise
    raise value
  File "C:\Python37\lib\site-packages\flask\app.py", line 1813, in full_dispatch_request
    rv = self.dispatch_request()
  File "C:\Python37\lib\site-packages\flask\app.py", line 1799, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "C:\Python37\lib\site-packages\dash\dash.py", line 899, in dispatch
    return self.callback_map[target_id]['callback'](*args)
  File "C:\Python37\lib\site-packages\dash\dash.py", line 839, in add_context
    output_value = func(*args, **kwargs)
  File "C:\Users\Brecht\Documents\python_work\Display_graph\dash_graph_new.py", line 112, in update_graph
    df = pd.read_sql_query('SELECT _date,Val FROM esync_tagshistory WHERE TagId=%i AND DATE(_date)>"2017-01-01"' % (int(input_data)), con)
  File "C:\Python37\lib\site-packages\pandas\io\sql.py", line 314, in read_sql_query
    parse_dates=parse_dates, chunksize=chunksize)
  File "C:\Python37\lib\site-packages\pandas\io\sql.py", line 1413, in read_query
    cursor = self.execute(*args)
  File "C:\Python37\lib\site-packages\pandas\io\sql.py", line 1373, in execute
    cur = self.con.cursor()
  File "C:\Python37\lib\site-packages\mysql\connector\connection.py", line 813, in cursor
    raise errors.OperationalError("MySQL Connection not available.")
mysql.connector.errors.OperationalError: MySQL Connection not available.

[jeanMichelBain]

If I'm right, mysql is multi-threads, so you could manage the problem if, at the first step, you start a transaction, and commit (or rollback) after reading what you want. By this way the second step (the same code actually) will start a new transaction when running, wich will wait the end of the first step (the commit) in the appropriate way.
I'm not sure of what's following, but this could be difficult to do if you don't use classes. I allways do it, creating a class for data access, and overriding that class when I need to access data from another part of the script. Establish and keep the connection in the class data ; it will done one time and will stay ready for the successive requests. I allways do it and it works well in that cases of the GUI event loop cross itself, making situations when you don't know exactly what occurs because it's almost difficult to debug.
Also, check what happens if mysql do a transaction for each request if you don't start it explicitely and commit it when you want.
I don't know if it's helpfull in your case, but I'm pretty sure if you make the effort to create separated classes for the different parts of your code, you will get a good value.