How to use Python, PySide6 and Google Sheets to make a Database App (4): Add Account

Each Investor has a unique ID, while may have several accouts. There are 3 steps to add an account for an investor in a database based on Google Sheets:

1. Select the Investor;
2. Edit the information;
3. Submit.

Call the function of Add an Account

class MainWindow(QMainWindow):
    def __init__(self, google_sheets_client):

    #...


    def handle_secondary_action(self, top_level_item, second_level_item):
        # Handle the action based on the top-level and second-level item
        if top_level_item == "Investors" and second_level_item == "Add Investor":
            self.display_add_investor_form()
        elif top_level_item == "Accounts" and second_level_item == "Add Account":
            self.display_add_account_form()        
        # Add more conditions here for other menu items
        # ...

Function display_add_account_form

This function is responsible for displaying the form to add account details for a selected investor.

• Opens a SelectInvestorDialog to choose an investor.
• If an investor is selected (dialog accepted), it fetches detailed data of the selected investor by calling fetch_investor_data.
• If investor data is successfully retrieved, it displays the AddAccountForm using this data.

class MainWindow(QMainWindow):
    def __init__(self, google_sheets_client):

    #...

    def display_add_account_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.showAddAccountForm(investor_data)

Method fetch_investor_data

Fetches detailed data for a specific investor from the Google Sheets worksheet.

• Connects to the “01_Investors” worksheet and maps the column names to their indices.
• Checks for the presence of required columns.
• Finds the row corresponding to the given investor_id and fetches its data.
• Returns a dictionary containing key information about the investor.

class MainWindow(QMainWindow):
    def __init__(self, google_sheets_client):

    #...

    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)  # +1 for 1-based index

        # 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"]]
        }

Method showAddAccountForm

Displays the form to add or edit an account for a selected investor.

• Creates an instance of AddAccountForm with the retrieved investor data.
• Adds this form to the main content layout of the application.

class MainWindow(QMainWindow):
    def __init__(self, google_sheets_client):

    #...

    def showAddAccountForm(self, investor_data):
        # Create the AddAccountForm with the fetched investor data
        add_account_form = AddAccountForm(self.google_sheets_client, investor_data)
        self.content.layout().addWidget(add_account_form)

Create AddAccountForm

Class Initialization — __init__ method

• google_sheets_client: This parameter is an instance of a Google Sheets client, which is used to interact with Google Sheets for data manipulation.
• investor_data: A dictionary containing data about an investor, such as their name and company name.
• worksheet_name: Specifies the name of the worksheet within the Google Sheet where account data will be added.
• Fields and Labels Setup: The class defines fields and labels for both investor and account information. These fields will be displayed as labels and input fields in the UI.
• Random Account ID Generation: Generates a random Account ID using the random.randint function.
• UI Initialization: Calls the initUI method to set up the user interface.

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

        self.google_sheets_client = google_sheets_client
        self.investor_data = investor_data
        self.worksheet_name = "02_Accounts"

        # Define the fields and labels for the investor form
        self.investor_labels = list(self.investor_data.keys())
        self.investor_fields = list(self.investor_data.values())

        # Define the fields and labels for the account form
        self.account_labels = ["Bank Name:", "Institution Number:", "Branch Number:", "Account Number:", "Initial Investment:",
                               "Money Received On:", "Share Issue Date:", "Retraction Date:", "Share Class:",
                               "Share Issued Amount:", "Share Certificate Number:"]
        self.account_fields = ["Bank Name", "Institution Number", "Branch Number", "Account Number", "Initial Investment",
                               "Money Received On", "Share Issue Date", "Retraction Date", "Share Class",
                               "Share Issued Amount", "Share Certificate Number"]

        # Initialize input fields
        self.input_fields = {field: QLineEdit() for field in self.account_fields}

        self.account_id_labels = ["Account ID:"]
        self.account_id_fields = [str(random.randint(100000, 999999))]

        # Initialize UI
        self.initUI()

UI Setup — initUI method

• Scroll Area: Sets up a scrollable area to accommodate the form contents.
• Group Boxes: Creates group boxes for displaying investor information, assigned account ID, and account information. Each group box is created using the create_group_box method.
• Submit Button: A button for submitting the form. It’s connected to the submit_form method.

