Python for Profit

Building a Complete Inventory System

12

Interacting with the Database via SQL

The Big Idea

This chapter replaces our old, file-based logic from Part 1 with a new set of powerful functions that communicate directly with our SQLite database using raw SQL commands, and then hooks these functions up to our interactive menu.

Roadmap

  • From Files to Functions: We'll discuss how we're now ready to write the core logic that will power our desktop application.

  • The Four SQL Commands of CRUD: We'll map our application's needs to specific SQL commands: INSERT, SELECT, UPDATE, and DELETE.

  • A Critical Security Topic: SQL Injection: You will learn what SQL Injection is and how to prevent it using parameter substitution (?), a non-negotiable professional skill.

  • Building Our Database Functions Step-by-Step:

1.  Create the `add_product` function using the `INSERT` command.

2.  Create the `view_products` function using the `SELECT` command to fetch and display data.

3.  Create the `update_product_quantity` function using the `UPDATE` command.

4.  Create the `delete_product` function using the `DELETE` command.

5.  Re-implement the interactive menu to create a usable application.

Full Chapter Content

From Files to Functions

Our inventory.db file is like a newly built, empty warehouse. We have a structure (the Products table), but we have no way to put things in, look at what's inside, change items, or throw them away. In this chapter, we will build the machinery—the Python functions—to do just that. We will be rewriting our core CRUD functions from Part 1, but this time, they will speak the language of databases: SQL.

We will continue working in the main.py file we created in Chapter 11.

A Critical Security Warning: SQL Injection

Before we write any code, we must cover the most important security rule of database programming. It is tempting to use Python's f-strings to insert data into an SQL query, like this: f"INSERT INTO Products (name) VALUES ('{product_name}');"

NEVER DO THIS. This is called SQL Injection, a classic and dangerous security vulnerability. If a malicious user entered a product_name like Laptop'); DROP TABLE Products;--, your f-string would create the following command: INSERT INTO Products (name) VALUES ('Laptop'); DROP TABLE Products;--');

The database would execute this, creating the laptop and then immediately deleting your entire Products table.

The professional and secure way to insert data is to use parameter substitution. We use a question mark ? as a placeholder in our SQL string, and then we pass the data as a separate tuple to the execute method. The sqlite3 library will safely sanitize the input, preventing any malicious commands from running.

Safe Method: cursor.execute("INSERT INTO Products (name) VALUES (?);", (product_name,))

We will use this safe method for every command.

Step 1: Create the add_product Function (The "C" in CRUD)

Let's start by creating a function to INSERT a new product into our table. This function will open a connection, execute the command with safe parameters, commit the change, and close the connection.

Add this new function to your main.py file, below the initialize_database function:

def add_product(name, quantity, price):
    """Adds a new product to the database."""
    conn = sqlite3.connect(DATABASE_FILE)
    cursor = conn.cursor()

    # Using the safe '?' placeholder for parameter substitution
    sql_command = "INSERT INTO Products (name, quantity, price) VALUES (?, ?, ?);"

    try:
        # The data is passed as a separate tuple
        cursor.execute(sql_command, (name, quantity, price))
        conn.commit()
        print(f"SUCCESS: Added '{name}' to the database.")
    except sqlite3.IntegrityError:
        # This error occurs if the name is not unique.
        print(f"ERROR: A product with the name '{name}' already exists.")

    conn.close()

Explanation:

  • INSERT INTO Products (name, quantity, price) VALUES (?, ?, ?);: This is the SQL command for creating a new record. The ? are our safe placeholders.

  • cursor.execute(sql_command, (name, quantity, price)): Notice we pass the SQL command first, and then a tuple of our data (name, quantity, price). The sqlite3 driver will safely substitute the ? placeholders with this data.

  • try...except sqlite3.IntegrityError: We've wrapped the execution in a try...except block. Because we defined the name column as UNIQUE in Chapter 11, the database will raise an IntegrityError if we try to add a product with a name that already exists. We catch this error and show a user-friendly message.

Step 2: Create the view_products Function (The "R" in CRUD)

Now let's create a function to SELECT all the products from the database and display them.

Add this function to main.py:

