Python Bits and Pieces with Cyber Security: September 2024

Wednesday, September 11, 2024

Enhancing PyQt6 Applications with Modular Code and Dynamic Widget Management

Introduction

When developing GUI applications, maintaining a clean and scalable codebase is crucial for future enhancements and maintenance. PyQt6, a popular Python binding for the Qt application framework, offers a flexible way to create desktop applications with a rich set of features. In this blog post, we will explore a Python program that demonstrates best practices in PyQt6 coding, including dynamic widget management and modular design principles.

Understanding the Code

The program presented below is a PyQt6 application that creates a main window with a scrollable area containing several buttons. It dynamically retrieves all widgets within the scroll area and disables a specific button based on its name. Here’s a breakdown of the code:

from PyQt6.QtWidgets import QApplication, QMainWindow, QScrollArea, QWidget, QVBoxLayout, QPushButton

app = QApplication([])

window = QMainWindow()
scroll_area = QScrollArea()
container = QWidget()
layout = QVBoxLayout(container)

# Creating buttons with specific object names
for i in range(5):
    button = QPushButton(f"Button {i+1}")
    button.setObjectName(f"button_{i+1}")  # Set object name for each button
    layout.addWidget(button)

scroll_area.setWidget(container)
scroll_area.setWidgetResizable(True)
window.setCentralWidget(scroll_area)
window.show()

def get_all_widgets(scroll_area):
    # Get the container widget inside the QScrollArea
    container = scroll_area.widget()

    # Check if the container has a layout
    if container.layout() is not None:
        # Get all widgets from the container
        widgets = []
        layout = container.layout()
        for i in range(layout.count()):
            widget = layout.itemAt(i).widget()
            if widget is not None:
                widgets.append(widget)
        return widgets
    else:
        return []

# Retrieve all widgets in the scroll area
widgets = get_all_widgets(scroll_area)

# Iterate over widgets and disable buttons with a specific name
for widget in widgets:
    # Print the object name of each widget
    print(f"Widget Name: {widget.objectName()}")
    
    # Check if the widget is a QPushButton and has a specific name
    if isinstance(widget, QPushButton) and widget.objectName() == "button_3":
        widget.setDisabled(True)  # Disable the button with the name "button_3"

app.exec()

Key Advantages and Best Practices

Dynamic Widget Management
The function get_all_widgets() is a robust way to dynamically retrieve all widgets within a QScrollArea. This practice is particularly useful when working with complex UIs where you may need to interact with multiple widgets based on certain conditions or user interactions. By accessing widgets dynamically, you can implement features like mass updating, filtering, or conditional formatting without hardcoding widget references.
Modular and Readable Code Structure
The code uses separate functions (get_all_widgets()) to encapsulate specific functionality, enhancing readability and maintainability. This modular approach allows developers to isolate and test individual components of the codebase independently, reducing the risk of introducing bugs when making changes.
Use of Object Names for Identification
By setting object names for widgets (setObjectName()), the code effectively tags each widget with a unique identifier that can be used to perform specific actions. This practice is advantageous in scenarios where widgets need to be accessed or modified based on their roles or functions in the UI, such as disabling a specific button when certain conditions are met.
Scalable UI Design with Layout Management
The use of QVBoxLayout within a scrollable container (QScrollArea) showcases a scalable approach to UI design in PyQt6. As more widgets are added to the layout, the scroll area automatically adjusts to accommodate them, maintaining a clean and user-friendly interface. This design pattern is ideal for applications that display a variable number of widgets, such as forms, lists, or dynamic content.
Enhanced User Experience with Conditional Interactions
Disabling the button named "button_3" based on its object name demonstrates a conditional interaction that can be expanded to various user interface behaviors. This approach can be adapted to enable, disable, or modify widgets based on user input, application state, or data conditions, creating a more interactive and responsive application.

Conclusion

The example program highlights effective coding practices in PyQt6, emphasizing the importance of modularity, dynamic widget management, and scalable UI design. By leveraging these techniques, developers can build robust and maintainable desktop applications that are easy to extend and adapt to changing requirements. Whether you are building simple tools or complex enterprise applications, these practices will help you create clean, efficient, and user-friendly interfaces with PyQt6.

Tuesday, September 10, 2024

Creating Forms and Print Preview with Jinja2 Templates in PyQt6

