Personal Expense Tracker (PET)

• A Streamlit + MySQL app for tracking expenses, managing users, and generating reports.
• Features a dark glassmorphism UI for quick personal finance tracking.

---

Table of Contents

• Features
• Tech stack
• Project structure
• Quick start
• Database setup
• Database schema
• SQL views (reporting)
• Application modules
• UI / UX
• Security
• Development & testing
• Contributing
• License
• Contact

---

Features

• User management — create, edit, delete users (username, email, password)
• Expense tracking — add, edit, delete expenses; filter by user
• 14 pre-built SQL views — aggregate and time-based reports; user_id views join with users to show usernames
• Dark glassmorphism UI — animated gradient background, frosted-glass containers, styled inputs
• MySQL + pandas — parameterized queries, results returned as DataFrames
• Cached connection — @st.cache_resource reuses a single DB connection per session

---

Tech stack

Component	Technology
Language	Python 3.10+
Web framework	Streamlit 1.31.1
Data manipulation	pandas 2.2.0
DB connector	mysql-connector-python 8.2.0
Database server	MySQL 8.0+
UI styling	Custom CSS (glassmorphism)

See requirements.txt for pinned versions.

---

Project structure

Personal Expense Tracker/
├─ .gitignore                      # Ignores secrets, cache, venvs, IDE files
├─ .streamlit/
│  └─ secrets.toml                 # MySQL credentials (git-ignored)
├─ Data Dictionary/
│  ├─ Data Dictionary (PDF ver.).pdf   # Schema reference (PDF)
│  └─ Data Dictionary (Sheet ver.).xlsx # Schema reference (Excel)
├─ Database & ERD/
│  ├─ ERD_expense_db.mwb           # MySQL Workbench model
│  ├─ ERD_expense_db.pdf           # ERD diagram (PDF)
│  ├─ expense_tracker_report (updated).sql  # 14 reporting views
│  ├─ personal_expense_tracker (updated).sql # DB + tables DDL
│  └─ sample_expense_entries.sql   # 50 sample users + 50 sample expenses
├─ database.py                     # Cached DB connection + run_query()
├─ expenses.py                     # Expense CRUD
├─ main.py                         # App entry, CSS theme, routing
├─ README.md                       # This file
├─ reports.py                      # Report viewer (14 SQL views)
├─ requirements.txt                # Python dependencies
└─ users.py                        # User CRUD

---

Quick start

• Clone the repo:

  git clone https://github.com/Miko-Explorer/MySQL-Based-Projects.git
  cd "MySQL-Based-Projects/Personal Expenses Tracker"

• Set up a virtual environment and install deps:

  python -m venv .venv
  source .venv/bin/activate        # Linux/macOS
  .venv\Scripts\activate           # Windows
  pip install -r requirements.txt

• Configure .streamlit/secrets.toml with MySQL credentials:

  [mysql]
  host = "localhost"
  user = "your_user"
  password = "your_password"
  database = "expense_db"
  port = 3306

  Never commit this file — it's in .gitignore.

• Run database scripts (see Database setup).

• Launch the app:

  streamlit run main.py

  Open http://localhost:8501.

---

Database setup

• Scripts live in Database & ERD/.

• Run in order:

  1. Create database and tables:

     mysql -u your_user -p < "Database & ERD/personal_expense_tracker (updated).sql"

     Creates expense_db, users, and expenses (with FK → users(id) + ON DELETE CASCADE).

  2. Create reporting views:

     mysql -u your_user -p expense_db < "Database & ERD/expense_tracker_report (updated).sql"

     Creates 14 views consumed by reports.py.

  3. (Optional) Insert sample data:

     mysql -u your_user -p expense_db < "Database & ERD/sample_expense_entries.sql"

     Adds 50 sample users and 50 sample expenses for testing.

• Alternatively, execute the SQL files in MySQL Workbench or any MySQL client.

---

Database schema

users table

Column	Type	Constraints
id	INT	PRIMARY KEY, AUTO_INCREMENT
username	VARCHAR(100)	NOT NULL, UNIQUE
email	VARCHAR(255)	NOT NULL, UNIQUE
passwords	VARCHAR(255)	NOT NULL
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP

expenses table

Column	Type	Constraints
id	INT	PRIMARY KEY, AUTO_INCREMENT
user_id	INT	NOT NULL, FK → users(id) ON DELETE CASCADE
amount_spent	DECIMAL(12,2)	NOT NULL
category	ENUM('Food','Transport','Utilities','Subscription','Health','Work','School','Entertainment','Insurance','Miscellaneous')
description	VARCHAR(500)
dates	DATE	NOT NULL
payment_method	ENUM('Debit','Credit','Cash','Online Payment')
location	VARCHAR(255)
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP
updated_at	TIMESTAMP	ON UPDATE CURRENT_TIMESTAMP

---

SQL views (reporting)

expense_tracker_report (updated).sql creates 14 views:

View name	Display name	user_id
high_expense_based_cat	Highest Amount Spent per Category (by User)	Yes
low_expense_based_cat	Lowest Amount Spent per Category (by User)	Yes
high_amount_paid_based_paymethod	Highest Amount Paid per Payment Method (by User)	Yes
low_amount_paid_based_paymethod	Lowest Amount Paid per Payment Method (by User)	Yes
latest_created_expense	10 Most Recent Expenses	No
outdated_created_expense	30 Oldest Expenses	No
recently_updated_expense	Most Recently Updated Expenses	No
not_updated_expense	Expenses Never Updated (oldest first)	No
total_amount_spent	Total Amount Spent (by User)	Yes
average_amount_spent_based_cat	Average Amount Spent per Category (by User)	Yes
average_amount_spent_based_paymethod	Average Amount Paid per Payment Method (by User)	Yes
total_amount_paid_based_paymethod	Total Amount Paid per Payment Method (by User)	Yes
total_amount_spent_based_cat	Total Amount Spent per Category (by User)	Yes
total_entries	Total Number of Expense Entries	No

• Views with user_id are LEFT JOINed with users to show username.
• Empty results display "No data available for this report."

---

Application modules

Module	File	Role
Entry point	main.py	Page config, glassmorphism CSS, sidebar logo + radio nav, page routing
Database layer	database.py	get_db_connection() (cached) + run_query() — parameterized executor returning DataFrame or row count
User management	users.py	show_users() — display table, add/edit/delete with confirmation; dynamic UPDATE for changed fields; cascading delete
Expense management	expenses.py	show_expenses() — filter by user, full CRUD form (category, amount, date, payment method, location, description); guards empty user table
Report viewer	reports.py	show_reports() — queries 14 views in 4-per-tab layout; joins users on user_id views

---

UI / UX

• Dark glassmorphism theme — animated gradient background, frosted containers, rounded inputs, subtle borders
• Sidebar — #1E1E24 backdrop, centered "PET" logo with glow, hidden-label radio nav (Users / Expenses / Reports)
• Interactions — blue glow on button hover, transparent data tables, st.rerun() on mutations for instant refresh

---

Security

• Plain-text passwords — passwords column stores cleartext. Hash with bcrypt/Argon2 before deploying.
• Secrets — .streamlit/secrets.toml is git-ignored. Use env vars or a secrets manager in production.
• SQL injection — prevented by parameterized queries (maintain this pattern).
• Recommended — input validation, rate limiting, TLS/SSL for DB and deployment.

---

Development & testing

• Run locally: ensure MySQL is running with expense_db created, then streamlit run main.py
• Schema changes: update reports.py (view names, display names, user_id set) if altering views
• Testing: no test suite yet. Consider:
  • Unit tests for run_query() with a mock connection
  • Integration tests with a dedicated test DB
  • streamlit.testing for UI tests

---

Contributing

• Fork the repo, create a feature branch (feat/your-feature), make changes, and open a PR.
• Avoid committing secrets or large binaries.

---

Contact

Maintained by Miko-Explorer — open an issue on GitHub.