Delete Code in Streamlit

Example : Streamlit program to delete a specific record from an Xampp MySql database without a confirmation message.

import datetime
from datetime import date
from sqlalchemy import create_engine, text
import streamlit as st

# Database connectivity using SQLAlchemy
engine = create_engine("mysql+pymysql://root:@localhost/cakebake")
st.success("Database Connected Successfully")

# Function to fetch all unique emails from the database
def hang():
    query = text("SELECT DISTINCT email5 FROM registration")
    with engine.connect() as conn:
        result = conn.execute(query)
        emails = [row[0] for row in result]
    return emails if emails else ["No emails found"]

# Function to fetch user details based on selected email
def fetch_record(email):
    query = text(
        "SELECT name5, age5, email5, gen5, pass5, addr5, dob5, nat5, matric5, inter5, grad5, rem5 FROM registration WHERE email5 = :email LIMIT 1"
    )
    with engine.connect() as conn:
        result = conn.execute(query, {"email": email}).fetchone()
        if result:
            return dict(
                zip(["name", "age", "email", "gender", "password", "address", "dob", "nationality", "matric", "inter",
                     "grad", "remarks"], result)
            )
        return None

# Fetch emails for dropdown
email_options = hang()

# Initialize session state
if "user_data" not in st.session_state:
    st.session_state.user_data = {}

# Function to delete a record from MySQL
def delete_record(email):
    query = text("DELETE FROM registration WHERE email5 = :email")
    with engine.connect() as conn:
        conn.execute(query, {"email": email})
        conn.commit()
    st.success(f"✅ Record with email **{email}** deleted successfully!")

# Dropdown to select email
selected_email = st.selectbox("Choose an email to find record", ["Choose One"] + email_options, key="search2")

# If an email is selected, fetch and update session state
if selected_email != "Choose One":
    user_data = fetch_record(selected_email)
    if user_data:
        st.session_state.user_data = user_data

# Streamlit UI Form
st.title("User Registration Form")
with st.form("form1"):
    name1 = st.text_input("Enter Your Name", value=st.session_state.user_data.get("name", ""), max_chars=30, key="name2")

    age1 = st.number_input("Enter Your Age", value=int(st.session_state.user_data.get("age", 18)or 18), key="age2")

    email1 = st.text_input("Enter Your Email", value=st.session_state.user_data.get("email", ""), key="email2")

    gender1 = st.radio(
        "Select Your Gender",
        ["Male", "Female", "Transgender"],
        index=["Male", "Female", "Transgender"].index(st.session_state.user_data.get("gender", "Male")),
        key="gender2"
    )

    passwd1 = st.text_input('Enter Password', value=st.session_state.user_data.get("password", ""), type="password", key="passwd2")

    addr1 = st.text_area('Enter Your Address', value=st.session_state.user_data.get("address", ""), height=150, key="addr2")

    # Set and Fix DOB format issue
    try:
        dob_value = datetime.datetime.strptime(st.session_state.user_data.get("dob", date.today().strftime("%d-%m-%Y")), "%d-%m-%Y").date()
    except ValueError:
        dob_value = date.today()

    dob1 = st.date_input("Choose Your Birth Date", value=dob_value, key="dob2")

    nat1 = st.selectbox(
        "Select Your Nationality",
        ('India', 'USA', 'Nepal', 'Bhutan', 'Sri Lanka'),
        index=['India', 'USA', 'Nepal', 'Bhutan', 'Sri Lanka'].index(st.session_state.user_data.get("nationality", "India")),
        key="nat2"
    )

    st.write("Choose Your Qualification")
    matric1 = st.checkbox("Matric", value=bool(int(st.session_state.user_data.get("matric", 0))), key="matric2")
    inter1 = st.checkbox("Intermediate", value=bool(int(st.session_state.user_data.get("inter", 0))), key="inter2")
    grad1 = st.checkbox("Graduation", value=bool(int(st.session_state.user_data.get("grad", 0))), key="grad2")

    rem1 = st.text_input("Enter Your Remarks, if any", value=st.session_state.user_data.get("remarks", "N/A"), key="rem2")

    # Buttons Code
    col1, col2, col3, col4, col5 = st.columns(5)
    with col1:
        submit1 = st.form_submit_button("Submit")
    with col2:
        reset1 = st.form_submit_button("Reset")
    with col3:
        if st.form_submit_button("Delete"):
            if selected_email != "Choose One":
                delete_record(selected_email)                
            else:
                st.warning("⚠️ Please select an email to delete.")
    with col4:
        update1 = st.form_submit_button("Update")
    with col5:
        search1 = st.form_submit_button("Search")

Example : Streamlit program to delete a specific record from an Xampp MySql database with a confirmation message.

import datetime
from datetime import date
from sqlalchemy import create_engine, text
import streamlit as st

# Database connectivity using SQLAlchemy
engine = create_engine("mysql+pymysql://root:@localhost/cakebake")
st.success("Database Connected Successfully")

