Hi all,
I created a dash datatable to monitor the database data as a table. I want to change the data in the database with using editable datatable functionality. However, when I use active cell, I am not able to get the changed data in the respective cell and process those changes in the database. I need to understand how each change can be stored in a variable so that I can apply those changes in the database. Does anyone know how I can implement this ?
Thanks in advance,
Hi,
Thank you so much, I will check.
Can you help me understand what does this part for ?
if callback_context.triggered[0]["prop_id"].split(".")[0] == my_table.id:
style = [{
'if': {
'filter_query': f"{{product}} eq '{i}'"
},
'color': 'tomato',
'fontWeight': 'bold'
} for i in df1[df0['amount'] != df1['amount']]['product']]
else:
style = []
if callback_context.triggered[0]["prop_id"].split(".")[0] == my_btn1.id:
with engine.begin() as connection:
df0[["product", "amount", "user"
]][df0['amount'] != df1['amount']].to_sql('sales_targets',
con=engine,
index=False,
if_exists='append')
The if statement is used to know which trigger fired. It can be simplified now.
FYI
The first block is to find the modified row, and the second is to submit it to the database.
Hello @secret,
The code proposed will add items to the database, if you want to modify an existing row, youâd have to use a different interaction with the database tables.
This example takes a snapshot of the sql table and compares it to the data that is present in the table, then inserts things that didnt match as new rows.
Alternatively, you can used something like this to determine your changes:
State("information", "data"),
State("information", "data_previous"),
You can then iterate through the two sets of data and see where there was a change, then push the change to your database. Or you could filter the data to only the differences and then iterate and push.
That is what I was thinking but instead of iterating both tables all over I wanted to add visited rows into a set and just iterate through the set to decrease the number of iteration so I wanted to use active cell for that but could not figure out if I need two different callbacks for this action.
Do you want your changes to be real time in your database, or do you want to store up the changes and then push them in one sweep with a button?
@jinnyzor ,Real time changes needed. So, I have a datatable getting the data from the database I want to submit the changes on the database when I click on submit button and when I click on submit button I should have the visited rows previous data updated data iterate over the visited rows and exchange it from new data to old data( it can be done in pandas) and submit the data frame to sql server(with UPDATE ).
Here is a clientside callback that I use:
It builds a secondary table with the changes, using the Key (unique identifier column, identifier). Shows the column that was changed and the oldvalue and the newvalue:
app.clientside_callback(
"""function addToHistory(ts, data, data_previous, diff_store_data, keys) {
if (ts) {
let difference = data.filter(x => !data_previous.includes(x))[0]
let old = data_previous.filter(x => !data.includes(x))[0]
newKeys = Object.keys(difference)
keys = keys.toLowerCase().split('|')
info = ''
oldValue = ''
newValue = ''
column = ''
for (x=0; x<keys.length; x++) {
for (y=0; y<newKeys.length; y++) {
if (newKeys[y].toLowerCase() == keys[x]) {
newData = String(difference[newKeys[y]])
if (newData.includes('[')) {
newData = newData.split(']')[0].split('[')[1]
}
info += newData + '|'
}
if (difference[newKeys[y]] != old[newKeys[y]]) {
column = newKeys[y]
oldValue = old[newKeys[y]]
newValue = difference[newKeys[y]]
}
}
}
info = info.substring(0,info.length-1)
diff_store_data.unshift({"Key":info, "Column":column, "OldValue":oldValue, "NewValue":newValue})
return diff_store_data
}}""",
Output("history", "data"),
[Input("information", "data_timestamp")],
[
State("information", "data"),
State("information", "data_previous"),
State("history", "data"),
State('keys', 'value'),
], prevent_initial_call=True
)
Then on âsubmitâ, you roll all the changes up to their key and submit the changes to the backend.
Also, I have another clientside callback which uses the key to undo a change upon deletion of the record:
app.clientside_callback(
"""function reloadHistory(ts, data, data_previous, history_data, keys) {
if (ts && data.length > 0) {
let difference = data_previous.filter(x => !history_data.includes(x))[0]
newKeys = Object.keys(data[0])
oldKeys = keys.split('|')
for (x=0; x<data.length; x++) {
test = false
for (y=0; y<oldKeys.length; y++) {
for (z=0; z<newKeys.length; z++) {
if (newKeys[z].toLowerCase() == oldKeys[y]) {
newData = String(data[x][newKeys[z]])
try {
if (newData.includes('[')) {
newData = newData.split(']')[0].split('[')[1]
} } catch {}
if (String(newData) == difference['Key'].split('|')[y]) {
test = true
} else {
test = false
break
}
}
}
if (!test) {
break
}
}
if (test) {
data[x][difference['Column']] = difference['OldValue']
break
}
}
return data
}}""",
Output("information", "data"),
Input("history", "data_timestamp"),
[State('information', 'data'),
State('history', 'data_previous'),
State('history', 'data'),
State('keys', 'value')], prevent_initial_call=True
)
Please note, I have my keys delimited by â|â for ease, you could substitute with a list or just one for something where there is only one identifier.
I allow for multiple keys in the event that I dont want to use a record uid, but instead of combination of two columns.
thank you so much for the response @jinnyzor . I am new to dash and frontend. Concepts are a little confusing but thank you so much. I will work on it to understand deeply. So, do you think I should integrate the submit click in to this callback ? Because this process should be done after process button is clicked.
With how you described what you want to accomplish:
You will want to store changes made to that table.
Then you want to submit all the changes to the database.
Yeah, I would use a button push. It gives you more flexibility of what you want to do. Plus, you can reject or give feedback on changes that didnt work without interrupting workflow.
Dash and Plotly is pretty flexible with what it can accomplish.
If you have questions, this community is always very helpful, and at least should point you in the right direction.
@jinnyzor thank you so much. Will check your code and try to implement. I need to understand clientside_callback more.