Build a Product Inventory Manager

Connect Python to SQL Server and master transaction safety with mssql-python.

Introduction

30 Second Summary

Think about what happens when an online store transfers stock between warehouses and the operation fails halfway through. Ten units leave one location but never arrive at the other. The items vanish into thin air. That is a data integrity problem, and it is shockingly easy to create one in code.

In this project, you will build a command-line Product Inventory Manager that connects to SQL Server using Microsoft's new mssql-python driver. You'll perform full CRUD operations, watch a stock transfer corrupt your data when it fails midway, then fix it with proper transaction handling.

What You'll Build

You'll type a command and watch your inventory manager print a formatted product table, transfer stock between items, and prove that when a transfer fails, your data bounces back untouched.

By the end of this project, you'll have:

  • A running product catalog displayed in a formatted table, pulled live from a SQL Server database running in Docker.
  • Full CRUD operations (add, update, delete) using parameterized queries that protect your database from SQL injection.
  • A stock transfer operation that you'll first watch corrupt data with a naive approach, then fix using mssql-python's context manager so failures roll back cleanly.
  • Secret Mission: A bulk import feature that loads products from a CSV file in one atomic transaction, rolling back the entire batch if a single row fails.

Are there any prerequisites?

This project assumes you have Python 3.10+ and Docker already installed on a Linux system (Fedora). You should be comfortable writing basic Python scripts. No prior SQL Server or database driver experience is needed.

Before We Start

Before We Start...

Before we dive in, let's lock in what you're about to build and why it matters. This project teaches you how to connect Python to SQL Server using Microsoft's new mssql-python driver, and how to handle multi-step database operations safely.

Set Up Your Environment

To build a Product Inventory Manager, you need two things: a running SQL Server database to store data, and the mssql-python driver to let Python talk to it.

In this step, you'll install the driver and its dependencies, spin up a SQL Server instance in Docker, and confirm everything connects end-to-end.

In this step, get ready to:
  • Install system libraries and the mssql-python driver.
  • Start a SQL Server 2022 container in Docker.
  • Create a test script that connects and prints the server version.
Install system dependencies and mssql-python

The mssql-python driver uses Direct Database Connectivity to bypass the system ODBC Driver Manager entirely. It still needs two system libraries present on Fedora before it can install.

  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir inventory-manager
cd inventory-manager
  • Install the required system libraries by running:
sudo dnf install -y libtool-ltdl krb5-libs

You should see dnf download and install both packages, ending with a "Complete!" message.

What are these libraries?

The libtool-ltdl library handles dynamic library loading, and krb5-libs provides Kerberos authentication support. These are runtime dependencies that mssql-python links against when connecting to SQL Server.

  • Create a Python virtual environment and activate it by running:
python3 -m venv .venv
source .venv/bin/activate

Your terminal prompt now shows (.venv) at the start, confirming the virtual environment is active.

  • Install the Python packages by running:
pip install mssql-python==1.10.0 python-dotenv==1.2.2

Pip downloads and installs both packages along with their dependencies.

  • Confirm the driver installed correctly by running:
python -c "import mssql_python; print('mssql-python ready')"

You should see mssql-python ready printed to the terminal.

Seeing an ImportError?

Make sure your virtual environment is active. Your prompt should start with (.venv). If not, run source .venv/bin/activate again.

If you see a library-loading error, re-run the sudo dnf install -y libtool-ltdl krb5-libs command and try the import again.

Help me fix this ImportError for mssql_python

Start SQL Server in Docker

SQL Server 2022 Developer Edition runs for free in a Docker container. It needs at least 2 GB of RAM.

  • Pull and start the SQL Server container by running:
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=YourStr0ngP@ssw0rd" \
  -p 1433:1433 --name sql1 --hostname sql1 \
  -d mcr.microsoft.com/mssql/server:2022-latest

What does this command do?

  • ACCEPT_EULA=Y accepts the license agreement for SQL Server.
  • MSSQL_SA_PASSWORD sets the system administrator password. SQL Server requires at least 8 characters with uppercase, lowercase, digits, and symbols.
  • -p 1433:1433 maps the container's SQL Server port to your machine so Python can connect on localhost.
  • -d runs the container in the background so your terminal stays free.
  • Verify the container is running by checking:
