Aggrid Editable Dataframe with Session State and Postgres

[Parth_Chokhra]

Summary

Hey, I am trying to deploy a streamlit app connected to the Postgres database. The app is a multiuser app and each user is saving and fetching data from the same table. But I am having a problem, if one user saves their data in a database another user’s browser tab automatically refreshes even though I am using st.cache_data. I know having session_state might help but not sure how would I implement it in this case.

Steps to reproduce

Code snippet: main.py

import streamlit as st
import inspect
st.set_page_config(layout='wide')
# from st_pages import add_page_title
from src.sql_db import get_datas_db,save_changes_to_database,get_datas_db_2
from src import agstyler
from src.helper import dataframe_to_excel, get_download_link
from src.agstyler import PINLEFT, PRECISION_TWO
import numpy as np
import pandas as pd


def extract_player_names(df, index_locations):
    player_names = df.loc[index_locations, 'player_name'].tolist()
    return player_names


def compare_dataframes(df1, df2, rel_tol):
    numeric_cols = df1.select_dtypes(include=np.number).columns.tolist() # Get numeric columns
    non_numeric_cols = df1.select_dtypes(exclude=np.number).columns.tolist() # Get non-numeric columns
    mask_numeric = np.isclose(df1[numeric_cols], df2[numeric_cols], rtol=rel_tol) | df1[numeric_cols].eq(df2[numeric_cols])
    mask_non_numeric = df1[non_numeric_cols].eq(df2[non_numeric_cols])
    mask = pd.concat([mask_numeric, mask_non_numeric], axis=1)
    diff = ~mask
    return np.where(diff.any(axis=1))[0].tolist() # Return indices of rows that differ


def main():
    # if set:
    #     df = get_datas_db_2()
    #     set = False

    # col1, col2, col3 = st.beta_columns(3)

    if st.button("Refresh"):
        st.cache_data.clear()
    df = get_datas_db()

    
    st.subheader('Select a starting 5 for the All-Star Game')

    # with st.echo():
    positions = ['PG', 'SG', 'SF', 'PF', 'C']
    formatter = {
            'player_name': ('Player', PINLEFT),
            'team': ('Team', {'width': 80}),
            'poss': ('Possessions', {'width': 110}),
            'raptor_offense': ('RAPTOR Off', {**PRECISION_TWO, 'width': 110}),
            'raptor_defense': ('RAPTOR Def', {**PRECISION_TWO, 'width': 110}),
            'raptor_total': ('RAPTOR', {**PRECISION_TWO, 'width': 110}),
            'pos': ('Position', {
                'editable': True,
                'cellEditor': 'agSelectCellEditor',
                'cellEditorParams': {
                    'values': [''] + positions,
                },
                'width': 100
            }),
            # 'note': ('Note', {'editable': True})
        }  

    data = agstyler.draw_grid(df, formatter,fit_columns=True,
                                        selection='multiple',  # or 'single', or None
                                        use_checkbox='True',  # or False by default
                                        max_height=300)
       
    if data['selected_rows']:
        sd = data['selected_rows']
        st.info(
            'Selected players:\n\n' + '\n\n'.join(f"{p['player_name']} (RAPTOR : {p['raptor_total']}) (Possesions : {p['poss']}) (Position : {p['pos']})" for p in sd)
        )
    

# From future
    btn = st.button("📥 Download table as XLSX")
    if btn:
        download_data = dataframe_to_excel(
            df=data['data'],
            formatter=formatter,
        )
        download_link = get_download_link(
            download_data,
            filename="download.xlsx",
            caption="Download file"
        )
        st.markdown(download_link, unsafe_allow_html=True)

    btn_save_db = st.button(" Save to DataBase :)")
    if btn_save_db:
        latest = data["data"]
        earlier = get_datas_db().reset_index(drop=True)
        list_of_edited_rows = compare_dataframes(earlier,latest,0.005)
        list_of_player_names = extract_player_names(latest,list_of_edited_rows)
        st.text(str(list_of_edited_rows))
        st.text(str(list_of_player_names))
        save_changes_to_database(latest,list_of_edited_rows,list_of_player_names)
        # st.session_state["data"] = data
        
    
    
        
if __name__ == '__main__':
    main()
    # st.run_server(host="0.0.0.0",port=8501)

Code snippet: sql_db.py

import pandas as pd
from sqlalchemy import create_engine
import streamlit as st

engine = create_engine('postgresql://****:****@127.0.0.1:5432/new_database')
conn = engine.connect()


@st.cache_data
def get_datas_db():
    table_name = "players_new"
    data_read = pd.read_sql_table(table_name, engine)
    return data_read


def save_changes_to_database(df, edited_row_indexes,edited_row_names):

    # Save changes to the database
    table_name = 'players_new'  # Replace with your table name
    i = 0 
    for index in edited_row_indexes:
        # Get the row data for the current index
        row_data = df.loc[index].to_dict()
        # Generate the update query
        update_query = f"UPDATE {table_name} SET "
        update_query += ", ".join([f"{column} = '{row_data[column]}'" for column in row_data])
        update_query += f" WHERE player_name = '{edited_row_names[i]}'"
        # Execute the update query
        conn.execute(update_query)
        i+=1
    # Close the connection
    return "Changes Completed"

Expected behavior:

I don’t want session tab getting refreshed for any user till they hit Refresh button.