Mastering Order Processing Systems: A Comprehensive Guide to Building Efficient Business Solutions with Python

Part 5: Populating the Database with Initial Data

Introduction

Now that we’ve designed and initialized our database, the next step is to populate it with some initial data. Having a set of predefined users and products allows us to test the functionality of the system right from the start. This part of the blog will guide you through populating the database with initial user and product data, which is essential for testing and development. We'll use two key functions: add_initial_products() and add_initial_users().

Why Initial Data is Important

Before launching any application or system, it is crucial to have initial data for testing purposes. In the case of our order processing system, the initial data will:

• Help verify that the database schema is functioning as expected.

• Provide a dataset to simulate real-world scenarios such as placing orders, managing stock, and processing user actions.

• Allow us to test features such as user authentication and role-based access control.

For instance, adding initial users with different roles (admin, manager, and employee) will enable us to simulate how the system handles various permissions. Similarly, having products in the inventory will allow us to test order placement and inventory management features.

Populating Initial Data: Users and Products

Adding Initial Products

We start by populating the products table with a few sample products. These products will serve as the items that customers can order.

Here's how the add_initial_products() function works:

• It prepares a list of sample products, each with a unique product ID, name, price, and initial stock level.

• It then inserts these products into the products table in the SQLite database.

• If the product already exists (to avoid duplicates), the function ensures that the product isn’t added again.

Here’s the full implementation of the add_initial_products() function:

def add_initial_products():
    products = [
        ('P001', 'Widget A', 19.99, 100),
        ('P002', 'Widget B', 25.99, 200),
        ('P003', 'Widget C', 9.99, 150)
    ]
    try:
        with sqlite3.connect('order_system.db') as conn:
            c = conn.cursor()
            c.executemany('INSERT OR IGNORE INTO products (product_id, name, price, stock) VALUES (?, ?, ?, ?)', products)
            conn.commit()
        logging.info("Initial products added")
    except sqlite3.OperationalError as e:
        logging.error(f"Error adding initial products: {e}")

Explanation:

• List of Products: A predefined list of products is created, each with a unique product ID, a name, a price, and a stock quantity.

• Inserting into Database: The function uses executemany() to insert multiple products into the products table. The INSERT OR IGNORE clause ensures that duplicate products are not inserted into the table if they already exist.

• Error Handling: If there’s any issue during the insertion (such as database connection problems), the function logs an error message.

Adding Initial Users

Next, we add a few initial users to the users table, each with a different role (admin, manager, and employee). These roles will determine what actions each user can perform within the system.

Here’s how the add_initial_users() function works:

• It prepares a list of users with unique usernames, hashed passwords (for security), and roles.

• It inserts these users into the users table, using the same INSERT OR IGNORE approach to avoid duplicate entries.

Here’s the full implementation of the add_initial_users() function:

from werkzeug.security import generate_password_hash

def add_initial_users():
    users = [
        ('admin', generate_password_hash('password'), 'admin'),
        ('manager', generate_password_hash('password'), 'manager'),
        ('employee', generate_password_hash('password'), 'employee')
    ]
    try:
        with sqlite3.connect('order_system.db') as conn:
            c = conn.cursor()
            c.executemany('INSERT OR IGNORE INTO users (username, password, role) VALUES (?, ?, ?)', users)
            conn.commit()
        logging.info("Initial users added")
    except sqlite3.OperationalError as e:
        logging.error(f"Error adding initial users: {e}")

Explanation:

• List of Users: The function creates a list of users, where each user has a username, a hashed password, and a role.

• Password Hashing: To ensure security, user passwords are hashed using generate_password_hash(). Storing hashed passwords instead of plain text is a best practice for securing user credentials.

• Inserting into Database: Similar to the add_initial_products() function, this function inserts the users into the users table using executemany() and INSERT OR IGNORE to avoid duplicates.

• Error Handling: If any issues arise during the insertion, an error is logged.

Running the Script to Populate the Database

To populate the database with the initial data, follow these steps:

1. Create the Script (e.g., populate_db.py):

   • Add both the add_initial_products() and add_initial_users() functions in a Python script named populate_db.py.

Here’s the complete script:

    import sqlite3
    import logging
    from werkzeug.security import generate_password_hash

    logging.basicConfig(filename='order_system.log', level=logging.INFO, format='%(asctime)s %(levelname)s %(message)s')

    def add_initial_products():
        products = [
            ('P001', 'Widget A', 19.99, 100),
            ('P002', 'Widget B', 25.99, 200),
            ('P003', 'Widget C', 9.99, 150)
        ]
        try:
            with sqlite3.connect('order_system.db') as conn:
                c = conn.cursor()
                c.executemany('INSERT OR IGNORE INTO products (product_id, name, price, stock) VALUES (?, ?, ?, ?)', products)
                conn.commit()
            logging.info("Initial products added")
        except sqlite3.OperationalError as e:
            logging.error(f"Error adding initial products: {e}")

    def add_initial_users():
        users = [
            ('admin', generate_password_hash('password'), 'admin'),
            ('manager', generate_password_hash('password'), 'manager'),
            ('employee', generate_password_hash('password'), 'employee')
        ]
        try:
            with sqlite3.connect('order_system.db') as conn:
                c = conn.cursor()
                c.executemany('INSERT OR IGNORE INTO users (username, password, role) VALUES (?, ?, ?)', users)
                conn.commit()
            logging.info("Initial users added")
        except sqlite3.OperationalError as e:
            logging.error(f"Error adding initial users: {e}")

    if __name__ == "__main__":
        add_initial_products()
        add_initial_users()
        logging.info("Database populated with initial data.")

2. Run the Script:

   • Open your terminal or command prompt.

   • Navigate to the folder where your script is saved.

   • Run the following command:

    python populate_db.py

This will execute the script and insert the initial users and products into the database.

3. Verify the Data:

   • Open the SQLite database (using an SQLite viewer, such as DB Browser for SQLite) to check if the data has been inserted correctly.

   • You should see the users (admin, manager, employee) in the users table and the products (Widget A, Widget B, Widget C) in the products table.

Conclusion

Populating the database with initial data is a crucial step in the development of our order processing system. By inserting sample users and products, we can ensure that the system is functional and ready for testing. In this post, we covered the add_initial_products() and add_initial_users() functions, which help set up the users and products tables for testing purposes.

In the next part of the series, we’ll dive into creating the user interface using Tkinter, starting with the login screen and user authentication functionality.

Stay tuned for the next part, where we begin creating the User Interface - Introduction to Tkinter.

Link to My Source code : https://github.com/BryanSJamesDev/-Order-Processing-System-OPS-/tree/main