In this blog post, we will walk through creating a PyQt6 application that uses Jinja2 templates for rendering printable content. Our example application is a manufacturing order entry system with forms for data entry and functionality for print preview and printing.

We expect to create the following screen:

And here is its print preview:

Step-by-Step Breakdown

Setup PyQt6 and Jinja2
Ensure you have PyQt6 and Jinja2 installed. You can install these via pip if you haven’t already:

pip install PyQt6 jinja2

2. Create the Main Application Class
We'll start by creating a class ManufacturingOrderEntry that inherits from QMainWindow. This class will contain all the UI elements and functionality for our form.

import sys
from PyQt6.QtWidgets import QApplication, QMainWindow, QWidget, QVBoxLayout, QFormLayout, QLineEdit, QDateEdit, QLabel, QHBoxLayout, QTableWidget, QTableWidgetItem, QGridLayout, QPushButton, QTextEdit
from PyQt6.QtGui import QFont, QColor, QPalette
from PyQt6.QtCore import Qt
from PyQt6 import QtPrintSupport
from jinja2 import Template

3. Define the Constructor and Layouts
In the __init__ method, set up the main window layout and UI components

class ManufacturingOrderEntry(QMainWindow):
    def __init__(self):
        super().__init__()
        self.setWindowTitle("Manufacturing Order Entry")
        self.setGeometry(100, 100, 800, 600)

        # Main Layout
        main_widget = QWidget(self)
        self.setCentralWidget(main_widget)
        main_layout = QVBoxLayout()
        main_widget.setLayout(main_layout)

        # Header Sections
        header_layout = self.create_header_layout()
        main_layout.addLayout(header_layout)

        # Process and Inspection Sections
        process_layout = self.create_process_layout()
        main_layout.addLayout(process_layout)

        # Prepared and Approved By Section
        prepared_approved_layout = self.create_prepared_approved_layout()
        main_layout.addLayout(prepared_approved_layout)

        # Adding TextEdit for rendering printable content
        self.etext = QTextEdit(self)
        self.etext.setReadOnly(True)
        main_layout.addWidget(self.etext)

        # Buttons for Print and Preview
        button_layout = QHBoxLayout()
        self.print_button = QPushButton('Print', self)
        self.preview_button = QPushButton('Preview', self)
        button_layout.addWidget(self.preview_button)
        button_layout.addWidget(self.print_button)
        main_layout.addLayout(button_layout)

        # Connecting buttons to handlers
        self.preview_button.clicked.connect(self.handle_print_preview)
        self.print_button.clicked.connect(self.handle_print)

        # Generate the content for the QTextEdit
        self.generate_content()

4. Define Layout Creation Methods
Methods like create_header_layout, create_process_layout, and create_prepared_approved_layout are responsible for setting up different sections of the form.

def create_header_layout(self):
    layout = QGridLayout()
    layout.setContentsMargins(5, 5, 5, 5)
    # Add widgets and layout configurations here
    return layout

def create_process_layout(self):
    layout = QVBoxLayout()
    # Add widgets and layout configurations here
    return layout

def create_prepared_approved_layout(self):
    layout = QHBoxLayout()
    # Add widgets and layout configurations here
    return layout

5. Generate Content with Jinja2
Use Jinja2 templates to render HTML content for printing. This content is set in a QTextEdit widget, which will be used for both print preview and printing.

def generate_content(self):
    # Collecting data
    # Define the data to be used in the template
    template = """
    <style>
        /* Define your CSS styles here */
    </style>
    <h1>Production Order</h1>
    <!-- Manufacturing Order Details -->
    <table>
        <!-- Table content here -->
    </table>
    <!-- Process I, II, Inspection, Packing tables -->
    <!-- Signature section -->
    """
    # Render the template with data
    rendered_html = Template(template).render(
        # Pass variables here
    )
    self.etext.setHtml(rendered_html)

6. Implement Print Preview and Printing
Implement methods to handle print preview and printing using QPrintPreviewDialog and QPrinter.

def handle_print_preview(self):
    dialog = QtPrintSupport.QPrintPreviewDialog()
    dialog.paintRequested.connect(self.etext.print)
    dialog.exec()

def handle_print(self):
    printer = QtPrintSupport.QPrinter(QtPrintSupport.QPrinter.PrinterMode.HighResolution)
    printer.setPageSize(QtPrintSupport.QPageSize(QtPrintSupport.QPageSize.PageSizeId.A4))
    self.etext.print(printer)

