How to Build an Online Database APP by Python, PySide6 and Google Sheets (6): Edit Account

Previously

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

This article is about how to “edit account” in the app:

Previously

Call function in the Main Window

1. display_edit_account_form (in MainWindow Class):

• Clears existing content in the main window.
• Opens a dialog (SelectAccountDialog) to select an account for editing.
• If an account is selected, fetches its data and displays the form for editing account details using showEditAccountForm.

class MainWindow(QMainWindow):
    def display_edit_account_form(self):
        # Clear existing content
        self.clear_content()

        # Open the dialog to select an investor
        dialog = SelectAccountDialog(self.google_sheets_client)
        result = dialog.exec()

        if result == QDialog.Accepted:
            selected_id = dialog.getSelectedAccountID()
            if selected_id:
                account_data = self.fetch_edit_account_data(selected_id)

                if account_data:
                    self.showEditAccountForm(account_data)

2. fetch_edit_account_data (in MainWindow Class):

• Connects to the Google Sheets worksheet named “02_Accounts”.
• Maps column names to indices and verifies the presence of required columns.
• Searches for the specified account by its ID and retrieves its row data.
• Constructs and returns a dictionary with the account’s data, including related investor information.

    def fetch_edit_account_data(self, account_id):
        # Connect to Google Sheets and select the appropriate worksheet
        worksheet = self.google_sheets_client.open_spreadsheet(GOOGLESHEET_ID).worksheet("02_Accounts")

        # Get the header row (first row) to map column names to their indices
        header_row = worksheet.row_values(1)
        column_index_map = {name: index for index, name in enumerate(header_row)}

        # Ensure all required columns are present
        required_columns = ["ID_Account", "ID_Investor", "Type", "Last Name", "First Name", "Company Name"]
        for column in required_columns:
            if column not in column_index_map:
                print(f"Column '{column}' not found in the worksheet.")
                return None
        
        # Get all values in the 'Account ID' column
        account_id_column = worksheet.col_values(column_index_map["ID_Account"] + 1)

        # Find the row index for the matching account_id
        try:
            row_index = account_id_column.index(account_id) + 1  # +1 because spreadsheet indices start at 1
        except ValueError:
            # Account ID not found
            return None
        
        # Fetch only the row with the matching Account ID
        row_data = worksheet.row_values(row_index)

        # Construct account data ensuring all keys exist in column_index_map and row_data
        account_data = {}
        for key, index in column_index_map.items():
            if index < len(row_data):
                account_data[key] = row_data[index]
            else:
                account_data[key] = "N/A"

        # Fetch the investor data associated with this account
        investor_id = account_data.get("ID_Investor")
        if investor_id:
            investor_data = self.fetch_investor_data(investor_id)
            account_data["investor_data"] = investor_data
        
        return account_data

3. showEditAccountForm (in MainWindow Class):

• Creates an instance of EditAccountForm using the fetched account data.
• Adds the edit form widget to the main window’s layout.

    def showEditAccountForm(self, account_data):
        # Create the EditAccountForm with the fetched account data
        edit_account_form = EditAccountForm(self.google_sheets_client, account_data)
        self.content.layout().addWidget(edit_account_form)

Create Edit Account Class

EditAccountForm Class inherits from AddAccountForm.

• __init__: Initializes the form using the super class constructor with investor data and populates it with the existing account data.

class EditAccountForm(AddAccountForm):
    def __init__(self, google_sheets_client, account_data):
        # Call the super class constructor with investor data
        super().__init__(google_sheets_client, account_data["investor_data"])

        # Populate the form with existing account data
        self.populate_form(account_data)

• populate_form: Fills in the form fields with the account data. It ensures that certain fields like 'Reinvest/Cash Dividend' and 'Account ID' are set correctly and made uneditable.

    def populate_form(self, account_data):
        # Populate the account fields
        for field, widget in self.input_fields.items():
            if field in account_data:
                if isinstance(widget, QLineEdit):
                    widget.setText(account_data[field])
                elif isinstance(widget, QComboBox):
                    index = widget.findText(account_data[field])
                    widget.setCurrentIndex(index)

        # Special handling for 'Reinvest/Cash Dividend' QComboBox
        reinvest_cash_dividend_widget = self.account_id_fields.get("Reinvest/Cash Dividend")
        if reinvest_cash_dividend_widget:
            reinvest_cash_dividend_widget.setEnabled(False)
            reinvest_cash_dividend_widget.setStyleSheet("background-color: lightgrey;")

        # Set Account ID as uneditable
        self.account_id_fields["Account ID"].setText(account_data["ID_Account"])
        self.account_id_fields["Account ID"].setReadOnly(True)
        self.account_id_fields["Account ID"].setStyleSheet("background-color: lightgrey;")

        self.account_data = account_data

• submit_form: Collects updated data from the form, updates the Google Sheets database, and handles success or error messages.

    def submit_form(self):
        # Collect updated data from input fields
        updated_data = {}
        for field_name, widget in self.input_fields.items():
            if isinstance(widget, QLineEdit):
                updated_data[field_name] = widget.text()
            elif isinstance(widget, QComboBox):
                updated_data[field_name] = widget.currentText()

        self.account_data.update(updated_data)
        updated_data = self.account_data

        try:
            # Update the existing account data in Google Sheets
            self.update_account_data_in_sheet(updated_data)

            QMessageBox.information(self, "Success", "Account data updated successfully.")
        except Exception as e:
            QMessageBox.critical(self, "Error", f"Failed to update account data: {e}")

        # Clear the input fields after submission
        self.clear_input_fields()

• update_account_data_in_sheet: Updates the existing account data in the Google Sheets worksheet "02_Accounts" by finding the correct row and updating it with the new data.

    def update_account_data_in_sheet(self, updated_data):
        try:
            worksheet = self.google_sheets_client.open_spreadsheet(GOOGLESHEET_ID).worksheet("02_Accounts")
            data = worksheet.get_all_values()
            headers = data[0]
            id_column_index = headers.index("ID_Account") + 1  # +1 for 1-based index in Google Sheets

            # Find the row of the account to update
            row_to_update = None
            for i, row in enumerate(data):
                if row[id_column_index - 1] == updated_data["ID_Account"]:  # -1 for 0-based index in Python
                    row_to_update = i + 1  # +1 for 1-based index in Google Sheets
                    break

            if row_to_update is None:
                raise Exception("Account ID not found in the sheet.")

            # Prepare the updated row data
            updated_row = [updated_data.get(header, "") for header in headers]

            # Update the row in the sheet
            worksheet.update(f"A{row_to_update}:{chr(64 + len(headers))}{row_to_update}", [updated_row])
        except Exception as e:
            raise Exception(f"Error updating account data: {e}")

Next Step

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