Python for Profit

Building a Complete Inventory System

13

The Pythonic Way: Refactoring with an ORM

The Big Idea

This chapter introduces a powerful, professional tool called an Object-Relational Mapper (ORM) to refactor our raw SQL code, allowing us to interact with our database using intuitive Python objects instead of manually written strings.

Roadmap

  • Why Our Code Can Be Better: We'll discuss the downsides of writing raw SQL (it's repetitive, error-prone, and not very "Pythonic").

  • The "Automatic Transmission": Object-Relational Mappers: Introducing ORMs as a translator that maps database tables to Python classes.

  • Our Tool of Choice: Peewee: We'll choose a simple, beginner-friendly ORM for our project.

  • A New Skill: Installing Third-Party Libraries with pip: You'll learn the essential skill of using Python's package manager to install external code.

  • Refactoring main.py Step-by-Step with Peewee:

1.  Install the `peewee` library.

2.  Set up our `Product` model, a class that defines our `Products` table.

3.  Rewrite our CRUD functions (`add`, `view`, `update`, `delete`) using Peewee's elegant, object-oriented syntax.

4.  Observe how our main menu requires almost no changes, demonstrating the power of this new abstraction.

Full Chapter Content

Why Our Current Code Can Be Better

In the last chapter, we successfully built a database-driven application. It works, but if you look closely, our Python functions are filled with code from another language: SQL.

sql_command = "UPDATE Products SET quantity = ? WHERE name = ?;"

This approach has a few problems:

  1. It's Repetitive: We have to write conn = sqlite3.connect(...), cursor = conn.cursor(), and conn.close() in every single function.

  2. It's Error-Prone: A small typo in a long SQL string can be difficult to spot.

  3. It's Not Pythonic: We're not working with Python objects; we're working with tuples of data (product[0], product[1], etc.), which isn't very descriptive.

Professionals use a tool that automates this translation process. This tool is an Object-Relational Mapper (ORM).

The ORM: Your Personal SQL Translator

An ORM is a library that automatically maps database tables to Python classes. It lets you think in Python, not SQL.

Operation

Raw SQL (Manual)

With an ORM (Automatic)

Get all products

SELECT * FROM Products;

Product.select()

Create a product

INSERT INTO ...

Product.create(name=...)

Get a product's name

product_tuple[1]

product_object.name

We are going to use a simple yet powerful ORM called Peewee.

Step 1: Install Peewee using pip

Peewee is a "third-party" library, meaning it's not built into Python. We must install it using pip, the Package Installer for Python. This is a fundamental skill for any developer.

Open your computer's command line (the same one you use to run your script) and execute this command:

pip install peewee

This downloads and installs the Peewee library, making it available for us to import.

Step 2: Re-architecting main.py for Peewee

We are now going to significantly refactor our main.py. We'll remove the old sqlite3 logic and replace it with the cleaner Peewee structure.

First, replace the top of your main.py file with this new setup code. This will handle all our imports and database configuration.

# --- PyInventory: A Step-by-Step Journey to Profit ---
# Chapter 13: The Pythonic Way: Refactoring with an ORM

from peewee import * # Import all the necessary tools from peewee

DATABASE_FILE = "inventory.db"

# Create a database instance. This becomes our central database object.
db = SqliteDatabase(DATABASE_FILE)

# --- Define the Database Model ---
# This class defines the structure of our 'Product' table.
class Product(Model):
    # The fields (columns) of our table, defined using Peewee's Field types.
    # Peewee automatically creates a 'id' primary key for us.
    name = CharField(unique=True) # A text field for the product's name
    quantity = IntegerField()    # An integer field for the quantity
    price = FloatField()         # A float field for the price

    class Meta:
        database = db # Link this model to our database object.

Explanation of the New Structure:

  • from peewee import *: We import the necessary classes from the library we just installed.

  • db = SqliteDatabase(DATABASE_FILE): We create a single, central database object that our entire application will use.

  • class Product(Model):: This is the core of the ORM. We define our Products table as a Python class. Peewee is smart enough to use the class name (Product) to name the table in the database (product).

  • CharField, IntegerField, FloatField: These are Peewee's field types. They are much more descriptive than the SQL TEXT or INTEGER. We specify unique=True on the name field to enforce the same rule we had before.

  • class Meta:: This special inner class tells our Product model which database to use—our central db object.

Step 3: Refactor the CRUD Functions

