A Complete Guide to Integrating Streamlit with Databases: Building a CRUD App with SQLite

When building data-driven applications, databases are an essential component. Streamlit allows you to develop web applications with minimal code while seamlessly integrating with databases like SQLite. In this post, we’ll explore how to create a data management app with CRUD (Create, Read, Update, Delete) functionality using Streamlit and SQLite.

Why Integrate a Database with Streamlit?

Integrating Streamlit with a database offers several advantages:

• Persistent Data Storage: Store and manage application data permanently.
• Complex Query Processing: Use SQL to search, filter, and aggregate data.
• Dynamic Data Management: Create, update, and delete data based on user input.

SQLite, a file-based database, is easy to integrate with Streamlit and convenient to use.

Database Design

We’ll design a simple table using SQLite to store user data.

Database Structure

Column Name	Data Type	Description
id	INTEGER	Primary key, auto-increment
name	TEXT	Name
age	INTEGER	Age
profession	TEXT	Profession

---

3. Code Example – Integrating Streamlit with SQLite for a CRUD App

Full Code

import sqlite3
import pandas as pd
import streamlit as st

# Initialize database
def init_db():
    conn = sqlite3.connect("example.db")
    cursor = conn.cursor()
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            age INTEGER NOT NULL,
            profession TEXT NOT NULL
        )
    """)
    conn.commit()
    conn.close()

# Insert data
def insert_user(name, age, profession):
    conn = sqlite3.connect("example.db")
    cursor = conn.cursor()
    cursor.execute("INSERT INTO users (name, age, profession) VALUES (?, ?, ?)", (name, age, profession))
    conn.commit()
    conn.close()

# Read data
def read_users():
    conn = sqlite3.connect("example.db")
    df = pd.read_sql("SELECT * FROM users", conn)
    conn.close()
    return df

# Update data
def update_user(user_id, name, age, profession):
    conn = sqlite3.connect("example.db")
    cursor = conn.cursor()
    cursor.execute("""
        UPDATE users
        SET name = ?, age = ?, profession = ?
        WHERE id = ?
    """, (name, age, profession, user_id))
    conn.commit()
    conn.close()

# Delete data
def delete_user(user_id):
    conn = sqlite3.connect("example.db")
    cursor = conn.cursor()
    cursor.execute("DELETE FROM users WHERE id = ?", (user_id,))
    conn.commit()
    conn.close()

# Initialize Streamlit app
st.title("Building a CRUD App with Streamlit")
init_db()

# Tab layout
tab1, tab2, tab3, tab4 = st.tabs(["Create", "Read", "Update", "Delete"])

# Create
with tab1:
    st.header("👤 Add User")
    name = st.text_input("Name")
    age = st.number_input("Age", min_value=1, max_value=120, step=1)
    profession = st.text_input("Profession")
    if st.button("Add"):
        if name and profession:
            insert_user(name, age, profession)
            st.success(f"User {name} added successfully!")
        else:
            st.error("Please fill in all fields.")

# Read
with tab2:
    st.header("📋 User List")
    df = read_users()
    st.dataframe(df)

# Update
with tab3:
    st.header("✏️ Update User")
    df = read_users()
    user_id = st.selectbox("Select User to Update (ID)", df["id"])
    name = st.text_input("New Name")
    age = st.number_input("New Age", min_value=1, max_value=120, step=1)
    profession = st.text_input("New Profession")
    if st.button("Update"):
        update_user(user_id, name, age, profession)
        st.success("User details updated successfully!")

# Delete
with tab4:
    st.header("🗑️ Delete User")
    df = read_users()
    user_id = st.selectbox("Select User to Delete (ID)", df["id"])
    if st.button("Delete"):
        delete_user(user_id)
        st.success("User deleted successfully!")

---

Code Explanation

• Database Initialization: The init_db function sets up the database and table.
• CRUD Functions: Functions are defined for creating, reading, updating, and deleting data.
• Streamlit UI Components:
  • st.text_input, st.number_input, and st.button are used to implement the CRUD features.

By following this example, you can learn how to integrate Streamlit with a database. With just a simple integration of SQLite and Streamlit, you can build a powerful data management app and expand it into various application scenarios.