4
\$\begingroup\$

This is a simple GUI to display employee data stored in the database, with editing options that sync with the database and adjust display according to mouse clicks/ keyboard scrolling/add/delete ... and please do not mind pictures of dogs, I thought they might look cooler for the sake of demonstration. Awaiting your feedback/suggestions for improvements.

You can find the code repo here with necessary files http://bit.ly/3caE7sc

Main interface:

Main

Code:

#!/usr/local/bin/python3
from PyQt5.QtWidgets import (QWidget, QApplication, QDesktopWidget, QHBoxLayout, QFormLayout, QVBoxLayout,
                             QTableView, QPushButton, QLabel, QLineEdit, QTextEdit, QFileDialog, QMessageBox,
                             QAbstractItemView)
from PyQt5.QtGui import QPixmap, QFont, QImage
from PyQt5.QtCore import QAbstractTableModel, Qt
import pandas as pd
import numpy as np
import sqlite3
import sys
import cv2
import os


class _PandasModel(QAbstractTableModel):
    """
    Sub-class of QAbstractTableModel, provides a standard interface for displaying a pandas DataFrame.
    """

    def __init__(self, data):
        """
        Initialize a pandas DataFrame for display.
        Args:
            data: pandas DataFrame.
        """
        QAbstractTableModel.__init__(self)
        self._data = data

    def rowCount(self, parent):
        """
        Return pandas DataFrame number of rows.
        """
        return self._data.shape[0]

    def columnCount(self, parent=None):
        """
        Return pandas DataFrame number of columns.
        """
        return self._data.shape[1]

    def data(self, index, role=Qt.DisplayRole):
        """
        Return pandas DataFrame data at a given index.
        """
        if index.isValid():
            if role == Qt.DisplayRole:
                return str(self._data.iloc[index.row(), index.column()])
        return None

    def headerData(self, col, orientation, role):
        """
        Return pandas DataFrame column name
        """
        if orientation == Qt.Horizontal and role == Qt.DisplayRole:
            return self._data.columns[col]
        return None


class _EmployeeBase(QWidget):
    """
    Abstract base class for the employees app not for direct use,
    to be sub-classed by by other employee app classes.
    """
    def __init__(self, window_title, geometry=None, db='Employees.db', img_new_size=(200, 200)):
        """
        Initialize database connection, dimensions and logistics.
        Args:
            window_title: Title to be displayed for the current interface.
            geometry: int x, int y, int w, int h
            db: Path to .db file.
            img_new_size: new size to be fit inside all displayed windows.
        """
        super().__init__()
        self.image_size = img_new_size
        self.place_holder = None
        self.connection = sqlite3.connect(db)
        self.cursor = self.connection.cursor()
        if geometry:
            self.setGeometry(*geometry)
        self.setWindowTitle(window_title)
        win_rectangle = self.frameGeometry()
        center_point = QDesktopWidget().availableGeometry().center()
        win_rectangle.moveCenter(center_point)
        self.move(win_rectangle.topLeft())
        self.setStyleSheet('QPushButton:!hover {color: yellow}')
        self.icons = {'Person': 'Icons/person.png'}
        self.image = QPixmap(self.icons['Person'])
        self.binary_image = open(self.icons['Person'], 'rb').read()

    def main_window(self):
        """
        Display the main interface.

        Return:
            None
        """
        self.place_holder = EmployeeMain()
        self.close()

    @staticmethod
    def clear_cached_images():
        """
        Cleanup cached images.

        Return:
             None
        """
        current_dir = os.getcwd()
        for item in os.listdir(current_dir):
            if item.endswith('.png'):
                os.remove(f'{current_dir}/{item}')

    def closeEvent(self, event):
        """
        Setup for closing event.
        Args:
            event: QCloseEvent.

        Return:
            None

        """
        self.clear_cached_images()
        event.accept()


