save the AgGrid data to sqlite with python-CodePudding

Hi im building an app to save results of projects that we do at work and I want to update a table with aggrid and it will also update the sqlite DB that im using I manage to edit on the web app but it will not update the DB if anyone can tell me how it will be great this are the libraries that I imported:

import streamlit as st
import pandas as pd
from streamlit_option_menu import option_menu
from st_aggrid import AgGrid,GridUpdateMode
from st_aggrid.grid_options_builder import GridOptionsBuilder
if selected == 'Editor':
    def data_upload():
        dfa = pd.read_sql('SELECT * FROM LightSpeed_Project',con=sqlite3.connect('Performance.db',))
        return dfa
    dfe = data_upload()
    gd = GridOptionsBuilder.from_dataframe(dfe)
    gd.configure_pagination(enabled=True)
    gd.configure_default_column(editable=True, groupable=True)
    sel_mode = st.radio('Selection Type', options=['single', 'multiple'])
    gd.configure_selection(selection_mode=sel_mode, use_checkbox=True)
    gridoptions = gd.build()
    grid_table = AgGrid(dfe, gridOptions=gridoptions,
                        update_mode=GridUpdateMode.SELECTION_CHANGED,
                        height=500,
                        allow_unsafe_jscode=True,
                        # enable_enterprise_modules = True,
                        theme='fresh')

    sel_row = grid_table["selected_rows"]
    st.subheader("Output")
    st.write(sel_row)

CodePudding user response：

Here is an example code. Notice I added GridUpdateMode.VALUE_CHANGED in update mode to see the changes in grid_table whenever a value changes. Also a button is added to update the db based from the changes returned by AgGrid.

Code

import streamlit as st 
import sqlite3
import pandas as pd
from streamlit_option_menu import option_menu
from st_aggrid import AgGrid,GridUpdateMode
from st_aggrid.grid_options_builder import GridOptionsBuilder


conn = sqlite3.connect('stocks.db')
cur = conn.cursor()

def create_table():
    cur.execute('CREATE TABLE IF NOT EXISTS product(id integer PRIMARY KEY, name TEXT, count integer)')

def add(name, count):
    cur.execute('INSERT INTO product(name, count) VALUES (?,?)', (name, count))
    conn.commit()

def update(id, count):
    cur.execute('UPDATE product SET count=? WHERE id=?', (count, id))
    conn.commit()


create_table()

# Run once to add data to table.
# add('cpu', 5)
# add('psu', 5)
# add('mother board', 8)
# add('gpu', 10)

st.write('##### Intital contents of db')
df = pd.read_sql('SELECT * FROM product', con=conn)
st.write(df)

gd = GridOptionsBuilder.from_dataframe(df)
gd.configure_pagination(enabled=True)
gd.configure_default_column(editable=True, groupable=True)
sel_mode = st.radio('Selection Type', options=['single', 'multiple'])
gd.configure_selection(selection_mode=sel_mode, use_checkbox=True)
gridoptions = gd.build()
grid_table = AgGrid(df, gridOptions=gridoptions,
                    update_mode=GridUpdateMode.SELECTION_CHANGED | GridUpdateMode.VALUE_CHANGED,
                    height=500,
                    allow_unsafe_jscode=True,
                    # enable_enterprise_modules = True,
                    theme='fresh')

sel_row = grid_table["selected_rows"]
st.subheader("Output")
st.write(sel_row)

df_selected = pd.DataFrame(sel_row)

if st.button('Update db', key=1):
    for i, r in df_selected.iterrows():
        id = r['id']
        cnt = r['count']
        update(id, cnt)

    st.write('##### Updated db')
    df_update = pd.read_sql('SELECT * FROM product', con=conn)
    st.write(df_update)

cur.close()
conn.close()