docker ps

You should see a row for sql1 with a status of "Up" and port 1433 listed.

SQL Server needs a moment to start

The container reports "Up" immediately, but SQL Server takes several seconds to initialize its databases and begin accepting connections. Wait about 10-15 seconds before running the next command.

  • Confirm SQL Server is accepting connections by running:
docker exec sql1 /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P 'YourStr0ngP@ssw0rd' -Q 'SELECT @@VERSION' -C

You should see the SQL Server 2022 version string printed, confirming the database engine is responsive.

Seeing "Sqlcmd: Error" or connection refused?

SQL Server may still be starting up. Wait another 10 seconds and try the command again.

If docker ps shows the container has exited, your system may not have enough free RAM. SQL Server requires at least 2 GB.

Help me get my SQL Server Docker container working

Create the .env file with connection string

Instead of hardcoding credentials in your Python file, you'll store the connection string in a .env file. The python-dotenv library loads these values as environment variables at runtime.

  • Create the .env file inside your inventory-manager folder with this content:
SQL_CONNECTION_STRING=Server=localhost,1433;Database=master;UID=sa;PWD=YourStr0ngP@ssw0rd;Encrypt=yes;TrustServerCertificate=yes;

What does this connection string mean?

  • Server=localhost,1433 points to your Docker container's mapped port.
  • Database=master connects to the default system database. You'll create your own database in the next step.
  • TrustServerCertificate=yes skips certificate validation for local development.
  • Create inventory.py with a test connection that prints the SQL Server version:
"""Product Inventory Manager using mssql-python"""
from os import getenv
from dotenv import load_dotenv
from mssql_python import connect


def get_connection():
    """Create and return a database connection."""
    load_dotenv()
    return connect(getenv("SQL_CONNECTION_STRING"))


if __name__ == "__main__":
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT @@VERSION")
    row = cursor.fetchone()
    print(row[0])
    cursor.close()
    conn.close()

What does this code do?

  • get_connection() loads the .env file and creates a connection to SQL Server using the connection string.
  • The __main__ block runs a quick test query (SELECT @@VERSION) to prove the connection works.
  • row[0] grabs the first column of the first row, which is the full version string.
  • Save both files.

Before you run this, what do you think you'll see? A version string, an error, or something else?

  • Test your connection by running:
python inventory.py

You should see the full SQL Server 2022 version string printed to your terminal, confirming your Python code connected successfully.

Seeing a connection error?

Check that the SQL Server container is still running with docker ps. If it exited, restart it with docker start sql1.

Verify your .env file has no extra spaces or line breaks in the connection string. The entire value must be on a single line.

Help me troubleshoot my mssql-python connection

✔️ Awesome, I've got everything!

Great. Double check you've saved both .env and inventory.py.

ⓧ I'd like to double check the full code

Here is the complete .env file:

SQL_CONNECTION_STRING=Server=localhost,1433;Database=master;UID=sa;PWD=YourStr0ngP@ssw0rd;Encrypt=yes;TrustServerCertificate=yes;

Here is the complete inventory.py file:

"""Product Inventory Manager using mssql-python"""
from os import getenv
from dotenv import load_dotenv
from mssql_python import connect


def get_connection():
    """Create and return a database connection."""
    load_dotenv()
    return connect(getenv("SQL_CONNECTION_STRING"))


if __name__ == "__main__":
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT @@VERSION")
    row = cursor.fetchone()
    print(row[0])
    cursor.close()
    conn.close()

Your environment is locked in. You have a running SQL Server, a working Python driver, and a proven connection. Next up, you'll create your Inventory database and build the functions that manage products.

Connect and Create Your Inventory

Your SQL Server container is running, and your Python driver can talk to it. But right now you have an empty server with no database and no tables.

Before you can manage products, you need a structured schema. In this step, you'll create an Inventory database, define a Products table, and prove your connection works end-to-end by inserting and displaying real data.

In this step, get ready to:
  • Create the Inventory database and Products table with a setup function.
  • Insert sample products using parameterized queries.
  • Display your product catalog in a formatted table.
Create the Inventory database and Products table

