update a table on pandas by id

Question

Hi I have a code connected to DB (SQLite) this is the data frame that shows on the web app

Dataframe

as you can see, in row 9 there is no value in the result column what I want to happen is that a user can update a specific result by row id

this is a snippet of the code:

if selected == 'Editor':
        project_names = st.selectbox('Select Project:', ['Lightspeed', 'Quantom'])

        if project_names == 'Lightspeed':
            con = sqlite3.connect('Performance.db')
            c = con.cursor()
            c.execute('''
                                      SELECT
                                      *
                                      FROM Results
                                      ''')

            # machines_option = ('Select Machine', ('QLS250', 'QLS450', 'QLS650', 'QLS800'))
            tdf = pd.DataFrame(c.fetchall(), columns=['Name', 'Test', 'Machine', 'SmartNics', 'Result'])

what I want to do exactly is that a user can choose a row and update a column in that row

Is that possible?

p.s: if any information is missing please let me know

Thanks!

Answer 1

This is possible study the sqlite update statement at https://www.sqlitetutorial.net/sqlite-update/.

• Create a streamlit widget like number input for the user to input the ID to modify. Call it var1.
• Create a number input widget for the result. Call it var2
• Create a button, once the user has completed the input, update sqlite db with the following statement when the button is pressed.

UPDATE Results
SET Result = var2
WHERE ID = var1;

Results is the table name.

Sample code

You can add data and update data by id.

import streamlit as st 
import sqlite3
import pandas as pd


conn = sqlite3.connect('performance.db')
c = conn.cursor()

def create_result_table():
    c.execute('CREATE TABLE IF NOT EXISTS Results(ID integer PRIMARY KEY, Name TEXT, Result integer)')

def add_result(name, result):
    c.execute('INSERT INTO Results(name, result) VALUES (?,?)', (name, result))
    conn.commit()

def update_result(id, result):
    c.execute('UPDATE Results SET result=? WHERE ID=?', (result, id))
    conn.commit()


create_result_table()

df = pd.read_sql_query("SELECT * from Results", conn)
st.table(df)

st.subheader('Add')
with st.form(key='add', clear_on_submit=True):
    name = st.text_input(label='Enter name')
    result = st.number_input(label='Enter result value', min_value=0)
    if st.form_submit_button(label='Add'):
        add_result(name, result)

st.subheader('Update')
with st.form(key='update', clear_on_submit=True):
    idnum = st.number_input(label='Enter id to update', min_value=0)
    result = st.number_input(label='Enter result value', min_value=0)
    if st.form_submit_button(label='Update'):
        update_result(idnum, result)

st.subheader('Updated table')
df = pd.read_sql_query("SELECT * from Results", conn)
st.table(df)

conn.close()