User input that updates an SQL Query

Dash Python
1

Currently I have a database connected to dash. A query is run, put into a dataframe and put into my graphs. i need the graphs to be able to run a different query for different graphs. Right now I am just updating variables that formats the string that the query is contained in.

What is the best way for the user to update the query? I was trying a radio button but it seems like the output of that radio button needs to be returned somewhere inside the layout. Is there a way to have the radio button’s ‘value’ update a variable so it will insert that value into the query and run again?

What would a callback look like for something like this?

I am posting some stripped down version of my code without any callback at the bottom. Does anyone have an idea of the best way to do something like this in the most “DRY” way and least taxing?
Thanks in advance!!

server = 'server' 
database = 'database' 
username = 'user' 
password = 'password' 
conn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = conn.cursor()

format_select = 'format1'
table_select = 'table1'


sql = '''

SELECT {} FROM {}


'''.format(format_select, table_select)

df = pandas.io.sql.read_sql(sql, conn)

conn.close()

status_tracker = df.Digit_Status
Counter(status_tracker)

app = dash.Dash()

app.layout=html.Div([
            
      dcc.Checklist(
              id = 'checklist',
    options=[
        {'label': 'format1', 'value': 'format1'},
        {'label': 'format2', 'value': 'format2'},
        {'label': 'format3', 'value': 'format3'}
    ],
    values=['format1']
),      
    
     dcc.Graph(
            id='Status Tracker K7',
            figure={
                'data': [
                    {'x': Counter(status_tracker).keys(), 
                     'y':  Counter(status_tracker).values(),
                     'type': 'bar',  
                     'width': .05},
                    
                ],
                'layout': {
                    'title': 'Status Tracker K7',
                    'width': '50%',   
                }},
                    style={'border': '5px solid maroon', 
                    'margin': '30px',
                    'width': '700px',
                    'float': 'left'}
            )
            
            )),
    
])



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

The general strategy I think you want to use is have the query generated within a callback that is listening for the user inputs. So perhaps something like this might work, assuming a function run_query_and_get_output which executes an string containing an SQL query against a DB and prepares the output you need:

@app.callback(Output('target', 'children'), [Input('checklist', 'values')])
def run_query(values):
    sql = "SELECT {} from some_table".format(','.join(values))
    return run_query_and_get_output(sql)
3 Likes
3

Thanks a lot for your quick reply! It took me a while to get back to this project but I have taken what you said into consideration and was able to get the code to do what I need it to do!

Thank you sooo much for your help. I’m sure I will have more questions as I proceed through this project. I truly appreciate everyone’s care and responsiveness in this forum!

Thanks again.

1 Like
4

Did you ever deploy this? if yes how did you plug in those sql credentials into the app?