Your connection string currently points at the master system database. Every other function in this project will connect to the new Inventory database instead, so the first thing to do is update that connection string.

  • In .env, find this line:
SQL_CONNECTION_STRING=Server=localhost,1433;Database=master;UID=sa;PWD=YourStr0ngP@ssw0rd;Encrypt=yes;TrustServerCertificate=yes;
  • Replace Database=master with Database=Inventory, so the line now looks like this:
SQL_CONNECTION_STRING=Server=localhost,1433;Database=Inventory;UID=sa;PWD=YourStr0ngP@ssw0rd;Encrypt=yes;TrustServerCertificate=yes;

Why point at Inventory when it doesn't exist yet?

The setup_database() function you'll write next handles the chicken-and-egg problem. It swaps the connection string back to master internally to create the Inventory database, then reconnects using the normal string for everything else.

  • Save .env.

Now you need a function that creates the database and defines the table structure. DDL statements like CREATE DATABASE cannot run inside a transaction in SQL Server, so the function needs autocommit mode enabled.

  • In inventory.py, add the following setup_database() function directly below get_connection():
def setup_database():
    """Create the Inventory database and Products table."""
    load_dotenv()
    conn_str = getenv("SQL_CONNECTION_STRING").replace(
        "Database=Inventory", "Database=master"
    )
    conn = connect(conn_str)
    conn.setautocommit(True)
    cursor = conn.cursor()
    cursor.execute("""
        IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = 'Inventory')
        CREATE DATABASE Inventory
    """)
    cursor.close()
    conn.close()

    conn = get_connection()
    conn.setautocommit(True)
    cursor = conn.cursor()
    cursor.execute("""
        IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Products')
        CREATE TABLE Products (
            id INT IDENTITY(1,1) PRIMARY KEY,
            name NVARCHAR(100) NOT NULL,
            category NVARCHAR(50) NOT NULL,
            quantity INT NOT NULL DEFAULT 0,
            price DECIMAL(10,2) NOT NULL
        )
    """)
    cursor.close()
    conn.close()
    print("Database and table ready.")

What does this code do?

This function handles two separate connections because SQL Server requires them:

  • The first connection targets master (by swapping the database name in the connection string) because you can only create a new database while connected to an existing one.
  • setautocommit(True) is required because DDL statements cannot execute inside a transaction.
  • The second connection uses get_connection() (pointing at Inventory) to create the Products table inside the new database.
  • IF NOT EXISTS guards make the function safe to run multiple times without errors.

Now replace the existing if __name__ == "__main__": block so it calls your new function instead of the version-check code.

  • In inventory.py, find the existing main block at the bottom of the file:
if __name__ == "__main__":
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT @@VERSION")
    row = cursor.fetchone()
    print(row[0])
    cursor.close()
    conn.close()
  • Replace that entire block with this:
if __name__ == "__main__":
    setup_database()
  • Save inventory.py.
  • Run your script to verify the database and table get created:
python inventory.py

You should see:

Database and table ready.

Seeing a connection error?

  • Make sure your Docker container is still running. Check with docker ps and look for sql1 in the output.
  • Confirm your .env has Database=Inventory (not Database=master). The function swaps to master internally, so the base string must say Inventory.
  • If you see a password error, double-check the password in .env matches what you used in the docker run command from Step 1.

Help me debug my SQL Server connection error.

Insert sample products and display them

Your database and table exist. Now you need functions to put data in and get data out. The insert function will use parameterized queries with ? placeholders to keep your SQL safe from injection.

  • In inventory.py, add the add_product() function below setup_database():
def add_product(name, category, quantity, price):
    """Insert a new product into the inventory."""
    with get_connection() as conn:
        cursor = conn.cursor()
        cursor.execute(
            "INSERT INTO Products (name, category, quantity, price) VALUES (?, ?, ?, ?)",
            name, category, quantity, price
        )
    print(f"Added: {name} (qty: {quantity}, ${price:.2f})")

What does this code do?

  • with get_connection() as conn: uses the context manager pattern. If the insert succeeds, the connection auto-commits. If an exception occurs, it auto-rolls-back.
  • The ? placeholders are the qmark parameter style. The driver safely substitutes the values without string concatenation, preventing SQL injection.
  • Parameters are passed as positional arguments directly to cursor.execute() after the SQL string.

