No chart show when connect to Microsoft SQL

Dash Python
1

Hi,

How to fix the issue of no chart shown in Plotly Express when connecting to MIcrosoft SQL?

#Connect server
server = 'your_server'
database = 'your_database' 
username = os.environ.get('SERVER_USERNAME')
password = os.environ.get('SERVER_PWD')
conn = pyodbc.connect('DRIVER={your_driver};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = conn.cursor()


df = pd.read_sql_query('''

                SELECT * FROM database 

                ''', conn) 



month_labels = {1: 'Jan', 2: 'Feb', 3: 'Mar', 4: 'Apr', 5: 'May', 6: 'Jun', 7: 'Jul', 8: 'Aug',9: 'Sep', 10: 'Oct', 11: 'Nov', 12: 'Dec'}
df['month'] = pd.to_datetime(df['Date'], format = '%Y-%m-%d', errors='coerce').dt.month
df['monthname']= df['month'].apply(lambda x: month_labels[x]) 


df['date'] = pd.to_datetime(df['Date'], format = '%Y-%m-%d', errors='coerce')

df['day'] = pd.to_datetime(df['Date'], format = '%Y-%m-%d', errors='coerce').dt.day

df.sort_values(by= [ 'month', 'Date'], inplace = True)         
month_cat = list(df['monthname'].unique())            
sort_mm = sorted(month_cat, key=lambda m: datetime.strptime(m, "%b"))


app.layout = dbc.Container([
    dbc.Row([
        dbc.Col(html.H1("DRR",
                        className = 'text-center text-primary, mb-4 '),
                width=12
                ),
      
        ]),
    
    dbc.Row([
        dbc.Col([
            html.H3('Month'),
            html.Br(),
            
            dcc.Dropdown(id='month_dd', value= month_labels[df['month'].max()],
                          options = [{'label':x, 'value':x} 
                                    for x in sort_mm],
                          
                          searchable = True, search_value='',
                          placeholder= 'Please select ...'
                          
                          ),
            html.Br(),
            
            html.H3('Date'),
            html.Br(),
            
             dcc.Dropdown(id='date_dd', value='',
                         searchable = True, search_value='',
                         placeholder= 'Please select ...')
            
                ])
            ]),

    

    
    
    dbc.Row([
        dbc.Col([
            html.P("Pie Chart:",
                    style={"textDecoration":"underline"}),
            
                                    
            dcc.Graph(id='pie-fig', figure={}),
            

])



@app.callback(
    Output('date_dd','options'),
    Input('month_dd', 'value')
    )


def update_dd (month_dd):

    
    month_date= df.drop_duplicates(['Date'], inplace= False)
    
    relevant_date= month_date[ df['monthname'] == month_dd]['Date'].values.tolist()
    date_option= [dict(label=x,value=x)for x in relevant_date]
    
    
    return date_option



@app.callback(
    Output('date_dd','value'),
    Input('date_dd', 'options')
    )


def default_value(latest_date):
    value = latest_date[-1]['value']
    
    return value



#pie chart
@app.callback(  
    Output('pie-fig', 'figure'),
    Input('date_dd', 'value')
)


def update_graph(selection):
    if len (selection) ==0:
        return dash.no_update
    
    else:     
        dff = df[df['Date'] == selection]      
        fig = px.pie(dff, values='Sale', names='Product', color_discrete_sequence=px.colors.sequential.RdBu)
        fig.update_traces(textinfo= "label+value+percent").update_layout(title_x=0.5)
        return fig
1 Like
2

Hi @beginof,

It’s difficult to say what’s going wrong, but starting from the top, do you successfully get data back from the pandas SQL query? I noticed you have SELECT * FROM database, I assume in your code that the database portion is actually pointing to a table: database.YourTable or just YourTable? Do you see any errors?

Taking your example and adding some example data, I see it’s working without issue:

image
image982Ă—792 13.7 KB 

from dash import Dash, Input, Output, State, dcc, html, no_update
import dash
import pandas as pd
import dash_bootstrap_components as dbc
from datetime import datetime
import plotly_express as px

df = pd.DataFrame(
    [
        ['2022-06-23', 5, 'Apple'],
        ['2022-06-13', 7, 'Apple'],
        ['2022-06-03', 7, 'Banana'],
        ['2022-06-23', 5, 'Apple'],

        ['2022-07-23', 8, 'Banana'],
        ['2022-07-13', 2, 'Orange'],
        ['2022-07-08', 1, 'Orange'],
        ['2022-07-17', 9, 'Apple'],

        ['2022-08-23', 12, 'Banana'],
        ['2022-08-23', 1, 'Banana'],
        ['2022-08-23', 8, 'Banana'],
        ['2022-08-23', 2, 'Banana'],
        ['2022-08-23', 7, 'Apple']
    ],
    columns=['Date', 'Sale', 'Product']
)

month_labels = {1: 'Jan', 2: 'Feb', 3: 'Mar', 4: 'Apr', 5: 'May', 6: 'Jun', 7: 'Jul', 8: 'Aug', 9: 'Sep', 10: 'Oct',
                11: 'Nov', 12: 'Dec'}
df['month'] = pd.to_datetime(df['Date'], format='%Y-%m-%d', errors='coerce').dt.month
df['monthname'] = df['month'].apply(lambda x: month_labels[x])

df['date'] = pd.to_datetime(df['Date'], format='%Y-%m-%d', errors='coerce')

df['day'] = pd.to_datetime(df['Date'], format='%Y-%m-%d', errors='coerce').dt.day

df.sort_values(by=['month', 'Date'], inplace=True)
month_cat = list(df['monthname'].unique())
sort_mm = sorted(month_cat, key=lambda m: datetime.strptime(m, "%b"))

app = Dash(__name__)

app.layout = dbc.Container([
    dbc.Row([
        dbc.Col(html.H1("DRR",
                        className='text-center text-primary, mb-4 '),
                width=12
                ),

    ]),

    dbc.Row([
        dbc.Col([
            html.H3('Month'),
            html.Br(),

            dcc.Dropdown(id='month_dd', value=month_labels[df['month'].max()],
                         options=[{'label': x, 'value': x}
                                  for x in sort_mm],

                         searchable=True, search_value='',
                         placeholder='Please select ...'

                         ),
            html.Br(),

            html.H3('Date'),
            html.Br(),

            dcc.Dropdown(id='date_dd', value='',
                         searchable=True, search_value='',
                         placeholder='Please select ...')

        ])
    ]),

    dbc.Row([
        dbc.Col([
            html.P("Pie Chart:",
                   style={"textDecoration": "underline"}),

            dcc.Graph(id='pie-fig', figure={}),

        ])
    ])
])


@app.callback(
    Output('date_dd', 'options'),
    Input('month_dd', 'value')
)
def update_dd(month_dd):
    month_date = df.drop_duplicates(['Date'], inplace=False)

    relevant_date = month_date[month_date['monthname'] == month_dd]['Date'].tolist()
    date_option = [dict(label=x, value=x) for x in relevant_date]

    return date_option


@app.callback(
    Output('date_dd', 'value'),
    Input('date_dd', 'options')
)
def default_value(latest_date):
    value = latest_date[-1]['value']

    return value


# pie chart
@app.callback(
    Output('pie-fig', 'figure'),
    Input('date_dd', 'value')
)
def update_graph(selection):
    if len(selection) == 0:
        return dash.no_update

    else:
        dff = df[df['Date'] == selection]
        fig = px.pie(dff, values='Sale', names='Product', color_discrete_sequence=px.colors.sequential.RdBu)
        fig.update_traces(textinfo="label+value+percent").update_layout(title_x=0.5)
        return fig


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