7. Run the Application
Finally, set up the main function to run the application

def main():
    app = QApplication(sys.argv)
    window = ManufacturingOrderEntry()
    window.show()
    sys.exit(app.exec())

if __name__ == "__main__":
    main()

Conclusion

This example demonstrates how to integrate Jinja2 templating with PyQt6 to create dynamic, printable forms. The approach allows for the separation of form data and presentation logic, making it easier to manage and update form layouts and styles. You can further customize the template and styles as needed for your specific use case.

Here is the rest complete program listing:

import sys
from PyQt6.QtWidgets import (
    QApplication, QMainWindow, QWidget, QVBoxLayout, QFormLayout, QLineEdit, 
    QDateEdit, QLabel, QHBoxLayout, QTableWidget, QTableWidgetItem, QGridLayout, QPushButton, QTextEdit
)
from PyQt6.QtGui import QFont, QColor, QPalette
from PyQt6.QtCore import Qt
from PyQt6 import QtPrintSupport
from jinja2 import Template

class ManufacturingOrderEntry(QMainWindow):
    def __init__(self):
        super().__init__()
        self.setWindowTitle("Manufacturing Order Entry")
        self.setGeometry(100, 100, 800, 600)

        # Main Layout
        main_widget = QWidget(self)
        self.setCentralWidget(main_widget)
        main_layout = QVBoxLayout()
        main_widget.setLayout(main_layout)

        # Header Sections
        header_layout = self.create_header_layout()
        main_layout.addLayout(header_layout)

        # Process and Inspection Sections
        process_layout = self.create_process_layout()
        main_layout.addLayout(process_layout)

        # Prepared and Approved By Section
        prepared_approved_layout = self.create_prepared_approved_layout()
        main_layout.addLayout(prepared_approved_layout)

        # Adding TextEdit for rendering printable content
        self.etext = QTextEdit(self)
        self.etext.setReadOnly(True)
        main_layout.addWidget(self.etext)

        # Buttons for Print and Preview
        button_layout = QHBoxLayout()
        self.print_button = QPushButton('Print', self)
        self.preview_button = QPushButton('Preview', self)
        button_layout.addWidget(self.preview_button)
        button_layout.addWidget(self.print_button)
        main_layout.addLayout(button_layout)

        # Connecting buttons to handlers
        self.preview_button.clicked.connect(self.handle_print_preview)
        self.print_button.clicked.connect(self.handle_print)

        # Generate the content for the QTextEdit
        self.generate_content()

    def generate_content(self):
        # Collecting data from the form fields
        order_no = "12345"
        product = "Widget A"
        job_quantity = "500"
        customer_delivery_date = "2024-09-25"
        job_started_date = "2024-09-20"
        rm_transfer_date = "2024-09-21"
        packing_target_date = "2024-09-22"
        dispatch_target_date = "2024-09-23"
        finish_product_transfer_date = "2024-09-24"
        job_end_date = "2024-09-25"

        process_i = [
            ["1", "100", "pcs", "Stage 1", "0", "$100", "100"],
            ["2", "200", "pcs", "Stage 2", "0", "$200", "200"],
            ["3", "300", "pcs", "Stage 3", "0", "$300", "300"],
        ]
        process_ii = [
            ["1", "100", "pcs", "Stage 1", "0", "$100", "100"],
            ["2", "200", "pcs", "Stage 2", "0", "$200", "200"],
        ]
        inspection = [
            ["1", "100", "pcs", "Inspection 1", "0", "$50", "100"],
        ]
        packing = [
            ["1", "100", "pcs", "Packing 1", "0", "$70", "100"],
        ]
        prepared_by = "John Doe"
        approved_by = "Jane Smith"

        template = """
<style>
    body {
        font-family: Arial, sans-serif;
    }
    table {
        width: 100%;
        border-collapse: collapse;
    }
    th, td {
        border: 1px solid #000;
        padding: 5px;
        text-align: center;
    }
    th {
        background-color: #000;
        color: #fff;
    }
    .section-header {
        background-color: #000;
        color: #fff;
        text-align: center;
        font-weight: bold;
        padding: 5px;
        border: 1px solid #000;
        margin-top: 10px;
    }
    .process-table {
        margin-top: 10px;
    }
    .process-header {
        background-color: #FFD700;
        text-align: center;
        font-weight: bold;
    }
    .signature-section {
        width: 50%;
        margin: 30px auto;
        text-align: center;
    }
    .signature-line {
        border-top: 1px solid #000;
        width: 60%;
        margin: 0 auto;
    }
    .signature-label {
        margin-top: 5px;
        font-weight: bold;
    }
    .signature-container {
        display: flex;
        justify-content: space-between;
    }
    .signature-item {
        flex: 1;
        text-align: center;
    }
</style>

<h1>Production Order</h1>

<!-- Manufacturing Order Details -->
<table>
    <tr>
        <td>Manufacturing Order No. & Date:</td>
        <td>{{ order_no }}</td>
        <td>Job Started Date:</td>
        <td>{{ job_started_date }}</td>
    </tr>
    <tr>
        <td>Product:</td>
        <td>{{ product }}</td>
        <td>R.M. Location - Mat. Transfer Date:</td>
        <td>{{ rm_transfer_date }}</td>
    </tr>
    <tr>
        <td>Job Quantity:</td>
        <td>{{ job_quantity }}</td>
        <td>Target of Packing:</td>
        <td>{{ packing_target_date }}</td>
    </tr>
    <tr>
        <td>Customer Delivery Date:</td>
        <td>{{ customer_delivery_date }}</td>
        <td>Target of Dispatch:</td>
        <td>{{ dispatch_target_date }}</td>
    </tr>
    <tr>
        <td></td>
        <td></td>
        <td>Finish Product Location - Mat. Transfer Date:</td>
        <td>{{ finish_product_transfer_date }}</td>
    </tr>
    <tr>
        <td></td>
        <td></td>
        <td>Job End Date:</td>
        <td>{{ job_end_date }}</td>
    </tr>
</table>

<!-- Process I -->
<div class="section-header">Process I</div>
<table class="process-table">
    <thead class="process-header">
        <tr>
            <th>Serial No.</th>
            <th>QTY</th>
            <th>U.M.O</th>
            <th>Internal Process Stage</th>
            <th>Rej. Qty</th>
            <th>Cost</th>
            <th>Passed Quantity</th>
        </tr>
    </thead>
    <tbody>
        {% for row in process_i %}
        <tr>
            {% for element in row %}
            <td>{{ element }}</td>
            {% endfor %}
        </tr>
        {% endfor %}
    </tbody>
</table>

<!-- Process II -->
<div class="section-header">Process II</div>
<table class="process-table">
    <thead class="process-header">
        <tr>
            <th>Serial No.</th>
            <th>QTY</th>
            <th>U.M.O</th>
            <th>Internal Process Stage</th>
            <th>Rej. Qty</th>
            <th>Cost</th>
            <th>Passed Quantity</th>
        </tr>
    </thead>
    <tbody>
        {% for row in process_ii %}
        <tr>
            {% for element in row %}
            <td>{{ element }}</td>
            {% endfor %}
        </tr>
        {% endfor %}
    </tbody>
</table>

<!-- Inspection -->
<div class="section-header">Inspection</div>
<table class="process-table">
    <thead class="process-header">
        <tr>
            <th>Serial No.</th>
            <th>QTY</th>
            <th>U.M.O</th>
            <th>Internal Process Stage</th>
            <th>Rej. Qty</th>
            <th>Cost</th>
            <th>Passed Quantity</th>
        </tr>
    </thead>
    <tbody>
        {% for row in inspection %}
        <tr>
            {% for element in row %}
            <td>{{ element }}</td>
            {% endfor %}
        </tr>
        {% endfor %}
    </tbody>
</table>

<!-- Packing -->
<div class="section-header">Packing</div>
<table class="process-table">
    <thead class="process-header">
        <tr>
            <th>Serial No.</th>
            <th>QTY</th>
            <th>U.M.O</th>
            <th>Internal Process Stage</th>
            <th>Rej. Qty</th>
            <th>Cost</th>
            <th>Passed Quantity</th>
        </tr>
    </thead>
    <tbody>
        {% for row in packing %}
        <tr>
            {% for element in row %}
            <td>{{ element }}</td>
            {% endfor %}
        </tr>
        {% endfor %}
    </tbody>
</table>

<!-- Prepared and Approved By -->
<div class="signature-section" style="margin-top: 50px;">
    <div class="signature-container">
        <div class="signature-item">
            <div class="signature-label">Prepared By</div>
            <div class="signature-line"></div>
            <div style="height: 50px;"></div> <!-- Space for signature -->
            <div>{{ prepared_by }}</div>
        </div>
        <p /> <br />
        <div class="signature-item">
            <div class="signature-label">Approved By</div>
            <div class="signature-line"></div>
            <div style="height: 50px;"></div> <!-- Space for signature -->
            <div>{{ approved_by }}</div>
        </div>
    </div>
</div>
"""

        # Render the table with Jinja2 template
        rendered_html = Template(template).render(
            order_no=order_no,
            product=product,
            job_quantity=job_quantity,
            customer_delivery_date=customer_delivery_date,
            job_started_date=job_started_date,
            rm_transfer_date=rm_transfer_date,
            packing_target_date=packing_target_date,
            dispatch_target_date=dispatch_target_date,
            finish_product_transfer_date=finish_product_transfer_date,
            job_end_date=job_end_date,
            process_i=process_i,
            process_ii=process_ii,
            inspection=inspection,
            packing=packing,
            prepared_by=prepared_by,
            approved_by=approved_by
        )
        self.etext.setHtml(rendered_html)


    def handle_print_preview(self):
        dialog = QtPrintSupport.QPrintPreviewDialog()
        dialog.paintRequested.connect(self.etext.print)
        dialog.exec()

    def handle_print(self):
        printer = QtPrintSupport.QPrinter(QtPrintSupport.QPrinter.PrinterMode.HighResolution)
        printer.setPageSize(QtPrintSupport.QPageSize(QtPrintSupport.QPageSize.PageSizeId.A4))
        self.etext.print(printer)

    def create_header_layout(self):
        layout = QGridLayout()
        layout.setContentsMargins(5, 5, 5, 5)

        # Manufacturing Order Details
        layout.addWidget(self.create_colored_label("MANUFACTURING ORDER NO. & DATE:", "lightblue"), 0, 0)
        layout.addWidget(QLineEdit(), 0, 1)
        layout.addWidget(self.create_colored_label("PRODUCT:", "lightblue"), 1, 0)
        layout.addWidget(QLineEdit(), 1, 1)
        layout.addWidget(self.create_colored_label("JOB QUANTITY:", "lightblue"), 2, 0)
        layout.addWidget(QLineEdit(), 2, 1)
        layout.addWidget(self.create_colored_label("CUSTOMER DELIVERY DATE:", "lightblue"), 3, 0)
        layout.addWidget(QDateEdit(calendarPopup=True), 3, 1)

        # Job Details
        layout.addWidget(self.create_colored_label("JOB STARTED DATE:", "lightgreen"), 0, 2)
        layout.addWidget(QDateEdit(calendarPopup=True), 0, 3)
        layout.addWidget(self.create_colored_label("R.M. LOCATION - MAT. TRANSFER DATE:", "lightgreen"), 1, 2)
        layout.addWidget(QDateEdit(calendarPopup=True), 1, 3)
        layout.addWidget(self.create_colored_label("TARGET OF PACKING:", "lightgreen"), 2, 2)
        layout.addWidget(QDateEdit(calendarPopup=True), 2, 3)
        layout.addWidget(self.create_colored_label("TARGET OF DISPATCH:", "lightgreen"), 3, 2)
        layout.addWidget(QDateEdit(calendarPopup=True), 3, 3)
        layout.addWidget(self.create_colored_label("FINISH PRODUCT LOC. - MAT. TRANSFER DATE:", "lightgreen"), 4, 2)
        layout.addWidget(QDateEdit(calendarPopup=True), 4, 3)
        layout.addWidget(self.create_colored_label("JOB END DATE:", "lightgreen"), 5, 2)
        layout.addWidget(QDateEdit(calendarPopup=True), 5, 3)

        return layout

    def create_process_layout(self):
        layout = QVBoxLayout()
        
        # Process I Section
        layout.addWidget(self.create_colored_label("Process I", "lightpurple", True, font_color="white"))
        layout.addWidget(self.create_process_table("Process I", QColor(245, 222, 255)))  # Light purple

        # Process II Section
        layout.addWidget(self.create_colored_label("Process II", "lightpurple", True, font_color="white"))
        layout.addWidget(self.create_process_table("Process II", QColor(245, 222, 255)))  # Light purple

        # Inspection Section
        layout.addWidget(self.create_colored_label("Inspection", "yellow", True))
        layout.addWidget(self.create_process_table("Inspection", QColor(255, 255, 204)))  # Light yellow

        # Packing Section
        layout.addWidget(self.create_colored_label("Packing", "lightgreen", True))
        layout.addWidget(self.create_process_table("Packing", QColor(204, 255, 204)))  # Light green

        return layout

    def create_process_table(self, title, color):
        table = QTableWidget(5, 7)
        table.setHorizontalHeaderLabels([
            "Serial No.", "QTY", "U.M.O", "Internal Process Stage", 
            "Rej. Qty", "Cost", "Passed Quantity"
        ])
        for i in range(5):
            for j in range(7):
                table.setItem(i, j, QTableWidgetItem(""))

        # Set background color for each cell
        palette = table.palette()
        palette.setColor(QPalette.ColorRole.Base, color)
        table.setPalette(palette)
        table.setAlternatingRowColors(True)
        return table

    def create_colored_label(self, text, color, is_section=False, font_color="black"):
        label = QLabel(text)
        if is_section:
            label.setAlignment(Qt.AlignmentFlag.AlignCenter)
            label.setFont(QFont('Arial', 10, QFont.Weight.Bold))
            label.setStyleSheet(f"background-color: {color}; color: {font_color}; padding: 5px; border: 1px solid black;")
        else:
            label.setStyleSheet(f"background-color: {color}; padding: 5px;")
        return label

    def create_prepared_approved_layout(self):
        layout = QHBoxLayout()
        layout.addWidget(self.create_colored_label("PREPARED BY", "white"))
        layout.addWidget(QLineEdit())
        layout.addWidget(self.create_colored_label("APPROVED BY", "white"))
        layout.addWidget(QLineEdit())
        return layout

