Can a simple CRUD app be made with streamlit?

Curious if it’s currently possible to create a streamlit app where I just ask for inputs from a user and then the input values are then inserted/written back to a database. With streamlit’s top to bottom execution model, not sure if that is possible. I guess this leads to my next question. Is there a way to wait for input data to be fully provided prior to streamlit executing everything?

2 Likes

I apologize, looks like I need to somehow utilize @st.cache to “suspend” or capture data that I want to write to a database with per this topic: Not to run entire script when a widget value is changed?.

Anybody in the community has a minimum example of writing back to a database?

EDIT: Looks like this GiHub issue is also relevant: https://github.com/streamlit/streamlit/issues/168#issuecomment-544256387

Hi @pybokeh

I see three issues with accepting user input to write to a database

  1. only connecting to the database once
  2. waiting for input data to be fully provided
  3. clearing input fields on submit

Regarding 1, this would be a good use case for the @cache decorator, as you could cache your database connection so it’s only initialized once.

If you try to do this now you’ll probably encounter an error saying cannot cache database client or something similar.

This should be fixed with https://github.com/streamlit/streamlit/issues/551 which is currently being worked on.

As a workaround you could do something like this

import streamlit as st
from pymongo import MongoClient

if not hasattr(st, "client"):
    st.client = MongoClient('mongodb://127.0.0.1/local')
collection = st.client.local.user

name = st.text_input('name')
if name:
    collection.insert_one({'name': name})

st.write(list(collection.find()))

Regarding 2, this is not currently possible but is being discussed at Have a widget not trigger an immediate update?

Regarding 3, if you reload the report after inputting data a second record will be inserted, as the widgets maintain their state, so you’ll probably want to clear the inputs after inserting.

This is not currently possible but is being discussed at https://github.com/streamlit/streamlit/issues/623

1 Like

Thanks @Jonathan_Rhone! I was actually able to address your # 2 item by following this person’s YouTube steamlit NLP demo. Basically, he implemented a button whereby when you click on it, it grabs the values from the textbox or other widgets and execute accordingly. Here’s the link to his app.py source file.

I was also able to take care of your item # 1 using st.cache decorator. But yeah as for your item # 3, I was not able to find a way to address that. But, not a big concern for me at the moment. I can patiently wait for a fix for that.

At work, I was able to make a CRUD app with streamlit by following the pattern that the NLP demo used, so I am glad! The only issue with my CRUD app is that the widget text boxes still had the value that was entered (your # 3 item) and I also had to clear the cache to force update of cached data. These are minor issues and I am confident that will be addressed in future versions of streamlit.

1 Like

Amazing :smiley: Glad most of your issues are sorted and hopefully we can resolve item 3 soon!

Thanks! Having the ability to make CRUD apps will definitely open up streamlit to many possibilities or applications. Most business/corporate world’s applications are client-server CRUD apps.

1 Like

Please, can you share CRUD project with us, maybe the GitHub repo

Sorry, my CRUD app is hosted at my company’s GitHub Enterprise at the moment. Hopefully, I have time this weekend to make a non-confidential/stripped version available on my personal GitHub. In the meantime, I hope my sample code below will suffice and give you an idea.

My CRUD app is a multi-page app where links to the individual pages are located on the left sidebar. Then each page or link is dedicated to one specific task:

  • read a table and return as dataframe
  • add a new record
  • delete a record,
  • update a record

Below is my early sample streamlit code to render the page for returning results from a PostgreSQL table and displaying the results either as a pandas dataframe or raw HTML table.

# This page allows the user to view a list of members
# By default, the output will be a streamlit rendered pandas dataframe
# But there is also an option to render the output in raw HTML so that
# the member's profile URL can be visited by clicking on the
# "Profile" hyperlink that is only available in the raw HTML version.

import psycopg2
import os
import pandas as pd
import streamlit as st

# Obtain username/password saved as environment variables
user = os.environ['windowsuser']
pwd = os.environ['windowspwd']

# @st.cache prevents the streamlit app from executing redundant processes
# repeatedly that are expensive such as database connections or reading input
@st.cache(allow_output_mutation=True)
def get_query_results():
    """ A function that returns a table of members.
        Until streamlit provides a way to pageinate results,
        maximum of 1000 rows will be returned.
        Output can be either a streamlit rendered dataframe
        or raw HTML version.  Raw HTML version will provide
        a hyperlink that will take you directly to the person's
        company profile page.  This can be used to double-check
        that the profile URL has been correctly generated.
    """

    # Connect to the PostgreSQL database server
    with psycopg2.connect(host='redacted.somecompany.com',
                          port='5432',
                          database='your_database',
                          user=user,
                          password=pwd) as conn:

        sql = """
        SELECT
            *
        FROM
            public.basic_member_info
        ORDER BY
            last_name
        LIMIT 1000
        """

        # Execute query and return results as a pandas dataframe
        df = pd.read_sql(sql, conn, index_col=None)

        # Define a function to create a "Profile" hyperlink
        def createProfileHref(url: str):
            """ Function to create a new column that converts URL as HTML hyperlink """
    
            value = '<a href="' + url + '"' + "/>Profile</a>"
    
            return value

        # Apply the function we created above and create our new hyperlink column
        df['profile_href'] = df['web_url'].apply(createProfileHref)

        # Change order of dataframe columns
        df = df[['profile_href', 'first_name', 'last_name', 'web_id', 
                 'web_url', 'is_ahm']]

    return df

def write():
    """ Writes content to the app """

    st.title("Get Members Data from PostgreSQL")

    # Check to see if checkbox was checked or not (boolean) and will be used to
    # determine if the output should be a streamlit dataframe or raw HTML.
    html = st.checkbox(
        'OPTIONAL: Render output as raw html to access the \"Profile\" hyperlink. ' +
         'Otherwise, just click on Execute botton.',
        False)

    # Define what happens when user clicks on the "Execute" button
    if st.button("Execute"):
        '''
        ### Query results:
        '''
        if html:
            # Render or display the raw HTML version of the dataframe
            st.write(get_query_results().to_html(escape=False, index=False), unsafe_allow_html=True)
        else:
            # Render or display the streamlit dataframe
            st.dataframe(get_query_results())

if __name__ == "__main__":
    write()

Please note that since Streamlit doesn’t support multi-page setup out-of-the-box, I used this very nice streamlit repo: https://github.com/MarcSkovMadsen/awesome-streamlit and adjusted my code accordingly. Just clone this repo and look at his code. You can then easily see how you can tailor or adapt your streamlit app code to be multi-page oriented.

BTW, I hope the streamlit team will adopt a way for us to easily make multi-page app. That would be awesome!

I hope with this example code above, you can then see how to make simple modifications to then create pages to perform a SQL UPDATE, INSERT, DELETE, etc.

So it is possible to create a CRUD app with streamlit!