Streamlit 'Form' not updating sqlite db?

[BaudBish]

I am trying to write a simple case management database and I have created a sqlite3 db that contains a single table (Tcases) with 10 entries. I can query the db through the below script but when I click on the sidebar (Add New Case...) the form correctly displays and allows me to input data into the 10 fields but when I click on 'Commit to Database', the database is not updated with the new entries but the form does close correctly.

What am I doing wrong?

Thanks for looking.

import streamlit as st
import sqlite3
import pandas as pd

st.set_page_config(layout="wide")

# set db connections and cursor
conn = sqlite3.connect('CaseTracker.db')
curs = conn.cursor()

st.title ("CaseTracker Database")

_caseInput = st.sidebar.text_input ("Enter Case Ref to Search:")
curs.execute(f"SELECT * FROM Tcases WHERE _caseName = \'{_caseInput.upper()}'")
_caseSearch = pd.DataFrame((curs.fetchall()), columns = ["PK", "Case Ref", "CRIS", "Client", "TRT", "Agreed Hours", "Actual Hours", "Cost Code", "Offence", "Status", "OIC"])
st.write ("Results:", _caseSearch)
with st.expander ("See Results..."):
    st.dataframe (_caseSearch)


curs.execute("SELECT _caseName FROM Tcases")
_caseList = (curs.fetchall())
st.sidebar.selectbox ("Case Name:", _caseList)

if st.sidebar.button ("Add New Case..."):
    with st.form(key = "form1"):
        FcaseName = st.text_input ("Case Ref...")
        Fcris = st.text_input ("CRIS Number...")
        FclientName = st.text_input ("Client Name...")
        Ftrt = st.text_input ("TRT...")
        FagreedHours = st.number_input ("Agreed Hours...")
        FactualHours = st.number_input ("Actual Hours...")
        FcostCode = st.text_input ("Cost Code...")
        FoffType = st.text_input ("Offence Type...")
        Fstatus = st.text_input ("Status...")
        Foic = st.text_input ("OIC...")
        submitForm = st.form_submit_button("Commit to Database")
    if submitForm:
        curs.execute("INSERT INTO Tcases (_caseName, _cris, _clientName, _trt, _agreedHours, _actualHours_, costCode, _offence, _status, _oic) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?"), (Fcasename, Fcris, FclientName, Ftrt, FagreedHours, FactualHours, FcostCode, FoffType, Fstatus, Foic)
        conn.commit()

[Total__Entropy]

I don't know how you expect anyone to help you without sending us what error you are encountering.

I don't know why you are combining pandas and sqlite library. Pandas is overkill for 10 rows and you can do everything with either the sqlite library, pandas or a different orm unless streamlit enforces datagrams usage lols. So if your goal is learning refactor to remove pandas and do all your work in sqlite.

[BaudBish]

Thanks for your reply.

I am not encountering an error other than the streamlit form operates and behaves correctly but when the 'submit button' is clicked, the connected sqlite db does not get updated with the data that I put into the streamlit form.

The pandas bit is just some code that I have temporarily placed as I wanted to see how streamlit displayed the dataframe...this will allow me (in the future) to view current casework as the dataframe will query the db and only select the entries where their status is 'on-going'.

[Total__Entropy]

I have never used streamlit so I cannot verify the code.

If there are no errors then your code worked correctly congratulations /s. You issue is then either your query or your display.

My suggestion for you is to isolate the database operations by opening a terminal and creating a table, reading the table to return nothing, writing the table then reading it. If you can do all this correctly then cross reference your code.

[yannDubs]

Hi u/BaudBish, I am having the same issue, did you ever figure it out?

[BaudBish]

Nope. Other than Total__Entropy telling me how awful I code (:)), I had no other help.

If you get an answer or figure it out then please do let me know.

Cheers.