Example : An Integrated Streamlit code in Python to show all CRUD operations.
from time import strftime
import streamlit as st
import os
from datetime import date
from sqlalchemy import create_engine, text
import time
# Connect to MySQL
engine = create_engine("mysql+pymysql://root:@localhost/REMS")
st.title("Database connected Successfully")
# Default user data
default_user_data = {
"slno": "",
"date": date.today(),
"brokername": "",
"fname": "",
"mname": "",
"addr": "",
"peraddr": "",
"nat": "India",
"rel": "Hindu",
"contno": 0,
"whatno": 0,
"email": "",
"nonmat": False,
"mat": False,
"inter": False,
"grad": False,
"pg": False,
"others": False,
"gender": "Female",
"age": 0,
"occu": "",
"marristatus": "Unmarried",
"aadharno": 0,
"panno": 0,
"bankname": "",
"ifsccode": 0,
"accno": 0,
"remarks": "N/A"
}
def clear_fields():
for key in default_user_data:
st.session_state[key + "2"] = default_user_data[key]
def fetch_emails():
query = text("SELECT email5 FROM Broker_Details")
with engine.connect() as conn:
result = conn.execute(query)
emails = [row[0] for row in result]
return emails if emails else ["No emails found"]
def fetch_record(email):
query = text("""
SELECT Slno5, Date5, BrokerName5, FName5, MName5, Addr5, PerAddr5, Nation5, Religion5, Contno5, Whatno5, Email5,
NonMat5, Mat5, Inter5, Grad5, Pg5, Others5, Gender5, Age5, Occu5, MarriStatus5, Aadharno5, Panno5,
BankName5, IfscCode5, AccNo5, Remarks5
FROM Broker_Details
WHERE email5 = :email
LIMIT 1
""")
with engine.connect() as conn:
result = conn.execute(query, {"email": email}).fetchone()
if result:
keys = list(default_user_data.keys())
return dict(zip(keys, result))
return None
# Initialize session state
if "User_data" not in st.session_state:
st.session_state.User_data = default_user_data.copy()
else:
for key in default_user_data:
st.session_state.User_data.setdefault(key, default_user_data[key])
# Email selection
email_options = fetch_emails()
st.write("<div style='text-align: center;color:green;'><h1>Broker Details</h1></div>", unsafe_allow_html=True)
search1 = st.selectbox("Select Your Email", ["Select One"] + email_options, key="search2")
# Load user data when email selected
if search1 != "Select One":
user_data = fetch_record(search1)
if user_data:
st.session_state.User_data = user_data
else:
st.warning("No record found for the selected email.")
with st.form("form1"):
# Populate form fields
slno1 = st.text_input('Slno', value=st.session_state.User_data.get("slno", ""), key="slno2")
date1 = st.date_input("Date", value=st.session_state.User_data.get("date", date.today()), key="date2")
broname1 = st.text_input('Broker Name', value=st.session_state.User_data.get("brokername", ""), key="broname2")
fname1 = st.text_input('Father Name', value=st.session_state.User_data.get("fname", ""), key="fname2")
mname1 = st.text_input('Mother Name', value=st.session_state.User_data.get("mname", ""), key="mname2")
addr1 = st.text_area('Address', value=st.session_state.User_data.get("addr", ""), key="addr2")
peraddr1 = st.text_area('Permanent Address', value=st.session_state.User_data.get("peraddr", ""), key="peraddr2")
nat1 = st.selectbox("Nationality", ('India', 'USA', 'Nepal', 'Bhutan', 'sriLanka'),
index=['India', 'USA', 'Nepal', 'Bhutan', 'sriLanka'].index(st.session_state.User_data.get("nat", "India")), key="nat2")
rel1 = st.radio('Religion', ["Hindu", "Muslim", "Others"], index=["Hindu", "Muslim", "Others"].index(st.session_state.User_data.get("rel", "Hindu")), key="rel2")
cont1 = st.number_input("Contact Number", value=int(st.session_state.User_data.get("contno", 0)), key="cont2")
whatsapp1 = st.number_input("WhatsApp Number", value=int(st.session_state.User_data.get("whatno", 0)), key="whatsapp2")
email1 = st.text_input("Email ID", value=st.session_state.User_data.get("email", ""), key="email2")
st.write("Qualification")
nonmat1 = st.checkbox("Non Matric", value=bool(int(st.session_state.User_data.get("nonmat", 0))), key="nonmat2")
mat1 = st.checkbox("Matric", value=bool(int(st.session_state.User_data.get("mat", 0))), key="mat2")
inter1 = st.checkbox("Intermediate", value=bool(int(st.session_state.User_data.get("inter", 0))), key="inter2")
grade1 = st.checkbox("Graduate", value=bool(int(st.session_state.User_data.get("grad", 0))), key="grade2")
pg1 = st.checkbox("Post Graduate", value=bool(int(st.session_state.User_data.get("pg", 0))), key="pg2")
oth1 = st.checkbox("Others", value=bool(int(st.session_state.User_data.get("others", 0))), key="oth2")
gen1 = st.radio("Gender", ["Male", "Female", "Transgender"],
index=["Male", "Female", "Transgender"].index(st.session_state.User_data.get("gender", "Female")), key="gen2")
age1 = st.number_input("Age", value=int(st.session_state.User_data.get("age", 0)), key="age2")
occu1 = st.text_input("Occupation", value=st.session_state.User_data.get("occu", ""), key="occu2")
marr1 = st.radio("Married Status", ["Married", "Unmarried"],
index=["Married", "Unmarried"].index(st.session_state.User_data.get("marristatus", "Unmarried")), key="marr2")
aadhar1 = st.number_input("Aadhar Number", value=int(st.session_state.User_data.get("aadharno", 0)), key="aadhar2")
pan1 = st.number_input("Pan Number", value=int(st.session_state.User_data.get("panno", 0)), key="pan2")
bank1 = st.text_input("Bank Name", value=st.session_state.User_data.get("bankname", ""), key="bank2")
ifsc1 = st.number_input("Ifsc Code", value=int(st.session_state.User_data.get("ifsccode", 0)), key="ifsc2")
accno1 = st.number_input('Account Number', value=int(st.session_state.User_data.get("accno", 0)), key="accno2")
remark1 = st.text_input("Remarks", value=st.session_state.User_data.get("remarks", "N/A"), key="remark2")
col1, col2, col3, col4, col5 = st.columns(5)
with col1:
if st.form_submit_button("Submit"):
query = text("""
INSERT INTO Broker_Details (Slno5, Date5, BrokerName5, FName5, MName5, Addr5, PerAddr5, Nation5, Religion5, Contno5, Whatno5, Email5,
NonMat5, Mat5, Inter5, Grad5, Pg5, Others5, Gender5, Age5, Occu5, MarriStatus5, Aadharno5, Panno5, BankName5, IfscCode5, AccNo5, Remarks5)
VALUES (:slno1, :date1, :broname1, :fname1, :mname1, :addr1, :peraddr1, :nat1, :rel1, :cont1, :whatsapp1, :email1, :nonmat1, :mat1, :inter1,
:grade1, :pg1, :oth1, :gen1, :age1, :occu1, :marr1, :aadhar1, :pan1, :bank1, :ifsc1, :accno1, :remark1)
""")
with engine.connect() as conn:
conn.execute(query, locals())
conn.commit()
st.success("Data inserted Successfully")
with col2:
if st.form_submit_button("Reset", on_click=clear_fields):
st.rerun()
with col3:
if st.form_submit_button("Update"):
query = text("""
UPDATE Broker_Details SET Slno5 = :slno1, Date5 = :date1, BrokerName5 = :broname1, FName5 = :fname1, MName5 = :mname1, Addr5 = :addr1,
PerAddr5 = :peraddr1, Nation5 = :nat1, Religion5 = :rel1, Contno5 = :cont1, Whatno5 = :whatsapp1, Email5 = :email1,
NonMat5 = :nonmat1, Mat5 = :mat1, Inter5 = :inter1, Grad5 = :grade1, Pg5 = :pg1, Others5 = :oth1,
Gender5 = :gen1, Age5 = :age1, Occu5 = :occu1, MarriStatus5 = :marr1, Aadharno5 = :aadhar1, Panno5 = :pan1,
BankName5 = :bank1, IfscCode5 = :ifsc1, AccNo5 = :accno1, Remarks5 = :remark1
WHERE Email5 = :email1
""")
with engine.connect() as conn:
conn.execute(query, locals())
conn.commit()
st.success("Record Updated Successfully")
import time # Make sure this is at the top of your file
with col4:
if st.form_submit_button("Delete"):
if search1 != "Select One":
query = text("DELETE FROM Broker_Details WHERE Email5 = :email")
with engine.connect() as conn:
conn.execute(query, {"email": email1})
conn.commit()
st.success(" Record deleted successfully!")
time.sleep(2) # <-- Delay so the message is visible
st.rerun()
else:
st.warning("Please select an email to delete.")
with col5:
if st.form_submit_button("Exit"):
st.write("App is closing...")
os._exit(0)
0 Comments