Skip to content
Tutorials Center
My learning

Topics

Lesson 26 of 38 ยท Data Connections

Editing and Saving Data with st.data_editor

Let users edit tables in Streamlit with st.data_editor. Read edited_rows, added_rows and deleted_rows, validate input and save the changes to SQLite safely.

Showing data is half of an app. The other half is letting people change it: fix a typo in a price, add a row, delete a duplicate. st.data_editor gives users a spreadsheet-like grid in the browser. Your job is to take what they typed, check it, and save it somewhere that survives a restart. This lesson covers the grid, the exact structure of the edits it reports, and a safe save-to-database pattern.

Streamlit cannot run on this site, so the st.data_editor code is read-only. The runnable examples reproduce the edit structure with plain dictionaries and save to SQLite in memory, so the save logic is real and tested.

st.data_editor basics

Python
import pandas as pd
import streamlit as st

df = pd.DataFrame({
    "product": ["tea", "coffee", "juice"],
    "price": [4.5, 3.0, 5.25],
    "in_stock": [True, True, False],
})

edited = st.data_editor(df, num_rows="dynamic", key="products")
st.write("Total price of everything:", edited["price"].sum())

The function returns a DataFrame with the user's edits applied, so you can use it like any other frame. num_rows="dynamic" lets users add rows (a plus row appears at the bottom) and delete them (select rows, press Delete). With the default "fixed" they can only edit existing cells.

Column behaviour is set with column_config:

Python
edited = st.data_editor(
    df,
    column_config={
        "product": st.column_config.TextColumn("Product", required=True, max_chars=40),
        "price": st.column_config.NumberColumn("Price (USD)", min_value=0, format="$%.2f"),
        "in_stock": st.column_config.CheckboxColumn("In stock"),
    },
    disabled=["product"],        # read-only columns
    hide_index=True,
    key="products",
)

Other column types include SelectboxColumn (a fixed list of choices), DateColumn, DatetimeColumn, LinkColumn, ImageColumn and ProgressColumn. Constraints like min_value and required stop invalid input in the browser, but treat them as a convenience: always validate again in Python before saving.

What changed? The edit state

Because the script reruns constantly, the editor's own state is stored under the key you gave it. st.session_state["products"] holds a dictionary describing the user's edits relative to the original df:

Python
{
    "edited_rows": {0: {"price": 4.75}, 2: {"in_stock": True}},
    "added_rows": [{"product": "water", "price": 1.0, "in_stock": True}],
    "deleted_rows": [1],
}

Row numbers in edited_rows and deleted_rows are positions in the original frame. This is useful when you want to save only what changed instead of rewriting the whole table. The next TryIt applies such a dictionary to a DataFrame, which is also how you can rebuild a final frame yourself.

Python Applying an edit-state dictionary

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Tea got a new price, juice came back in stock, coffee was deleted and water was added.

Validate before saving

A grid accepts whatever the user types within its column rules, and added rows can have blanks. Write a validation function that returns a list of problems:

Python
def check(df):
    problems = []
    if df["product"].isna().any() or (df["product"].astype(str).str.strip() == "").any():
        problems.append("Every product needs a name.")
    if df["product"].duplicated().any():
        problems.append("Product names must be unique.")
    if (df["price"].fillna(-1) < 0).any():
        problems.append("Prices must be zero or more.")
    return problems

Then show the problems and only enable saving when there are none:

Python
problems = check(edited)
for message in problems:
    st.error(message)
st.button("Save changes", disabled=bool(problems), on_click=save, args=(edited,))

Using a separate Save button (rather than saving on every keystroke) matches how people expect forms to work, and avoids writing half-finished edits to the database. A st.form cannot contain data_editor state tricks easily, so the button plus callback is the usual approach (see Callbacks, Forms and Fragments).

Saving to SQLite

The simplest correct save is to replace the table contents inside one transaction, so a failure halfway leaves the old data intact. For a small table that is perfectly fine. The example validates, writes with parameterized SQL and shows that an invalid frame is rejected without touching the database.

Python Validate, then save in one transaction

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The invalid frame is rejected with two messages and the table is unchanged; the valid frame replaces the contents.

Row-level saves for bigger tables

Deleting and re-inserting everything gets wasteful and risky for large tables or when several people edit at once. Use the edit-state dictionary and write only the changes, matching rows by primary key, not by position:

Python
def save_changes(conn, original, state):
    with conn:
        for row, changes in state["edited_rows"].items():
            product_id = int(original.iloc[row]["id"])
            for column, value in changes.items():
                if column not in {"price", "in_stock"}:       # allow-list of columns
                    continue
                conn.execute(f"UPDATE products SET {column} = ? WHERE id = ?", (value, product_id))
        for row in state["deleted_rows"]:
            conn.execute("DELETE FROM products WHERE id = ?", (int(original.iloc[row]["id"]),))
        for new in state["added_rows"]:
            conn.execute("INSERT INTO products (product, price, in_stock) VALUES (?, ?, ?)",
                         (new["product"], new["price"], int(new["in_stock"])))

Column names cannot be parameters in SQL, which is why this code checks the name against a fixed allow-list before putting it into the statement. That small check is what keeps a dynamic UPDATE safe from injection.

After a save, reload the original data (clear the cache if you cached it) and call st.rerun() so the grid starts from the stored state. If you skip this, the editor's saved edits and the new original can disagree and the same changes might be applied twice.

Concurrency and honesty

A database that several people edit needs a plan: last write wins is easy but can silently overwrite someone's work. Options include a version or updated_at column checked in the UPDATE ... WHERE id = ? AND version = ?, or locking rows. Tell users clearly when a save fails because someone else changed the row. For a personal tool, last write wins is usually fine.

Common mistakes

  • Saving on every rerun instead of on an explicit button.
  • Trusting browser-side column rules and skipping server validation.
  • Matching changed rows by display position instead of a primary key.
  • Building UPDATE column names from user data without an allow-list.
  • Forgetting to reload data after saving, so edits are applied twice.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does st.data_editor return?

    It returns the edited DataFrame, while the raw changes are available in st.session_state[key].
  2. 2. Which key in the edit state holds rows the user removed?

    The edit state has edited_rows, added_rows and deleted_rows.
  3. 3. Why validate in Python even though column_config has min_value and required?

    Never assume client-side limits are enforced. Check the data on the server before saving.
  4. 4. Why wrap the delete and insert in with conn: when saving?

    A transaction prevents a half-saved table if something fails between the delete and the inserts.

Summary

  • st.data_editor returns the edited DataFrame; num_rows="dynamic" allows adding and deleting rows.
  • column_config sets types and limits, disabled locks columns, but validate again on the server.
  • The edit state in st.session_state[key] has edited_rows, added_rows and deleted_rows.
  • Save from an explicit button, in one transaction, with parameterized SQL.
  • For larger tables update by primary key and allow-list column names.
  • Reload data after saving so the grid and the database agree.

Next: Building a Model App