Build a Finance Tracker App with Python and SQLite

tags: python, finance, sqlite, tutorial

---

tags: python, finance, sqlite, tutorial

---

Build a Finance Tracker App with Python and SQLite

You know that frustrating moment when you check your bank account and wonder, “Wait, where did all my money go?” Instead of scrolling through endless spreadsheets or paying for expensive subscription apps, you can build your own personal finance tracker right now using tools you likely already have: Python and SQLite.

This isn’t just another tutorial about printing “Hello, World.” We’re building something real—a command-line app that lets you add transactions, view your history, and instantly see how much you’ve spent or earned. You can run it today, tweak it tomorrow, and eventually wrap it in a GUI or web interface. Let’s get coding.

Set Up Your Project Folder

First, create a dedicated folder for your project. This keeps things organized and makes it easy to find your files later.

mkdir finance_tracker
cd finance_tracker
touch tracker.py

Open tracker.py in your favorite code editor. If you’re using VS Code, PyCharm, or even Notepad++, you’re ready to start.

Connect to SQLite and Create Your Database

SQLite is a lightweight, serverless database that’s included in Python’s standard library. No installation needed—just import it.

In tracker.py, start by connecting to a database file named finance.db. If it doesn’t exist, SQLite will create it automatically.

import sqlite3

# Connect to the database (or create it)
conn = sqlite3.connect("finance.db")
c = conn.cursor()

# Create the transactions table
c.execute('''
CREATE TABLE IF NOT EXISTS transactions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    date TEXT NOT NULL,
    category TEXT NOT NULL,
    description TEXT,
    amount REAL NOT NULL,
    type TEXT NOT NULL CHECK(type IN ('income', 'expense'))
)
''')

conn.commit()
conn.close()

This table stores every transaction with:

• A unique id
• The date (formatted as YYYY-MM-DD)
• A category (e.g., “Food,” “Salary”)
• A optional description
• The amount (positive number)
• The type (“income” or “expense”)

Using CHECK ensures data integrity, and AUTOINCREMENT gives each row a unique ID.

Add, View, and Summarize Transactions

Now let’s add the core functionality: inserting new transactions, viewing them, and calculating totals.

Add a Transaction

def add_transaction(date, category, description, amount, type):
    conn = sqlite3.connect("finance.db")
    c = conn.cursor()

    # Use parameterized queries to prevent SQL injection
    c.execute('''
        INSERT INTO transactions (date, category, description, amount, type)
        VALUES (?, ?, ?, ?, ?)
    ''', (date, category, description, amount, type))

    conn.commit()
    conn.close()
    print("✓ Transaction added successfully!")

Key point: We use ? placeholders with parameterized queries. This is critical for security and prevents SQL injection attacks [4].

View All Transactions

def view_transactions():
    conn = sqlite3.connect("finance.db")
    c = conn.cursor()

    c.execute('SELECT * FROM transactions ORDER BY date DESC')
    rows = c.fetchall()

    if not rows:
        print("No transactions found.")
        return

    print("\n" + "="*60)
    print("ID | Date       | Category     | Description      | Amount | Type")
    print("="*60)

    for row in rows:
        print(f"{row[0]:<3} | {row[1]:<10} | {row[2]:<12} | {row[3]:<16} | {row[4]:>6.2f} | {row[5]}")

    conn.close()

Summarize Your Finances

def summarize():
    conn = sqlite3.connect("finance.db")
    c = conn.cursor()

    # Total income
    c.execute('SELECT SUM(amount) FROM transactions WHERE type = "income"')
    total_income = c.fetchone()[0] or 0

    # Total expense
    c.execute('SELECT SUM(amount) FROM transactions WHERE type = "expense"')
    total_expense = c.fetchone()[0] or 0

    # Net balance
    net_balance = total_income - total_expense

    conn.close()

    print("\n" + "="*40)
    print("📊 FINANCE SUMMARY")
    print("="*40)
    print(f"Total Income:   $ {total_income:>10.2f}")
    print(f"Total Expense:  $ {total_expense:>10.2f}")
    print(f"Net Balance:    $ {net_balance:>10.2f}")
    print("="*40)

Build the Interactive Command-Line Interface

Let’s wrap everything in a simple loop that lets users interact with the app.

if __name__ == "__main__":
    # Initialize database on first run
    conn = sqlite3.connect("finance.db")
    c = conn.cursor()
    c.execute('''
        CREATE TABLE IF NOT EXISTS transactions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            date TEXT NOT NULL,
            category TEXT NOT NULL,
            description TEXT,
            amount REAL NOT NULL,
            type TEXT NOT NULL CHECK(type IN ('income', 'expense'))
        )
    ''')
    conn.commit()
    conn.close()

    while True:
        print("\n🔹 PERSONAL FINANCE TRACKER 🔹")
        print("1. Add Transaction")
        print("2. View Transactions")
        print("3. Summary")
        print("4. Exit")

        choice = input("Choose an option: ").strip()

        if choice == "1":
            date = input("Date (YYYY-MM-DD): ").strip()
            category = input("Category: ").strip()
            description = input("Description (optional): ").strip()
            amount = float(input("Amount: ").strip())
            type = input("Type (income/expense): ").strip().lower()

            if type not in ["income", "expense"]:
                print("❌ Invalid type. Use 'income' or 'expense'.")
                continue

            add_transaction(date, category, description, amount, type)

        elif choice == "2":
            view_transactions()

        elif choice == "3":
            summarize()

        elif choice == "4":
            print("👋 Goodbye! Track your money wisely.")
            break

        else:
            print("❌ Invalid option. Try again.")

Run Your App and Test It

Save the file and run it:

python tracker.py

Try adding a few transactions:

• Add an income: Salary, $2,500
• Add an expense: Groceries, $75

Then view your history and check the summary. You’ll see your net balance update instantly.

What’s Next?

You’ve built a working finance tracker in under 10 minutes. But this is just the foundation. Here’s how you can level it up:

• Add a GUI: Wrap it with Tkinter for a desktop interface [1][3].
• Build a Web App: Use Flask to create a browser-based tracker [5].
• Visualize Data: Integrate Matplotlib or Plotly to generate charts of your spending [1].
• Export to CSV: Use Pandas to export your data for external analysis [6].
• Add Security: Implement password hashing if you store account credentials [4].

Start Tracking Your Money Today

You don’t need to wait for the perfect app or pay for a subscription. With Python and SQLite, you have everything you need to build a finance tracker that fits your exact needs.

Your call to action: Copy the code above, run it, and add your first transaction. Then, tweak it—add categories, filter by date, or even connect it to your bank API. The only limit is your creativity.

Want to share your version? Post it on Dev.to, tag me, and let’s see what others build. Your journey to financial clarity starts with one line of code.

---

If you found this helpful, consider buying me a coffee ☕ — it keeps these articles coming!

Also check out my AI tools collection: AI 次元世界 — free AI tools for developers.