def main():
    app = QApplication(sys.argv)
    window = ManufacturingOrderEntry()
    window.show()
    sys.exit(app.exec())

if __name__ == "__main__":
    main()

Sunday, September 8, 2024

Creating Hierarchical Queries in SQLite Using Python: A Step-by-Step Guide

When working with databases, organizing data hierarchically can significantly improve data understanding and accessibility, especially in software development, customization, and enhancement projects. In this blog post, we will explore how to create hierarchical queries in SQLite using Python, focusing on a real-world scenario involving companies, plants, warehouses, sales organizations, and sales divisions. We will use subqueries to achieve this and save the result as a view for easy reuse.

Why Hierarchical Queries Matter in Database Management

Hierarchical queries are essential in applications where data is naturally structured in parent-child relationships. For instance, in a manufacturing setup:

A company might own multiple plants and sales organizations.
Each plant could have several warehouses.
Each sales organization might have multiple sales divisions.

Such hierarchical structures help in:

Visualizing complex relationships between different data entities.
Simplifying data retrieval by providing a structured view.
Enhancing performance by reducing the need for multiple joins in frequently executed queries.

By creating a hierarchical view in the database, developers and analysts can query this view directly, significantly simplifying application logic and report generation.

Setting Up the Scene: The Database Schema

Imagine the following database tables:

