Performing CRUD Operations with PostgreSQL and PyQt6 in Python

 In today's technological landscape, efficient data management is crucial for any application. When it comes to developing desktop applications with Python, combining it with PostgreSQL for data storage and PyQt6 for the user interface can be a powerful combination. In this guide, we'll walk through the process of building a simple CRUD (Create, Read, Update, Delete) application using Python, PostgreSQL, and PyQt6.

Pros and Cons of Using PostgreSQL

Using PostgreSQL as a database management system (DBMS) offers several advantages and disadvantages, depending on the specific requirements and context of the application. Let's delve into the pros and cons:

Pros:

1. Robustness and Reliability:
   • PostgreSQL is known for its robustness and reliability. It has a proven track record of being highly stable, even under high loads and concurrent access.
2. Feature-Rich:
   • PostgreSQL offers a wide range of advanced features such as support for JSON and JSONB data types, full-text search capabilities, and geospatial data support. These features make it suitable for various types of applications.
3. ACID Compliance:
   • PostgreSQL adheres to ACID (Atomicity, Consistency, Isolation, Durability) properties, ensuring data integrity and reliability, which is crucial for transactional applications.
4. Extensibility:
   • PostgreSQL's architecture allows for the development and integration of custom extensions, enabling users to add new functionality and tailor the database to their specific needs.
5. Community and Support:
   • PostgreSQL has a large and active community of developers and users who contribute to its ongoing development, provide support, and share knowledge through forums, mailing lists, and conferences.
6. Cross-Platform Compatibility:
   • PostgreSQL is available on various operating systems, including Linux, macOS, and Windows, making it suitable for deployment in diverse environments.
7. Scalability:
   • PostgreSQL supports advanced replication and clustering features, allowing it to scale horizontally and vertically to meet the demands of growing applications.

Cons:

1. Complexity of Administration:
   • Managing and administering PostgreSQL databases may require a certain level of expertise, especially for tasks such as performance tuning, backup and recovery, and security configuration.
2. Resource Intensive:
   • PostgreSQL may consume significant system resources, especially in scenarios involving large datasets or complex queries. Proper resource management and optimization are essential for optimal performance.
3. Learning Curve:
   • Compared to some other DBMS solutions, such as MySQL, PostgreSQL may have a steeper learning curve, particularly for users who are new to relational databases or SQL.
4. Limited Compatibility with Some Tools:
   • While PostgreSQL supports SQL standards, it may have limited compatibility with certain tools and frameworks compared to more widely adopted databases like MySQL or Oracle.
5. Concurrency and Performance:
   • While PostgreSQL is highly performant, in certain scenarios, especially those with extremely high concurrency requirements, other database solutions might offer better performance out-of-the-box.
6. Initial Setup Complexity:
   • Setting up a PostgreSQL instance with specific configurations tailored to the application's needs may require more effort compared to some other DBMS solutions.
7. Commercial Support Costs:
   • While PostgreSQL itself is open source and free to use, organizations may opt for commercial support from third-party vendors, which could incur additional costs.

In summary, PostgreSQL is a powerful and reliable open-source DBMS with a rich feature set, making it suitable for a wide range of applications. However, its adoption may require careful consideration of factors such as administration complexity, performance requirements, and available expertise within the development team.

Setting Up the Environment

Before we dive into coding, ensure you have Python installed on your system. Additionally, you'll need to have PostgreSQL installed and running, along with the psycopg2 library for Python, which enables interaction with PostgreSQL databases.

pip install PyQt6 psycopg2

Creating the Database

Let's start by setting up our PostgreSQL database. We'll create a table named items with columns for id, name, and quantity. Below is the SQL script to create this table:

CREATE TABLE IF NOT EXISTS items ( id SERIAL PRIMARY KEY, name VARCHAR(255), quantity INTEGER );

Building the Application

Now, let's dive into the Python code. We'll use PyQt6 to create the GUI and psycopg2 to interact with the PostgreSQL database. The code is structured into two main classes: Database and MainWindow.

The Database Class

The Database class handles database operations such as creating the table, inserting, updating, and deleting items. It establishes a connection to the PostgreSQL database using psycopg2.

The MainWindow Class

The MainWindow class represents the main window of our application. It contains input fields for name and quantity, buttons for adding, updating, and deleting items, and a table to display the items from the database. This class also handles user interactions and updates the UI accordingly.

Running the Application

To run the application, execute the Python script. It will launch a window where you can perform CRUD operations on the items table in the PostgreSQL database.

python your_script_name.py

Conclusion