def view_products():
    """Retrieves and displays all products from the database."""
    conn = sqlite3.connect(DATABASE_FILE)
    cursor = conn.cursor()

    sql_command = "SELECT * FROM Products ORDER BY name;"
    cursor.execute(sql_command)

    # fetchall() retrieves all resulting rows from the query
    all_products = cursor.fetchall()

    conn.close()

    print("\n--- Current Inventory ---")
    if not all_products:
        print("The inventory is empty.")
    else:
        for product in all_products:
            # Each 'product' is a tuple: (product_id, name, quantity, price)
            print(f"ID: {product[0]}, Name: {product[1]}, Quantity: {product[2]}, Price: ${product[3]:.2f}")
    print("-------------------------")

Explanation:

  • SELECT * FROM Products ORDER BY name;: This SQL command fetches all columns (*) from the Products table, sorting the results alphabetically by name.

  • cursor.fetchall(): After executing a SELECT query, the results are held by the cursor. fetchall() retrieves all of them as a list of tuples. Each tuple represents one row from the database. For example: [(1, 'Laptop', 10, 1200.00), (2, 'Mouse', 50, 25.00)].

  • for product in all_products:: We loop through this list and print the details, accessing the data in each tuple using its index (e.g., product[0] for the ID).

Step 3: Create Functions for UPDATE and DELETE

Finally, let's add the functions for updating a product's quantity and deleting a product by its name.

Add these two functions to main.py:

def update_product_quantity(name, new_quantity):
    """Updates the quantity of a specific product."""
    conn = sqlite3.connect(DATABASE_FILE)
    cursor = conn.cursor()

    # The 'WHERE' clause specifies which row(s) to update
    sql_command = "UPDATE Products SET quantity = ? WHERE name = ?;"
    cursor.execute(sql_command, (new_quantity, name))

    conn.commit()
    conn.close()
    print(f"SUCCESS: Quantity for '{name}' updated.")

def delete_product(name):
    """Deletes a specific product from the database."""
    conn = sqlite3.connect(DATABASE_FILE)
    cursor = conn.cursor()

    sql_command = "DELETE FROM Products WHERE name = ?;"
    cursor.execute(sql_command, (name,)) # Note the comma for a single-item tuple

    conn.commit()
    conn.close()
    print(f"SUCCESS: Deleted '{name}' from the database.")

Explanation:

  • UPDATE Products SET quantity = ? WHERE name = ?;: The UPDATE command changes data. The SET clause specifies which column to change and to what new value. The WHERE clause is critical—it specifies which row to apply the change to. Without it, you would update every single row in the table!

  • DELETE FROM Products WHERE name = ?;: Similarly, the DELETE command removes rows. The WHERE clause is essential to ensure you only delete the specific product you intend to.

  • (name,): When passing a single value as a tuple for parameter substitution, you must include a trailing comma to let Python know it's a tuple and not just parentheses around a value.

Step 4: Re-implement the Interactive Menu

Our database functions are ready. Now, we'll replace the simple test block with the interactive menu from Part 1. This menu will handle getting input from the user and then call our new database functions with that input. This makes our application truly interactive and useful again.

Replace the if __name__ == "__main__": block at the end of your file with this complete menu system:

# This block ensures the code runs only when the script is executed directly.
if __name__ == "__main__":
    initialize_database() # Make sure the table exists

    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: ")
            # We will add proper error handling for numbers in a later chapter
            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.")

Explanation of the Menu:

  • We first call initialize_database() to ensure our Products table exists before we enter the main loop.

  • The while True: loop presents the user with a list of options.

  • The if/elif/else block checks the user's choice.

  • For options '2', '3', and '4', the program now asks the user for the necessary information (like product name and quantity) before calling the appropriate database function (add_product, update_product_quantity, etc.) with that information.

  • This structure cleanly separates the user interface (getting input) from the database logic (the functions we built).

Chapter 12: Summary & What's Next

You have successfully built the data-handling core of our application using Python and raw SQL, and connected it to a working user interface.

  • You can now perform all four CRUD operations on a database via an interactive menu.

  • You understand the critical importance of preventing SQL Injection using placeholders (?).

  • You wrote Python functions to INSERT, SELECT, UPDATE, and DELETE data.

You have mastered the "manual transmission" of database work. You now understand exactly what is happening under the hood. In the next chapter, we will introduce the "automatic transmission": an Object-Relational Mapper (ORM). You will see how an ORM can take all the SQL commands we just wrote and translate them into clean, elegant, and Pythonic code.