CompanyCodes: Contains company code details.
Plants: Contains plant details, related to a company code.
SalesOrganizations: Contains sales organization details, related to a company code.
Warehouses: Contains warehouse details, related to a plant.
SalesDivisions: Contains sales division details, related to a sales organization.

The goal is to create a hierarchical view where each company code lists its associated plants, sales organizations, warehouses, and sales divisions.

Step-by-Step Guide to Creating the Hierarchical Query

Step 1: Connect to Your SQLite Database

First, connect to your SQLite database using Python’s sqlite3 module. This module provides a lightweight and easy-to-use interface for working with SQLite databases.

import sqlite3

# Path to your SQLite database
db_path = 'your_database.db'

# Connect to the database
connection = sqlite3.connect(db_path)
cursor = connection.cursor()

Step 2: Write the Hierarchical Query

We will use subqueries within our main query to nest the results. Each subquery will gather related data, such as plants under each company, warehouses under each plant, etc.

CREATE VIEW CompanyHierarchy AS
SELECT 
    cc.company_code, 
    -- Subquery to get all plants related to the company code
    (
        SELECT GROUP_CONCAT(plant_name)
        FROM (
            SELECT p.plant_name 
            FROM Plants p 
            WHERE p.company_code = cc.company_code
        ) AS PlantList
    ) AS plants,
    -- Subquery to get all sales organizations related to the company code
    (
        SELECT GROUP_CONCAT(sales_org_name)
        FROM (
            SELECT so.sales_org_name 
            FROM SalesOrganizations so 
            WHERE so.company_code = cc.company_code
        ) AS SalesOrgList
    ) AS sales_organizations,
    -- Subquery to get warehouses for each plant of the company
    (
        SELECT GROUP_CONCAT(wh.warehouse_name)
        FROM Warehouses wh
        JOIN Plants p ON wh.plant_id = p.plant_id
        WHERE p.company_code = cc.company_code
    ) AS warehouses,
    -- Subquery to get sales divisions for each sales organization of the company
    (
        SELECT GROUP_CONCAT(sd.sales_division_name)
        FROM SalesDivisions sd
        JOIN SalesOrganizations so ON sd.sales_org_id = so.sales_org_id
        WHERE so.company_code = cc.company_code
    ) AS sales_divisions