class EmployeeMain(_EmployeeBase):
    """
    Main window interface.
    """
    def __init__(self, window_title='Employee Manager', left_ratio=4, right_ratio=6,
                 geometry=(0, 0, 1050, 500), db='Employees.db', img_new_size=(200, 200)):
        """
        Initialize database connection, dimensions and logistics.
        Args:
            window_title: Title to be displayed for the current interface.
            left_ratio: int, left main interface relative size.
            right_ratio: int, right main interface relative size.
            geometry: int x, int y, int w, int h
            db: Path to .db file.
            img_new_size: new size to be fit inside all displayed windows.
        """
        _EmployeeBase.__init__(self, window_title, geometry, db, img_new_size)
        self.left_ratio = left_ratio
        self.right_ratio = right_ratio
        self.main_layout = QHBoxLayout()
        self.left_layout = QFormLayout()
        self.right_main_layout = QVBoxLayout()
        self.right_upper = QHBoxLayout()
        self.right_lower = QHBoxLayout()
        self.employee_data = QTableView()
        self.employee_frame = None
        self.right_widgets = {'New': (QPushButton('New'), self.create_employee),
                              'Update': (QPushButton('Update'), self.update_employee),
                              'Delete': (QPushButton('Delete'), self.delete_employee)}
        self.left_widgets = {'Image': QLabel()}
        self.adjust_layouts()
        self.get_employees()
        self.show()

    def adjust_layouts(self):
        """
        Setup main interface layouts.

        Return:
            None
        """
        self.right_main_layout.addLayout(self.right_upper)
        self.right_main_layout.addLayout(self.right_lower)
        self.main_layout.addLayout(self.left_layout, self.left_ratio)
        self.main_layout.addLayout(self.right_main_layout, self.right_ratio)
        self.left_layout.setContentsMargins(0, 0, 0, 0)
        self.setLayout(self.main_layout)
        self.adjust_widgets()

    def adjust_widgets(self):
        """
        Setup main interface widgets.

        Return:
            None
        """
        self.right_upper.addWidget(self.employee_data)
        self.employee_data.setStyleSheet('color: yellow')
        for widget, widget_method in self.right_widgets.values():
            self.right_lower.addWidget(widget)
            widget.clicked.connect(widget_method)
        for widget in self.left_widgets.values():
            self.left_layout.addWidget(widget)

    def get_employees(self):
        """
        Fetch employee data from database and display the data.

        Return:
            None
        """
        self.employee_frame = pd.read_sql_query('SELECT * FROM emp_data', self.connection)
        model = _PandasModel(self.employee_frame.drop('image', axis=1))
        self.employee_data.setModel(model)
        self.employee_data.setSelectionBehavior(QAbstractItemView.SelectRows)
        self.employee_data.selectRow(0)
        self.display_selected()
        self.employee_data.clicked.connect(self.display_selected)
        self.employee_data.selectionModel().currentChanged.connect(self.display_selected)

    @staticmethod
    def clear_layout(layout):
        """
        Clear a given layout.
        Args:
            layout: PyQt layout.

        Return:
            None
        """
        items = [layout.itemAt(i) for i in range(1, layout.count())]
        for item in items:
            widget = item.widget()
            widget.deleteLater()

    def display_selected(self):
        """
        Display selection from the right layout in the left display layout.

        Return:
            None
        """
        self.clear_layout(self.left_layout)
        indexes = self.employee_data.selectionModel().selectedRows()
        if indexes:
            selected = indexes[0].row()
            values = self.employee_frame.iloc[selected].values
            labels = ['ID: ', 'Name: ', 'Surname: ', 'Phone: ', 'e-mail: ', 'Address: ']
            image_name = f'{values[0]}.png'
            if image_name not in os.listdir():
                with open(image_name, 'wb') as temp:
                    temp.write(values[5])
            image = cv2.imread(image_name)
            resized = cv2.resize(image, self.image_size)
            height, width = self.image_size
            self.image = QImage(resized, height, width, QImage.Format_RGB888)
            self.left_widgets['Image'].setPixmap(QPixmap(self.image))
            values = np.delete(values, 5)
            for label, value in zip(labels, values):
                self.left_layout.addRow(QLabel(label), QLabel(str(value)))

    def create_employee(self):
        """
        Shift to employee creation interface.

        Return:
            None
        """
        self.place_holder = CreateEmployee()
        self.close()

    def update_employee(self):
        """
        Update employee data, sync to database and shift to main interface.

        Return:
            None
        """
        if not self.employee_data.selectedIndexes():
            message = QMessageBox()
            message.information(self, 'Information', 'You must select a row to delete')
            return
        indexes = self.employee_data.selectionModel().selectedRows()
        if indexes:
            selected = indexes[0].row()
            self.place_holder = UpdateEmployee(self.employee_frame, selected)
            self.close()

    def delete_employee(self):
        """
        Delete employee data sync to database and adjust display accordingly.

        Return:
            None
        """
        if not self.employee_data.selectedIndexes():
            message = QMessageBox()
            message.information(self, 'Information', 'You must select a row to delete')
            return
        indexes = self.employee_data.selectionModel().selectedRows()
        if indexes:
            selected_row = indexes[0].row()
            selected_id = self.employee_frame.iloc[selected_row]['id']
            temp_image = f'{selected_id}.png'
            if temp_image in os.listdir():
                os.remove(temp_image)
            name = self.employee_frame['name'].iloc[selected_row]
            message = QMessageBox()
            answer = message.question(self, 'Warning', f'Delete {name}, are you sure?')
            if answer == QMessageBox.Yes:
                delete_query = f'DELETE FROM emp_data WHERE id={selected_id}'
                self.cursor.execute(delete_query)
                self.connection.commit()
                self.get_employees()


