Finance Manager Tutorial - Part 2

In this part of the tutorial, we will extend our finance manager app to store the expenses in a SQLite database using the SQLAlchemy Python package. This will allow us to persist the data and retrieve it even after the application is closed.

To download the complete source code for this tutorial, visit Finance Manager Example - Part 2.

Prerequisites

Before we begin, make sure you have SQLAlchemy installed within your Python environment.

You can install it using pip:

pip install sqlalchemy

Project Structure

The overall project structure is the same as in the previous part of the tutorial.

Let’s Get Started!

Creating the Database

The first step is to create a database.py that takes care of creating and initializing the database. This Python code will define the database schema and provide a session object to interact with the database. The database is initialized with a single table named finances with the same initial data we used in the part one of the tutorial.

database.py
database.py
 1# Copyright (C) 2024 The Qt Company Ltd.
 2# SPDX-License-Identifier: LicenseRef-Qt-Commercial OR BSD-3-Clause
 3
 4from sqlalchemy import create_engine, Column, Integer, String, Float
 5from sqlalchemy.ext.declarative import declarative_base
 6from sqlalchemy.orm import sessionmaker
 7import os
 8import platform
 9from pathlib import Path
10
11Base = declarative_base()
12
13
14class Finance(Base):
15    __tablename__ = 'finances'
16    id = Column(Integer, primary_key=True)
17    item_name = Column(String)
18    category = Column(String)
19    cost = Column(Float)
20    date = Column(String)
21
22
23# Check for an environment variable for the database path
24env_db_path = os.getenv('FINANCE_MANAGER_DB_PATH')
25
26if env_db_path:
27    db_path = Path(env_db_path)
28else:
29    # Determine the application data directory based on the operating system using pathlib
30    if platform.system() == 'Windows':
31        app_data_location = Path(os.getenv('APPDATA')) / 'FinanceManager'
32    elif platform.system() == 'Darwin':  # macOS
33        app_data_location = Path.home() / 'Library' / 'Application Support' / 'FinanceManager'
34    else:  # Linux and other Unix-like systems
35        app_data_location = Path.home() / '.local' / 'share' / 'FinanceManager'
36
37    db_path = app_data_location / 'finances.db'
38
39DATABASE_URL = f'sqlite:///{db_path}'
40engine = create_engine(DATABASE_URL)
41Session = sessionmaker(bind=engine)
42
43# Default data to be added to the database
44default_data = [
45    {"item_name": "Mobile Prepaid", "category": "Electronics", "cost": 20.00, "date": "15-02-2024"},
46    {"item_name": "Groceries-Feb-Week1", "category": "Groceries", "cost": 60.75,
47     "date": "16-01-2024"},
48    {"item_name": "Bus Ticket", "category": "Transport", "cost": 5.50, "date": "17-01-2024"},
49    {"item_name": "Book", "category": "Education", "cost": 25.00, "date": "18-01-2024"},
50]
51
52
53def initialize_database():
54    if db_path.exists():
55        print(f"Database '{db_path}' already exists.")
56        return
57
58    app_data_location.mkdir(parents=True, exist_ok=True)
59    Base.metadata.create_all(engine)
60    print(f"Database '{db_path}' created successfully.")
61    session = Session()
62
63    for data in default_data:
64        finance = Finance(**data)
65        session.add(finance)
66
67    session.commit()
68    print("Default data has been added to the database.")

Note: The database called finances.db will be created in the directory specified by the FINANCE_MANAGER_DB_PATH environment variable if it is set. If the environment variable is not set, the database will be created in the appropriate application data directory based on the operating system.

Updating the FinanceModel Class

Next, we need to update the FinanceModel class to interact with the database.

In financemodel.py, make the following highlighted changes to load the existing expenses from the database into the model, and also save new expenses to the database.

