Hi All,
I am new in Plotly Dash.
Currently, I am work with a project by building interactive visualization and the data will be uploaded to PostgreSQL database.
I would like to ask is there any way to synchronous “add row” button to all tab. It means that if I click “add row” button in any tab, the datatable in every tabs will automatically add the new row at the same time.
This is the code that I have:
import dash
from dash import dash_table
from dash.dash_table.Format import Group
from dash import dcc
from dash import html
import plotly.express as px
import pandas as pd
from dash.dependencies import Input, Output, State
from flask_sqlalchemy import SQLAlchemy
from flask import Flask
import psycopg2
import pytz
import datetime
app = dash.Dash(__name__)
colors = {
'background': '#111111',
'text': '#7FDBFF'
}
server = Flask(__name__)
app = dash.Dash(__name__, server=server, suppress_callback_exceptions=True)
app.server.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
# for your home PostgreSQL test table
app.server.config["SQLALCHEMY_DATABASE_URI"] = "postgresql://postgres:FSDAnalytics@localhost/Plants_Historians"
db = SQLAlchemy(app.server)
#Inputs
time_zone = pytz.timezone('Asia/Jakarta')
hostname = 'localhost'
databasename = 'Plants_Historians'
db_userID= 'postgres'
db_password = 'FSDAnalytics'
#Inputs - Select all columns in the PostgreSQL Database
psql_event_detection = ''' SELECT * FROM plants.event_detection'''
psql_event_action= ''' SELECT * FROM plants.event_action'''
psql_event_severity= ''' SELECT * FROM plants.severity'''
psql_event_status= ''' SELECT * FROM plants.status'''
psql_event_closeout= ''' SELECT * FROM plants.closeout'''
psql_event_failure_mode= ''' SELECT * FROM plants.failure_mode'''
psql_event_failure_cause= ''' SELECT * FROM plants.failure_cause'''
psql_event_activity= ''' SELECT * FROM plants.activity'''
psql_event_execution_timeline= ''' SELECT * FROM plants.execution_timeline'''
# Colors code for table's background and fonts
colors = {
'background': '#111111',
'text': '#7FDBFF'
}
# Style for Tabs (Event Detection, Event Action, Closeout, Execution Timeline)
tabs_styles = {'zIndex': 99, 'display': 'inlineBlock', 'height': '4vh', 'width': '12vw',
'position': 'fixed', "background": "#323130", 'top': '12.5vh', 'left': '7.5vw',
'border': 'grey', 'border-radius': '4px'}
tab_style = {
"background": "#323130",
'text-transform': 'uppercase',
'color': 'white',
'border': 'grey',
'font-size': '11px',
'font-weight': 600,
'align-items': 'center',
'justify-content': 'center',
'border-radius': '4px',
'padding':'6px'
}
tab_selected_style = {
"background": "grey",
'text-transform': 'uppercase',
'color': 'white',
'font-size': '11px',
'font-weight': 600,
'border': 'grey',
'align-items': 'center',
'justify-content': 'center',
'border-radius': '4px',
'padding':'6px'
}
#____________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________#
# Event Detection Table Layout
event_detection_table = html.Div([
html.H1(children ='Event Detection',
style = { 'textAlign' : 'center','color' : colors['text']}
),
html.Div(id='event_detection_postgres_datatable'),
html.Div(id='event_detection_placeholder', children=[]),
dcc.Store(id="event_detection_store", data = 0),
])
# Event Action Table Layout
event_action_table = html.Div([
html.H1(children ='Event Action',
style = { 'textAlign' : 'center','color' : colors['text']}
),
html.Div(id='event_action_postgres_datatable'),
html.Div(id='event_action_placeholder', children=[]),
dcc.Store(id="event_action_store", data = 0),
])
# Closeout Table Layout
closeout_table = html.Div([
html.H1(children ='Closeout',
style = { 'textAlign' : 'center','color' : colors['text']}
),
html.Div(id='closeout_postgres_datatable'),
html.Div(id='closeout_placeholder', children=[]),
dcc.Store(id="closeout_store", data = 0),
])
# Execution Timeline Table Layout
execution_timeline_table = html.Div([
html.H1(children ='Execution Timeline',
style = { 'textAlign' : 'center','color' : colors['text']}
),
html.Div(id='execution_timeline_postgres_datatable'),
html.Div(id='execution_timeline_placeholder', children=[]),
dcc.Store(id="execution_timeline_store", data = 0),
])
#____________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________#
# Main Layout
app.layout = html.Div([
dcc.Tabs(id='tabs-example', value='tab-1', children=[
dcc.Tab(label='Event Detection', value='tab-1', style=tab_style, selected_style=tab_selected_style),
dcc.Tab(label='Event Action', value='tab-2', style=tab_style, selected_style=tab_selected_style),
dcc.Tab(label='Close Out', value='tab-3', style=tab_style, selected_style=tab_selected_style),
dcc.Tab(label='Execution Timeline', value='tab-4', style=tab_style, selected_style=tab_selected_style),
]),
html.Div(id='tabs-example-content-1'),
dcc.Interval(id='interval_pg', interval=86400000*7, n_intervals=0), # activated once/week or when page refreshed
html.Button('Add Row', id='editing-rows-button', n_clicks=0),
html.Button('Save to PostgreSQL', id='save_to_postgres', n_clicks=0),
dcc.Interval(id='interval', interval=1000)
])
#____________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________#
# Callback for display the Event Detection Table in the webpage
@app.callback(Output('event_detection_postgres_datatable', 'children'),
[Input('interval_pg', 'n_intervals')])
def populate_datatable(n_intervals):
event_detection = pd.read_sql_query(psql_event_detection, con = db.engine)
return [
dash_table.DataTable(
id = 'event_detection_table',
data = event_detection.to_dict('records'),
columns = [
{'id':'event_id', 'name': 'Event ID'},
{'id':'event_start_time', 'name': 'Event Start Time'},
{'id':'event_end_time', 'name': 'event_end_time'},
{'id':'event_condition', 'name': 'event_condition'},
{'id':'tag_description', 'name': 'tag_description'},
{'id':'tag_code', 'name': 'tag_code'},
{'id':'tag_limit', 'name': 'tag_limit'},
{'id':'value_of_limit', 'name': 'value_of_limit'},
{'id':'threshold_value', 'name': 'threshold_value'},
{'id':'latched', 'name': 'latched'},
{'id': 'trigger_delay_in_seconds', 'name': 'trigger_delay_sec'}
],
editable =True,
filter_action = "native",
sort_action = "native", # give user capability to sort columns
sort_mode = "multi", # sort across 'multi' or 'single' columns
page_action = 'none', # render all of the data at once. No paging
merge_duplicate_headers=True,
),
html.Div(id='table-dropdown-1'),
]
# Callback for adding new rows in event detection table
@app.callback(
Output('event_detection_table', 'data'),
[Input('editing-rows-button', 'n_clicks')],
[State('event_detection_table', 'data'),
State('event_detection_table', 'columns')],
prevent_initial_call=True)
def add_row(n_clicks, rows, columns):
if n_clicks > 0:
rows.append({c['id']: '' for c in columns})
return rows
# Callback for store the update data/ new data to the postgres table for event detection
@app.callback(
[Output('event_detection_placeholder', 'children'),
Output("event_detection_store", "data")],
[Input('save_to_postgres', 'n_clicks'),
Input("interval", "n_intervals")],
[State('event_detection_table', 'data'),
State('event_detection_store', 'data')],
prevent_initial_call=True)
def df_to_csv(n_clicks, n_intervals, dataset, s):
output = html.Plaintext("The data has been saved to your PostgreSQL database.",
style={'color': 'green', 'font-weight': 'bold', 'font-size': 'large'})
no_output = html.Plaintext("", style={'margin': "0px"})
input_triggered = dash.callback_context.triggered[0]["prop_id"].split(".")[0]
if input_triggered == "save_to_postgres":
s = 6
pg = pd.DataFrame(dataset)
for index, row in pg.iterrows():
insertdata = "('"+str(row[0])+"','"+str(row[1])+"','"+str(row[2])+"','"+str(row[3])+"','"+str(row[4])+"','"+str(row[5])+"','"+str(row[6])+"','"+str(row[7])+"','"+str(row[8])+"','"+str(row[9])+"','"+str(row[10])+"')"
print("insertdata:", insertdata)
try:
db.session.execute("INSERT INTO plants.event_detection(event_id, event_start_time, event_end_time, event_condition, tag_Description, tag_code, tag_limit, value_of_limit, threshold_value, latched, trigger_delay_in_seconds) OVERRIDING SYSTEM VALUE values" + insertdata + "on conflict (event_id) do nothing")
print ("row inserted:", insertdata)
db.session.commit()
except psycopg2.IntegrityError:
print("Row already exist")
pass
except Exception as e:
print("some insert error:", e, "ins: ", insertdata)
db.session.commit()
return output, s
elif input_triggered == 'interval' and s > 0:
s = s - 1
if s > 0:
return output, s
else:
return no_output, s
elif s == 0:
return no_output, s
#____________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________#
# Callback for display the Event Action Table in the webpage
@app.callback(Output('event_action_postgres_datatable', 'children'),
[Input('interval_pg', 'n_intervals')])
def populate_datatable(n_intervals):
event_action = pd.read_sql_query(psql_event_action, con = db.engine)
severity = pd.read_sql_query(psql_event_severity, con = db.engine)
status = pd.read_sql_query(psql_event_status, con = db.engine)
return [
dash_table.DataTable(
id = 'event_action_table',
data = event_action.to_dict('records'),
columns = [
{'id':'event_id', 'name': 'Event ID'},
{'id':'cmms_ref_no', 'name': 'CMMS Ref No.'},
{'id':'severity', 'name': 'Severity', 'presentation': 'dropdown'},
{'id':'status', 'name': 'Status', 'presentation': 'dropdown'},
{'id':'owner', 'name': 'Owner'},
{'id': 'risk_ranking', 'name': 'Risk Ranking'}
],
editable =True,
filter_action = "native",
sort_action = "native",
sort_mode = "multi",
page_action = "none",
merge_duplicate_headers=True,
dropdown = {
'severity': {
'options':[
{'label': i, 'value': i}
for i in severity['severity'].unique()
]
},
'status': {
'options':[
{'label': i, 'value': i}
for i in status['status'].unique()
]
}
}
),
html.Div(id='table-dropdown-2'),
]
# Callback for adding new rows in event action table
@app.callback(
Output('event_action_table', 'data'),
[Input('editing-rows-button', 'n_clicks')],
[State('event_action_table', 'data'),
State('event_action_table', 'columns')],
prevent_initial_call=True)
def add_row(n_clicks, rows, columns):
if n_clicks > 0:
rows.append({c['id']: '' for c in columns})
return rows
# Callback for store the update data/ new data to the postgres table for event action
@app.callback(
[Output('event_action_placeholder', 'children'),
Output("event_action_store", "data")],
[Input('save_to_postgres', 'n_clicks'),
Input("interval", "n_intervals")],
[State('event_action_table', 'data'),
State('event_action_store', 'data')],
prevent_initial_call=True)
def df_to_csv(n_clicks, n_intervals, dataset, s):
output = html.Plaintext("The data has been saved to your PostgreSQL database.",
style={'color': 'green', 'font-weight': 'bold', 'font-size': 'large'})
no_output = html.Plaintext("", style={'margin': "0px"})
input_triggered = dash.callback_context.triggered[0]["prop_id"].split(".")[0]
if input_triggered == "save_to_postgres":
s = 6
pg = pd.DataFrame(dataset)
for index, row in pg.iterrows():
insertdata = "('"+str(row[0])+"','"+str(row[1])+"','"+str(row[2])+"','"+str(row[3])+"','"+str(row[4])+"','"+str(row[5])+"')"
print("insertdata:", insertdata)
try:
db.session.execute("INSERT INTO plants.event_action(event_id, cmms_ref_no, severity, status, owner, risk_ranking)OVERRIDING SYSTEM VALUE values" + insertdata + "on conflict (cmms_ref_no) do nothing")
print ("row inserted:", insertdata)
db.session.commit()
except psycopg2.IntegrityError:
print("Row already exist")
pass
except Exception as e:
print("some insert error:", e, "ins: ", insertdata)
db.session.commit()
return output, s
elif input_triggered == 'interval' and s > 0:
s = s - 1
if s > 0:
return output, s
else:
return no_output, s
elif s == 0:
return no_output, s
#____________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________#
# Callback for display the Closeout Table in the webpage
@app.callback(Output('closeout_postgres_datatable', 'children'),
[Input('interval_pg', 'n_intervals')])
def populate_datatable(n_intervals):
closeout = pd.read_sql_query(psql_event_closeout, con = db.engine)
damage = pd.read_sql_query(psql_event_failure_mode, con = db.engine)
cause = pd.read_sql_query(psql_event_failure_cause, con = db.engine)
action = pd.read_sql_query(psql_event_activity, con = db.engine)
return [
dash_table.DataTable(
id = 'closeout_table',
data = closeout.to_dict('records'),
columns = [
{'id':'event_id', 'name': 'Event ID'},
{'id':'cause', 'name': 'Cause', 'presentation':'dropdown'},
{'id':'damage', 'name': 'Damage', 'presentation':'dropdown'},
{'id':'action', 'name': 'Action', 'presentation':'dropdown'},
{'id': 'closeout_date', 'name': 'Closeout Date'}
],
editable =True,
filter_action = "native",
sort_action = "native",
sort_mode = "multi",
merge_duplicate_headers=True,
dropdown = {
'cause': {
'options': [
{'label': i, 'value': i}
for i in cause["failure_cause"].unique()
],
'clearable': False
},
'damage': {
'options': [
{'label': i, 'value': i}
for i in damage["failure_mode"].unique()
],
'clearable': False
},
'action': {
'options': [
{'label': i, 'value': i}
for i in action["activity"].unique()
],
'clearable': False
}
}
),
html.Div(id='table-dropdown-3'),
]
# Callback for adding new rows in closeout table
@app.callback(
Output('closeout_table', 'data'),
[Input('editing-rows-button', 'n_clicks')],
[State('closeout_table', 'data'),
State('closeout_table', 'columns')],
prevent_initial_call=True)
def add_row(n_clicks, rows, columns):
if n_clicks > 0:
rows.append({c['id']: '' for c in columns})
return rows
# Callback for store the update data/ new data to the postgres table for closeout
@app.callback(
[Output('closeout_placeholder', 'children'),
Output("closeout_store", "data")],
[Input('save_to_postgres', 'n_clicks'),
Input("interval", "n_intervals")],
[State('closeout_table', 'data'),
State('closeout_store', 'data')],
prevent_initial_call=True)
def df_to_csv(n_clicks, n_intervals, dataset, s):
output = html.Plaintext("The data has been saved to your PostgreSQL database.",
style={'color': 'green', 'font-weight': 'bold', 'font-size': 'large'})
no_output = html.Plaintext("", style={'margin': "0px"})
input_triggered = dash.callback_context.triggered[0]["prop_id"].split(".")[0]
if input_triggered == "save_to_postgres":
s = 6
pg = pd.DataFrame(dataset)
for index, row in pg.iterrows():
insertdata = "('"+str(row[0])+"','"+str(row[1])+"','"+str(row[2])+"','"+str(row[3])+"','"+str(row[4])+"')"
print("insertdata:", insertdata)
try:
db.session.execute("INSERT INTO plants.closeout(event_id, cause, damage, action, closeout_date)OVERRIDING SYSTEM VALUE values" + insertdata + "on conflict (event_id) do nothing")
print ("row inserted:", insertdata)
db.session.commit()
except psycopg2.IntegrityError:
print("Row already exist")
pass
except Exception as e:
print("some insert error:", e, "ins: ", insertdata)
db.session.commit()
return output, s
elif input_triggered == 'interval' and s > 0:
s = s - 1
if s > 0:
return output, s
else:
return no_output, s
elif s == 0:
return no_output, s
#____________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________#
# Callback for display the Execution Timeline Table in the webpage
@app.callback(Output('execution_timeline_postgres_datatable', 'children'),
[Input('interval_pg', 'n_intervals')])
def populate_datatable(n_intervals):
execution_timeline = pd.read_sql_query(psql_event_execution_timeline, con = db.engine)
return [
dash_table.DataTable(
id = 'execution_timeline_table',
data = execution_timeline.to_dict('records'),
columns = [
{'id':'event_id', 'name': 'Event ID'},
{'id':'comments_or_updates', 'name': 'Comments'},
{'id':'recommendation', 'name': 'Recommendation'},
{'id':'realized_value', 'name': 'Realized Value'},
{'id': 'execution_timeline', 'name': 'Execution Timeline'},
{'id': 'last_execution_responsible_name', 'name': 'Last Execution Responsible Name'},
{'id': 'next_updated_date', 'name': 'Next Updated Date'},
],
editable =True,
filter_action = "native",
sort_action = "native",
sort_mode = "multi",
merge_duplicate_headers=True,
),
html.Div(id='table-dropdown-4'),
]
# Callback for adding new rows in execution timeline table
@app.callback(
Output('execution_timeline_table', 'data'),
[Input('editing-rows-button', 'n_clicks')],
[State('execution_timeline_table', 'data'),
State('execution_timeline_table', 'columns')],
prevent_initial_call=True)
def add_row(n_clicks, rows, columns):
if n_clicks > 0:
rows.append({c['id']: '' for c in columns})
return rows
# Callback for store the update data/ new data to the postgres table for execution timeline
@app.callback(
[Output('execution_timeline_placeholder', 'children'),
Output("execution_timeline_store", "data")],
[Input('save_to_postgres', 'n_clicks'),
Input("interval", "n_intervals")],
[State('execution_timeline_table', 'data'),
State('execution_timeline_store', 'data')],
prevent_initial_call=True)
def df_to_csv(n_clicks, n_intervals, dataset, s):
output = html.Plaintext("The data has been saved to your PostgreSQL database.",
style={'color': 'green', 'font-weight': 'bold', 'font-size': 'large'})
no_output = html.Plaintext("", style={'margin': "0px"})
input_triggered = dash.callback_context.triggered[0]["prop_id"].split(".")[0]
if input_triggered == "save_to_postgres":
s = 6
pg = pd.DataFrame(dataset)
for index, row in pg.iterrows():
insertdata = "('"+str(row[0])+"','"+str(row[1])+"','"+str(row[2])+"','"+str(row[3])+"','"+str(row[4])+"','"+str(row[5])+"','"+str(row[6])+"')"
print("insertdata:", insertdata)
try:
db.session.execute("INSERT INTO plants.execution_timeline(event_id, comments_or_updates, recommendation, realized_value, execution_timeline, last_execution_responsible_name, next_updated_date) OVERRIDING SYSTEM VALUE values" + insertdata + "on conflict (event_id) do nothing")
print ("row inserted:", insertdata)
db.session.commit()
except psycopg2.IntegrityError:
print("Row already exist")
pass
except Exception as e:
print("some insert error:", e, "ins: ", insertdata)
db.session.commit()
return output, s
return output, s
elif input_triggered == 'interval' and s > 0:
s = s - 1
if s > 0:
return output, s
else:
return no_output, s
elif s == 0:
return no_output, s
#____________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________#
# Callback for the tabs selection
@app.callback(
Output('tabs-example-content-1', 'children'),
Input('tabs-example', 'value')
)
def render_content(tab):
if tab == 'tab-1':
return event_detection_table
elif tab == 'tab-2':
return event_action_table
elif tab == 'tab-3':
return closeout_table
elif tab == 'tab-4':
return execution_timeline_table
if __name__ == '__main__':
app.run_server(debug=True)
I’d much appreciate your all guidance
Thanks