Next, you need a function to read products back out and display them in a readable format.

  • Add the list_products() function below add_product():
def list_products():
    """Display all products in the inventory."""
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT id, name, category, quantity, price FROM Products")
    rows = cursor.fetchall()
    cursor.close()
    conn.close()
    if not rows:
        print("No products found.")
        return rows
    print(f"\n{'ID':<4} {'Name':<25} {'Category':<15} {'Qty':<6} {'Price':<8}")
    print("-" * 60)
    for row in rows:
        print(f"{row.id:<4} {row.name:<25} {row.category:<15} {row.quantity:<6} ${row.price:<7.2f}")
    print()
    return rows

What does this code do?

  • fetchall() returns all rows from the query as a list of Row objects.
  • mssql-python Row objects support column-name attribute access (row.name, row.quantity) instead of index-only access. This makes your code more readable.
  • The f-string format specifiers like :<25 left-align text in a 25-character column, producing a neat table layout.

Time to wire everything together. You'll update the main block to create the database, add three sample products, and list them.

  • Replace the current if __name__ == "__main__": block (which only calls setup_database()) with this:
if __name__ == "__main__":
    setup_database()

    add_product("Mechanical Keyboard", "Electronics", 50, 89.99)
    add_product("USB-C Cable", "Electronics", 200, 12.99)
    add_product("Notebook", "Stationery", 500, 4.99)
    list_products()
  • Save inventory.py.

Before you run this, what do you expect to see printed? Think about what each function call in the main block outputs.

  • Run your script:
python inventory.py

You should see "Database and table ready." followed by three "Added:" confirmations, then a formatted table showing all three products with their IDs, names, categories, quantities, and prices.

Seeing 'No products found' or duplicate products?

  • If you see "No products found", the INSERT statements may have failed silently. Check that add_product() uses the with get_connection() as conn: context manager pattern. Without it, the insert won't commit.
  • If you see duplicate products, you ran the script more than once. The table has no unique constraint on name, so re-running adds new rows. To start fresh, stop and remove the Docker container with docker stop sql1 && docker rm sql1, then re-run the docker run command from Step 1.

Help me fix my product insert or display issue.

✔️ Awesome, I've got everything!

Great. Double-check you've saved both .env and inventory.py.

ⓧ I'd like to double check the full code

Your .env should contain exactly this:

SQL_CONNECTION_STRING=Server=localhost,1433;Database=Inventory;UID=sa;PWD=YourStr0ngP@ssw0rd;Encrypt=yes;TrustServerCertificate=yes;

Your inventory.py should contain the following sections in order. Scroll up to confirm each one is present:

  • Imports and get_connection() function (unchanged from Step 1).
  • setup_database() function that creates the Inventory database and Products table.
  • add_product() function using the context manager and parameterized queries.
  • list_products() function that fetches and displays all products.
  • if __name__ == "__main__": block calling setup_database(), three add_product() calls, and list_products().

Your inventory has data in it and you can see it on screen. Next up, you'll add UPDATE and DELETE operations to complete the full CRUD toolkit.

Complete the CRUD Operations

Your inventory manager can add products and display them in a table. A real inventory system also needs to update existing quantities and remove discontinued items.

In this step, you'll add UPDATE and DELETE operations to complete the standard CRUD pattern. These functions also set up the multi-step operation you'll build in the next step.

In this step, get ready to:
  • Build update_quantity() and delete_product() functions with parameterized queries.
  • Wire the new functions into the main block and verify the changes.
Build update and delete functions

Both functions follow the same pattern you used in add_product(): open a connection with the context manager, execute a parameterized query, and let the context manager commit automatically.

  • In inventory.py, add the update_quantity() function below list_products():
def update_quantity(product_id, new_quantity):
    """Update the quantity of a product."""
    with get_connection() as conn:
        cursor = conn.cursor()
        cursor.execute(
            "UPDATE Products SET quantity = ? WHERE id = ?",
            new_quantity, product_id
        )
        if cursor.rowcount == 0:
            print(f"No product found with ID {product_id}.")
        else:
            print(f"Updated product {product_id} quantity to {new_quantity}.")

