Query a database with dropdown input and then plot Dash

Dash Python
1

Hello Everyone,

A Dash and Plotly newbie here. Following the Dash tutorials I was able to produce graphs dynamically based on dropdown menus input.
In that case I used a csv file on my machine as a data source.


After that I tried to use a MySQL database as data source with the help of SQLalchemy and Pandas, and that worked for a static plot. Nevertheless, when I tried to make dynamic plots based on queries constructed with dropdown inputs, the plot never renders in the server.


This is what I tried so far:


import pandas as pd
import dash
import dash_core_components as dcc
import dash_html_components as html
import plotly.graph_objs as go
import sqlalchemy as sql
engine_string = (‘mysql+pymysql://username:PASSWORD’+
‘127.0.0.1/DBNAME’)
engine = sql.create_engine(engine_string)
query = "SELECT * FROM Table WHERE Product_id ={} "

app.layout = html.Div([
#some other dropdowns here
dcc.Dropdown(id=‘prod_drop’),
dcc.Graph(id=‘store_sales’)
])

@app.callback(
dash.dependencies.Output(“store_sales”, “figure”),
[dash.dependencies.Input(“prod_drop”, “value”)]
)

def update_graph3(selected_product):
sql_qry = sales_query.format(selected_product)
df = pd.read_sql_query(sql_qry, engine,
parse_dates=[‘Record_date’])
traces = []
for i in df.Type_name.unique():
traces.append(
go.Scatter(
x=df[df[‘Type_name’]==i][‘Record_date’],
y=df[df[‘Type_name’]==i][‘Units_sold’],
mode=‘markers’
)
)
return {
‘data’:traces,
‘layout’: go.Layout(
xaxis={‘title’: u’Año’},
yaxis={‘title’: ‘Unidades’},
hovermode=‘closest’
)
}


Thanks in advance for your attention, any guidance would be truly appreciated.
IvĂĄn
pd: Sorry for identation issues, don't know how to insert code yet :)
2 Likes
2

Have you gotten the solution yet?
I also want to know if possible

Thank you so much

3

Hi did you find any solution to this

4

Sorry, haven’t check this in a while, so I don’t have a solution. What did you try?

5

I didn’t get any feedback on my Medium article, but one thing I think I learned is you can’t send Python objects back to what DASH ultimately does with the Plotly graphics in javascript. Again, not sure on this, but maybe the problem in the code above is that it doesn’t expand the Python memory objects into JSON friendly data that is returned. I look forward to someone more knowledgeable clarifying this.