财务经理教程 - 第2部分¶
在本教程的这一部分中，我们将扩展我们的财务管理应用程序，使用SQLAlchemy Python包将支出存储在SQLite数据库中。这将使我们能够持久化数据，并在应用程序关闭后仍能检索它。
要下载本教程的完整源代码，请访问 Finance Manager Example - Part 2。
先决条件¶
在我们开始之前，请确保您的Python环境中已安装SQLAlchemy。
你可以使用pip安装它：
pip install sqlalchemy
项目结构¶
整体项目结构与教程的前一部分相同。
让我们开始吧！¶
创建数据库¶
第一步是创建一个database.py，负责创建和初始化数据库。这段Python代码将定义数据库模式，并提供一个会话对象来与数据库交互。数据库初始化时包含一个名为finances的表，其中包含我们在教程第一部分中使用的相同初始数据。
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.")
注意：如果设置了
FINANCE_MANAGER_DB_PATH环境变量，名为finances.db的数据库将在该环境变量指定的目录中创建。如果未设置环境变量，数据库将根据操作系统在适当的应用程序数据目录中创建。
更新FinanceModel类¶
接下来，我们需要更新 FinanceModel 类以与数据库进行交互。
在financemodel.py中，进行以下高亮显示的更改，以将现有的费用从数据库加载到模型中，并将新费用保存到数据库中。
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()
更新主应用程序¶
最后，我们需要更新main.py文件以初始化数据库并使用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)
代码的其余部分与教程的前一部分保持不变。
运行应用程序¶
要运行应用程序，请使用Python执行main.py文件：
python main.py
部署应用程序¶
要部署应用程序，请按照教程的前一部分中的相同步骤操作。
总结¶
在本教程的这一部分中，我们通过使用SQLAlchemy集成数据库扩展了财务经理应用程序。这使我们能够存储支出并在应用程序关闭后检索它们。
在教程的下一部分中，我们将继续通过使用FastApi和PyDantic来增强应用程序，为财务经理应用程序创建一个REST API，并将数据库迁移到单独的服务器。