What does this code do?

  • The with get_connection() as conn: context manager opens a connection that auto-commits on success or rolls back on error.
  • The ? placeholders keep the query safe from SQL injection by separating data from the SQL command.
  • cursor.rowcount tells you how many rows the UPDATE affected. If zero, the product ID does not exist.
  • Save inventory.py.
  • Now add the delete_product() function directly below update_quantity():
def delete_product(product_id):
    """Delete a product from the inventory."""
    with get_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("DELETE FROM Products WHERE id = ?", product_id)
        if cursor.rowcount == 0:
            print(f"No product found with ID {product_id}.")
        else:
            print(f"Deleted product {product_id}.")

What does this code do?

  • The DELETE query uses the same ? parameterized style to target a specific product by ID.
  • The rowcount check confirms whether the product actually existed before reporting success.
  • Save inventory.py.

Seeing a syntax error?

  • Make sure both functions are at the module level (no indentation) and not accidentally nested inside another function.
  • Check that the f" strings use matching quotes. Each print(f"...") needs a closing double-quote and parenthesis.
  • help me debug a syntax error in my update_quantity or delete_product function
Wire them into the main block and run

The functions exist but nothing calls them yet. You need to add calls to the if __name__ == "__main__": block to exercise both operations.

  • In inventory.py, find the if __name__ == "__main__": block. After the list_products() call, add these lines:
    update_quantity(1, 45)
    delete_product(3)
    list_products()

What does this code do?

  • update_quantity(1, 45) changes the Mechanical Keyboard's stock from 50 down to 45.
  • delete_product(3) removes the Notebook entirely from the inventory.
  • The final list_products() prints the table so you can confirm both changes applied.
  • Save inventory.py.

Before you run the script, what do you expect the final table to show? Which products should remain, and what quantity should the Mechanical Keyboard have?

  • Run the script to see your CRUD operations in action:
python inventory.py

You should see output showing the three products added, then an update message, a deletion message, and a final table with only two products remaining.

What should I see?

The output includes several sections. First, the setup and three product additions. Then:

  • Updated product 1 quantity to 45. confirms the UPDATE worked.
  • Deleted product 3. confirms the Notebook was removed.
  • The final table shows only Mechanical Keyboard (qty: 45) and USB-C Cable (qty: 200).

Not seeing the expected output?

  • If you see duplicate products, the previous run already added them. Drop the table first by running: docker exec sql1 /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P 'YourStr0ngP@ssw0rd' -d Inventory -Q "DROP TABLE Products" -C then re-run the script.
  • If you see "No product found with ID 1" it means the products were already deleted in a prior run. Drop the table using the command above and try again.
  • help me troubleshoot unexpected output from my inventory script

✔️ Awesome, I've got everything!

Great. Double check you've saved inventory.py and confirmed the output matches the expected table.

ⓧ I'd like to double check the full code

Scroll up and confirm your inventory.py contains each of these sections, in order:

  • Imports and get_connection() function.
  • setup_database() function.
  • add_product() function.
  • list_products() function.
  • update_quantity() function (the one you just added).
  • delete_product() function (the one you just added).
  • if __name__ == "__main__": block with setup, three add_product() calls, list_products(), then update_quantity(1, 45), delete_product(3), and a final list_products().

Your inventory manager now handles all four CRUD operations. Next up, you'll discover what happens when a multi-step operation fails halfway through.

The Stock Transfer That Corrupts Your Data

So far, every database operation in your inventory manager has been a single statement. Insert one product. Update one quantity. Delete one row. Each operation either works or it doesn't, and there's no in-between state to worry about.

Real business logic is rarely that simple. A stock transfer between two products requires two updates: deduct from one, add to another. What happens if the first update succeeds but the second one fails? In this step, you'll find out firsthand.

In this step, get ready to:
  • Write a transfer function that commits each statement individually.
  • Run a transfer to a non-existent product and observe the data corruption.
Build a naive transfer function that commits per-step

Many tutorials and quick scripts use autocommit mode for simplicity. Each SQL statement is committed the instant it executes. This works fine for single-statement operations, but for a two-step transfer it means the first deduction is permanent before the second addition even attempts to run.

  • In inventory.py, find the delete_product() function.
  • Add the following transfer_stock_unsafe() function below it:
