Skip to content
TheChatArchiveThe Chat Archive
o1-mini-2024-09-12· OpenAIAi2 WildChat-4.8M (AI2)

用Python写一个记账软件, 数据库格式如下: id:整数 年份:2024 月份:5 租户房间号:305 租户人名称:小强 已缴房租费:450 已缴电费:50…

  1. User

    用Python写一个记账软件, 数据库格式如下:

    id:整数 年份:2024 月份:5 租户房间号:305 租户人名称:小强 已缴房租费:450 已缴电费:50 已缴物业费:200 备注:记录一些其他的信息。 各项已缴费用修改记录:记录各项已缴费用的修改日志(日期格式:年月日时分秒)。 。。。。。。。。 界面设计自由发挥,越方便越好。(用pyside6写界面)

  2. 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())
    

    代码解释

    1. 数据库初始化:

      • 使用 sqlite3 创建两个表:
        • records:存储租户的基本信息和已缴费用。
        • logs:记录每次修改已缴费用的历史记录。
    2. 主窗口 (MainWindow):

      • 提供添加、编辑、删除记录和查看修改日志的按钮。
      • 使用 QTableWidget 显示所有记录。
    3. 添加/编辑记录 (RecordDialog):

      • 弹出对话框,允许用户输入或修改租户的详细信息。
      • 验证输入的年份、月份和费用是否合理。
    4. 记录修改日志:

      • 在编辑记录时,如果已缴费用有变化,会在 logs 表中插入相应的修改记录,包括字段名称、旧值、新值和时间戳。
    5. 查看日志 (LogDialog):

      • 选择一条记录后,可以查看该记录的所有修改历史。
    6. 删除记录:

      • 支持删除选定的租户记录,同时也可以选择删除相关的日志记录。

    使用说明

    1. 运行程序:

      保存上述代码为 accounting_app.py,然后在终端中运行:

      python accounting_app.py
      
    2. 添加记录:

      • 点击“添加记录”按钮,填写租户信息和已缴费用,点击“保存”。
    3. 编辑记录:

      • 选择一条记录,点击“编辑记录”按钮,修改需要更改的字段,点击“保存”。
      • 任何已缴费用的修改都会被记录在日志中。
    4. 删除记录:

      • 选择一条记录,点击“删除记录”按钮,确认删除。
    5. 查看修改日志:

      • 选择一条记录,点击“查看修改日志”按钮,查看该记录的所有修改历史。

    注意事项

    • 本示例为基础版本,适用于小规模使用。对于更复杂的需求(如用户权限管理、数据备份等),需要进一步扩展。
    • 费用字段使用 REAL 数据类型,确保可以存储小数值。
    • 时间戳采用 YYYYMMDDHHMMSS 格式,方便排序和查询。

    希望这个示例能够帮助你快速搭建一个简单的记账软件。如有进一步需求,可以根据具体情况进行扩展和优化。