How to Build an Online Database APP by Python, PySide6 and Google Sheets (8): Delete Transaction

Previously

How to Build an Online Database APP by Python, PySide6 and Google Sheets (7): Add Transaction

This article is about how to “Delete Transaction” in the App:

Call function in the Main Window

• Clears the existing content in the main window.
• Creates an instance of DeleteTransactionForm, passing google_sheets_client as a parameter.
• Adds the form to the main content area for user interaction.

    def display_del_transaction_form(self):
        # Clear existing content
        self.clear_content()

        # Create the DeleteTransactionForm
        delete_transaction_form = DeleteTransactionForm(self.google_sheets_client)
        # Add the form to the main content area
        self.content.layout().addWidget(delete_transaction_form)

Create Delete Transaction Class

• __init__: Initializes the form, sets up the Google Sheets client, and initializes the user interface.

class DeleteTransactionForm(QWidget):
    def __init__(self, google_sheets_client):
        super().__init__()

        self.google_sheets_client = google_sheets_client
        self.transaction_ids = {}  # Store transaction IDs mapped to table row indices
        self.initUI()

• initUI: Sets up the layout, which includes a table for displaying transactions and a delete button.

    def initUI(self):
        self.layout = QVBoxLayout(self)

        # Table for displaying transactions
        self.table = QTableWidget()
        self.table.setSelectionBehavior(QTableWidget.SelectRows)
        self.table.setSelectionMode(QTableWidget.SingleSelection)
        self.table.setEditTriggers(QTableWidget.NoEditTriggers)
        self.table.doubleClicked.connect(self.transaction_selected)
        self.loadTransactions()

        # Delete button
        self.delete_button = QPushButton("Delete")
        self.delete_button.setFixedWidth(100)
        self.delete_button.clicked.connect(self.delete_transaction)

        # Button layout
        button_layout = QHBoxLayout()
        button_layout.addStretch()  # Add stretch before the button
        button_layout.addWidget(self.delete_button)
        button_layout.addStretch()  # Add stretch after the button

        # Add widgets to the main layout
        self.layout.addWidget(self.table)
        self.layout.addLayout(button_layout)  # Add the button layout instead of the button directly

• get_current_quarter: Calculates the current quarter of the year, which is used to filter transactions.

    def get_current_quarter(self):
        month = datetime.now().month
        quarter = ((month - 1) // 3) + 1
        year = datetime.now().year
        return f"{year}Q{quarter}"

• loadTransactions: Loads transactions from the Google Sheets worksheet, focusing on the current quarter. It populates the table with these transactions and stores their IDs.

    def loadTransactions(self):
        current_quarter = self.get_current_quarter()

        worksheet = self.google_sheets_client.open_spreadsheet(GOOGLESHEET_ID).worksheet('03_Transactions')
        all_data = worksheet.get_all_values()
        header_row = all_data[0]
        dividend_period_index = header_row.index("Dividend Period")
        transaction_id_index = header_row.index("ID_Transaction")


        # Sort data in reverse order based on "ID_Transaction"
        sorted_data = sorted(all_data[1:], key=lambda x: x[transaction_id_index], reverse=True)

        self.table.setColumnCount(len(header_row))
        self.table.setHorizontalHeaderLabels(header_row)
        self.table.setRowCount(0)

        for row_data in sorted_data[1:]:
            if row_data[dividend_period_index] == current_quarter:
                row = self.table.rowCount()
                self.table.insertRow(row)
                for i, cell in enumerate(row_data):
                    self.table.setItem(row, i, QTableWidgetItem(cell))
                self.transaction_ids[row] = row_data[transaction_id_index]

• transaction_selected: Handles the selection of a transaction from the table.

    def transaction_selected(self, index):
        # Handle transaction selection
        self.selected_row = index.row()

• delete_transaction: Deletes the selected transaction from the Google Sheets worksheet and updates the UI accordingly.

    def delete_transaction(self):
        if hasattr(self, 'selected_row'):
            selected_transaction_id = self.transaction_ids.get(self.selected_row)
            if selected_transaction_id:
                self.delete_transaction_from_sheet("03_Transactions", selected_transaction_id)
                self.delete_transaction_from_sheet("04_Dividends", selected_transaction_id)

                self.table.removeRow(self.selected_row)
                QMessageBox.information(self, "Success", "Transaction deleted successfully.")
            else:
                QMessageBox.warning(self, "Warning", "Transaction ID not found.")
        else:
            QMessageBox.warning(self, "Warning", "Please double click select a transaction to delete.")

• delete_transaction_from_sheet: Helper function to delete a transaction from a specified Google Sheets worksheet.

    def delete_transaction_from_sheet(self, sheet_name, transaction_id):
        worksheet = self.google_sheets_client.open_spreadsheet(GOOGLESHEET_ID).worksheet(sheet_name)
        all_data = worksheet.get_all_values()
        transaction_id_index = all_data[0].index("ID_Transaction")

        for i, row_data in enumerate(all_data[1:], start=2):  # start=2 for header row and 1-based index
            if row_data[transaction_id_index] == transaction_id:
                worksheet.delete_rows(i)
                break

Next Step

How to Build an Online Database APP by Python, PySide6 and Google Sheets (9): View Dividend