def transfer_stock_unsafe(from_id, to_id, amount):
    """Transfer stock between products - UNSAFE: commits per step."""
    conn = get_connection()
    conn.setautocommit(True)
    cursor = conn.cursor()

    # Step 1: Deduct from source - committed immediately
    cursor.execute(
        "UPDATE Products SET quantity = quantity - ? WHERE id = ?",
        amount, from_id
    )
    print(f"  Deducted {amount} from product {from_id}. (committed)")

    # Step 2: Add to destination - but what if it doesn't exist?
    cursor.execute("SELECT id FROM Products WHERE id = ?", to_id)
    if cursor.fetchone() is None:
        cursor.close()
        conn.close()
        raise Exception(f"Destination product {to_id} not found! Stock is now LOST.")

    cursor.execute(
        "UPDATE Products SET quantity = quantity + ? WHERE id = ?",
        amount, to_id
    )
    cursor.close()
    conn.close()
    print(f"  Added {amount} to product {to_id}.")

What does this code do?

This function attempts a stock transfer in two separate steps, each committed immediately:

  • conn.setautocommit(True) makes every SQL statement commit the instant it runs. There is no way to undo it.
  • Step 1 deducts stock from the source product. This change is permanent the moment it executes.
  • Step 2 checks whether the destination product exists before adding stock. If it doesn't exist, the function raises an exception.
  • The problem: by the time step 2 fails, step 1's deduction is already permanent. The stock vanishes into thin air.
Call the unsafe transfer and observe the corruption

Now you'll call this function with a valid source (product 1) but an invalid destination (product ID 999, which doesn't exist). The exception needs to be caught so the program can continue and print the product table afterwards.

  • In inventory.py, scroll to the bottom of your if __name__ == "__main__": block.
  • Find the last line, which is list_products() (called after the update and delete).
  • Add the following code below that last list_products() call:
    print("--- Attempting UNSAFE transfer to non-existent product ---")
    try:
        transfer_stock_unsafe(1, 999, 10)
    except Exception as e:
        print(f"  ERROR: {e}")
    print("\nProducts after failed unsafe transfer:")
    list_products()

What does this code do?

  • The try/except block catches the exception so the program doesn't crash. It prints the error message instead.
  • After the failed transfer, list_products() prints the current state of the database so you can see what happened to the quantities.
  • Save inventory.py.

Before you run this, think about what you expect to happen. The transfer tries to move 10 units from product 1 to product 999. Product 999 doesn't exist. Do you think product 1's quantity will stay the same after the failure, or will it change?

  • Run your script to see what actually happens:
python inventory.py

Look at the product table printed after the failed transfer. Product 1 (Mechanical Keyboard) started at quantity 45 after the earlier update. The unsafe transfer deducted 10, dropping it to 35. Then the transfer failed because product 999 doesn't exist.

Product 1 permanently lost 10 units that went nowhere. That's data corruption.

Why did this happen?

With setautocommit(True), the first UPDATE statement was committed to the database the instant it executed. There is no undo button. When step 2 discovered that product 999 doesn't exist, it was already too late to reverse step 1.

In a real inventory system, this kind of bug could mean money disappearing from accounts, orders fulfilled from non-existent stock, or shipments recorded against the wrong warehouse.

Not seeing the quantity change?

  • Make sure transfer_stock_unsafe() is defined outside the if __name__ block, at the same indentation level as your other functions.
  • Check that the call transfer_stock_unsafe(1, 999, 10) is inside the if __name__ block with proper indentation (4 spaces).
  • If your Docker container stopped, restart it with docker start sql1 and run the script again.

Help me debug why my unsafe transfer isn't showing the quantity change.

✔️ Awesome, I've got everything!

Great. Double check you've saved inventory.py and you can see product 1's quantity dropped to 35 in your output.

ⓧ I'd like to double check the full code