class AddAccountForm(QWidget):
    def __init__(self, google_sheets_client, investor_data):

    #...

    def initUI(self):
        # Main layout within a scrollable area
        scroll = QScrollArea(self)
        scroll.setWidgetResizable(True)
        content_widget = QWidget()
        scroll.setWidget(content_widget)
        layout = QVBoxLayout(content_widget)

        # Add group boxes without additional stretching between them
        layout.addWidget(self.create_group_box("Investor Information", self.investor_labels, self.investor_fields, is_editable=False))
        layout.addSpacing(20)
        layout.addWidget(self.create_group_box("Assigned Account ID", self.account_id_labels, self.account_id_fields, is_editable=False))
        layout.addSpacing(20) 
        layout.addWidget(self.create_group_box("Account Information", self.account_labels, self.account_fields, is_editable=True))

        # Main layout
        main_layout = QVBoxLayout(self)
        main_layout.addWidget(scroll)

        # Submit Button outside the scroll area, centered at the bottom
        self.submit_button = QPushButton("Add Account")
        self.submit_button.clicked.connect(self.submit_form)
        button_layout = QHBoxLayout()
        button_layout.addStretch(1)
        button_layout.addWidget(self.submit_button)
        button_layout.addStretch(1)

        main_layout.addLayout(button_layout)
        self.setLayout(main_layout)

Creating Group Boxes — create_group_box method

• title: The title of the group box.
• labels and fields: Arrays of labels and corresponding fields. Labels are displayed as text, and fields are either displayed as text or as input fields, depending on is_editable.
• Grid Layout: Organizes the labels and fields in a grid layout within each group box.
• Editable Check: Determines if the fields should be editable or read-only.

class AddAccountForm(QWidget):
    def __init__(self, google_sheets_client, investor_data):

    #...

    def create_group_box(self, title, labels, fields, is_editable=True):
        group_box = QGroupBox(title)

        group_box.setAlignment(Qt.AlignCenter)  # Center the title
        title_font = QFont()
        title_font.setPointSize(QLabel().font().pointSize())  # Set the font size to match label size
        group_box.setFont(title_font)

        layout = QGridLayout(group_box)
        layout.setVerticalSpacing(10)  # Set a fixed vertical spacing of 10 pixels between rows

        for i, field_name in enumerate(fields):
            row, col = divmod(i, 2)

            label = QLabel(labels[i])
            label.setAlignment(Qt.AlignRight | Qt.AlignVCenter)
            label.setWordWrap(True)
            layout.addWidget(label, row, col * 2)


            # Determine the widget based on the editability
            if is_editable:
                widget = self.input_fields.get(field_name, QLineEdit())  # Fetch from input_fields or create a new QLineEdit
            else:
                widget = QLineEdit(str(field_name))
                widget.setReadOnly(True)
                widget.setStyleSheet("background-color: lightgrey;")

            layout.addWidget(widget, row, col * 2 + 1)

        # Adjust column stretch to make each column equally wide
        layout.setColumnStretch(0, 1)
        layout.setColumnStretch(1, 1)
        layout.setColumnStretch(2, 1)
        layout.setColumnStretch(3, 1)

        group_box.setLayout(layout)

        # Remove additional stretch at the end to let the height be content-based
        #layout.setRowStretch(row + 1, 0)

        return group_box

Form Submission — submit_form method

• Data Collection: Iterates over input fields to collect the data entered by the user.
• Data Integration: Combines the collected account data with the investor data.
• Google Sheets Interaction: Connects to the specified worksheet in Google Sheets and appends the collected data as a new row.

class AddAccountForm(QWidget):
    def __init__(self, google_sheets_client, investor_data):

    #...

    def submit_form(self):
        # Collect data from input fields
        account_data = {key: widget.text() for key, widget in self.input_fields.items()}

        # Add investor data to account_data
        account_data.update(self.investor_data)

        # Submit data to Google Sheets
        try:
            worksheet = self.google_sheets_client.open_spreadsheet(GOOGLESHEET_ID).worksheet(self.worksheet_name)
            
            # Get column headers from the first row of the worksheet
            column_headers = worksheet.row_values(1)

            # Prepare row_data according to the order of column headers
            row_data = [account_data.get(header, "") for header in column_headers]

            worksheet.append_row(row_data)
            QMessageBox.information(self, "Success", "Account data submitted successfully.")
        except Exception as e:
            QMessageBox.critical(self, "Error", f"Failed to submit account data: {e}")

        self.clear_input_fields()

    def clear_input_fields(self):
        # Clear all input fields
        for widget in self.input_fields.values():
            widget.clear()