Daily Tips - What rows of data have I modified? 🔨

Hi there,

Although the web app is very feature-rich now, I know that you will still download the file and modify it in Excel and then paste it back. Because that could be the data that affects your bonus, you are very deliberate, do the math, and fiddle with your formula in Excel. When you’re finally done, paste it into the web page and click Submit. Wait, which items did I just submit?

What if my table has such a feature?
Dash2
That username was typed by hand and can actually get the current user by calling some function.

Ctrl+V :arrow_heading_down:
Dash5

I couldn’t wait for the plotly teams to add the on-change flag to the dash_table, so I implemented one with the callback.

I have a table like this in my database.

id product amount user timestamp
11 TVs 3,000,000 Tom 2022-04-10 05:41:16.878357
12 refrigerators 1,980,000 Tom 2022-04-10 05:41:16.878357
11 rice cookers 800,000 John 2022-04-10 05:41:16.878357
12 water heaters 920,000 Tom 2022-04-10 05:41:16.878357

The id and timestamp are auto-increment sequences. The way this table is updated is by inserting new records all the time. I use the window function in the query to extract the latest records to display while keeping the history. If I had on-change flags or something, it would be easier to pick out which data needed to be uploaded. :man_shrugging:

Hi @Salah , is this one of more reliable way you mentioned?

Here’s my code.

from dash import Dash, html, dash_table, Output, Input, State, no_update, callback_context
import pandas as pd
from sqlalchemy import create_engine
from dash.dash_table.Format import Format, Symbol

engine = create_engine(
    'postgresql+psycopg2://postgres:postgres@127.0.0.1/postgres')

columns = ["product", "amount", "user", "timestamp"]
column_type = {
    k: v
    for k, v in zip(columns, ['text', 'numeric', 'text', 'datetime'])
}

app = Dash(__name__)

app.layout = html.Div([
    html.H1('Sales Targets'), my_table :=
    dash_table.DataTable(columns=[{
        "name":
        i,
        "id":
        i,
        'editable':
        True if i not in ['product', 'timestamp'] else False,
        'type':
        column_type[i],
        'format':
        Format(symbol=Symbol.yes).group(True)
    } for i in columns],
                         editable=True,
                         data_timestamp=0,
                         export_format='xlsx',
                         include_headers_on_copy_paste=True), my_btn1 :=
    html.Button('Submit'), my_btn2 := html.Button('Refresh',
                                                  n_clicks_timestamp=0)
])


@app.callback(
    [Output(my_table, 'data'),
     Output(my_table, 'style_data_conditional')], [
         Input(my_btn1, 'n_clicks_timestamp'),
         Input(my_btn2, 'n_clicks_timestamp'),
         Input(my_table, 'data_timestamp')
     ], State(my_table, 'data'))
def check(n1, n2, t, data):

    df0 = pd.DataFrame(data=data, columns=columns)

    df1 = pd.read_sql_query('''
    SELECT "product",
    	"amount",
    	"user",
    	"timestamp"
    FROM
    	(SELECT *,
    			ROW_NUMBER() OVER(PARTITION BY "product"
    					  ORDER BY "timestamp" DESC) AS NEWEST
    		FROM PUBLIC.SALES_TARGETS) AS TMP
    WHERE TMP.NEWEST = 1;
''',
                            con=engine)

    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')

    return df1.to_dict(
        'records') if callback_context.triggered[0]["prop_id"].split(
            ".")[0] == my_btn2.id or n2 >= t else no_update, style


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

Hope this helps you. XD

Keywords: Database Fill-in, On-change Flag, Timestamp, Window Function,

3 Likes