PythonPython tutorials

Python Inventory Management Application with GUI and MySQL

devtechtutor.com
devtechtutor.com
22/05/2024
6 Mins Read
68 Views
0 Comments

In this article, we will create a Python application for inventory management that includes a graphical user interface (GUI) and CRUD (Create, Read, Update, Delete) functionality with a MySQL database. This step-by-step guide will help you understand the structure and the code involved in building such an application using the tkinter library for the GUI and mysql-connector-python for database interactions.

Application Diagram

 

Prerequisites

  • Python 3.x
  • MySQL database
  • mysql-connector-python library (install via pip install mysql-connector-python)
  • tkinter library (usually included with Python installations)
InventoryManagement
│   main.py
│   database.py
│
├───gui
│       main_frame.py
│       product_panel.py
│
└───model
        product.py
        product_dao.py

Step-by-Step Implementation

1. database.py

This module handles the connection to the MySQL database.

import mysql.connector

class DatabaseConnection:
    @staticmethod
    def get_connection():
        """
        Establishes and returns a connection to the MySQL database.
        """
        connection = mysql.connector.connect(
            host="localhost",
            user="root",
            password="password",
            database="inventory"
        )
        return connection

Explanation:

  • The DatabaseConnection class establishes a connection to the MySQL database using mysql.connector.
  • The get_connection method returns a connection object.

2. product.py

This module defines the Product class representing a product entity.

class Product:
    def __init__(self, id, name, quantity, price):
        """
        Initializes a new product instance.

        Parameters:
        id (int): Product ID
        name (str): Product name
        quantity (int): Quantity of the product
        price (float): Price of the product
        """
        self.id = id
        self.name = name
        self.quantity = quantity
        self.price = price

    def __str__(self):
        """
        Returns a string representation of the product.
        """
        return f"{self.name} - {self.quantity} units - ${self.price:.2f}"

Explanation:

  • The Product class models a product with id, name, quantity, and price attributes.
  • The constructor initializes these attributes.
  • The __str__ method returns a string representation of the product.

3. product_dao.py

This module provides CRUD operations for the Product class.

from database import DatabaseConnection
from model.product import Product

class ProductDAO:
    def add_product(self, product):
        """
        Adds a new product to the database.

        Parameters:
        product (Product): The product to be added
        """
        connection = DatabaseConnection.get_connection()
        cursor = connection.cursor()
        sql = "INSERT INTO products (name, quantity, price) VALUES (%s, %s, %s)"
        values = (product.name, product.quantity, product.price)
        cursor.execute(sql, values)
        connection.commit()
        cursor.close()
        connection.close()

    def get_all_products(self):
        """
        Retrieves all products from the database.

        Returns:
        list: A list of Product objects
        """
        connection = DatabaseConnection.get_connection()
        cursor = connection.cursor()
        cursor.execute("SELECT * FROM products")
        products = []
        for (id, name, quantity, price) in cursor:
            products.append(Product(id, name, quantity, price))
        cursor.close()
        connection.close()
        return products

    def update_product(self, product):
        """
        Updates an existing product in the database.

        Parameters:
        product (Product): The product with updated information
        """
        connection = DatabaseConnection.get_connection()
        cursor = connection.cursor()
        sql = "UPDATE products SET name = %s, quantity = %s, price = %s WHERE id = %s"
        values = (product.name, product.quantity, product.price, product.id)
        cursor.execute(sql, values)
        connection.commit()
        cursor.close()
        connection.close()

    def delete_product(self, product_id):
        """
        Deletes a product from the database.

        Parameters:
        product_id (int): The ID of the product to be deleted
        """
        connection = DatabaseConnection.get_connection()
        cursor = connection.cursor()
        sql = "DELETE FROM products WHERE id = %s"
        values = (product_id,)
        cursor.execute(sql, values)
        connection.commit()
        cursor.close()
        connection.close()

Explanation:

  • The ProductDAO class provides methods to add, retrieve, update, and delete products from the database.
  • Each method establishes a connection to the database, prepares an SQL statement, executes it, and handles the results.
  • add_product inserts a new product into the database.
  • get_all_products retrieves all products from the database and returns a list of Product objects.
  • update_product updates the details of an existing product in the database.
  • delete_product removes a product from the database based on its ID.

4. main_frame.py

This module defines the main frame of the GUI application.

import tkinter as tk
from gui.product_panel import ProductPanel

