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?
5 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
- only connecting to the database once
- waiting for input data to be fully provided
- 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
2 Likes
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.
3 Likes
Amazing 
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.
2 Likes
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!
6 Likes
Hi @pybokeh
When ever I read your posts I feel like we have so many things in common. 
I would really like to be able to do a multipage app with a better layout. Something like
https://getbootstrap.com/docs/4.3/examples/dashboard/
For this i would need a “navigation” widget with “nav items”. Something like shown in the sidebar of the widget. I would like to use fontawesome icons or similar for the “nav items” (and for my buttons).
I would like a “top” bar and “footer” bar as well.
I would also like some kind of “url” routing such that if I go to the dashboard page “/dashboard/” is appended to the url. And if I go to the “Orders” page “/orders/” is appended. I need this so that my users can bookmark the pages if they normally use a specific one in a (deeply nested) multipage app.
And I would need a grid layout like discussed here https://github.com/streamlit/streamlit/issues/309
3 Likes
Hi @pybokeh and others
Based on the Bootstrap Dashboard example above I’ve created a few feature requests
Thank you for your kind sentiments @Marc! Likewise as well. I think it is because we perhaps have experience in corporate world and therefore would like to see Streamlit being used as a viable platform that could be used in production in corporate world. I agree, if we have a good layout system, support for multi-pages, and a mechanism to not trigger all widgets in a page, these features will really put Streamlit on a path towards the goal that I mentioned earlier. I really appreciate your contributions and ideas!
2 Likes
@pybokeh. I could not have said it better
Thanks. I will try working on it this weekend and see
I think that this is possible without playing with the cache or without worrying too much about database connections.
I’ve got a demo app running here:
https://jupyter.mine.nu/streamlit
As you can see, it’s an example of a medical procedure database and only really provides the CR of CRUD, but at about a hundred lines of code, it serves our purposes. I’ll leave it up for a bit, though I’m expecting spam will need me to pull it down eventually!
Updates and deletions are fairly easy to implement - a select box for each entry can then trigger a delete, or a set of widgets with the defaults set to the existing values to update an entry.
(feel free to have a poke around or enter some data, its a test version. Ideally break it for me and let me know!)
Madflier
2 Likes
Thank you madflier.
Do you have the code hosted on github though?
I’m afraid that I don’t have a github.
The basic gist is just a few functions that make a database call and then display the results (this uses an sqlite3 database for persistence). The radio boxes in the sidebar determine which function is run.
Maintaining the state using only streamlit is a little tricky, but fairly easy if you can access the file system.
Just to point out, I’m afraid that this app was swapped out a while ago for something else.
However, the basic principles were pretty straightforward - sqlite3 is very fast to setup and teardown connections, and so opening a new connection each time the script runs is negligible; I never bothered caching the database connection.
As to collecting input before updating the database (or querying it) - this can easily be done by having a final “submit” button which triggers the database access at the bottom of the page.
The more recent versions of streamlit, especially the form widget, make this even easier still, but this is certainly possible with vanilla streamlit.
Adding something like a selection box to drive a multipage app, and some sensible logic on the database to avoid multiple identical updates, and it’s actually a very versatile and rapid prototyping front end for a CRUD app.