Your inventory.py should contain the following sections in order. Scroll up and confirm each is present:

  • Imports (getenv, load_dotenv, connect).
  • get_connection() function.
  • setup_database() function.
  • add_product() function.
  • list_products() function.
  • update_quantity() function.
  • delete_product() function.
  • transfer_stock_unsafe() function with setautocommit(True).
  • if __name__ == "__main__": block with setup, CRUD operations, and the unsafe transfer wrapped in try/except.

You've just witnessed the exact scenario that transaction handling is designed to prevent. Stock vanished because each SQL statement was committed independently with no way to undo step 1 when step 2 failed. In the next step, you'll fix this with a single line change that wraps both updates in one atomic transaction.

Fix It with Transactions

In the previous step, you watched 10 units of stock vanish into thin air. The unsafe transfer committed the deduction before confirming the destination existed. That is the core problem with per-statement commits.

The fix is wrapping both updates in a single transaction. With mssql-python's context manager, either both statements succeed and get committed together, or both fail and get rolled back. No more lost stock.

In this step, get ready to:
  • Write a safe transfer_stock() function using the context manager pattern.
  • Prove the rollback works by attempting an invalid transfer and seeing quantities unchanged.
  • Perform a valid transfer and verify both products update correctly.
Rewrite the transfer using the context manager

The context manager (with get_connection() as conn:) auto-commits on a clean exit and auto-rolls-back if any exception is raised inside the block. This means both UPDATE statements live or die together.

  • In inventory.py, add the following transfer_stock() function below transfer_stock_unsafe():
def transfer_stock(from_id, to_id, amount):
    """Transfer stock between products - SAFE: single transaction."""
    with get_connection() as conn:
        cursor = conn.cursor()

        # Verify source has enough stock
        cursor.execute("SELECT quantity FROM Products WHERE id = ?", from_id)
        source = cursor.fetchone()
        if source is None:
            raise Exception(f"Source product {from_id} not found.")
        if source.quantity < amount:
            raise Exception(
                f"Insufficient stock. Available: {source.quantity}, requested: {amount}"
            )

        # Verify destination exists
        cursor.execute("SELECT id FROM Products WHERE id = ?", to_id)
        if cursor.fetchone() is None:
            raise Exception(f"Destination product {to_id} not found!")

        # Both updates in one transaction
        cursor.execute(
            "UPDATE Products SET quantity = quantity - ? WHERE id = ?",
            amount, from_id
        )
        cursor.execute(
            "UPDATE Products SET quantity = quantity + ? WHERE id = ?",
            amount, to_id
        )
        # Context manager commits here on success
    print(f"Transferred {amount} units from product {from_id} to product {to_id}.")

What does this code do?

This function performs the same two-step transfer but with a critical structural difference:

  • with get_connection() as conn: opens a connection where autocommit is off by default. Nothing is committed until the block exits cleanly.
  • The validation checks (source exists, has enough stock, destination exists) raise exceptions if something is wrong. An exception triggers the context manager's automatic rollback.
  • Both UPDATE statements execute within the same transaction. If the block finishes without error, the context manager commits both at once.

Why not just use try/except with manual rollback?

You could call conn.commit() and conn.rollback() manually inside a try/except. The context manager does the same thing with less code and fewer chances to forget the rollback path.

  • Save inventory.py. The function is defined but nothing calls it yet.
Demonstrate both failure and success

Now you will call the safe function twice. First with an invalid destination to prove the rollback works, then with two valid products to prove a successful transfer commits correctly.

  • In inventory.py, find the last line of your if __name__ == "__main__": block (the list_products() call after the unsafe transfer). Add the following code below it:
    # Safe transfer - this preserves data integrity
    print("--- Attempting SAFE transfer to non-existent product ---")
    try:
        transfer_stock(1, 999, 10)
    except Exception as e:
        print(f"  ERROR: {e}")
    print("\nProducts after failed safe transfer:")
    list_products()

    # Successful safe transfer
    print("--- Performing valid transfer ---")
    transfer_stock(2, 1, 25)
    print("\nFinal inventory:")
    list_products()

What does this code do?

  • The first block attempts transfer_stock(1, 999, 10). Product 999 does not exist, so the function raises an exception. The context manager rolls back the deduction from product 1. Quantities stay unchanged.
  • The second block calls transfer_stock(2, 1, 25). Both products exist and product 2 has enough stock. Both updates commit together.
  • Save inventory.py.

