#PyQt5 #SQLite #DesktopApp #WarehouseManagement #ERP #Python

Lesson: Advanced Warehouse ERP with PyQt5, SQLite, and Reporting

This tutorial covers building a complete desktop Enterprise Resource Planning (ERP) application for warehouse management. It features persistent storage using SQLite, inventory control, sales and purchase invoice management, a production module, and the ability to export reports to CSV.

---

#Step 1: Database Setup (database.py)

First, we create a dedicated file to handle all database interactions. This separation of concerns is crucial for a scalable application. Create a file named database.py.

import sqlite3
import csv

DB_NAME = 'warehouse.db'

def connect():
    return sqlite3.connect(DB_NAME)

def setup_database():
    conn = connect()
    cursor = conn.cursor()
    # Inventory Table: Stores raw materials and finished goods
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS inventory (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL UNIQUE,
            quantity INTEGER NOT NULL,
            price REAL NOT NULL
        )
    ''')
    # Invoices Table: Tracks both sales and purchases
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS invoices (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            type TEXT NOT NULL, -- 'SALE' or 'PURCHASE'
            party_name TEXT, -- Customer or Supplier Name
            date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    # Invoice Items Table: Links items from inventory to an invoice
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS invoice_items (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            invoice_id INTEGER,
            item_id INTEGER,
            quantity INTEGER NOT NULL,
            price_per_unit REAL NOT NULL,
            FOREIGN KEY (invoice_id) REFERENCES invoices (id),
            FOREIGN KEY (item_id) REFERENCES inventory (id)
        )
    ''')
    conn.commit()
    conn.close()

def get_inventory():
    conn = connect()
    cursor = conn.cursor()
    cursor.execute("SELECT id, name, quantity, price FROM inventory ORDER BY name")
    items = cursor.fetchall()
    conn.close()
    return items

def add_inventory_item(name, quantity, price):
    conn = connect()
    cursor = conn.cursor()
    try:
        cursor.execute("INSERT INTO inventory (name, quantity, price) VALUES (?, ?, ?)", (name, quantity, price))
        conn.commit()
    except sqlite3.IntegrityError:
        # Item with this name already exists
        return False
    finally:
        conn.close()
    return True

def update_item_quantity(item_id, change_in_quantity):
    conn = connect()
    cursor = conn.cursor()
    cursor.execute("UPDATE inventory SET quantity = quantity + ? WHERE id = ?", (change_in_quantity, item_id))
    conn.commit()
    conn.close()
    
def find_item_by_name(name):
    conn = connect()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM inventory WHERE name = ?", (name,))
    item = cursor.fetchone()
    conn.close()
    return item

# Add more functions here for invoices, etc. as we build the app.

# Hashtags: #SQLite #DatabaseDesign #DataPersistence #Python

---

#Step 2: Main Application Shell and Inventory Tab

Now, create the main application file, main.py. We'll build the window, tabs, and fully implement the Inventory tab, which will read from and write to our SQLite database.

By combining all the code from the steps above into database.py and main.py, you have a robust, database-driven desktop application.

Results:
Data Persistence: Your inventory and invoice data is saved in warehouse.db and will be there when you restart the application.
Integrated Workflow: Adding a purchase directly increases stock. A sale checks for and decreases stock. Production consumes raw materials and creates finished goods, all reflected in the central inventory table.
Separation of Concerns: The UI logic in main.py is cleanly separated from the data logic in database.py, making the code easier to maintain and extend.
Reporting: You can easily export a snapshot of your current inventory to a CSV file for analysis in other programs like Excel or Google Sheets.

Discussion and Next Steps:
Scalability: While SQLite is excellent for small-to-medium applications, a large-scale, multi-user system would benefit from a client-server database like PostgreSQL or MySQL.
Invoice Complexity: The current invoice system is simplified. A real system would allow multiple items per invoice and store historical invoice data for viewing and printing.
User Interface (UI/UX): The UI is functional but could be greatly improved with better layouts, icons, search/filter functionality in tables, and more intuitive workflows.
Error Handling: The error handling is basic. A production-grade app would have more comprehensive checks for user input and database operations.
• Advanced Features: Future additions could include user authentication, supplier and customer management, barcode scanning, and more detailed financial reporting.

This project forms a powerful template for building custom internal business tools with Python.

#ProjectComplete #SoftwareEngineering #ERP #PythonGUI #BusinessApp

━━━━━━━━━━━━━━━
By: @DataScience4 ✨