Using callbacks to query SQL Server not working

Dash Python
1

Hello Dash community,

I’m trying to build a simple dashboard that contains 2 dropdown menus and a graph. The first dropdown contains static values while the 2nd dropdown will update dynamically from the first. My graph will then update based off of the selected values from both dropdown menus.

I’m basing my dashboard off of what Aly Sivji wrote in this tutorial and am structuring my SQL queries in like fashion.

Here’s a snippet of my code:

def fetch_data(q):
    result = pd.read_sql(
        sql=q,
        con=conn
    )
    result = pd.DataFrame(result)
    conn.close()
    return result


def get_items(selected_filter):
    items_query = (
        f'''
        SELECT DISTINCT {selected_filter}
        FROM test
        '''
    )
    selected_items = fetch_data(items_query)
    selected_items = list(selected_items[selected_filter].sort_values(ascending=False))
    return selected_items


def get_wids(selected_filter, selected_items):
    table_query = (
        f'''
        SELECT WID
        FROM test
        WHERE {selected_filter} = '{selected_items}'
        '''
    )
    wid_list = fetch_data(table_query)
    wid_list = wid_list['WID'].tolist()
    return wid_list


def get_graphdf(wid_list):
    graph_query = (
        f'''
            SELECT DOFP, Load
            FROM graph
            WHERE WID
            IN ('%s')
            GROUP BY DOFP, Load
            ORDER BY DOFP, Load
            '''
        % ("','".join(wid_list))
    )
    graphdf = fetch_data(graph_query)
    return graphdf


def draw_graph(graphdf):
    figure = go.Figure(
        data=[
            go.Scatter(x=graphdf['DOFP'], y=graphdf['Load'], mode='lines+markers')
        ],
        layout=go.Layout(
            title='Amperage',
            showlegend=False
        )
    )
    return figure


#############################################################
#                Dashboard Layout / View
#############################################################


app = dash.Dash(__name__, external_stylesheets=external_stylesheets)

app.layout = html.Div([
    html.H1("Load Forecast"),
    html.Div([
        html.Div([
            # Select Filter Dropdown
            html.Div([
                html.Div('Select Filter', className='three columns'),
                html.Div(dcc.Dropdown(id='filter-selector',
                                      options=[
                                          {'label': 'Production Area', 'value': 'prodArea'},
                                          {'label': 'Meter', 'value': 'meter'},
                                          {'label': 'Project', 'value': 'project'},
                                      ]),
                         className='nine columns')
            ]),

            # Select Item Dropdown
            html.Div([
                html.Div('Select Items', className='three columns'),
                html.Div(dcc.Dropdown(id='item-selector'),
                         className='nine columns')
            ]),
        ], className='six columns'),

        # Empty
        html.Div(className='six columns'),
    ], className='twleve columns'),

    # Results Grid
    html.Div([

        # Summary Graph
        html.Div([
            # graph
            dcc.Graph(id='item-graph')
            # style={},

        ], className='six columns')
    ]),
])

#############################################################
#         Interaction Between Components / Controller
#############################################################


# Load Items in Item Dropdown
@app.callback(
    Output(component_id='item-selector', component_property='options'),
    [Input(component_id='filter-selector', component_property='value')])
def populate_item_selector(selected_filter):
    return [{'label': i, 'value': i} for i in get_items(selected_filter)]


# Update Graph
@app.callback(
    Output(component_id='item-graph', component_property='figure'),
    [Input(component_id='filter-selector', component_property='value'),
     Input(component_id='item-selector', component_property='value')])
def load_item_points_graph(selected_filter, selected_items):
    results = get_wids(selected_filter, selected_items)
    resultsdf = get_graphdf(results)

    figure = []
    if len(results) > 0:
        figure = draw_graph(resultsdf)

    return figure

The problem I’m having is that my SQL queries are not working within my callbacks. All of my functions work great outside of my callbacks but for some reason when I call them inside my callbacks they return an error like this:

pandas.io.sql.DatabaseError: Execution failed on sql '
        SELECT DISTINCT None
        FROM test
        ': ('42S22', "[42S22] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name 'None'. (207) (SQLExecDirectW)")

For some reason the callback isn’t passing the variable through to my function (I think). I’ve searched through this forum and have read quite a few posts and tutorials detailing how to execute a query from a callback and nothing stands out to me in my code that would be giving me this error.

Any help would be appreciated!

I would be happy to share excel/csv files of the two different database tables that I’m pulling from if that would help too.

2

Anybody have any experience working with callbacks and SQL queries in Dash? Would love some expertise on this problem, thanks guys!