Before you run this, think about what you expect to see. The unsafe transfer already corrupted product 1's quantity in the previous step. Will the safe transfer to product 999 reduce it further, or will the rollback protect it?

  • Run the script by executing this command:
python inventory.py

In the output, look for the safe transfer section. After the failed safe transfer to product 999, the quantities should be unchanged. Then the valid transfer of 25 units from product 2 to product 1 should update both correctly in the final table.

Quantities still dropping after the safe transfer fails?

  • Make sure your transfer_stock() function uses with get_connection() as conn: and does NOT call conn.setautocommit(True). Autocommit defeats the context manager's rollback behavior.
  • Check that the validation checks (source exists, destination exists) raise exceptions rather than returning silently. The context manager only rolls back when an exception propagates out of the with block.
  • If you see NameError: name 'transfer_stock' is not defined, make sure the function is defined above the if __name__ == "__main__": block, not inside it.

help me debug my transfer_stock function

✔️ Awesome, I've got everything!

Great. Double check you have saved inventory.py and your output shows data integrity preserved in both cases.

ⓧ I'd like to double check the full code

Your inventory.py file should contain these sections in order. Scroll through and confirm each one is present:

  • Imports (os, dotenv, mssql_python).
  • get_connection() function.
  • setup_database() function.
  • add_product() function.
  • list_products() function.
  • update_quantity() function.
  • delete_product() function.
  • transfer_stock_unsafe() function.
  • transfer_stock() function (new in this step).
  • if __name__ == "__main__": block with setup, CRUD operations, unsafe transfer, safe transfer to 999, and valid transfer from product 2 to product 1.

Secret mission

Bulk Import from CSV

Real inventory systems don't add products one at a time. They load data in batches from files, spreadsheets, or external feeds. Can you build a bulk import that loads an entire CSV in a single transaction, proving that one bad row rolls back the whole batch?

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project uses SQL Server Developer Edition in Docker, which is free for development use. There are no ongoing financial costs, but the container does consume approximately 1.5 GB of RAM while running.

Resources you used:

  • Docker container sql1 running SQL Server 2022 (Developer Edition).
  • Project folder on your Desktop containing inventory.py, .env, products.csv, and your Python virtual environment.

Keep everything running

No action needed. Choose this if you're still actively building or plan to extend the project soon.

  • Your Docker container sql1 continues running and your data persists between system restarts.
  • Your project files stay on the Desktop, ready to use.
  • The system libraries (libtool-ltdl, krb5-libs) remain installed. These are shared system dependencies that other software may also use.

Pause - I'll come back to this later

Stop the Docker container to free up RAM. Your data and project files stay intact for when you return.

  • Stop the SQL Server container by running:
docker stop sql1
  • When you're ready to resume, start it again by running:
docker start sql1

All your database data persists between stops and starts. Your project files remain on the Desktop.

Delete - I don't want to use this again

Remove all project resources and start fresh. This permanently deletes your database data and project files.

  • Stop and remove the SQL Server container by running:
docker stop sql1 && docker rm sql1
  • Confirm the container is gone by running:
docker ps -a | grep sql1

You should see no output, confirming the container has been removed.

  • Remove your project folder from the Desktop by running:
cd ~/Desktop
rm -rf inventory-manager
  • Optionally, remove the SQL Server Docker image to free disk space:
docker rmi mcr.microsoft.com/mssql/server:2022-latest

Nice Work!

Nice Work!

Nice work! You just built a fully functional Product Inventory Manager that connects to SQL Server using Microsoft's modern mssql-python driver and handles real-world data integrity challenges.

You've learned how to:

  • Built a Python application that connects to SQL Server using Microsoft's new mssql-python driver and performed full CRUD operations with parameterized queries safe from SQL injection.
  • Experienced firsthand how per-statement commits corrupt data in multi-step operations, watching stock disappear into the void when a transfer failed halfway through.
  • Fixed the corruption using mssql-python's context manager for automatic transaction handling. Commit on success, rollback on failure.
  • Secret Mission: Loaded bulk data from CSV using executemany within a single transaction, proving that an entire batch rolls back cleanly if any row fails.

Ready to quiz yourself?