Now, let's delete our old add_product, view_products, update_product_quantity, and delete_product functions. We will replace them with new, vastly cleaner versions that use our Product model.

Add these new functions to your main.py:

# --- CRUD Functions using Peewee ---

def add_product(name, quantity, price):
    """Adds a new product to the database using Peewee."""
    try:
        Product.create(name=name, quantity=quantity, price=price)
        print(f"SUCCESS: Added '{name}' to the database.")
    except IntegrityError:
        print(f"ERROR: A product with the name '{name}' already exists.")

def view_products():
    """Retrieves and displays all products from the database using Peewee."""
    products = Product.select() # This is like 'SELECT * FROM Product'

    print("\n--- Current Inventory ---")
    if not products:
        print("The inventory is empty.")
    else:
        for product in products:
            # We can now access data by attribute name, not index!
            print(f"ID: {product.id}, Name: {product.name}, Quantity: {product.quantity}, Price: ${product.price:.2f}")
    print("-------------------------")

def update_product_quantity(name, new_quantity):
    """Updates the quantity of a specific product using Peewee."""
    Product.update(quantity=new_quantity).where(Product.name == name).execute()
    print(f"SUCCESS: Quantity for '{name}' updated.")

def delete_product(name):
    """Deletes a specific product from the database using Peewee."""
    Product.delete().where(Product.name == name).execute()
    print(f"SUCCESS: Deleted '{name}' from the database.")

Explanation of the Changes:

  • No more conn or cursor! Peewee manages the connections for us.

  • Product.create(...): This single line replaces all the INSERT SQL, parameter substitution, and commit logic. It creates a new Product object and saves it to the database.

  • Product.select(): This reads from the database and, crucially, returns a collection of Product objects, not tuples.

  • product.name, product.quantity: This is the biggest improvement. We access data using descriptive attribute names, making the code far more readable and less error-prone than product[1], product[2].

  • .update(...).where(...) and .delete().where(...): Peewee provides a "chainable" syntax for UPDATE and DELETE that is very clear and reads like an English sentence.

Step 4: Update the Main Application Block

Finally, we need to update our main execution block. We no longer need the old initialize_database function. Peewee can handle it.

Replace your if __name__ == "__main__": block with this final version:

# --- Main Application Block ---

if __name__ == "__main__":
    db.connect()
    # This safely creates the table if it doesn't already exist.
    db.create_tables([Product])

    # The interactive menu loop remains exactly the same!
    while True:
        print("\n--- PyInventory Main Menu ---")
        print("1: View All Products")
        print("2: Add a New Product")
        print("3: Update a Product's Quantity")
        print("4: Delete a Product")
        print("q: Quit")

        choice = input("Please enter your choice: ")

        if choice == '1':
            view_products()

        elif choice == '2':
            name = input("Enter new product name: ")
            quantity = int(input("Enter quantity: "))
            price = float(input("Enter price: "))
            add_product(name, quantity, price)

        elif choice == '3':
            name = input("Enter the product name to update: ")
            quantity = int(input("Enter new quantity: "))
            update_product_quantity(name, quantity)

        elif choice == '4':
            name = input("Enter the product name to delete: ")
            delete_product(name)

        elif choice.lower() == 'q':
            print("Exiting PyInventory. Goodbye!")
            break

        else:
            print("Invalid choice. Please try again.")

    # Close the database connection when the loop is exited
    db.close()

Explanation:

  • db.connect() and db.close() now wrap our entire application loop.

  • db.create_tables([Product]) replaces our old initialize_database function. It's safe to run every time and will only create the table on the very first run.

  • The while loop itself is unchanged. Because we kept our function names the same (add_product, view_products, etc.), we were able to completely swap out the underlying data logic without changing the user interface logic at all. This is a powerful demonstration of good application design.

Before you run: Delete your old inventory.db file to let Peewee create a fresh one. Now run python main.py. Your application will work exactly as before, but its internal code is now far more professional, readable, and maintainable.

Chapter 13: Summary & What's Next

This was a major professional upgrade.

  • You learned what an ORM is and why it's the preferred way to work with databases.

  • You installed your first third-party library, Peewee, using pip.

  • You refactored your entire data layer to use elegant, object-oriented code, replacing raw SQL.

We now have a professional data access layer for our products. As you may think, a real inventory system needs more than just a product list. In the next chapter, we will expand our application's core logic by adding a new table for Supplier Management, linking it to our products, and integrating it into our command-line interface.