Hi all
I want to create a callback so that when user select Year, Market, Business, from the Dropdown , it should work as where clause in the sql query and than it return the filtered data only in the data table.
query for reference
thank in advance
import dash
import dash_bootstrap_components as dbc
import dash_core_components as dcc
import dash_html_components as html
import pandas as pd
import dash_table as dt
from collections import OrderedDict
from dash.dependencies import Input, Output, State
import plotly.express as px
import urllib
from sqlalchemy import create_engine
server = 'xxx'
database = 'axxx'
username = 'xxx'
password = 'xxxx'
driver = '{ODBC Driver 17 for SQL Server}'
params = urllib.parse.quote_plus(
'DRIVER=' + driver + ';SERVER=tcp:' + server + ';PORT=1433;DATABASE=' + database + ';UID=' + username + ';PWD=' + password)
engine = create_engine('mssql+pyodbc:///?odbc_connect=%s' % params)
query = '''
Select * from ABC WHERE Market = '{Market}' AND YEAR ='{year}' and Business ='{Business}'
'''
app = dash.Dash(external_stylesheets=[dbc.themes.BOOTSTRAP])
# df = pd.read_csv("https://cdn.opensource.faculty.ai/old-faithful/data.csv")
data = pd.read_sql_query(query, engine)
df = pd.DataFrame(
data
)
print(df.head())
df = df[['Name','Market','Year','Business'
]]
Checkbox = html.Div([
html.Div(children=[
html.Label('Category (Business)'),
dcc.Dropdown(df.Business.unique(), id='Business'),
# dcc.Dropdown(df.nation.unique(), id='pandas-dropdown-2'),
html.Br(),
html.Label('Year'),
dcc.Dropdown(df.Year.unique(), id='Year',
multi=True),
html.Br(),
], style={'padding': 10, 'flex': 1}),
html.Div(children=[
html.Label('Market'),
dcc.Dropdown(df.Market.unique(), id='Market',multi=True),
html.Br(),
], style={'padding': 10, 'flex': 1})
], style={'display': 'flex', 'flex-direction': 'row'})
navbar = dbc.NavbarSimple(
children=[
dbc.NavItem(dbc.NavLink(" Sheet", href="#")),
dbc.DropdownMenu(
children=[
dbc.DropdownMenuItem("More pages", header=True),
dbc.DropdownMenuItem("Sheet -2", href="#"),
dbc.DropdownMenuItem("Sheet -3", href="#"),
],
nav=True,
in_navbar=True,
label="More"
),
],
brand="Test",
brand_href="#",
color="primary",
dark=True,
fixed="top",
sticky="top"
)
table = dt.DataTable(data=df.to_dict('records'), columns=[{'id': c, 'name': c,"hideable": True} for c in df.columns],
id='FC_TABLE',
editable=True,
filter_action="native",
sort_action="native",
sort_mode='multi',
# row_selectable='multi',
# row_deletable=True,
# selected_rows=[],
page_action='native',
page_current=0,
page_size=11,
persistence=True,
style_header={'border': '1px solid black', 'background': 'DodgerBlue', 'color': 'white'},
)
Button_Sytle = {
'background-color': '#4CAF50',
'border': 'none',
'color': 'white',
'padding': '15px 32px',
'text-align': 'center',
'text-decoration': 'none',
'display': 'inline-block',
'font-size': '16px',
'margin': '4px 2px',
'cursor': 'pointer',
}
button = html.Div(
[
dbc.Button("Download ", className="me-md-2", style=Button_Sytle),
dbc.Button("Add ", className="me-md-2", style=Button_Sytle),
dbc.Button("Refresh", style=Button_Sytle),
],
className="d-grid gap-4 d-md-flex justify-content-md-end",
)
app.layout = html.Div([navbar, Checkbox, table])
@app.callback(
Output("FC_TABLE", "data"),
Input("Business", "value"),
Input("Market", "value"),
Input("Year","value")
)
def update_dropdown_options(Business, Market,Year):
dff = df.copy()
if Business:
dff = dff[dff.Business==Business]
if Market:
dff = dff[dff.Market.isin(Market)]
if CTN:
dff = dff[dff.Year.isin(Year)]
return dff.to_dict('records')
if __name__ == "__main__":
app.run_server(debug=False, port=9090)