Spaces:
Paused
Paused
File size: 5,200 Bytes
e4fe207 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 |
# student.py
import streamlit as st
import pandas as pd
from app5_selectbox.database_con import cursor, db_connection
from app5_selectbox.app5_selectbox_func import display_table, generate_unique_4
import re
import pandas as pd
import streamlit as st
# Function to add data analysis to the student table
def analyze_student_data(cursor):
# Fetch distinct program codes
cursor.execute("SELECT DISTINCT prog_code FROM program")
program_codes = [code[0] for code in cursor.fetchall()]
# Select a program code
selected_program = st.selectbox("Select Program Code", program_codes)
# Update the SQL query to fetch the year along with the class section
cursor.execute(f"""SELECT class.class_section, class.class_year, COUNT(student.stud_id)
FROM class
LEFT JOIN student ON class.class_id = student.class_id
WHERE class.prog_id = (SELECT prog_id FROM program WHERE prog_code = '{selected_program}')
GROUP BY class.class_section, class.class_year""")
analysis_data = cursor.fetchall()
# Create a DataFrame to display the updated analysis
df = pd.DataFrame(analysis_data, columns=['Section', 'Year', 'Number of Students'])
# Display the updated analysis in a table
st.write(f"Data Analysis - Number of Students per Section in Program ({selected_program})")
st.dataframe(df)
# Your code for connecting to the database and obtaining the cursor
def student(table_name):
stud_id = generate_unique_4(cursor, "stud_id", table_name)
stud_name = st.text_input("Student Name", key="stud_name").upper()
stud_username = st.text_input("Student Username", key="stud_username")
stud_password = st.text_input("Student Password", type="password", key="stud_password")
# Fetch data from the "class" and "program" tables to populate the selectboxes
cursor.execute("SELECT program.prog_code FROM program")
program_data = cursor.fetchall()
program_options = [code[0] for code in program_data]
selected_program = st.selectbox("Select Program Code", program_options, key="selected_program")
# Fetch class data based on the selected program
cursor.execute(f"""SELECT class.class_id, class.class_year, class.class_section, program.prog_code
FROM class
JOIN program ON class.prog_id = program.prog_id
WHERE program.prog_code = '{selected_program}'""")
class_data = cursor.fetchall()
class_options = [f"{year}{section} - ID:{class_id}" for class_id, year, section, prog_code in class_data]
selected_class = st.selectbox("Select Class", sorted(class_options), key="selected_class")
if st.button("Insert Student Record"):
# Check if all fields are filled
if not stud_name or not stud_username or not stud_password or not selected_class:
st.error("All fields are required. Please provide values for all fields.")
else:
try:
# Extract class_id from the selected option
selected_class_id = re.search(r'ID:(\d+)', selected_class).group(1)
# Check if the username is already taken
cursor.execute(f"SELECT stud_username FROM student WHERE stud_username = {stud_username}")
result = cursor.fetchone()
if result:
st.error("Username already taken. Please choose a different username.")
else:
# Insert a record into the student table
cursor.execute(f"""INSERT INTO student (stud_id, stud_name, stud_username, stud_password, class_id)
VALUES ({stud_id}, {stud_name}, {stud_username}, {stud_password}, {selected_class_id})""")
db_connection.commit()
st.success("Record inserted successfully.")
except Exception as e:
st.error(f"An error occurred: {str(e)}")
# Call the data analysis function to display student data analysis
analyze_student_data(cursor)
# Function to display the table with an additional column for concatenation
def display_student_table(cursor, table_name):
selected_program = st.selectbox("Select Program Code", program_options, key="display_student_table")
cursor.execute(f"SELECT s.*, c.class_year, c.class_section, p.prog_code, "
"p.prog_code || c.class_year || c.class_section AS custom_column "
f"FROM {table_name} s "
"JOIN class c ON s.class_id = c.class_id "
"JOIN program p ON c.prog_id = p.prog_id")
data = cursor.fetchall()
df = pd.DataFrame(data, columns=['stud_id', 'stud_name', 'stud_username', 'stud_password', 'class_id',
'is_eval', 'user_type', 'class_year', 'class_section', 'prog_code', 'custom_column'])
df = df[df['prog_code'] == selected_program]
st.dataframe(df.style.set_properties(**{'text-align': 'center'}))
# Call the function to display the table with the concatenated column
display_student_table(cursor, table_name) |