Build a Personal Expense Tracker with Python and SQLite

Keeping track of your personal finances is essential, and building a simple command-line interface (CLI) tool for tracking expenses is a great way to practice Python and SQLite. In this blog post, we’ll create a lightweight expense tracker that allows you to log transactions, summarize your spending, and analyze trends directly from your terminal. No third-party web apps or sign-ins—just Python, SQLite, and a bit of scripting glue.

1. Setting Up the SQLite Database

First, we need a local SQLite database to store our expenses. Each expense record will have a few key fields: ID, date, category, description, and amount. SQLite is perfect for lightweight applications like this, as it requires no server setup.

import sqlite3

def init_db():
    conn = sqlite3.connect('expenses.db')
    cursor = conn.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS expenses (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            date TEXT NOT NULL,
            category TEXT NOT NULL,
            description TEXT,
            amount REAL NOT NULL
        )
    ''')
    conn.commit()
    conn.close()

if __name__ == '__main__':
    init_db()

This script checks if the expenses table exists and creates it if not. The amount field uses the REAL type to store currency values. The database file expenses.db will be created in the current directory.

2. Logging Expenses from the Command Line

Now let’s add functionality to record an expense from the terminal. This script will take user inputs for date, category, description, and amount, then insert them into the database.

def add_expense(date, category, description, amount):
    conn = sqlite3.connect('expenses.db')
    cursor = conn.cursor()
    cursor.execute('''
        INSERT INTO expenses (date, category, description, amount)
        VALUES (?, ?, ?, ?)''', (date, category, description, amount))
    conn.commit()
    conn.close()

# Example usage
add_expense('2024-06-01', 'Food', 'Lunch with friends', 18.75)

Use Python’s built-in input() within a CLI script to prompt for values dynamically. Remember to validate user input before insertion, especially for date and amount fields.

3. Viewing and Summarizing Data

Let’s add functionality to review the data—both raw logs and summarized by category or date range. Below is a function to print all expenses:

def list_expenses():
    conn = sqlite3.connect('expenses.db')
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM expenses ORDER BY date DESC')
    rows = cursor.fetchall()
    for row in rows:
        print(row)
    conn.close()

Now, let’s summarize expenses by category:

def summarize_by_category():
    conn = sqlite3.connect('expenses.db')
    cursor = conn.cursor()
    cursor.execute('''
        SELECT category, SUM(amount) as total
        FROM expenses
        GROUP BY category
        ORDER BY total DESC
    ''')
    summary = cursor.fetchall()
    for category, total in summary:
        print(f"{category}: ${total:.2f}")
    conn.close()

You can similarly create a summarizer by month using SQLite’s strftime('%Y-%m', date) function.

4. Searching and Filtering Expenses

Filtering by date, category, or keyword helps users retrieve specific records. Here’s a sample function to get expenses from a specific month:

def get_expenses_by_month(year, month):
    conn = sqlite3.connect('expenses.db')
    cursor = conn.cursor()
    query = '''
        SELECT date, category, description, amount
        FROM expenses
        WHERE strftime('%Y-%m', date) = ?
        ORDER BY date
    '''
    date_filter = f"{year}-{str(month).zfill(2)}"
    cursor.execute(query, (date_filter,))
    rows = cursor.fetchall()
    for row in rows:
        print(row)
    conn.close()

This query uses SQLite’s date formatting functions to filter entries and can easily be wrapped with argparse or click for CLI argument support.

5. Optimization, Automation, and CLI Integration Tips

For a smoother developer experience, consider these improvements:

Use argparse or click: These libraries help build CLI with flags and subcommands like add, list, and summary.
Input validation: Validate numbers and dates to avoid corrupt data entries.
Automated backup: Use cron jobs or a Python schedule library to back up the SQLite DB periodically.
JSON export: Support exporting data to JSON for compatibility with visualization tools or cloud storage.
Performance tips: Indexing on the date and category fields can speed up queries for large datasets.

# Adding index for better performance on frequent queries
conn = sqlite3.connect('expenses.db')
cursor = conn.cursor()
cursor.execute('CREATE INDEX IF NOT EXISTS idx_date ON expenses(date)')
conn.commit()
conn.close()

These small enhancements make your tracker faster, more reliable, and easier to use in your daily routine.

Conclusion

By combining Python with SQLite, you’ve now built a personal expense tracking tool that lives in your terminal. This light CLI solution is perfect for learning database integration and Python scripting, plus it promotes healthy financial habits. You can continue building on this foundation with CSV import/export features, data visualization, or even converting it to a Flask web app. Happy tracking!

Useful links: