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

Previously

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

Call function in the Main Window

1. display_edit_investor_form (in MainWindow Class):

• Clears existing content.
• Opens a dialog (SelectInvestorDialog) to choose an investor.
• If an investor is selected, it fetches their data using fetch_investor_data and displays the edit form using showEditInvestorForm.

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

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

        if result == QDialog.Accepted:
            selected_id = dialog.getSelectedInvestorID()
            if selected_id:
                investor_data = self.fetch_investor_data(selected_id)
                if investor_data:
                    self.showEditInvestorForm(investor_data)

2. fetch_investor_data (in MainWindow Class):

• Connects to the Google Sheets worksheet.
• Maps column names to their indices to ensure data integrity.
• Searches for a specific investor by their ID and retrieves their row data.
• Returns a dictionary with key-value pairs representing the investor’s data.

    def fetch_investor_data(self, investor_id):
        # Connect to Google Sheets and select the appropriate worksheet
        worksheet = self.google_sheets_client.open_spreadsheet(GOOGLESHEET_ID).worksheet("01_Investors")

        # 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_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 'ID_Investor' column
        id_investor_column = worksheet.col_values(column_index_map["ID_Investor"] + 1)


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

        # Use the column names to access the data
        return {
            "ID_Investor": row_data[column_index_map["ID_Investor"]],
            "Type": row_data[column_index_map["Type"]],
            "Last Name": row_data[column_index_map["Last Name"]],
            "First Name": row_data[column_index_map["First Name"]],
            "Company Name": row_data[column_index_map["Company Name"]]
        }

3. showEditInvestorForm (in MainWindow Class):

• Instantiates EditInvestorForm with the fetched investor data.
• Adds the edit form widget to the layout.

    def showEditInvestorForm(self, investor_data):
        # Create the EditInvestorForm with the fetched investor data
        edit_investor_form = EditInvestorForm(self.google_sheets_client, investor_data)
        self.content.layout().addWidget(edit_investor_form)

Create Eidt Investor Class

EditInvestorForm Class (inherits from AddInvestorForm):

• __init__: Calls the constructor of AddInvestorForm and then populates the form with the existing investor data.

class EditInvestorForm(AddInvestorForm):
    def __init__(self, google_sheets_client, investor_data):
        # Call the super class constructor
        super().__init__(google_sheets_client)

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

• populate_form: Updates the form's input fields with the investor's data.

    def populate_form(self, investor_data):
        # Set the values of input fields based on investor_data
        for field, value in investor_data.items():
            if field in self.input_fields:
                widget = self.input_fields[field]
                if isinstance(widget, QLineEdit):
                    widget.setText(value)
                elif isinstance(widget, QComboBox):
                    widget.setCurrentText(value)

• submit_form: Collects updated data from the form, attempts to update 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()

        try:
            # Update the existing investor data in Google Sheets
            self.update_investor_data_in_sheet(updated_data)
            QMessageBox.information(self, "Success", "Investor data updated successfully.")
        except Exception as e:
            QMessageBox.critical(self, "Error", f"Failed to update investor data: {e}")

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

• update_investor_data_in_sheet: Updates the main investor data in Google Sheets and also updates related sheets.

    def update_investor_data_in_sheet(self, updated_data):
        try:
            # Update the main Investors sheet first
            self.update_sheet_data("01_Investors", "ID_Investor", updated_data)

            # Define the mappings for other sheets
            sheets_columns_mapping = {
                "02_Accounts": ["Type", "Last Name", "First Name", "Company Name", "Residency Status in Canada"],
                "03_Transactions": ["Type", "Last Name", "First Name", "Company Name"],
                "04_Dividends": ["Type", "Last Name", "First Name", "Company Name", "Residency Status in Canada"],
                "05_Balances": ["Type", "Last Name", "First Name", "Company Name"]
            }

            # Update related sheets
            for sheet_name, columns in sheets_columns_mapping.items():
                self.update_sheet_data(sheet_name, "ID_Investor", updated_data, columns)

        except Exception as e:
            raise Exception(f"Error updating investor data: {e}")