class MainFrame(tk.Tk):
    def __init__(self):
        """
        Initializes the main application window.
        """
        super().__init__()
        self.title("Inventory Management")
        self.geometry("800x600")
        
        product_panel = ProductPanel(self)
        product_panel.pack(fill=tk.BOTH, expand=True)

if __name__ == "__main__":
    app = MainFrame()
    app.mainloop()

Explanation:

  • The MainFrame class sets up the main window of the application using tk.Tk.
  • The window title, size, and layout are defined.
  • A ProductPanel instance is added to the main frame.
  • The __main__ section starts the application loop, creating and displaying the main window.

5. product_panel.py

This module provides the GUI for managing products

import tkinter as tk
from tkinter import messagebox
from tkinter import ttk
from model.product import Product
from model.product_dao import ProductDAO

class ProductPanel(tk.Frame):
    def __init__(self, parent):
        """
        Initializes the product panel.

        Parameters:
        parent (tk.Tk): The parent window
        """
        super().__init__(parent)
        self.product_dao = ProductDAO()
        self.create_widgets()
        self.load_products()

    def create_widgets(self):
        """
        Creates and arranges the GUI components.
        """
        self.name_label = tk.Label(self, text="Name:")
        self.name_label.grid(row=0, column=0, padx=5, pady=5)
        self.name_entry = tk.Entry(self)
        self.name_entry.grid(row=0, column=1, padx=5, pady=5)

        self.quantity_label = tk.Label(self, text="Quantity:")
        self.quantity_label.grid(row=1, column=0, padx=5, pady=5)
        self.quantity_entry = tk.Entry(self)
        self.quantity_entry.grid(row=1, column=1, padx=5, pady=5)

        self.price_label = tk.Label(self, text="Price:")
        self.price_label.grid(row=2, column=0, padx=5, pady=5)
        self.price_entry = tk.Entry(self)
        self.price_entry.grid(row=2, column=1, padx=5, pady=5)

        self.add_button = tk.Button(self, text="Add", command=self.add_product)
        self.add_button.grid(row=3, column=0, padx=5, pady=5)
        self.update_button = tk.Button(self, text="Update", command=self.update_product)
        self.update_button.grid(row=3, column=1, padx=5, pady=5)
        self.delete_button = tk.Button(self, text="Delete", command=self.delete_product)
        self.delete_button.grid(row=3, column=2, padx=5, pady=5)

        self.product_list = ttk.Treeview(self, columns=("ID", "Name", "Quantity", "Price"), show="headings")
        self.product_list.heading("ID", text="ID")
        self.product_list.heading("Name", text="Name")
        self.product_list.heading("Quantity", text="Quantity")
        self.product_list.heading("Price", text="Price")
        self.product_list.grid(row=4, column=0, columnspan=3, padx=5, pady=5)

    def load_products(self):
        """
        Loads products from the database and displays them in the list.
        """
        for row in self.product_list.get_children():
            self.product_list.delete(row)
        products = self.product_dao.get_all_products()
        for product in products:
            self.product_list.insert("", "end", values=(product.id, product.name, product.quantity, product.price))

    def add_product(self):
        """
        Adds a new product to the database.
        """
        name = self.name_entry.get()
        quantity = int(self.quantity_entry.get())
        price = float(self.price_entry.get())
        product = Product(0, name, quantity, price)
        self.product_dao.add_product(product)
        self.load_products()

    def update_product(self):
        """
        Updates the selected product in the database.
        """
        selected_item = self.product_list.selection()
        if selected_item:
            item = self.product_list.item(selected_item)
            product_id = item["values"][0]
            name = self.name_entry.get()
            quantity = int(self.quantity_entry.get())
            price = float(self.price_entry.get())
            product = Product(product_id, name, quantity, price)
            self.product_dao.update_product(product)
            self.load_products()

    def delete_product(self):
        """
        Deletes the selected product from the database.
        """
        selected_item = self.product_list.selection()
        if selected_item:
            item = self.product_list.item(selected_item)
            product_id = item["values"][0]
            self.product_dao.delete_product(product_id)
            self.load_products()

Explanation:

  • The MainFrame class sets up the main window of the application using tk.Tk.
  • The window title, size, and layout are defined.
  • A ProductPanel instance is added to the main frame.
  • The __main__ section starts the application loop, creating and displaying the main window.

5. product_panel.py

This module provides the GUI for managing products.