FROM 
    CompanyCodes cc;

Step 3: Execute the Query and Save as a View

The CREATE VIEW statement saves the hierarchical query as a view named CompanyHierarchy. This allows you to query this structure directly in future database operations.

# Define the query to create the hierarchical view
create_view_query = """
CREATE VIEW CompanyHierarchy AS
SELECT 
    cc.company_code, 
    (
        SELECT GROUP_CONCAT(plant_name)
        FROM (
            SELECT p.plant_name 
            FROM Plants p 
            WHERE p.company_code = cc.company_code
        ) AS PlantList
    ) AS plants,
    (
        SELECT GROUP_CONCAT(sales_org_name)
        FROM (
            SELECT so.sales_org_name 
            FROM SalesOrganizations so 
            WHERE so.company_code = cc.company_code
        ) AS SalesOrgList
    ) AS sales_organizations,
    (
        SELECT GROUP_CONCAT(wh.warehouse_name)
        FROM Warehouses wh
        JOIN Plants p ON wh.plant_id = p.plant_id
        WHERE p.company_code = cc.company_code
    ) AS warehouses,
    (
        SELECT GROUP_CONCAT(sd.sales_division_name)
        FROM SalesDivisions sd
        JOIN SalesOrganizations so ON sd.sales_org_id = so.sales_org_id
        WHERE so.company_code = cc.company_code
    ) AS sales_divisions
FROM 
    CompanyCodes cc;
"""

