To delete a row and re-read the update data from Postgresql in Streamlit

Using Streamlit
1

Summary

How I can delete any row in Postgresql database and then show the updated datatable to user in Streamlit.

Steps to reproduce

Code snippet:

add code here

If applicable, please provide the steps we should take to reproduce the error or specified behavior.

Expected behavior:

Explain what you expect to happen when you run the code above.

Actual behavior:

Explain the undesired behavior or error you see when you run the code above.
If you’re seeing an error message, share the full contents of the error message here.

Debug info

  • Streamlit version: (get it with $ streamlit version)
  • Python version: (get it with $ python --version)
  • Using Conda? PipEnv? PyEnv? Pex?
  • OS version:
  • Browser version:

Requirements file

Using Conda? PipEnv? PyEnv? Pex? Share the contents of your requirements file here.
Not sure what a requirements file is? Check out this doc and add a requirements file to your app.

Links

  • Link to your GitHub repo:
  • Link to your deployed app:

Additional information

If needed, add any other context about the problem here.

2

Hi @Mayank_Rathore,

Thanks for posting!

Here’s a section in our docs that you’ll find helpful to get you started:

Following the example provided in the docs link above, you can modify it to include the delete button as below:

# streamlit_app.py

import streamlit as st
import psycopg2

# Initialize connection.
# Uses st.cache_resource to only run once.
@st.cache_resource
def init_connection():
    return psycopg2.connect(**st.secrets["postgres"])

conn = init_connection()

# Perform query.
# Uses st.cache_data to only rerun when the query changes or after 10 min.
@st.cache_data(ttl=600)
def run_query(query, params=None):
    with conn.cursor() as cur:
        cur.execute(query, params)
        return cur.fetchall()

# Allow users to input the ID of the row to delete
row_id_to_delete = st.text_input("Enter the ID of the row to delete:")

# Delete button to delete a row based on the input ID
if st.button("Delete Row"):
    run_query("DELETE FROM mytable WHERE id = %s;", (row_id_to_delete,))
    st.success(f"Row with ID {row_id_to_delete} has been deleted.")

# Fetch and display the updated data
rows = run_query("SELECT * from mytable;")
for row in rows:
    st.write(f"{row[0]} has a :{row[1]}:")

I hope this helps!

3

Hello! I’m assuming you want to automatically reload and display the updated table in a Streamlit web application after deleting a row in a PostgreSQL database. Here’s how you can achieve this:

import streamlit as st
import psycopg2
import pandas as pd

# Connect to the database
connection = psycopg2.connect(
    host="your_host",
    user="your_username",
    password="your_password",
    database="your_database"
)

# Function to delete a row and update the displayed table
def delete_and_refresh_data(row_id):
    cursor = connection.cursor()

    # Define the SQL query to delete the row
    delete_query = f"DELETE FROM your_table WHERE id = {row_id}"

    # Execute the delete query
    cursor.execute(delete_query)
    connection.commit()

    # Fetch updated data
    cursor.execute("SELECT * FROM your_table")
    updated_data = cursor.fetchall()

    cursor.close()

    # Display the updated data in a Streamlit table
    st.write(pd.DataFrame(updated_data, columns=["Column1", "Column2", ...]))

st.title("Data Table")

# Fetch and display initial data
cursor = connection.cursor()
cursor.execute("SELECT * FROM your_table")
initial_data = cursor.fetchall()
cursor.close()

st.write(pd.DataFrame(initial_data, columns=["Column1", "Column2", ...]))

# Get user input for row deletion
row_id_to_delete = st.number_input("Enter the ID of the row to delete:")
if st.button("Delete"):
    delete_and_refresh_data(row_id_to_delete)

# Close the database connection
connection.close()

Hope this helps!

1 Like
4

Hi @tonykip ,
Thanks a lot for the detailed answer. I am able to get the app up and running when it loads for the first time. I am also able to read the table in my app. But, when I click on “Delete” button:, I am getting this error:

2023-09-06 13:34:01.896 Uncaught app exception
Traceback (most recent call last):
File “C:\Users\xxxx\Anaconda3\lib\site-packages\streamlit\runtime\caching\storage\in_memory_cache_storage_wrapper.py”, line 87, in get
entry_bytes = self._read_from_mem_cache(key)
File “C:\Users\xxxx\Anaconda3\lib\site-packages\streamlit\runtime\caching\storage\in_memory_cache_storage_wrapper.py”, line 137, in _read_from_mem_cache
raise CacheStorageKeyNotFoundError(“Key not found in mem cache”)
streamlit.runtime.caching.storage.cache_storage_protocol.CacheStorageKeyNotFoundError: Key not found in mem cache

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File “C:\Users\xxx\Anaconda3\lib\site-packages\streamlit\runtime\caching\cache_data_api.py”, line 634, in read_result
pickled_entry = self.storage.get(key)
File “C:\Users\xxxx\Anaconda3\lib\site-packages\streamlit\runtime\caching\storage\in_memory_cache_storage_wrapper.py”, line 89, in get
entry_bytes = self._persist_storage.get(key)
File “C:\Users\xxxx\Anaconda3\lib\site-packages\streamlit\runtime\caching\storage\local_disk_cache_storage.py”, line 155, in get
raise CacheStorageKeyNotFoundError(
streamlit.runtime.caching.storage.cache_storage_protocol.CacheStorageKeyNotFoundError: Local disk cache storage is disabled (persist=None)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File “C:\Users\xxxx\Anaconda3\lib\site-packages\streamlit\runtime\scriptrunner\script_runner.py”, line 552, in _run_script
exec(code, module.dict)
File “C:\Users\xxxx\Desktop\laptop-backup\desktop-data\Jellyfishpoc\GloproxxxxStreamlitpoc\src\app\main.py”, line 339, in
run_query(“DELETE FROM xxxx_dev.xxxx_explorer WHERE project_id = %s;”, (row_id_to_delete,))
File “C:\Users\xxxx\Anaconda3\lib\site-packages\streamlit\runtime\caching\cache_utils.py”, line 211, in wrapper
return cached_func(*args, **kwargs)
File “C:\Users\xxxx\Anaconda3\lib\site-packages\streamlit\runtime\caching\cache_utils.py”, line 240, in call
return self._get_or_create_cached_value(args, kwargs)
File “C:\Users\xxxx\Anaconda3\lib\site-packages\streamlit\runtime\caching\cache_utils.py”, line 266, in _get_or_create_cached_value
return self._handle_cache_miss(cache, value_key, func_args, func_kwargs)
File “C:\Users\xxxx\Anaconda3\lib\site-packages\streamlit\runtime\caching\cache_utils.py”, line 320, in _handle_cache_miss
computed_value = self._info.func(*func_args, **func_kwargs)
File “C:\Users\xxxx\Desktop\laptop-backup\desktop-data\Jellyfishpoc\GloproxxxxStreamlitpoc\src\app\main.py”, line 332, in run_query
return cur.fetchall()
psycopg2.ProgrammingError: no results to fetch

5

Hi @gaurovgiri ,
Thanks a lot for the help. This did work with some tweaks.
Thanks again.

6

This topic was automatically closed 2 days after the last reply. New replies are no longer allowed.