o1-mini-2024-09-12· OpenAI
WildChat-4.8M (AI2)
用Python写一个记账软件, 数据库格式如下: id:整数 年份:2024 月份:5 租户房间号:305 租户人名称:小强 已缴房租费:450 已缴电费:50…
- User
用Python写一个记账软件, 数据库格式如下:
id:整数 年份:2024 月份:5 租户房间号:305 租户人名称:小强 已缴房租费:450 已缴电费:50 已缴物业费:200 备注:记录一些其他的信息。 各项已缴费用修改记录:记录各项已缴费用的修改日志(日期格式:年月日时分秒)。 。。。。。。。。 界面设计自由发挥,越方便越好。(用pyside6写界面)
- Assistant
下面是一个使用 Python 和 PySide6 编写的简单记账软件示例。该软件使用 SQLite 作为数据库,提供了基本的增删改查功能,并记录各项已缴费用的修改日志。
功能概述
- 添加记录:输入租户信息及已缴费用,并保存到数据库。
- 查看记录:在表格中显示所有记录。
- 编辑记录:修改已缴费用时,记录修改日志。
- 删除记录:删除选定的记录。
- 查看修改日志:查看各项已缴费用的修改历史。
所需安装
确保已安装以下 Python 库:
pip install PySide6代码实现
import sys import sqlite3 import datetime from PySide6.QtWidgets import ( QApplication, QMainWindow, QWidget, QVBoxLayout, QHBoxLayout, QPushButton, QTableWidget, QTableWidgetItem, QMessageBox, QDialog, QLabel, QLineEdit, QComboBox, QTextEdit ) from PySide6.QtCore import Qt # 数据库名称 DB_NAME = 'accounting.db' # 初始化数据库 def init_db(): conn = sqlite3.connect(DB_NAME) cursor = conn.cursor() cursor.execute(''' CREATE TABLE IF NOT EXISTS records ( id INTEGER PRIMARY KEY AUTOINCREMENT, year INTEGER, month INTEGER, room_number TEXT, tenant_name TEXT, rent_paid REAL, electricity_paid REAL, property_paid REAL, remarks TEXT ) ''') cursor.execute(''' CREATE TABLE IF NOT EXISTS logs ( log_id INTEGER PRIMARY KEY AUTOINCREMENT, record_id INTEGER, field_name TEXT, old_value TEXT, new_value TEXT, timestamp TEXT, FOREIGN KEY(record_id) REFERENCES records(id) ) ''') conn.commit() conn.close() # 添加或编辑记录的对话框 class RecordDialog(QDialog): def __init__(self, parent=None, record=None): super().__init__(parent) self.setWindowTitle("添加记录" if record is None else "编辑记录") self.record = record self.init_ui() def init_ui(self): layout = QVBoxLayout() # 年份 self.year_input = QLineEdit() layout.addWidget(QLabel("年份:")) layout.addWidget(self.year_input) # 月份 self.month_input = QLineEdit() layout.addWidget(QLabel("月份:")) layout.addWidget(self.month_input) # 房间号 self.room_input = QLineEdit() layout.addWidget(QLabel("租户房间号:")) layout.addWidget(self.room_input) # 租户姓名 self.name_input = QLineEdit() layout.addWidget(QLabel("租户姓名:")) layout.addWidget(self.name_input) # 已缴房租费 self.rent_input = QLineEdit() layout.addWidget(QLabel("已缴房租费:")) layout.addWidget(self.rent_input) # 已缴电费 self.electricity_input = QLineEdit() layout.addWidget(QLabel("已缴电费:")) layout.addWidget(self.electricity_input) # 已缴物业费 self.property_input = QLineEdit() layout.addWidget(QLabel("已缴物业费:")) layout.addWidget(self.property_input) # 备注 self.remarks_input = QTextEdit() layout.addWidget(QLabel("备注:")) layout.addWidget(self.remarks_input) # 按钮 btn_layout = QHBoxLayout() self.save_btn = QPushButton("保存") self.save_btn.clicked.connect(self.save) self.cancel_btn = QPushButton("取消") self.cancel_btn.clicked.connect(self.reject) btn_layout.addWidget(self.save_btn) btn_layout.addWidget(self.cancel_btn) layout.addLayout(btn_layout) self.setLayout(layout) if self.record: self.year_input.setText(str(self.record['year'])) self.month_input.setText(str(self.record['month'])) self.room_input.setText(self.record['room_number']) self.name_input.setText(self.record['tenant_name']) self.rent_input.setText(str(self.record['rent_paid'])) self.electricity_input.setText(str(self.record['electricity_paid'])) self.property_input.setText(str(self.record['property_paid'])) self.remarks_input.setText(self.record['remarks']) def save(self): try: year = int(self.year_input.text()) month = int(self.month_input.text()) room = self.room_input.text() name = self.name_input.text() rent = float(self.rent_input.text()) electricity = float(self.electricity_input.text()) property_fee = float(self.property_input.text()) remarks = self.remarks_input.toPlainText() if not (1 <= month <= 12): raise ValueError("月份必须在1到12之间") self.record_data = { 'year': year, 'month': month, 'room_number': room, 'tenant_name': name, 'rent_paid': rent, 'electricity_paid': electricity, 'property_paid': property_fee, 'remarks': remarks } self.accept() except ValueError as e: QMessageBox.warning(self, "输入错误", str(e)) # 查看日志的对话框 class LogDialog(QDialog): def __init__(self, parent=None, record_id=None): super().__init__(parent) self.setWindowTitle("修改日志") self.record_id = record_id self.init_ui() def init_ui(self): layout = QVBoxLayout() self.log_table = QTableWidget() self.log_table.setColumnCount(5) self.log_table.setHorizontalHeaderLabels(["日志ID", "字段名称", "旧值", "新值", "时间戳"]) layout.addWidget(self.log_table) self.setLayout(layout) self.load_logs() def load_logs(self): conn = sqlite3.connect(DB_NAME) cursor = conn.cursor() cursor.execute(''' SELECT log_id, field_name, old_value, new_value, timestamp FROM logs WHERE record_id = ? ORDER BY log_id DESC ''', (self.record_id,)) logs = cursor.fetchall() self.log_table.setRowCount(len(logs)) for row_idx, log in enumerate(logs): for col_idx, item in enumerate(log): self.log_table.setItem(row_idx, col_idx, QTableWidgetItem(str(item))) self.log_table.resizeColumnsToContents() conn.close() # 主窗口 class MainWindow(QMainWindow): def __init__(self): super().__init__() self.setWindowTitle("记账软件") self.resize(1000, 600) self.init_ui() self.load_data() def init_ui(self): central_widget = QWidget() main_layout = QVBoxLayout() # 按钮 btn_layout = QHBoxLayout() self.add_btn = QPushButton("添加记录") self.add_btn.clicked.connect(self.add_record) self.edit_btn = QPushButton("编辑记录") self.edit_btn.clicked.connect(self.edit_record) self.delete_btn = QPushButton("删除记录") self.delete_btn.clicked.connect(self.delete_record) self.view_log_btn = QPushButton("查看修改日志") self.view_log_btn.clicked.connect(self.view_logs) btn_layout.addWidget(self.add_btn) btn_layout.addWidget(self.edit_btn) btn_layout.addWidget(self.delete_btn) btn_layout.addWidget(self.view_log_btn) btn_layout.addStretch() main_layout.addLayout(btn_layout) # 表格 self.table = QTableWidget() self.table.setColumnCount(9) self.table.setHorizontalHeaderLabels([ "ID", "年份", "月份", "房间号", "租户姓名", "已缴房租费", "已缴电费", "已缴物业费", "备注" ]) self.table.setEditTriggers(QTableWidget.NoEditTriggers) self.table.setSelectionBehavior(QTableWidget.SelectRows) main_layout.addWidget(self.table) central_widget.setLayout(main_layout) self.setCentralWidget(central_widget) def load_data(self): conn = sqlite3.connect(DB_NAME) cursor = conn.cursor() cursor.execute('SELECT * FROM records') records = cursor.fetchall() self.table.setRowCount(len(records)) for row_idx, record in enumerate(records): for col_idx, item in enumerate(record): self.table.setItem(row_idx, col_idx, QTableWidgetItem(str(item))) self.table.resizeColumnsToContents() conn.close() def add_record(self): dialog = RecordDialog(self) if dialog.exec() == QDialog.Accepted: data = dialog.record_data conn = sqlite3.connect(DB_NAME) cursor = conn.cursor() cursor.execute(''' INSERT INTO records (year, month, room_number, tenant_name, rent_paid, electricity_paid, property_paid, remarks) VALUES (?, ?, ?, ?, ?, ?, ?, ?) ''', ( data['year'], data['month'], data['room_number'], data['tenant_name'], data['rent_paid'], data['electricity_paid'], data['property_paid'], data['remarks'] )) conn.commit() conn.close() self.load_data() def get_selected_record(self): selected_items = self.table.selectedItems() if not selected_items: QMessageBox.warning(self, "选择错误", "请先选择一条记录") return None row = selected_items[0].row() record = {} for col in range(self.table.columnCount()): header = self.table.horizontalHeaderItem(col).text() record[header] = self.table.item(row, col).text() return record def edit_record(self): record = self.get_selected_record() if not record: return record_id = int(record['ID']) # Fetch current data from DB conn = sqlite3.connect(DB_NAME) cursor = conn.cursor() cursor.execute('SELECT * FROM records WHERE id = ?', (record_id,)) db_record = cursor.fetchone() conn.close() if not db_record: QMessageBox.warning(self, "错误", "未找到选定的记录") return record_data = { 'id': db_record[0], 'year': db_record[1], 'month': db_record[2], 'room_number': db_record[3], 'tenant_name': db_record[4], 'rent_paid': db_record[5], 'electricity_paid': db_record[6], 'property_paid': db_record[7], 'remarks': db_record[8] } dialog = RecordDialog(self, record=record_data) if dialog.exec() == QDialog.Accepted: new_data = dialog.record_data changes = {} if new_data['rent_paid'] != record_data['rent_paid']: changes['rent_paid'] = (record_data['rent_paid'], new_data['rent_paid']) if new_data['electricity_paid'] != record_data['electricity_paid']: changes['electricity_paid'] = (record_data['electricity_paid'], new_data['electricity_paid']) if new_data['property_paid'] != record_data['property_paid']: changes['property_paid'] = (record_data['property_paid'], new_data['property_paid']) # Update DB conn = sqlite3.connect(DB_NAME) cursor = conn.cursor() cursor.execute(''' UPDATE records SET year = ?, month = ?, room_number = ?, tenant_name = ?, rent_paid = ?, electricity_paid = ?, property_paid = ?, remarks = ? WHERE id = ? ''', ( new_data['year'], new_data['month'], new_data['room_number'], new_data['tenant_name'], new_data['rent_paid'], new_data['electricity_paid'], new_data['property_paid'], new_data['remarks'], record_id )) # 记录日志 timestamp = datetime.datetime.now().strftime('%Y%m%d%H%M%S') for field, (old, new) in changes.items(): cursor.execute(''' INSERT INTO logs (record_id, field_name, old_value, new_value, timestamp) VALUES (?, ?, ?, ?, ?) ''', (record_id, field, str(old), str(new), timestamp)) conn.commit() conn.close() self.load_data() def delete_record(self): record = self.get_selected_record() if not record: return reply = QMessageBox.question( self, '删除确认', f"确定要删除租户 '{record['租户姓名']}' 的记录吗?", QMessageBox.Yes | QMessageBox.No, QMessageBox.No ) if reply == QMessageBox.Yes: record_id = int(record['ID']) conn = sqlite3.connect(DB_NAME) cursor = conn.cursor() cursor.execute('DELETE FROM records WHERE id = ?', (record_id,)) # Optionally, delete associated logs cursor.execute('DELETE FROM logs WHERE record_id = ?', (record_id,)) conn.commit() conn.close() self.load_data() def view_logs(self): record = self.get_selected_record() if not record: return record_id = int(record['ID']) dialog = LogDialog(self, record_id=record_id) dialog.exec() if __name__ == '__main__': init_db() app = QApplication(sys.argv) window = MainWindow() window.show() sys.exit(app.exec())代码解释
-
数据库初始化:
- 使用
sqlite3创建两个表:records:存储租户的基本信息和已缴费用。logs:记录每次修改已缴费用的历史记录。
- 使用
-
主窗口 (
MainWindow):- 提供添加、编辑、删除记录和查看修改日志的按钮。
- 使用
QTableWidget显示所有记录。
-
添加/编辑记录 (
RecordDialog):- 弹出对话框,允许用户输入或修改租户的详细信息。
- 验证输入的年份、月份和费用是否合理。
-
记录修改日志:
- 在编辑记录时,如果已缴费用有变化,会在
logs表中插入相应的修改记录,包括字段名称、旧值、新值和时间戳。
- 在编辑记录时,如果已缴费用有变化,会在
-
查看日志 (
LogDialog):- 选择一条记录后,可以查看该记录的所有修改历史。
-
删除记录:
- 支持删除选定的租户记录,同时也可以选择删除相关的日志记录。
使用说明
-
运行程序:
保存上述代码为
accounting_app.py,然后在终端中运行:python accounting_app.py -
添加记录:
- 点击“添加记录”按钮,填写租户信息和已缴费用,点击“保存”。
-
编辑记录:
- 选择一条记录,点击“编辑记录”按钮,修改需要更改的字段,点击“保存”。
- 任何已缴费用的修改都会被记录在日志中。
-
删除记录:
- 选择一条记录,点击“删除记录”按钮,确认删除。
-
查看修改日志:
- 选择一条记录,点击“查看修改日志”按钮,查看该记录的所有修改历史。
注意事项
- 本示例为基础版本,适用于小规模使用。对于更复杂的需求(如用户权限管理、数据备份等),需要进一步扩展。
- 费用字段使用
REAL数据类型,确保可以存储小数值。 - 时间戳采用
YYYYMMDDHHMMSS格式,方便排序和查询。
希望这个示例能够帮助你快速搭建一个简单的记账软件。如有进一步需求,可以根据具体情况进行扩展和优化。