# Execute the query to create the view
cursor.execute(create_view_query)
connection.commit()

# Close the connection
connection.close()

print("View 'CompanyHierarchy' created successfully.")

Step 4: Query the Hierarchical View

Now that the view is created, you can easily query the hierarchical data structure:

# Reconnect to the database
connection = sqlite3.connect(db_path)
cursor = connection.cursor()

# Query the hierarchical view
cursor.execute("SELECT * FROM CompanyHierarchy")
rows = cursor.fetchall()

for row in rows:
    print(row)

# Close the connection
connection.close()

Benefits of Using Hierarchical Views in Development

Simplified Queries: By encapsulating the complex joins and relationships within a view, your application code becomes cleaner and easier to maintain.
Performance Optimization: Views can sometimes help in performance optimization by allowing the database engine to optimize the query execution plan.
Enhanced Readability: It improves the readability of data relationships, making it easier for developers and analysts to understand and work with the data.
Reusability: Once the view is created, it can be reused across different parts of the application, ensuring consistency in data representation.

Conclusion

Creating hierarchical queries in SQLite using Python is a powerful technique for managing and visualizing complex data relationships. By saving these queries as views, you not only simplify the development process but also enhance the performance and maintainability of your applications. Whether you are developing new features, customizing existing systems, or enhancing current workflows, understanding and leveraging hierarchical data structures will provide significant advantages.

Storing Images in Databases: A Practical Approach for ERP Systems

In enterprise resource planning (ERP) systems, data management is crucial for operational efficiency and accuracy. While traditional ERP systems primarily handle structured data like numbers, text, and dates, there's a growing need to store unstructured data such as images, scanned documents, and other media files. This is especially important for maintaining records of scanned copies of invoices, purchase orders, delivery receipts, and other critical documents. In this blog post, we will explore a Python program that demonstrates how to store and retrieve images (like JPG files) in an SQLite3 database, and discuss the advantages of this approach in ERP systems.

Why Store Images in Databases?

