Create a Personal Expense Tracker with SQLite and Python
Managing personal finances doesn’t need a fancy web application or integration with a bank API. Sometimes, a simple terminal-based utility can be just as effective—especially when you want full control over your data. In this tutorial, we’ll build a lightweight command-line personal expense tracker using Python and SQLite. This tool will let you add expenses, filter transactions, and generate summaries—all from the terminal.
1. Setting Up the Project and Database
Before diving into the functionality, let’s create the project structure and initialize the database. We’ll use Python’s built-in sqlite3 module, which provides a lightweight, serverless database engine perfect for local applications.
import sqlite3
# Initialize the database and create the expenses table
def initialize_db():
conn = sqlite3.connect('expenses.db')
cursor = conn.cursor()
cursor.execute('''
CREATE TABLE IF NOT EXISTS expenses (
id INTEGER PRIMARY KEY AUTOINCREMENT,
amount REAL NOT NULL,
category TEXT NOT NULL,
description TEXT,
date TEXT NOT NULL
)
''')
conn.commit()
conn.close()
initialize_db()
This function checks if the expenses table exists and creates it if it doesn’t. The date is stored as a TEXT field in ‘YYYY-MM-DD’ format, making it easier to sort and filter chronologically.
2. Adding Expense Entries
Now that we have a table, let’s create a function to add new expenses to the database. Users can input amount, category, description, and date (we’ll default to today’s date if not provided).
from datetime import datetime
def add_expense(amount, category, description="", date=None):
if not date:
date = datetime.now().strftime('%Y-%m-%d')
conn = sqlite3.connect("expenses.db")
cursor = conn.cursor()
cursor.execute('''
INSERT INTO expenses (amount, category, description, date)
VALUES (?, ?, ?, ?)
''', (amount, category, description, date))
conn.commit()
conn.close()
# Example usage
add_expense(29.99, 'Food', 'Lunch at cafe')
This insertion uses parameterized queries to prevent SQL injection (even though it’s local, it’s still a good habit). You can use this function in an interactive CLI wrapper later for user input.
3. Viewing and Filtering Expenses
It’s useful to filter your transactions by date range or category. Let’s build a function to fetch expenses based on filter parameters:
def get_expenses(category=None, start_date=None, end_date=None):
conn = sqlite3.connect("expenses.db")
cursor = conn.cursor()
query = 'SELECT amount, category, description, date FROM expenses WHERE 1=1'
params = []
if category:
query += ' AND category = ?'
params.append(category)
if start_date:
query += ' AND date >= ?'
params.append(start_date)
if end_date:
query += ' AND date <= ?'
params.append(end_date)
cursor.execute(query, params)
results = cursor.fetchall()
conn.close()
for row in results:
print(f"Amount: ${row[0]:.2f}, Category: {row[1]}, Description: {row[2]}, Date: {row[3]}")
# Example usage:
# get_expenses(category="Food", start_date="2024-01-01", end_date="2024-01-31")
This flexible function lets you apply any combination of filters. Passing no parameters shows all transactions. This kind of dynamic SQL is a clean and efficient pattern for command-line tools with optional filters.
4. Generating Monthly Summaries
To get value from our tracker, we need summaries—like total expenses per month or per category. Here’s a simple way to calculate the total amount spent each month:
def monthly_summary():
conn = sqlite3.connect("expenses.db")
cursor = conn.cursor()
cursor.execute('''
SELECT substr(date, 1, 7) AS month, SUM(amount)
FROM expenses
GROUP BY month
ORDER BY month DESC
''')
data = cursor.fetchall()
conn.close()
print("Monthly Expense Summary:")
for month, total in data:
print(f"{month}: ${total:.2f}")
# Example usage
monthly_summary()
This aggregates expenses by their ‘year-month’ string. For even finer detail, you can extend this to group by both month and category for a breakdown of spending habits.
5. Adding a Simple Command-Line Interface
To make this usable daily, we can wrap the functionality into a basic terminal interface using Python’s argparse module.
import argparse
def main():
parser = argparse.ArgumentParser(description="Personal Expense Tracker")
subparsers = parser.add_subparsers(dest="command")
# Add expense
add_parser = subparsers.add_parser("add")
add_parser.add_argument("amount", type=float)
add_parser.add_argument("category")
add_parser.add_argument("--description", default="")
add_parser.add_argument("--date", default=None)
# View expenses
view_parser = subparsers.add_parser("view")
view_parser.add_argument("--category", default=None)
view_parser.add_argument("--start-date", default=None)
view_parser.add_argument("--end-date", default=None)
# Summary
subparsers.add_parser("summary")
args = parser.parse_args()
if args.command == "add":
add_expense(args.amount, args.category, args.description, args.date)
elif args.command == "view":
get_expenses(args.category, args.start_date, args.end_date)
elif args.command == "summary":
monthly_summary()
else:
parser.print_help()
if __name__ == "__main__":
initialize_db()
main()
Running python expense_tracker.py add 15.5 Groceries --description "Milk and eggs" will add a record. Try python expense_tracker.py summary to get your spending patterns. This type of CLI tool is perfect for automating daily entries via scripts or even cron jobs.
Conclusion
We’ve built a practical and extensible personal expense tracker using Python and SQLite—all from the command line. This simple app demonstrates the power of combining a lightweight database with Python’s expressive syntax to create something useful and personalized. You could expand this by exporting reports to CSV, integrating with email alerts, or using a GUI toolkit like Tkinter.
For now, enjoy the control and clarity that comes from logging your expenses via the terminal. Happy tracking!
Useful links:
