Expense Tracker System

Now you will build the third core feature: expense tracking.

Each user will be able to:

add expenses
categorize expenses
view all expenses
calculate total spending

All expenses will be stored in the database and linked to a specific user.

Goal of This Lesson

By the end of this lesson, you will:

create expense-related database functions
store financial data
calculate totals from the database

Step 1: Create Expense Service File

Create:

text

app/services/expense_service.py

Step 2: Import Database Connection

python

from app.services.database import connect

Step 3: Add Expense

python

def add_expense(user_id, amount, category):
    conn = connect()
    cursor = conn.cursor()

    cursor.execute(
        "INSERT INTO expenses (user_id, amount, category) VALUES (?, ?, ?)",
        (user_id, amount, category)
    )

    conn.commit()
    conn.close()

Step 4: Get All Expenses

python

def get_expenses(user_id):
    conn = connect()
    cursor = conn.cursor()

    cursor.execute(
        "SELECT id, amount, category FROM expenses WHERE user_id = ?",
        (user_id,)
    )

    expenses = cursor.fetchall()
    conn.close()

    return expenses

Step 5: Calculate Total Expenses

python

def get_total_expenses(user_id):
    conn = connect()
    cursor = conn.cursor()

    cursor.execute(
        "SELECT SUM(amount) FROM expenses WHERE user_id = ?",
        (user_id,)
    )

    total = cursor.fetchone()[0]
    conn.close()

    return total if total else 0

Step 6: Test in main.py

Update your main.py:

python

from app.services.database import create_tables
from app.services.user_service import register_user, login_user
from app.services.task_service import add_task, get_tasks
from app.services.expense_service import add_expense, get_expenses, get_total_expenses

def main():
    create_tables()

    register_user("tom", "123")
    user_id = login_user("tom", "123")

    # add expenses
    add_expense(user_id, 20, "Food")
    add_expense(user_id, 50, "Transport")

    # view expenses
    print("Expenses:", get_expenses(user_id))

    # total
    print("Total:", get_total_expenses(user_id))

if __name__ == "__main__":
    main()

Example Output

text

Expenses: [(1, 20, 'Food'), (2, 50, 'Transport')]
Total: 70

Data Structure

Each expense:

text

(id, amount, category)

Important Notes

amount should be a number (int or float)
SUM() returns None if no data exists
Always return 0 if total is empty

Current Project Structure

text

app/
    services/
        database.py
        user_service.py
        task_service.py
        expense_service.py

Why This Step Matters

You now have a second real data feature:

persistent financial data
calculations from database
user-specific data

This is how real finance and tracking apps work.

Summary

You built an expense tracking system that stores user expenses and calculates totals from the database.