Example : Streamlit Python codes to fetch specific records from Xampp’s MySql database and fill them into respective UI elements/boxes.
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 = {}
# 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:
delete1 = st.form_submit_button("Delete")
with col4:
update1 = st.form_submit_button("Update")
with col5:
search1 = st.form_submit_button("Search")
---------------------- OR -----------------------
import streamlit as st
from datetime import date
import os
from sqlalchemy import create_engine, text
# Database connection
engine = create_engine("mysql+pymysql://root:@localhost/GRS")
st.success("Database Connected Successfully")
# Function to fetch user details based on selected contact
def fetch_record(contact):
query = text("""
SELECT srno5, username5, dob5, gender5, nat5, contact5, tempaddress5, peraddress5, zipcode5, email5,
deg5, deprt5, staffid5, doj5, employtyp5, slry5, qulification5, university5, passyear5, specialize5,
orgname5, designation5, experience5, rfleaving5, skillexperties5, certificate5, anymedcond5,
emrgnum5, declaration5, verifiedby5, verifieddate5, remark5, approvedby5, approveddate5
FROM staffdetails WHERE contact5 = :contact LIMIT 1
""")
with engine.connect() as conn:
result = conn.execute(query, {"contact": contact}).fetchone()
if result:
keys = [
"srno", "username", "dob", "gender", "nat", "contact", "tempaddress", "peraddress", "zipcode", "email",
"deg", "deprt", "staffid", "doj", "employtyp", "slry", "qulification", "university", "passyear", "specialize",
"orgname", "designation", "experience", "rfleaving", "skillexperties", "certificate", "anymedcond",
"emrgnum", "declaration", "verifiedby", "verifieddate", "remark", "approvedby", "approveddate"
]
return dict(zip(keys, result))
return None
# Fetch contacts for dropdown list
def hang():
query = text("SELECT DISTINCT contact5 FROM staffdetails")
with engine.connect() as conn:
result = conn.execute(query)
contacts = [row[0] for row in result]
return contacts if contacts else ["No contacts are found/fetched"]
contact_options = hang()
# Initialize session state
if "user_data" not in st.session_state:
st.session_state.user_data = {}
selected_contact = st.selectbox("Choose a contact to find record", ["Choose One"] + contact_options, key="search2")
# If contact selected, fetch and update user_data
if selected_contact != "Choose One":
user_data = fetch_record(selected_contact)
if user_data:
st.session_state.user_data = user_data
# Reset function
def reset_fields():
st.session_state.srno2 = 1
st.session_state.username2 = ""
st.session_state.dob2 = None
st.session_state["gender2"] = "Male"
st.session_state["nat2"] = ""
st.session_state.contact2 = 0
st.session_state.tempaddress2 = ""
st.session_state.percpy2 = False
st.session_state.peraddress2 = ""
st.session_state.zipcoad2 = 0
st.session_state.email2 = ""
st.session_state.deg2 = ""
st.session_state["deprt2"] = None
st.session_state.staffid2 = ""
st.session_state.doj2 = None
st.session_state["employtyp2"] = None
st.session_state.slry2 = 0
st.session_state["qulification2"] = None
st.session_state["university2"] = None
st.session_state.passyear2 = None
st.session_state.specialize2 = ""
st.session_state.orgname2 = ""
st.session_state.designation2 = ""
st.session_state.experience2 = 0
st.session_state.rfleaving2 = ""
st.session_state.skillexperties2 = ""
st.session_state.certificate2 = ""
st.session_state.anymedcond2 = ""
st.session_state.emrgnum2 = 0
st.session_state.declaration2 = False
st.session_state.verifiedby2 = ""
st.session_state.verifieddate2 = None
st.session_state.remark2 = ""
st.session_state.approvedby2 = ""
st.session_state.approveddate2 = None
# Main UI Form
st.title("Staff Details:")
with st.form("form2"):
srno1 = st.number_input("Srno.", value=int(st.session_state.user_data.get("srno", 1)), key="srno2")
st.write("Presonal Info:")
username1 = st.text_input("Full Name", value=st.session_state.user_data.get("username", ""), key="username2")
dob1 = st.date_input("Choose your birth date", value=st.session_state.user_data.get("dob", date.today()), key="dob2")
gender1 = st.radio("Select anyone", ["Male", "Female", "other"], index=["Male", "Female", "other"].index(st.session_state.user_data.get("gender", "Male")), key="gender2")
nat1 = st.selectbox("Nationality", ('', 'America', 'Bhutan', 'China', 'Denmark', 'Indian', 'Other'), index=('','America','Bhutan','China','Denmark','Indian','Other').index(st.session_state.user_data.get("nat", "")), key="nat2")
contact1 = st.number_input("Contact Number", value=int(st.session_state.user_data.get("contact", 0)), key="contact2")
tempaddress1 = st.text_area("temporary Address", value=st.session_state.user_data.get("tempaddress", ""), key="tempaddress2")
percpy1 = st.checkbox("Same as Temporary", value=st.session_state.user_data.get("percpy", False), key="percpy2")
peraddress1 = st.text_area("Permanent Address", value=st.session_state.user_data.get("peraddress", ""), key="peraddress2")
zipcode1 = st.number_input("Zip Code", value=int(st.session_state.user_data.get("zipcode", 0)), key="zipcoad2")
email1 = st.text_input("E-mail:", value=st.session_state.user_data.get("email", ""), key="email2")
st.write("Staff Details:")
deg1 = st.text_input("Designation", value=st.session_state.user_data.get("deg", ""), key="deg2")
deprt1 = st.selectbox("Department", ('', 'Science', 'Arts', 'Computer science', 'Commerce'), index=('', 'Science', 'Arts', 'Computer science', 'Commerce').index(st.session_state.user_data.get("deprt", "")), key="deprt2")
staffid1 = st.text_input("Staff ID:", value=st.session_state.user_data.get("staffid", ""), key="staffid2")
doj1 = st.date_input("Date of Joining", value=st.session_state.user_data.get("doj", date.today()), key="doj2")
employtyp1 = st.radio("Employee Type", ['Permanent', 'Contract', 'Part-Time'], index=['Permanent', 'Contract', 'Part-Time'].index(st.session_state.user_data.get("employtyp", "Permanent")), key="employtyp2")
slry1 = st.number_input("Salary", value=int(st.session_state.user_data.get("slry", 0)), key="slry2")
st.write("Educational Details:")
qulification1 = st.selectbox("Highest Qualification", ('', 'M.tech', 'B.tech', 'MCA', 'MBA', 'PG', 'P.hd', 'Medical Science', 'Others'), index=('', 'M.tech', 'B.tech', 'MCA', 'MBA', 'PG', 'P.hd', 'Medical Science', 'Others').index(st.session_state.user_data.get("qulification", "")), key="qulification2")
university_list = ('', 'B.R.A. Bihar University, Muzaffarpur', 'B.N. Mandal University, Madhepura', 'Jai Prakash University, Chapra', 'K.S.D. Sanskrit University, Darbhanga', 'L.N.Mithila University, Darbhanga', 'Nalanda Open University', 'Patna University, Patna', 'T.M.Bhagalpur University, Bhagalpur', 'Veer Kunwar Singh University, Ara', 'Aryabhatta Knowledge University, Patna', 'Bihar Animal Sciences University,Patna', 'Patliputra University, Patna', 'Munger University, Munger', 'Purnea University, Purnea')
university1 = st.selectbox("University/Institute", university_list, index=university_list.index(st.session_state.user_data.get("university", "")), key="university2")
passyear1 = st.date_input("Passing Year", value=st.session_state.user_data.get("passyear", date.today()), key="passyear2")
specialize1 = st.text_input("Specialization", value=st.session_state.user_data.get("specialize", ""), key="specialize2")
st.write("Previous Employment Details")
orgname1 = st.text_input("Previous Organization Name", value=st.session_state.user_data.get("orgname", ""), key="orgname2")
designation1 = st.text_input("Perivious Designation", value=st.session_state.user_data.get("designation", ""), key="designation2")
experience1 = st.number_input("Year of Experience", value=int(st.session_state.user_data.get("experience", 0)), key="experience2")
rfleaving1 = st.text_input("Reason for Leaving:", value=st.session_state.user_data.get("rfleaving", ""), key="rfleaving2")
st.write("Additional Info:")
skillexperties1 = st.text_input("Skills & Experties:", value=st.session_state.user_data.get("skillexperties", ""), key="skillexperties2")
certificate1 = st.text_input("Certification If any:", value=st.session_state.user_data.get("certificate", ""), key="certificate2")
anymedcond1 = st.text_input("Any Medical Condition:", value=st.session_state.user_data.get("anymedcond", ""), key="anymedcond2")
emrgnum1 = st.number_input("Emergency Contact Number", value=int(st.session_state.user_data.get("emrgnum", 0)), key="emrgnum2")
st.write("Declaration")
declaration1 = st.checkbox("I hereby declare that the above provided information is true...", value=st.session_state.user_data.get("declaration", False), key="declaration2")
st.write("For Office Use Only")
verifiedby1 = st.text_input("Verified By", value=st.session_state.user_data.get("verifiedby", ""), key="verifiedby2")
verifieddate1 = st.date_input("Verified Date", value=st.session_state.user_data.get("verifieddate", date.today()), key="verifieddate2")
remark1 = st.text_input("Remark:", value=st.session_state.user_data.get("remark", "N/A"), key="remark2")
approvedby1 = st.text_input("Approved By:", value=st.session_state.user_data.get("approvedby", ""), key="approvedby2")
approveddate1 = st.date_input("Approved Date", value=st.session_state.user_data.get("approveddate", date.today()), key="approveddate2")
col1, col2, col3, col4, col5, col6 = st.columns(6)
with col1:
if st.form_submit_button("Submit"):
query = text("""
INSERT INTO staffdetails
(srno5, username5, dob5, gender5, nat5, contact5, tempaddress5, peraddress5, zipcode5, email5, deg5, deprt5, staffid5, doj5, employtyp5, slry5, qulification5, university5, passyear5, specialize5, orgname5, designation5, experience5, rfleaving5, skillexperties5, certificate5, anymedcond5, emrgnum5, declaration5,verifiedby5, verifieddate5, remark5, approvedby5, approveddate5)
VALUES
(:srno1, :username1, :dob1, :gender1, :nat1, :contact1, :tempaddress1, :peraddress1, :zipcode1, :email1, :deg1, :deprt1, :staffid1, :doj1, :employtyp1, :slry1, :qulification1, :university1, :passyear1, :specialize1, :orgname1, :designation1, :experience1, :rfleaving1, :skillexperties1, :certificate1, :anymedcond1, :emrgnum1, :declaration1, :verifiedby1, :verifieddate1, :remark1, :approvedby1, :approveddate1)
""")
with engine.connect() as conn:
conn.execute(query, {
"srno1": srno1, "username1": username1, "dob1": dob1, "gender1": gender1, "nat1": nat1,
"contact1": contact1, "tempaddress1": tempaddress1, "peraddress1": peraddress1,
"zipcode1": zipcode1, "email1": email1, "deg1": deg1, "deprt1": deprt1, "staffid1": staffid1,
"doj1": doj1, "employtyp1": employtyp1, "slry1": slry1, "qulification1": qulification1,
"university1": university1, "passyear1": passyear1, "specialize1": specialize1,
"orgname1": orgname1, "designation1": designation1, "experience1": experience1,
"rfleaving1": rfleaving1, "skillexperties1": skillexperties1, "certificate1": certificate1,
"anymedcond1": anymedcond1, "emrgnum1": emrgnum1, "declaration1": declaration1,
"verifiedby1": verifiedby1, "verifieddate1": verifieddate1, "remark1": remark1,
"approvedby1": approvedby1, "approveddate1": approveddate1
})
conn.commit()
st.success("Data inserted successfully!")
with col2:
if st.form_submit_button("Reset", on_click=reset_fields):
st.rerun()
with col3:
update1 = st.form_submit_button("Update")
with col4:
delete1 = st.form_submit_button("Delete")
with col5:
search1 = st.form_submit_button("Search")
with col6:
if st.form_submit_button("Exit"):
st.write("App is closing...")
os._exit(0)
0 Comments