In this tutorial, we've covered the basics of building a CRUD application using Python, PostgreSQL, and PyQt6. You can extend this application by adding features such as validation, search functionality, or more complex database interactions. With this foundation, you can create powerful desktop applications tailored to your specific needs. Happy coding!

Here is the source code:

import sys
from PyQt6.QtWidgets import (
    QApplication, QMainWindow, QVBoxLayout, QWidget, QHBoxLayout,
    QPushButton, QLabel, QLineEdit, QMessageBox, QTableWidget, QTableWidgetItem
)
import psycopg2
class Database:
    def __init__(self):
        self.conn = psycopg2.connect(
            dbname='db_user', user='postgres', password='xblaster', host='localhost', port='5432'
        )
        self.cur = self.conn.cursor()

    def create_table(self):
        self.cur.execute('''
            CREATE TABLE IF NOT EXISTS items (
                id SERIAL PRIMARY KEY,
                name VARCHAR(255),
                quantity INTEGER
            )
        ''')
        self.conn.commit()

    def insert_item(self, name, quantity):
        self.cur.execute('''
            INSERT INTO items (name, quantity) VALUES (%s, %s)
        ''', (name, quantity))
        self.conn.commit()

    def get_items(self):
        self.cur.execute('SELECT * FROM items')
        return self.cur.fetchall()

    def update_item(self, id, name, quantity):
        self.cur.execute('''
            UPDATE items SET name = %s, quantity = %s WHERE id = %s
        ''', (name, quantity, id))
        self.conn.commit()

    def delete_item(self, id):
        self.cur.execute('DELETE FROM items WHERE id = %s', (id,))
        self.conn.commit()

class MainWindow(QMainWindow):
    def __init__(self):
        super().__init__()
        self.setWindowTitle("CRUD Example")
        self.setGeometry(100, 100, 600, 400)

        self.database = Database()
        self.database.create_table()

        self.central_widget = QWidget()
        self.setCentralWidget(self.central_widget)
        layout = QVBoxLayout()
        self.central_widget.setLayout(layout)

        # Widgets
        self.name_input = QLineEdit()
        self.quantity_input = QLineEdit()
        self.add_button = QPushButton("Add")
        self.table = QTableWidget()
        self.table.setColumnCount(3)
        self.table.setHorizontalHeaderLabels(["ID", "Name", "Quantity"])
        self.update_button = QPushButton("Update")
        self.delete_button = QPushButton("Delete")

        # Connect buttons
        self.add_button.clicked.connect(self.add_item)
        self.update_button.clicked.connect(self.update_item)
        self.delete_button.clicked.connect(self.delete_item)

        # Add widgets to layout
        layout.addWidget(QLabel("Name:"))
        layout.addWidget(self.name_input)
        layout.addWidget(QLabel("Quantity:"))
        layout.addWidget(self.quantity_input)
        layout.addWidget(self.add_button)
        layout.addWidget(self.table)
        layout.addWidget(self.update_button)
        layout.addWidget(self.delete_button)

        # Load data
        self.load_data()

        # Connect itemClicked signal
        self.table.itemClicked.connect(self.on_table_item_clicked)

    def on_table_item_clicked(self, item):
        row = item.row()
        name = self.table.item(row, 1).text()
        quantity = self.table.item(row, 2).text()
        self.name_input.setText(name)
        self.quantity_input.setText(quantity)
        
    def load_data(self):
        self.table.setRowCount(0)
        items = self.database.get_items()
        for row_number, row_data in enumerate(items):
            self.table.insertRow(row_number)
            for column_number, data in enumerate(row_data):
                self.table.setItem(row_number, column_number, QTableWidgetItem(str(data)))

    def add_item(self):
        name = self.name_input.text()
        quantity = self.quantity_input.text()
        if name and quantity:
            self.database.insert_item(name, quantity)
            self.load_data()
            self.name_input.clear()
            self.quantity_input.clear()

    def update_item(self):
        row = self.table.currentRow()
        print(self.table.item(row, 0).text())
        if row != -1:
            id = int(self.table.item(row, 0).text())
            name = self.table.item(row, 1).text()
            quantity = self.table.item(row, 2).text()
            self.database.update_item(id, name, quantity)
            self.load_data()
            self.name_input.clear()
            self.quantity_input.clear()

    def delete_item(self):
        row = self.table.currentRow()
        if row != -1:
            id = int(self.table.item(row, 0).text())
            self.database.delete_item(id)
            self.load_data()

if __name__ == "__main__":
    app = QApplication(sys.argv)
    window = MainWindow()
    window.show()
    sys.exit(app.exec())