Centralized Data Management: By storing images in a database, all related data is centralized, making it easier to manage, backup, and restore. This is particularly useful in ERP systems where scanned documents like invoices and receipts need to be securely stored alongside other transactional data.
Enhanced Security: Databases offer robust security features like access control, encryption, and audit trails, which help protect sensitive data, including scanned documents. Storing images within the database ensures they are subject to the same security measures as other data.
Improved Data Integrity and Consistency: When images are stored in the same database as the transactional records they relate to, there is a higher level of data integrity. For example, linking a scanned purchase order directly to its corresponding entry in the system ensures consistency and traceability.
Streamlined Access and Retrieval: Storing images directly in the database allows for easy retrieval using standard SQL queries. This simplifies the process of accessing documents related to transactions, such as pulling up an invoice when reviewing a financial transaction.
Scalability: As ERP systems scale, so does the need for storage. Modern databases can handle large amounts of BLOB (Binary Large Object) data efficiently, making it feasible to store even large scanned documents without significant performance degradation.

The Python Program: Storing and Displaying Images in SQLite3

Below is a sample Python program that demonstrates how to store and display images using SQLite3. This approach can be extended to manage scanned copies of invoices, purchase orders, and delivery receipts within an ERP system.

import sqlite3
from PIL import Image
import io

# Step 1: Create the database connection and cursor
conn = sqlite3.connect('images.db')
cursor = conn.cursor()

# Step 2: Create the table with a BLOB field for storing images
cursor.execute('''
CREATE TABLE IF NOT EXISTS photos (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    image BLOB
)
''')
conn.commit()

# Function to convert image file to binary data
def convert_to_binary_data(filename):
    with open(filename, 'rb') as file:
        return file.read()

# Step 3: Insert an image into the database
def insert_image(name, filename):
    image_data = convert_to_binary_data(filename)
    cursor.execute('''
    INSERT INTO photos (name, image) VALUES (?, ?)
    ''', (name, image_data))
    conn.commit()
    print(f"Image {name} inserted successfully")

# Step 4: Retrieve and display the image from the database
def display_image(image_id):
    cursor.execute('SELECT name, image FROM photos WHERE id = ?', (image_id,))
    record = cursor.fetchone()
    
    if record:
        name, image_data = record
        print(f"Displaying Image: {name}")
        
        # Convert binary data to image and display
        image = Image.open(io.BytesIO(image_data))
        image.show()
    else:
        print("No image found with the given ID.")

# Example usage
# Insert an image into the database (ensure you have a 'sample.jpg' in the same directory)
insert_image('Sample Image', 'sample.jpg')

# Retrieve and display the inserted image
display_image(1)

# Close the database connection
conn.close()

How the Program Works

Database Setup: The program first establishes a connection to an SQLite3 database and creates a table called photos with fields for an ID, name, and image data (stored as BLOB).
Inserting Images: The insert_image function reads an image file in binary mode and inserts it into the database. This is particularly useful for storing scanned documents directly within the ERP database.
Displaying Images: The display_image function retrieves image data from the database using a given ID and displays it using the Python Imaging Library (PIL). This feature can be used to view scanned documents linked to transactions in real-time.

Advantages of Storing Images in a Database for ERP Systems

Direct Association with Transactions: Images can be directly linked to their corresponding records (e.g., invoices, purchase orders), simplifying access and enhancing the accuracy of record-keeping.
Reduced Risk of File Loss: Unlike files stored on separate servers or in file systems, database-managed images benefit from database backup and recovery protocols, reducing the risk of data loss.
Consistent Data Access: Users access all data, including images, through the same interface (the ERP system), ensuring consistent access controls and data handling procedures.
Simplified Auditing and Compliance: Storing images in the database aids in meeting compliance requirements, such as keeping copies of financial documents, by maintaining all records in one location with appropriate audit trails.
Enhanced Reporting and Analytics: With images stored in the database, it's easier to include them in reports or analytics processes, providing a complete view of transactions, including the supporting documents.

Considerations

Performance: While storing images in a database offers many benefits, it's essential to monitor performance, especially as the volume of images grows. Proper indexing and database tuning are required to maintain performance.
Database Size Management: Storing large BLOBs can increase the size of the database significantly. Strategies like archiving older records or using database storage optimization techniques can help manage this.

Conclusion

Storing images such as scanned copies of invoices, purchase orders, and delivery receipts in a database provides significant advantages in an ERP environment, including improved data integrity, security, and ease of access. The Python program presented above is a practical example of how this can be implemented using SQLite3, demonstrating the feasibility and benefits of integrating image storage into your ERP system's database.