class CreateEmployee(_EmployeeBase):
    """
    New employee data creation interface.
    """
    def __init__(self, window_title='Create Employee', geometry=(0, 0, 500, 500),
                 db='Employees.db', img_new_size=(200, 200), employee_frame=None, selected=None):
        """
        Initialize database connection, dimensions and logistics then display New/Edit interface.
        Args:
            window_title: Title to be displayed for the current interface.
            geometry: int x, int y, int w, int h
            db: Path to .db file.
            img_new_size: new size to be fit inside all displayed windows.
            employee_frame: pandas DataFrame with employee data to update.
            selected: int, row of the selected employee to edit.
        """
        _EmployeeBase.__init__(self, window_title, geometry, db, img_new_size)
        self.employee_frame = employee_frame
        self.selected_row = selected
        emp_id, name, surname, phone, email, image, address = 7 * [None]
        if isinstance(employee_frame, pd.DataFrame):
            emp_id, name, surname, phone, email, image, address = employee_frame.iloc[self.selected_row].values
        self.add_button = 'Update' if isinstance(self.employee_frame, pd.DataFrame) else 'Add'
        self.main_layout = QVBoxLayout()
        self.upper_layout = QVBoxLayout()
        self.lower_layout = QFormLayout()
        self.window_inner_title = 'Add Employee' if not isinstance(self.selected_row, int) else 'Edit Employee'
        self.upper_widgets = {'Title': (QLabel(self.window_inner_title), None),
                              'Image': (QLabel(), None)}
        self.lower_widgets = {'Back': (None, QPushButton('Back'), None, self.main_window),
                              'Name': (QLabel('Name :'), QLineEdit(name), 'Enter name', None),
                              'Surname': (QLabel('Surname :'), QLineEdit(surname), 'Enter surname', None),
                              'Phone': (QLabel('Phone: '), QLineEdit(phone), 'Enter phone number', None),
                              'e-mail': (QLabel('e-mail: '), QLineEdit(email), 'Enter e-mail', None),
                              'Image': (QLabel('Image: '), QPushButton('Browse'), None, self.upload_image),
                              'Address': (QLabel('Address: '), QTextEdit(address), 'Enter Address', None),
                              'Add': (None, QPushButton(self.add_button), None, self.add_employee),
                              }
        self.adjust_layouts()
        if isinstance(self.employee_frame, pd.DataFrame):
            self.set_image(self.selected_row)
        self.show()

    def adjust_layouts(self):
        """
        Setup layouts for employee editing interface.

        Return:
            None
        """
        self.main_layout.addLayout(self.upper_layout)
        self.main_layout.addLayout(self.lower_layout)
        self.setLayout(self.main_layout)
        self.adjust_widgets()
        self.upper_layout.setContentsMargins(115, 0, 0, 0)
        self.lower_layout.setContentsMargins(0, 0, 0, 0)

    def adjust_widgets(self):
        """
        Setup widgets for employee editing interface.

        Return:
            None
        """
        self.upper_widgets['Image'][0].setPixmap(self.image)
        self.upper_widgets['Title'][0].setFont(QFont('Arial', 20))
        for widget, widget_method in self.upper_widgets.values():
            self.upper_layout.addWidget(widget)
            if widget_method:
                widget.clicked.connect(widget_method)
        for label, widget, place_holder, widget_method in self.lower_widgets.values():
            self.lower_layout.addRow(label, widget)
            if place_holder:
                widget.setPlaceholderText(place_holder)
                widget.setStyleSheet('color: orange')
            if widget_method:
                widget.clicked.connect(widget_method)

    def set_image(self, selected):
        """
        Display selected to edit employee image.
        Args:
            selected: selected row.

        Return:
            None
        """
        values = self.employee_frame.iloc[selected].values
        image_name = f'{values[0]}.png'
        if image_name not in os.listdir():
            with open(image_name, 'wb') as temp:
                temp.write(values[5])
        self.binary_image = open(image_name, 'rb').read()
        image = cv2.imread(image_name)
        resized = cv2.resize(image, self.image_size)
        height, width = self.image_size
        self.image = QImage(resized, height, width, QImage.Format_RGB888)
        self.upper_widgets['Image'][0].setPixmap(QPixmap(self.image))

    def upload_image(self):
        """
        Browse and upload image, adjust display accordingly.

        Return:
            None
        """
        file_dialog = QFileDialog()
        file_name, _ = file_dialog.getOpenFileName(self, 'Upload Image')
        try:
            uploaded = cv2.imread(file_name)
            self.binary_image = open(file_name, 'rb').read()
            resized = cv2.resize(uploaded, self.image_size)
            height, width = self.image_size
            self.image = QImage(resized, height, width, QImage.Format_RGB888)
            self.upper_widgets['Image'][0].setPixmap(QPixmap(self.image))
        except (cv2.error, FileNotFoundError):
            print(f'Image could not be loaded')

    def add_employee(self):
        """
        Add new employee to the database, adjust and display main interface accordingly.

        Return:
            None
        """
        name = self.lower_widgets['Name'][1].text()
        surname = self.lower_widgets['Surname'][1].text()
        phone = self.lower_widgets['Phone'][1].text()
        email = self.lower_widgets['e-mail'][1].text()
        image = self.binary_image
        address = self.lower_widgets['Address'][1].toPlainText()
        items = [name, surname, phone, email, image, address]
        if not all(items):
            message = QMessageBox()
            message.information(self, 'Failure', f'Cannot add with empty fields')
            return
        query = (f'INSERT INTO emp_data(name, surname, phone, email, image, address) '
                 f'Values(?, ?, ?, ?, ?, ?)')
        self.cursor.execute(query, [name, surname, phone, email, image, address])
        self.connection.commit()
        message = QMessageBox()
        message.information(self, 'Success', f'{surname}, {name} has been added')
        self.main_window()