import tkinter as tk
from tkinter import messagebox
from tkinter import ttk
from model.product import Product
from model.product_dao import ProductDAO

class ProductPanel(tk.Frame):
    def __init__(self, parent):
        """
        Initializes the product panel.

        Parameters:
        parent (tk.Tk): The parent window
        """
        super().__init__(parent)
        self.product_dao = ProductDAO()
        self.create_widgets()
        self.load_products()

    def create_widgets(self):
        """
        Creates and arranges the GUI components.
        """
        self.name_label = tk.Label(self, text="Name:")
        self.name_label.grid(row=0, column=0, padx=5, pady=5)
        self.name_entry = tk.Entry(self)
        self.name_entry.grid(row=0, column=1, padx=5, pady=5)

        self.quantity_label = tk.Label(self, text="Quantity:")
        self.quantity_label.grid(row=1, column=0, padx=5, pady=5)
        self.quantity_entry = tk.Entry(self)
        self.quantity_entry.grid(row=1, column=1, padx=5, pady=5)

        self.price_label = tk.Label(self, text="Price:")
        self.price_label.grid(row=2, column=0, padx=5, pady=5)
        self.price_entry = tk.Entry(self)
        self.price_entry.grid(row=2, column=1, padx=5, pady=5)

        self.add_button = tk.Button(self, text="Add", command=self.add_product)
        self.add_button.grid(row=3, column=0, padx=5, pady=5)
        self.update_button = tk.Button(self, text="Update", command=self.update_product)
        self.update_button.grid(row=3, column=1, padx=5, pady=5)
        self.delete_button = tk.Button(self, text="Delete", command=self.delete_product)
        self.delete_button.grid(row=3, column=2, padx=5, pady=5)

        self.product_list = ttk.Treeview(self, columns=("ID", "Name", "Quantity", "Price"), show="headings")
        self.product_list.heading("ID", text="ID")
        self.product_list.heading("Name", text="Name")
        self.product_list.heading("Quantity", text="Quantity")
        self.product_list.heading("Price", text="Price")
        self.product_list.grid(row=4, column=0, columnspan=3, padx=5, pady=5)

    def load_products(self):
        """
        Loads products from the database and displays them in the list.
        """
        for row in self.product_list.get_children():
            self.product_list.delete(row)
        products = self.product_dao.get_all_products()
        for product in products:
            self.product_list.insert("", "end", values=(product.id, product.name, product.quantity, product.price))

    def add_product(self):
        """
        Adds a new product to the database.
        """
        name = self.name_entry.get()
        quantity = int(self.quantity_entry.get())
        price = float(self.price_entry.get())
        product = Product(0, name, quantity, price)
        self.product_dao.add_product(product)
        self.load_products()

    def update_product(self):
        """
        Updates the selected product in the database.
        """
        selected_item = self.product_list.selection()
        if selected_item:
            item = self.product_list.item(selected_item)
            product_id = item["values"][0]
            name = self.name_entry.get()
            quantity = int(self.quantity_entry.get())
            price = float(self.price_entry.get())
            product = Product(product_id, name, quantity, price)
            self.product_dao.update_product(product)
            self.load_products()

    def delete_product(self):
        """
        Deletes the selected product from the database.
        """
        selected_item = self.product_list.selection()
        if selected_item:
            item = self.product_list.item(selected_item)
            product_id = item["values"][0]
            self.product_dao.delete_product(product_id)
            self.load_products()

Explanation:

  • The ProductPanel class contains the GUI components and actions for managing products.
  • The create_widgets method sets up the GUI layout with labels, entries, buttons, and a treeview for displaying products.
  • The load_products method loads products from the database and populates the treeview.
  • The add_product, update_product, and delete_product methods handle CRUD operations and update the GUI accordingly.

Please share this article if you like it!

Share It!
devtechtutor.com
Written By

devtechtutor.com

Welcome to DevTechTutor.com, your ultimate resource for mastering web development and technology! Whether you're a beginner eager to dive into coding or an experienced developer looking to sharpen your skills, DevTechTutor.com is here to guide you every step of the way. Our mission is to make learning web development accessible, engaging, and effective.

Other Articles

Share Article

Facebook
Twitter
LinkedIn
Reddit
Telegram
WhatsApp
Email
Threads
X
Print

Our site uses cookies. By using this site, you agree to the Privacy Policy and Terms of Use.

Accept