Summary
trying to update the members table and insert changes into the audit_trails table but it is not working and not throwing up any error.members data are retrieved correctly, i am working in a local environment
Steps to reproduce
Code snippet:
# Function to retrieve the member's information from the database and update it
def retrieve_member(member_id):
# Retrieve the member's existing information from the database using their ID
cursor.execute("SELECT * FROM members WHERE id=?", (member_id,))
existing_member = cursor.fetchone()
# If the member is found, display their information and allow updates
if existing_member:
# Create a form to wrap the input fields
with st.form(key="update_form"):
# Editable fields
name = st.text_input("Name:", existing_member[1])
membership_no = st.text_input("Membership No:", existing_member[2])
gender = st.text_input("Gender:", existing_member[3])
institute = st.text_input("Institute:", existing_member[4])
state_chapter = st.text_input("State Chapter:", existing_member[5])
organization_name = st.text_input("Organization Name:", existing_member[6])
address = st.text_area("Address:", existing_member[7])
phone_number = st.text_input("Phone Number:", existing_member[8])
whatsapp_number = st.text_input("WhatsApp Number:", existing_member[9])
year_of_induction = st.text_input("Year of Induction:", existing_member[10])
status_grade = st.text_input("Status Grade:", existing_member[11])
email_address = st.text_input("Email Address:", existing_member[12])
state_residing = st.text_input("State Residing:", existing_member[13])
dateofbirth = st.text_input("Date of Birth:", existing_member[16]) # Display and edit Date of Birth
acronyms = st.text_input("Acronyms:", existing_member[17])
# Editable passport field
uploaded_file = st.file_uploader("Upload Passport Photo:", type=["jpg", "jpeg", "png"], key="passport")
if uploaded_file is not None:
# Display the uploaded passport photo
passport_image = Image.open(uploaded_file)
st.image(passport_image, caption="Uploaded Passport Photo", use_column_width=True)
# Add a button to save the updated information
if st.form_submit_button("Save"):
save_member(member_id, existing_member, name, membership_no, gender, institute, state_chapter,
organization_name, address, phone_number, whatsapp_number, year_of_induction,
status_grade, email_address, state_residing, dateofbirth, acronyms, uploaded_file)
# Function to save the updated member information
def save_member(member_id, existing_member, name, membership_no, gender, institute, state_chapter, organization_name,
address, phone_number, whatsapp_number, year_of_induction, status_grade,
email_address, state_residing, dateofbirth, acronyms, uploaded_file):
try:
# Check if any required field is empty
if any(value == '' for value in [name, membership_no, gender, institute, state_chapter, organization_name,
address, phone_number, whatsapp_number, year_of_induction, status_grade,
email_address, state_residing, dateofbirth, acronyms]):
st.error("All fields must be filled")
return
# Retrieve the current timestamp
timestamp = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
# Compare each field with the existing member data to identify changes
changed_fields = {}
for field_name, new_value, old_value in [
("name", name, existing_member[1]),
("membership_no", membership_no, existing_member[2]),
("gender", gender, existing_member[3]),
("institute", institute, existing_member[4]),
("state_chapter", state_chapter, existing_member[5]),
("organization_name", organization_name, existing_member[6]),
("address", address, existing_member[7]),
("phone_number", phone_number, existing_member[8]),
("whatsapp_number", whatsapp_number, existing_member[9]),
("year_of_induction", year_of_induction, existing_member[10]),
("status_grade", status_grade, existing_member[11]),
("email_address", email_address, existing_member[12]),
("state_residing", state_residing, existing_member[13]),
("dateofbirth", dateofbirth, existing_member[16]),
("acronyms", acronyms, existing_member[17])
]:
if new_value != old_value:
changed_fields[field_name] = (old_value, new_value)
# Insert audit trail records for each changed field
for field_name, (old_value, new_value) in changed_fields.items():
cursor.execute("INSERT INTO audit_trail (member_id, field_name, old_value, new_value, timestamp) VALUES (?, ?, ?, ?, ?)",
(member_id, field_name, str(old_value), str(new_value), timestamp)) # Use str() to convert values to strings
# Convert the uploaded passport image to a byte array
passport_blob = None
if uploaded_file is not None:
passport_image = Image.open(uploaded_file)
image_byte_array = io.BytesIO()
passport_image.save(image_byte_array, format='PNG')
passport_blob = image_byte_array.getvalue()
# Update the member's information in the database
cursor.execute("UPDATE members SET name=?, membership_no=?, gender=?, institute=?, state_chapter=?, "
"organization_name=?, address=?, phone_number=?, whatsapp_number=?, year_of_induction=?, "
"status_grade=?, email_address=?, state_residing=?, dateofbirth=?, acronyms=?, passport=? WHERE id=?",
(name, membership_no, gender, institute, state_chapter, organization_name, address,
phone_number, whatsapp_number, year_of_induction, status_grade, email_address,
state_residing, dateofbirth, acronyms, passport_blob, member_id))
# Commit the changes to the database
connection.commit()
st.success("Member information updated and saved!")
except Exception as e:
st.error(f"Error: {str(e)}")
# Streamlit app
def update_member():
st.title("Update Member")
# Add an input field for the member's ID and a search button
member_id = st.text_input("Member ID:")
if st.button("Search"):
retrieve_member(member_id)
update_member()
If applicable, please provide the steps we should take to reproduce the error or specified behavior.
Expected behavior:
update the members table and insert changes into the audit_trails table when save_member funtion runs.
Actual behavior:
No error was thrown up but not working.
Debug info
- Streamlit version: ( 1.26.0)
- Python version: (3.11.2)
- i am using virtualenv
- OS version: Windows 10 64 bit professional
- Browser version: Opera GX, Google chrome