financemodel.py
Finance model class definition
  1# Copyright (C) 2024 The Qt Company Ltd.
  2# SPDX-License-Identifier: LicenseRef-Qt-Commercial OR BSD-3-Clause
  3
  4from datetime import datetime
  5from dataclasses import dataclass
  6from enum import IntEnum
  7from collections import defaultdict
  8
  9from PySide6.QtCore import (QAbstractListModel, QEnum, Qt, QModelIndex, Slot,
 10                            QByteArray)
 11from PySide6.QtQml import QmlElement
 12import database
 13
 14QML_IMPORT_NAME = "Finance"
 15QML_IMPORT_MAJOR_VERSION = 1
 16
 17
 18@QmlElement
 19class FinanceModel(QAbstractListModel):
 20
 21    @QEnum
 22    class FinanceRole(IntEnum):
 23        ItemNameRole = Qt.DisplayRole
 24        CategoryRole = Qt.UserRole
 25        CostRole = Qt.UserRole + 1
 26        DateRole = Qt.UserRole + 2
 27        MonthRole = Qt.UserRole + 3
 28
 29    @dataclass
 30    class Finance:
 31        item_name: str
 32        category: str
 33        cost: float
 34        date: str
 35
 36        @property
 37        def month(self):
 38            return datetime.strptime(self.date, "%d-%m-%Y").strftime("%B %Y")
 39
 40    def __init__(self, parent=None) -> None:
 41        super().__init__(parent)
 42        self.session = database.Session()
 43        self.m_finances = self.load_finances()
 44
 45    def load_finances(self):
 46        finances = []
 47        for finance in self.session.query(database.Finance).all():
 48            finances.append(self.Finance(finance.item_name, finance.category, finance.cost,
 49                                         finance.date))
 50        return finances
 51
 52    def rowCount(self, parent=QModelIndex()):
 53        return len(self.m_finances)
 54
 55    def data(self, index: QModelIndex, role: int):
 56        row = index.row()
 57        if row < self.rowCount():
 58            finance = self.m_finances[row]
 59            if role == FinanceModel.FinanceRole.ItemNameRole:
 60                return finance.item_name
 61            if role == FinanceModel.FinanceRole.CategoryRole:
 62                return finance.category
 63            if role == FinanceModel.FinanceRole.CostRole:
 64                return finance.cost
 65            if role == FinanceModel.FinanceRole.DateRole:
 66                return finance.date
 67            if role == FinanceModel.FinanceRole.MonthRole:
 68                return finance.month
 69        return None
 70
 71    @Slot(result=dict)
 72    def getCategoryData(self):
 73        category_data = defaultdict(float)
 74        for finance in self.m_finances:
 75            category_data[finance.category] += finance.cost
 76        return dict(category_data)
 77
 78    def roleNames(self):
 79        roles = super().roleNames()
 80        roles[FinanceModel.FinanceRole.ItemNameRole] = QByteArray(b"item_name")
 81        roles[FinanceModel.FinanceRole.CategoryRole] = QByteArray(b"category")
 82        roles[FinanceModel.FinanceRole.CostRole] = QByteArray(b"cost")
 83        roles[FinanceModel.FinanceRole.DateRole] = QByteArray(b"date")
 84        roles[FinanceModel.FinanceRole.MonthRole] = QByteArray(b"month")
 85        return roles
 86
 87    @Slot(int, result='QVariantMap')
 88    def get(self, row: int):
 89        finance = self.m_finances[row]
 90        return {"item_name": finance.item_name, "category": finance.category,
 91                "cost": finance.cost, "date": finance.date}
 92
 93    @Slot(str, str, float, str)
 94    def append(self, item_name: str, category: str, cost: float, date: str):
 95        finance = self.Finance(item_name, category, cost, date)
 96        self.session.add(database.Finance(item_name=item_name, category=category, cost=cost,
 97                                          date=date))
 98        self.beginInsertRows(QModelIndex(), 0, 0)  # Insert at the front
 99        self.m_finances.insert(0, finance)  # Insert at the front of the list
100        self.endInsertRows()
101        self.session.commit()

Updating the Main Application

Finally, we need to update the main.py file to initialize the database and use the FinanceModel

main.py
main.py
 1# Copyright (C) 2024 The Qt Company Ltd.
 2# SPDX-License-Identifier: LicenseRef-Qt-Commercial OR BSD-3-Clause
 3
 4import sys
 5from pathlib import Path
 6
 7from PySide6.QtWidgets import QApplication
 8from PySide6.QtQml import QQmlApplicationEngine
 9
10from financemodel import FinanceModel  # noqa: F401
11from database import initialize_database
12
13if __name__ == '__main__':
14    # Initialize the database if it does not exist
15    initialize_database()
16
17    app = QApplication(sys.argv)
18    QApplication.setOrganizationName("QtProject")
19    QApplication.setApplicationName("Finance Manager")
20    engine = QQmlApplicationEngine()
21
22    engine.addImportPath(Path(__file__).parent)
23    engine.loadFromModule("Finance", "Main")
24
25    if not engine.rootObjects():
26        sys.exit(-1)
27
28    exit_code = app.exec()
29    del engine
30    sys.exit(exit_code)

The rest of the code remains the same as in the previous part of the tutorial.

Running the Application

To run the application, execute the main.py file using Python:

python main.py

Deploying the Application

To deploy the application, follow the same steps as in the previous part of the tutorial.

Summary

In this part of the tutorial, we have extended the finance manager app by integrating a database using SQLAlchemy. This allows us to store the expenses and retrieve them even after the application is closed.

In the next part of the tutorial, we will continue to enhance the application by using FastApi and PyDantic to create a REST API for the finance manager app, and move the database to a separate server.