Blog: Building a Visitor Management System with PyQt6 and SQLite

In this blog, we'll explore how to create a simple yet effective Visitor Management System (VMS) using Python's powerful PyQt6 library and SQLite for database management. This system allows users to register, search, edit, and delete visitor records in a building or organization. Let's break down the key features and explain how everything fits together.

---

What is the Visitor Management System?

A Visitor Management System (VMS) is designed to efficiently manage the flow of visitors in any facility. It captures details like the visitor's name, the company they represent, and contact information. This system ensures that these records are easily accessible and can be updated when necessary. In this project, we will be building a simple VMS using PyQt6 for the GUI and SQLite as the backend database.

---

Setting Up the Environment

Before we jump into the code, make sure you have Python installed on your system. You'll also need the following libraries:

1. PyQt6 – This will be used for the graphical user interface.
2. SQLite – For storing visitor data.

You can install the necessary libraries using pip:

pip install pyqt6 sqlite3

---

Database Design

At the heart of our VMS is the SQLite database. The system interacts with a database to store, retrieve, and modify visitor records. The visitors table will consist of the following fields:

• id – An auto-incremented primary key.
• name – Name of the visitor.
• company – The company the visitor is associated with.
• contact – Contact information for the visitor.

Here's the SQL script to create the visitors table:

CREATE TABLE IF NOT EXISTS visitors (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    company TEXT NOT NULL,
    contact TEXT NOT NULL
);

This ensures that if the table doesn't already exist, it will be created. The database connection is managed via the DatabaseConnection class in the code.

---

The PyQt6 User Interface

The user interface (UI) for this VMS is built using PyQt6. We create the main window and add functionality for adding, searching, editing, and deleting visitor records.

Menu Bar

The menu bar provides easy access to common operations. It includes:

• File Menu: This contains the action to add a new visitor.
• Help Menu: Displays an "About" dialog.
• Edit Menu: Contains actions like searching for visitors.

Table View

A table view (QTableWidget) is used to display the visitor records. The columns are set to show:

• Id: The visitor's ID.
• Visitor Name: Name of the visitor.
• Company: The company the visitor is associated with.
• Contact: Contact details.

---

Key Features and Dialogs

The system is designed with several dialogs to handle various operations like adding, editing, deleting, and searching for visitors.

1. Add Visitor Dialog (InsertDialog)

   • This dialog allows the user to input visitor details (name, company, and contact) and add them to the database.

2. Edit Visitor Dialog (EditDialog)

   • The user can update the details of an existing visitor by selecting a record from the table and modifying the information.

3. Delete Visitor Dialog (DeleteDialog)

   • This dialog prompts the user to confirm before deleting a visitor's record from the database.

4. Search Visitor Dialog (SearchDialog)

   • Users can search for a visitor by name. If found, the matching record is highlighted in the table.

---

Connecting Everything Together

Here's how the components interact:

1. Database Connection: The DatabaseConnection class manages the connection to the SQLite database. It provides methods to connect to the database and execute queries like creating the table, inserting, updating, and deleting records.

2. MainWindow Class: This is the heart of the UI. It contains a table (QTableWidget) to display records, a toolbar with actions like "Add Visitor" and "Search", and a status bar that shows options to edit or delete records when a table cell is clicked.

3. Dialogs: Each dialog handles a specific user action (insert, update, delete, search). When the user interacts with the main window, these dialogs allow for smooth data entry and modification.

---

Code Breakdown

1. Creating the Table: The first step is ensuring that the visitors table is created when the application starts. This is handled by the DatabaseConnection class, which includes the create_table method.

class DatabaseConnection:
    def __init__(self, database_file="visitor_db.db"):
        self.database_file = database_file

    def connect(self):
        connection = sqlite3.connect(self.database_file)
        return connection

    def create_table(self):
        connection = self.connect()
        cursor = connection.cursor()
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS visitors (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            company TEXT NOT NULL,
            contact TEXT NOT NULL
        );
        """)
        connection.commit()
        cursor.close()
        connection.close()

1. Adding a Visitor: The InsertDialog class allows users to input visitor information and store it in the SQLite database.

class InsertDialog(QDialog):
    def __init__(self):
        super().__init__()
        self.setWindowTitle("Insert Visitor Data")
        self.setFixedWidth(300)
        self.setFixedHeight(300)

        layout = QVBoxLayout()

        # Add visitor name widget
        self.visitor_name = QLineEdit()
        self.visitor_name.setPlaceholderText("Visitor Name")
        layout.addWidget(self.visitor_name)

        # Add company name widget
        self.company_name = QLineEdit()
        self.company_name.setPlaceholderText("Company Name")
        layout.addWidget(self.company_name)

        # Add contact number widget
        self.contact_number = QLineEdit()
        self.contact_number.setPlaceholderText("Contact Number")
        layout.addWidget(self.contact_number)

        # Add a submit button
        button = QPushButton("Register Visitor")
        button.clicked.connect(self.add_visitor)
        layout.addWidget(button)

        self.setLayout(layout)

    def add_visitor(self):
        name = self.visitor_name.text()
        company = self.company_name.text()
        contact = self.contact_number.text()
        connection = DatabaseConnection().connect()
        cursor = connection.cursor()
        cursor.execute("INSERT INTO visitors (name, company, contact) VALUES (?, ?, ?)",
                       (name, company, contact))
        connection.commit()
        cursor.close()
        connection.close()
        main_window.load_data()

---

Conclusion

The Visitor Management System created with PyQt6 and SQLite offers a simple yet functional solution to managing visitor data. The system features an intuitive graphical interface, with a database backend to persist visitor records. Users can easily add, search, edit, and delete records, making this system suitable for many real-world applications.

Check out the complete source code and contribute to the project on GitHub.

Feel free to customize and extend this application based on your needs.