• update_sheet_data: A helper method to update specific columns in a given sheet with the updated investor data.

    def update_sheet_data(self, sheet_name, id_column, updated_data, columns_to_update=None):
        worksheet = self.google_sheets_client.open_spreadsheet(GOOGLESHEET_ID).worksheet(sheet_name)
        all_data = worksheet.get_all_values()
        headers = all_data[0]
        id_column_index = headers.index(id_column) + 1  # +1 for 1-based index

        for i, row in enumerate(all_data[1:], start=2):  # +2 to account for header row and 1-based index
            if row[id_column_index - 1] == updated_data[id_column]:
                # Row matches the ID_Investor, prepare updated row data
                updated_row = row[:]
                for col in (columns_to_update or headers):
                    if col in updated_data:
                        col_index = headers.index(col)
                        updated_row[col_index] = updated_data[col]

                # Update the row in the sheet
                worksheet.update(f"A{i}:{chr(64 + len(headers))}{i}", [updated_row])

Create Select Investor Dialog Class:

• __init__: Initializes the dialog window for selecting an investor.

class SelectInvestorDialog(QDialog):
    def __init__(self, google_sheets_client):
        super().__init__()
        
        self.google_sheets_client = google_sheets_client
        self.selected_investor_id = None

        self.setWindowTitle("Select Investor")
        self.setGeometry(100, 100, 600, 400)

        # Center the window on the screen
        self.center_window()

        self.initUI()

• center_window: Centers the dialog window on the screen.

    def center_window(self):
        # Get the primary screen
        screen = QApplication.primaryScreen()

        # Get the size of the screen
        screen_geometry = screen.geometry()

        # Calculate the position to center the window
        x = (screen_geometry.width() - self.width()) // 2
        y = (screen_geometry.height() - self.height()) // 2

        # Set the position
        self.move(x, y)

• initUI: Sets up the UI of the dialog, including a table to display investors and buttons for confirmation or cancellation.

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

        # Table to display investors
        self.table = QTableWidget()
        self.table.setSelectionBehavior(QTableWidget.SelectRows)  # Select entire rows
        self.table.setSelectionMode(QTableWidget.SingleSelection)  # Allow only single row selection
        self.table.setEditTriggers(QTableWidget.NoEditTriggers)  # Make table content non-editable
        self.table.itemDoubleClicked.connect(self.onRowDoubleClicked)

        self.table.setColumnCount(5)  # ID_Investor, Last Name, First Name, Company Name
        self.table.setHorizontalHeaderLabels(["ID_Investor", "Type", "Last Name", "First Name", "Company Name"])
        self.loadInvestorsData()

        # Buttons
        btn_layout = QHBoxLayout()
        ok_btn = QPushButton("OK")
        ok_btn.setFixedWidth(100)
        ok_btn.clicked.connect(self.accept)

        cancel_btn = QPushButton("Cancel")
        cancel_btn.setFixedWidth(100)
        cancel_btn.clicked.connect(self.reject)

        btn_layout.addWidget(ok_btn)
        btn_layout.addWidget(cancel_btn)

        layout.addWidget(self.table)
        layout.addLayout(btn_layout)

• onRowDoubleClicked: Handles the event when a table row is double-clicked.

    def onRowDoubleClicked(self, item):
        # This method gets triggered on double-clicking a table row
        # Perform the same action as clicking the "OK" button
        self.accept()

• loadInvestorsData: Loads investor data from Google Sheets into the table.

    def loadInvestorsData(self):
        # Fetch data from Google Sheets and identify column indices
        worksheet = self.google_sheets_client.open_spreadsheet(GOOGLESHEET_ID).worksheet('01_Investors')
        all_data = worksheet.get_all_values()
        header_row = all_data[0]  # The first row is assumed to be the header

        # Create a dictionary to map column names to their indices
        column_indices = {name: index for index, name in enumerate(header_row)}

        # Define the desired column names
        desired_columns = ["ID_Investor", "Type", "Last Name", "First Name", "Company Name"]

        # Check if all desired columns are present in the sheet
        for col in desired_columns:
            if col not in column_indices:
                print(f"Column '{col}' not found in the spreadsheet.")
                return

        # Populate the table with data
        for row_data in all_data[1:]:  # Skip the header row
            row = self.table.rowCount()
            self.table.insertRow(row)
            for col_name in desired_columns:
                col_index = column_indices[col_name]
                self.table.setItem(row, desired_columns.index(col_name), QTableWidgetItem(row_data[col_index]))

• getSelectedInvestorID: Retrieves the ID of the selected investor.

    def getSelectedInvestorID(self):
        # Return the selected investor's ID
        selected_row = self.table.currentRow()
        if selected_row != -1:
            return self.table.item(selected_row, 0).text()  # ID_Investor is in the first column
        return None

Next Step

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