Building a Personal Finance Tracker with Python and SQLite: A Beginner’s Guide

Managing your personal finances is an important skill, and having a tool to track income and expenses can make it easier. In this tutorial, you'll learn how to build a basic personal finance tracker using Python and SQLite. SQLite is a lightweight and easy-to-use database that is perfect for beginners, and Python’s built-in library makes it straightforward to integrate.

We will create a program that helps you add income and expenses, store them in a SQLite database, and view a summary of your transactions. Let's get started!

First, let's import the necessary module `sqlite3` and set up our database and table for storing finance records.

import sqlite3

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

# Create table for transactions
cursor.execute('''
CREATE TABLE IF NOT EXISTS transactions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    date TEXT NOT NULL,
    description TEXT NOT NULL,
    amount REAL NOT NULL
)
''')

conn.commit()

Our `transactions` table will have four columns. `id` is a unique identifier for each transaction. `date` is the date of the transaction, `description` tells what the transaction was, and `amount` indicates the money involved — positive for income and negative for expenses.

Next, let's create a function to add transactions to the database. This function will require the date, description, and amount.

def add_transaction(date, description, amount):
    cursor.execute('INSERT INTO transactions (date, description, amount) VALUES (?, ?, ?)',
                   (date, description, amount))
    conn.commit()
    print('Transaction added successfully!')

You can now use this function to add income or expenses. For example, to record a salary payment:

# Example usage
add_transaction('2024-06-01', 'Salary', 3000.00)

And to record an expense like buying groceries:

add_transaction('2024-06-03', 'Groceries', -150.75)

Now, let's create a function to view all transactions and print a summary including total income, total expenses, and balance.

def view_transactions():
    cursor.execute('SELECT date, description, amount FROM transactions ORDER BY date')
    rows = cursor.fetchall()
    
    print('Date       | Description         | Amount')
    print('-' * 40)
    total_income = 0
    total_expense = 0
    for row in rows:
        date, desc, amount = row
        print(f'{date} | {desc:<18} | {amount:>7.2f}')
        if amount > 0:
            total_income += amount
        else:
            total_expense += amount
    print('-' * 40)
    print(f'Total Income:  ${total_income:.2f}')
    print(f'Total Expense: ${-total_expense:.2f}')
    print(f'Balance:      ${total_income + total_expense:.2f}')

You can call this function to see your current finances in a clean table format.

# View transactions and summary
view_transactions()

Finally, don’t forget to close your database connection when you’re done:

conn.close()

### Summary In this tutorial, you learned how to create a simple personal finance tracker using Python and SQLite. You: - Set up a database to store transactions - Added income and expense records - Retrieved and displayed transactions with a summary This tracker can be expanded with features like categories, monthly reports, or even a graphical interface. But as a beginner-friendly project, it provides a solid foundation to manage your personal finances programmatically.