class UpdateEmployee(CreateEmployee):
    """
    Update existing employee data interface.
    """
    def __init__(self, employee_frame, selected, window_title='Update Employee', geometry=(0, 0, 500, 500),
                 db='Employees.db', img_new_size=(200, 200)):
        """
        Initialize database connection, dimensions and logistics then display Edit interface.
        Args:
            window_title: Title to be displayed for the current interface.
            geometry: int x, int y, int w, int h
            db: Path to .db file.
            img_new_size: new size to be fit inside all displayed windows.
            employee_frame: pandas DataFrame with employee data to update.
            selected: int, row of the selected employee to edit.
        """
        CreateEmployee.__init__(self, window_title, geometry, db, img_new_size, employee_frame, selected)
        self.employee_frame = employee_frame
        self.selected_row = selected
        self.show()

    def add_employee(self):
        """
        Update existing employee data, adjust and display main interface accordingly.
        """
        name = self.lower_widgets['Name'][1].text()
        surname = self.lower_widgets['Surname'][1].text()
        phone = self.lower_widgets['Phone'][1].text()
        email = self.lower_widgets['e-mail'][1].text()
        image = self.binary_image
        address = self.lower_widgets['Address'][1].toPlainText()
        items = [name, surname, phone, email, image, address]
        if not all(items):
            message = QMessageBox()
            message.information(self, 'Failure', f'Cannot add with empty fields')
            return
        selected_id = self.employee_frame.iloc[self.selected_row]['id']
        delete_query = f'DELETE FROM emp_data WHERE id={selected_id}'
        add_query = (f'INSERT INTO emp_data(id, name, surname, phone, email, image, address) '
                     f'Values(?, ?, ?, ?, ?, ?, ?)')
        self.cursor.execute(delete_query)
        self.cursor.execute(add_query, [int(selected_id), name, surname, phone, email, image, address])
        self.connection.commit()
        message = QMessageBox()
        message.information(self, 'Success', f'{surname}, {name} has been updated')
        self.main_window()


if __name__ == '__main__':
    test = QApplication(sys.argv)
    window = EmployeeMain()
    sys.exit(test.exec_())
\$\endgroup\$

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.