# Ensure 'show_confirm' is initialized in session state
if "show_confirm" not in st.session_state:
    st.session_state.show_confirm = False

# Function to fetch all unique emails from the database
def hang():
    query = text("SELECT DISTINCT email5 FROM registration")
    with engine.connect() as conn:
        result = conn.execute(query)
        emails = [row[0] for row in result]
    return emails if emails else ["No emails found"]

# Function to fetch user details based on selected email
def fetch_record(email):
    query = text(
        "SELECT name5, age5, email5, gen5, pass5, addr5, dob5, nat5, matric5, inter5, grad5, rem5 FROM registration WHERE email5 = :email LIMIT 1"
    )
    with engine.connect() as conn:
        result = conn.execute(query, {"email": email}).fetchone()
        if result:
            return dict(
                zip(["name", "age", "email", "gender", "password", "address", "dob", "nationality", "matric", "inter",
                     "grad", "remarks"], result)
            )
        return None
# Fetch emails for dropdown
email_options = hang()

# Function to delete a record from MySQL
def delete_record(email):
    query = text("DELETE FROM registration WHERE email5 = :email")
    with engine.connect() as conn:
        conn.execute(query, {"email": email})
        conn.commit()
    st.success(f"✅ Record with email **{email}** deleted successfully!")

# Initialize session state
if "user_data" not in st.session_state:
    st.session_state.user_data = {}

# Dropdown to select email
selected_email = st.selectbox("Choose an email to find record", ["Choose One"] + email_options, key="search2")

# If an email is selected, fetch and update session state
if selected_email != "Choose One":
    user_data = fetch_record(selected_email)
    if user_data:
        st.session_state.user_data = user_data

# Streamlit UI Form
st.title("User Registration Form")
with st.form("form1"):
    name1 = st.text_input("Enter Your Name", value=st.session_state.user_data.get("name", ""), max_chars=30, key="name2")

    age1 = st.number_input("Enter Your Age", value=int(st.session_state.user_data.get("age", 18)or 18), key="age2")

    email1 = st.text_input("Enter Your Email", value=st.session_state.user_data.get("email", ""), key="email2")

    gender1 = st.radio(
        "Select Your Gender",
        ["Male", "Female", "Transgender"],
        index=["Male", "Female", "Transgender"].index(st.session_state.user_data.get("gender", "Male")),
        key="gender2"
    )

    passwd1 = st.text_input('Enter Password', value=st.session_state.user_data.get("password", ""), type="password", key="passwd2")

    addr1 = st.text_area('Enter Your Address', value=st.session_state.user_data.get("address", ""), height=150, key="addr2")

    # Set and Fix DOB format issue
    try:
        dob_value = datetime.datetime.strptime(st.session_state.user_data.get("dob", date.today().strftime("%d-%m-%Y")), "%d-%m-%Y").date()
    except ValueError:
        dob_value = date.today()

    dob1 = st.date_input("Choose Your Birth Date", value=dob_value, key="dob2")

    nat1 = st.selectbox(
        "Select Your Nationality",
        ('India', 'USA', 'Nepal', 'Bhutan', 'Sri Lanka'),
        index=['India', 'USA', 'Nepal', 'Bhutan', 'Sri Lanka'].index(st.session_state.user_data.get("nationality", "India")),
        key="nat2"
    )

    st.write("Choose Your Qualification")
    matric1 = st.checkbox("Matric", value=bool(int(st.session_state.user_data.get("matric", 0))), key="matric2")
    inter1 = st.checkbox("Intermediate", value=bool(int(st.session_state.user_data.get("inter", 0))), key="inter2")
    grad1 = st.checkbox("Graduation", value=bool(int(st.session_state.user_data.get("grad", 0))), key="grad2")

    rem1 = st.text_input("Enter Your Remarks, if any", value=st.session_state.user_data.get("remarks", "N/A"), key="rem2")

    # Buttons Code
    col1, col2, col3, col4, col5 = st.columns(5)
    with col1:
        submit1 = st.form_submit_button("Submit")
    with col2:
        reset1 = st.form_submit_button("Reset")
    with col3:
        if st.form_submit_button("Delete"):
            if selected_email != "Choose One":
                st.session_state.show_confirm = True  # Show confirmation popup
            else:
                st.warning("⚠️ Please select an email to delete.")
    with col4:
        update1 = st.form_submit_button("Update")
    with col5:
        search1 = st.form_submit_button("Search")

# Confirmation Message Before Deletion
if st.session_state.show_confirm:
    st.warning(f"⚠️ Are you sure you want to delete the record for **{selected_email}**?")
    col_confirm1, col_confirm2 = st.columns(2)
    with col_confirm1:
        if st.button("Yes, Delete"):
            delete_record(selected_email)
            st.session_state.show_confirm = False  # Hide confirmation
    with col_confirm2:
        if st.button("Cancel"):
            st.session_state.show_confirm = False  # Hide confirmation