import streamlit as st import sqlite3 import os # ---------------- DATABASE SETUP ---------------- DB_PATH = "/tmp/telangana.db" # Hugging Face writable directory def create_tables(): conn = sqlite3.connect(DB_PATH) cursor = conn.cursor() cursor.execute(""" CREATE TABLE IF NOT EXISTS districts ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT UNIQUE )""") cursor.execute(""" CREATE TABLE IF NOT EXISTS categories ( id INTEGER PRIMARY KEY AUTOINCREMENT, district_id INTEGER, category_name TEXT, FOREIGN KEY(district_id) REFERENCES districts(id) )""") cursor.execute(""" CREATE TABLE IF NOT EXISTS products ( id INTEGER PRIMARY KEY AUTOINCREMENT, category_id INTEGER, product_name TEXT, description TEXT, image_data BLOB, FOREIGN KEY(category_id) REFERENCES categories(id) )""") conn.commit() conn.close() create_tables() # ---------------- FUNCTIONS ---------------- def add_district(name): conn = sqlite3.connect(DB_PATH) cursor = conn.cursor() cursor.execute("INSERT OR IGNORE INTO districts(name) VALUES(?)", (name,)) conn.commit() conn.close() def add_category(district_name, category_name): conn = sqlite3.connect(DB_PATH) cursor = conn.cursor() cursor.execute("SELECT id FROM districts WHERE name=?", (district_name,)) district_id = cursor.fetchone() if district_id: cursor.execute("INSERT INTO categories(district_id, category_name) VALUES(?, ?)", (district_id[0], category_name)) conn.commit() conn.close() def add_product(category_id, product_name, description, image_bytes): conn = sqlite3.connect(DB_PATH) cursor = conn.cursor() cursor.execute(""" INSERT INTO products(category_id, product_name, description, image_data) VALUES(?, ?, ?, ?)""", (category_id, product_name, description, image_bytes)) conn.commit() conn.close() def get_all_data(): conn = sqlite3.connect(DB_PATH) cursor = conn.cursor() cursor.execute(""" SELECT d.name, c.category_name, p.product_name FROM districts d JOIN categories c ON d.id = c.district_id JOIN products p ON c.id = p.category_id """) data = cursor.fetchall() conn.close() return data # ---------------- APP CONFIG ---------------- st.set_page_config(page_title="Telangana Products Explorer", page_icon="🌾", layout="wide") menu = st.sidebar.radio("Navigation", ["Home", "Add Data", "View by District"]) # ---------------- HOME PAGE ---------------- if menu == "Home": st.title("🌾 Telangana Famous Products") st.subheader("Explore Products by District") data = get_all_data() if data: grouped = {} for district, category, product in data: grouped.setdefault(district, {}).setdefault(category, []).append(product) for district, categories in grouped.items(): with st.expander(f"📍 {district}"): for category, products in categories.items(): st.write(f"{category}: {', '.join(products)}") else: st.info("No data available. Please add data in 'Add Data' section.") # ---------------- ADD DATA PAGE ---------------- elif menu == "Add Data": st.header("Add New Data") # Add District with st.form("add_district_form"): district_name = st.text_input("District Name") submitted = st.form_submit_button("Add District") if submitted and district_name: add_district(district_name) st.success(f"✅ District '{district_name}' added!") st.write("---") # Add Category with st.form("add_category_form"): conn = sqlite3.connect(DB_PATH) cursor = conn.cursor() cursor.execute("SELECT name FROM districts") district_list = [row[0] for row in cursor.fetchall()] conn.close() if district_list: selected_district = st.selectbox("Select District", district_list) category_name = st.text_input("Category Name") cat_submitted = st.form_submit_button("Add Category") if cat_submitted and selected_district and category_name: add_category(selected_district, category_name) st.success(f"✅ Category '{category_name}' added to {selected_district}") else: st.warning("Please add districts first.") st.write("---") # Add Product with st.form("add_product_form"): conn = sqlite3.connect(DB_PATH) cursor = conn.cursor() cursor.execute(""" SELECT c.id, c.category_name, d.name FROM categories c JOIN districts d ON c.district_id = d.id """) category_list = [(row[0], f"{row[2]} - {row[1]}") for row in cursor.fetchall()] conn.close() if category_list: category_dict = {label: cat_id for cat_id, label in category_list} selected_category_label = st.selectbox("Select Category", list(category_dict.keys())) product_name = st.text_input("Product Name") product_description = st.text_area("Product Description") product_image = st.file_uploader("Product Image", type=["png", "jpg", "jpeg"]) prod_submitted = st.form_submit_button("Add Product") if prod_submitted and selected_category_label and product_name: image_bytes = product_image.getvalue() if product_image else None add_product( category_dict[selected_category_label], product_name, product_description, image_bytes ) st.success(f"✅ Product '{product_name}' added!") else: st.warning("Please add categories first.") # ---------------- VIEW BY DISTRICT ---------------- elif menu == "View by District": conn = sqlite3.connect(DB_PATH) cursor = conn.cursor() cursor.execute("SELECT name FROM districts") districts = [row[0] for row in cursor.fetchall()] conn.close() if districts: selected_district = st.selectbox("Select District", districts) if selected_district: conn = sqlite3.connect(DB_PATH) cursor = conn.cursor() cursor.execute(""" SELECT c.category_name, p.product_name, p.description, p.image_data FROM districts d JOIN categories c ON d.id = c.district_id JOIN products p ON c.id = p.category_id WHERE d.name = ? """, (selected_district,)) rows = cursor.fetchall() conn.close() if rows: st.write(f"### 📍 {selected_district}") category_dict = {} for category, product, description, image in rows: category_dict.setdefault(category, []).append((product, description, image)) for category, products in category_dict.items(): st.markdown(f"#### 🏷 {category}") for product_name, description, image_data in products: col1, col2 = st.columns([1, 3]) with col1: if image_data: st.image(image_data, use_column_width=True) with col2: st.write(f"**{product_name}**") st.caption(description) st.write("---") else: st.info("No products available for this district.") else: st.warning("Please add districts first.")