How Python and SQLite Work Together

Python offers a built-in library called sqlite3 that allows you to interact with SQLite databases without installing any external tools. SQLite is a lightweight, file-based relational database that’s perfect for local applications like a personal finance tracker. Together, they make it easy to write, store, retrieve, and analyze data using just Python code and a single database file.

Prerequisites

• Python 3 installed: Download Python
• Basic understanding of Python syntax and functions
• A code editor like VS Code, PyCharm, or IDLE

Step 1: Set Up Your Project

Create a new folder for your project and open a terminal inside that folder. Then, create a Python file named tracker.py where you’ll write your code:

mkdir finance_tracker
cd finance_tracker
touch tracker.py

You can now open tracker.py in your code editor to start building the app.

Step 2: Create the SQLite Database

In your tracker.py file, start by importing SQLite and creating a table to store transactions:

import sqlite3

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

# Create the table if it doesn't exist
cursor.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
)
''')

conn.commit()
conn.close()

This code sets up your database file (finance.db) and creates a table called transactions to store your income and expense records.

Step 3: Add a New Transaction

Now let’s add functionality to insert new transactions. Append this function to your tracker.py file:

def add_transaction(date, category, description, amount):
    conn = sqlite3.connect("finance.db")
    cursor = conn.cursor()
    cursor.execute("INSERT INTO transactions (date, category, description, amount) VALUES (?, ?, ?, ?)",
                   (date, category, description, amount))
    conn.commit()
    conn.close()

This function takes user input and inserts it into the database.

Step 4: View All Transactions

Add this function to retrieve and print all stored transactions:

def view_transactions():
    conn = sqlite3.connect("finance.db")
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM transactions")
    rows = cursor.fetchall()
    for row in rows:
        print(row)
    conn.close()

This allows you to view your income and expenses history in a readable format.

Step 5: Generate a Summary Report

To see a summary of how much you’ve spent or earned, add the following:

def summarize():
    conn = sqlite3.connect("finance.db")
    cursor = conn.cursor()
    cursor.execute("SELECT SUM(amount) FROM transactions")
    total = cursor.fetchone()[0]
    print(f"Total Balance: ${total:.2f}")
    conn.close()

This provides a quick look at your total balance, whether positive (income) or negative (expenses).

Step 6: Putting It All Together

Add a simple interface to run the above functions based on user input. At the bottom of tracker.py, paste:

if __name__ == "__main__":
    while True:
        print("\n1. Add Transaction")
        print("2. View Transactions")
        print("3. Summary")
        print("4. Exit")
        choice = input("Choose an option: ")

        if choice == "1":
            date = input("Date (YYYY-MM-DD): ")
            category = input("Category: ")
            description = input("Description: ")
            amount = float(input("Amount: "))
            add_transaction(date, category, description, amount)
        elif choice == "2":
            view_transactions()
        elif choice == "3":
            summarize()
        elif choice == "4":
            break
        else:
            print("Invalid option. Try again.")

This simple text interface lets you add, view, and summarize your finances directly from the command line.

Conclusion

You’ve now built a basic but effective personal finance tracker using Python and SQLite. You learned how to store transactions, retrieve and display them, and generate useful summaries — all in one script. This project can be extended with categories, date filtering, or even a graphical interface.