Build a Personal Inventory System

Design a relational PostgreSQL database and query it with Python.

Introduction

30 Second Summary

Ever lost track of something you own, or bought a duplicate because you forgot you already had one? Businesses solve this exact problem with databases that organize information across structured, connected tables.

In this project, you will build a personal home inventory system backed by PostgreSQL. You will design linked tables, insert data through Python, and write queries that answer questions about your belongings.

What You'll Build

A relational database system that tracks your belongings across categories and locations, with a Python interface for managing and querying the data.

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

  • A PostgreSQL database called inventory with two tables linked by a foreign key.
  • A Python script that performs full CRUD operations (Create, Read, Update, Delete) against your database.
  • A set of queries demonstrating WHERE filters, ORDER BY sorting, JOINs across tables, and GROUP BY aggregates.
  • Secret Mission: Add a low stock alert system that flags items below a quantity threshold you define.

Are there any prerequisites?

You should have basic Python knowledge (variables, functions, and running scripts). No prior database or SQL experience is needed.

Install PostgreSQL and Set Up Your Environment

Before you can store data in a relational database, you need the database engine itself. PostgreSQL is one of the most popular open-source databases in the world, trusted for its reliability and powerful SQL support.

In this step, you will install PostgreSQL 17 on your machine, create a fresh database called inventory, and connect Python to it using a library called psycopg. By the end, you will have confirmed that Python can talk to your database.

In this step, get ready to:
  • Install PostgreSQL 17 and verify it is running.
  • Create the inventory database.
  • Install psycopg and confirm Python can connect to the database.
Install PostgreSQL 17 on Windows

PostgreSQL needs to be installed on your machine before you can create databases or run queries. You will use the official EDB installer, which bundles the server, admin tools, and command-line utilities in one package.

  • Check if PostgreSQL is already installed by opening a terminal and running this command:
psql --version

✔️ I see version 17 or higher

PostgreSQL is already installed and ready to go. Skip ahead to the next substep where you create the database.

ⓧ I see an older version

You have an older version of PostgreSQL installed. You need version 17 for this project.

  • Download the PostgreSQL 17 installer from the EDB downloads page:
  • https://www.enterprisedb.com/downloads/postgres-postgresql-downloads.
  • Select the 17.9 row and click the Windows x86-64 download link.
  • Run the installer with administrator privileges.
  • Accept the default installation directory.
  • On the Select Components screen, ensure all components are checked: PostgreSQL Server, pgAdmin 4, Stack Builder, and Command Line Tools.
  • Set a password for the postgres superuser. Remember this password.
  • Accept the default port 5432 and default locale.
  • Uncheck Launch Stack Builder at exit and click Finish.

After the installer finishes, run psql --version again to confirm you now see version 17.

ⓧ Command not found

PostgreSQL is not installed yet. Follow these steps to install it.

  • Download the PostgreSQL 17 installer from the EDB downloads page:
  • https://www.enterprisedb.com/downloads/postgres-postgresql-downloads.
  • Select the 17.9 row and click the Windows x86-64 download link.
  • Run the downloaded installer with administrator privileges.
  • Accept the default installation directory.
  • On the Select Components screen, ensure all components are checked: PostgreSQL Server, pgAdmin 4, Stack Builder, and Command Line Tools.
  • Set a password for the postgres superuser. Remember this password. You will need it every time you connect to the database.
  • Accept the default port 5432 and default locale.
  • Uncheck Launch Stack Builder at exit and click Finish.

Why remember the password?

The password you set during installation is for the postgres superuser account. You will type it every time you connect to the database, both from the SQL Shell and from Python.

Still seeing command not found after installing?

The EDB installer may not add PostgreSQL to your system PATH automatically. You can still access psql through the SQL Shell (psql) shortcut in the Windows Start menu, which is all you need for this project.

help me troubleshoot why psql is not found in my terminal after installing PostgreSQL on Windows

Verify PostgreSQL is running and create the database

Now that PostgreSQL is installed, you need to open its interactive terminal and create a new database for your inventory project.

  • Press the Windows key to open your search bar.
  • Type SQL Shell and press Enter to open it.

What is SQL Shell?

SQL Shell (psql) is PostgreSQL's command-line interface. It lets you type SQL commands and see results immediately. The EDB installer creates a shortcut for it in your Start menu.

  • The shell will prompt you for connection details. Press Enter to accept the default for each one:
  • Server: localhost (press Enter).
  • Database: postgres (press Enter).
  • Port: 5432 (press Enter).
  • Username: postgres (press Enter).
  • Type your password (the one you set during installation) and press Enter. The cursor will not move as you type. This is normal.

You should now see the postgres=# prompt. This means you are connected to the default postgres database.

  • Create your project database by running this command:
CREATE DATABASE inventory;
  • Connect to your new database by running:
\c inventory

You should see the message: "You are now connected to database "inventory"". Your prompt changes to inventory=#.

Seeing a password authentication error?

Double-check that you are typing the exact password you set during installation. The password is case-sensitive.

If you forgot your password, you will need to reinstall PostgreSQL or edit the pg_hba.conf file to temporarily allow passwordless access.

help me fix a password authentication error when connecting to PostgreSQL

Install psycopg and verify the connection

Your database is running, but you need a way to talk to it from Python. The psycopg library is the official PostgreSQL adapter for Python. It translates your Python code into SQL commands that PostgreSQL understands.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type VS Code and press Enter to open it.
  • Open a terminal inside VS Code by clicking Terminal in the top menu bar, then clicking New Terminal.
  • Install psycopg by running this command:
pip install "psycopg[binary]==3.3.4"

What does this command do?

This installs psycopg version 3.3.4 with the binary option. The binary package includes a pre-compiled copy of the PostgreSQL client library, so you do not need to install any extra system dependencies.

Seeing a permissions error?

Try running the command with the --user flag to install for your user only instead of system-wide.

help me fix a pip install permissions error on Windows

Now you will create a small test script to confirm Python can connect to your inventory database.

  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir inventory-project
cd inventory-project
  • Confirm the folder was created by running:
ls

You should see an empty directory (no output). This confirms you are inside your new inventory-project folder.

  • In VS Code, click File in the top menu bar.
  • Click Open Folder.
  • Select the inventory-project folder on your Desktop, then click Open.
  • In the Explorer sidebar on the left, click the new file icon and name the file test_connection.py.
  • Paste the following code into test_connection.py:
import psycopg

with psycopg.connect("host=localhost dbname=inventory user=postgres password=your-password-here") as conn:
    cur = conn.execute("SELECT version()")
    print("Connected successfully!")
    print(cur.fetchone()[0])

What does this code do?

  • import psycopg loads the PostgreSQL adapter library.
  • psycopg.connect(...) opens a connection to your inventory database running on localhost.
  • The with block automatically closes the connection when the code finishes running.
  • SELECT version() is a SQL query that returns the PostgreSQL version string, confirming the connection works.
  • Replace your-password-here in the connection string with the password you set during PostgreSQL installation.
  • Save test_connection.py.
  • Run the script by entering this command in your VS Code terminal:
python test_connection.py

You should see output starting with "Connected successfully!" followed by a PostgreSQL version string.

Seeing a connection refused error?

Make sure PostgreSQL is running as a Windows service. Open the Start menu, search for Services, and look for a service with "postgresql" in the name. Its status should say Running. If it is stopped, right-click it and select Start.

Also confirm you replaced your-password-here with your actual password in the connection string.

help me fix a connection refused error when connecting to PostgreSQL from Python

✔️ Awesome, I've got everything!

Great. Double check you have saved test_connection.py and that you saw the "Connected successfully!" message in your terminal.

ⓧ I'd like to double check the full code

Here is the complete test_connection.py file:

import psycopg

with psycopg.connect("host=localhost dbname=inventory user=postgres password=your-password-here") as conn:
    cur = conn.execute("SELECT version()")
    print("Connected successfully!")
    print(cur.fetchone()[0])

Your environment is ready. PostgreSQL is running, your database exists, and Python can connect to it. Next up, you will design the schema for your inventory system and create your first tables.

Design and Create Your Database Schema

In Step 1, you installed PostgreSQL and confirmed that Python can talk to your inventory database.

Now it is time to design the structure of your data. A database without tables is like an empty filing cabinet. You need to define the drawers (tables), the labels on each drawer (columns), and the rules for what goes where (constraints).

In this step, get ready to:
  • Create a categories table that groups your inventory items.
  • Create an items table linked to categories by a foreign key.
  • Verify both tables exist in the database using the SQL Shell.
Create the categories table

Every item in your inventory belongs to a category (Electronics, Books, Kitchen). Storing categories in their own table means you can reuse them across many items without repeating yourself.

  • In VS Code, create a new file called create_tables.py in the same folder where test_connection.py lives.
  • Add the following code to connect to your database and create the categories table:
import psycopg

with psycopg.connect("host=localhost dbname=inventory user=postgres password=your-password-here") as conn:
    with conn.cursor() as cur:
        cur.execute("""
            CREATE TABLE IF NOT EXISTS categories (
                id SERIAL PRIMARY KEY,
                name VARCHAR(100) NOT NULL UNIQUE,
                description TEXT
            )
        """)

What does this code do?

This script connects to your inventory database using psycopg, then opens a cursor to run SQL commands.

  • SERIAL PRIMARY KEY makes id auto-increment with each new row. You never have to set it manually.
  • VARCHAR(100) NOT NULL UNIQUE means the category name is required, capped at 100 characters, and no two categories can share the same name.
  • TEXT allows an optional description of any length.
  • CREATE TABLE IF NOT EXISTS prevents errors if you accidentally run the script twice.

You will not run this file yet. The next substep adds the second table, then you will run the complete script.

Create the items table with a foreign key

The items table stores each individual thing you own. It links back to the categories table through a foreign key. This relationship means every item must belong to a category that already exists.

  • In create_tables.py, find the closing """) of the categories cur.execute call.
  • Add the following code directly below it, still inside the with conn.cursor() as cur: block:
        cur.execute("""
            CREATE TABLE IF NOT EXISTS items (
                id SERIAL PRIMARY KEY,
                name VARCHAR(200) NOT NULL,
                quantity INTEGER NOT NULL DEFAULT 0,
                category_id INTEGER REFERENCES categories(id),
                location VARCHAR(100),
                added_date DATE DEFAULT CURRENT_DATE
            )
        """)

        conn.commit()
        print("Tables created successfully!")

What does this code do?

This creates the items table and then commits both tables to the database.

  • INTEGER NOT NULL DEFAULT 0 on quantity means every item starts at zero unless you specify otherwise.
  • INTEGER REFERENCES categories(id) on category_id is the foreign key. PostgreSQL will reject any item that references a category id that does not exist.
  • DATE DEFAULT CURRENT_DATE on added_date automatically stamps today's date when you insert a new item.
  • conn.commit() saves both table creations permanently. Without it, your changes would be rolled back when the connection closes.
  • Save create_tables.py.

✔️ Awesome, I've got everything!

Great. Double check you have saved your file before moving on.

ⓧ I'd like to double check the full code

import psycopg

with psycopg.connect("host=localhost dbname=inventory user=postgres password=your-password-here") as conn:
    with conn.cursor() as cur:
        cur.execute("""
            CREATE TABLE IF NOT EXISTS categories (
                id SERIAL PRIMARY KEY,
                name VARCHAR(100) NOT NULL UNIQUE,
                description TEXT
            )
        """)

        cur.execute("""
            CREATE TABLE IF NOT EXISTS items (
                id SERIAL PRIMARY KEY,
                name VARCHAR(200) NOT NULL,
                quantity INTEGER NOT NULL DEFAULT 0,
                category_id INTEGER REFERENCES categories(id),
                location VARCHAR(100),
                added_date DATE DEFAULT CURRENT_DATE
            )
        """)

        conn.commit()
        print("Tables created successfully!")
Run the script and verify your tables
  • Run your script in the VS Code terminal by entering this command:
python create_tables.py

You should see Tables created successfully! printed in the terminal.

Seeing a connection error?

  • Make sure you replaced your-password-here in the connection string with the actual password you set for the postgres user during installation.
  • Confirm PostgreSQL is still running. Open the SQL Shell from the Start menu to check.
  • If you see a syntax error, double check your triple-quoted strings """ are not missing a closing quote.

My create_tables.py script is failing with an error. How do I fix it?

Now verify the tables exist inside the database using the SQL Shell.

  • Open the SQL Shell (psql) from the Windows Start menu.
  • Press Enter to accept the defaults for Server, Database, Port, and Username.
  • Type your password when prompted.
  • Connect to your inventory database by running:
\c inventory
  • List all tables in the database by running:
\dt

You should see both categories and items listed in the output.

  • Inspect the items table structure by running:
\d items

You should see all six columns listed with their types, plus a foreign key constraint referencing categories(id) at the bottom.

What do these psql commands do?

  • \c inventory switches your active connection to the inventory database.
  • \dt lists all user-created tables in the current database.
  • \d items describes the structure of the items table, showing column names, data types, and constraints.

Not seeing your tables?

  • Make sure you ran \c inventory first. If you are still connected to the default postgres database, the tables will not appear.
  • Confirm your Python script printed Tables created successfully! without any errors. If it did not, re-run it and check for typos.

I ran \dt in psql but my tables are not showing up. What went wrong?

Your database schema is in place. You have two tables connected by a relationship that PostgreSQL enforces automatically. Next up, you will populate these tables with real data using Python.

Insert and Read Data with Python

Your tables are created, but they're empty. A database without data is like a filing cabinet with nothing in the drawers.

In this step, you'll populate your tables with real inventory data using Python. You'll also read that data back out to confirm everything landed correctly.

In this step, get ready to:
  • Insert categories and items into your database using Python.
  • Read data back from the database and display it as a formatted table.
Insert categories and items

You need to insert data into categories first, then items. The order matters because each item references a category through the foreign key.

  • Create a new file called add_data.py in the same folder as your other scripts.
  • Add the following code to connect to your database and insert three categories:
import psycopg

with psycopg.connect("host=localhost dbname=inventory user=postgres password=your-password-here") as conn:
    with conn.cursor() as cur:
        # Define three categories for grouping items
        categories = [
            ("Electronics", "Gadgets, cables, and devices"),
            ("Books", "Physical books and textbooks"),
            ("Kitchen", "Cooking tools and appliances"),
        ]
        cur.executemany(
            "INSERT INTO categories (name, description) VALUES (%s, %s)",
            categories
        )

What does this code do?

The categories list holds tuples. Each tuple is one row of data matching the column order in the INSERT statement (name, description).

cur.executemany() runs the same INSERT statement once for each tuple in the list. This is more efficient than writing three separate cur.execute() calls.

  • Now add this code directly below the categories insertion (still inside the with conn.cursor() block) to insert six items:
        # Define inventory items linked to categories by ID
        items = [
            ("USB-C Cable", 5, 1, "Desk drawer"),
            ("Python Crash Course", 1, 2, "Bookshelf"),
            ("Wireless Mouse", 2, 1, "Office"),
            ("Cast Iron Skillet", 1, 3, "Cabinet"),
            ("HDMI Cable", 3, 1, "TV stand"),
            ("Clean Code", 1, 2, "Bookshelf"),
        ]
        cur.executemany(
            "INSERT INTO items (name, quantity, category_id, location) VALUES (%s, %s, %s, %s)",
            items
        )

What does this code do?

Each item tuple contains four values: name, quantity, category_id, and location.

The category_id values (1, 2, 3) match the auto-generated IDs from the categories you just inserted. That is how the foreign key links each item back to its category.

You won't see output yet. The next section adds a read query that confirms all the data was inserted.

Read and display all items

Inserting data is only half the story. You need to read it back to confirm everything landed in the right place.

  • Add this code below the items insertion, still inside the same with conn.cursor() block:
        # Save all changes to the database
        conn.commit()
        print("Data inserted successfully!")

        # Read back all items to confirm insertion worked
        cur.execute("SELECT id, name, quantity, location FROM items")
        rows = cur.fetchall()
        print("\nAll items in inventory:")
        print(f"{'ID':<4} {'Name':<25} {'Qty':<5} {'Location'}")
        print("-" * 50)
        for row in rows:
            print(f"{row[0]:<4} {row[1]:<25} {row[2]:<5} {row[3]}")

What does this code do?

conn.commit() saves all your INSERT operations permanently. Without it, the data disappears when the connection closes.

cur.fetchall() returns every row from the SELECT query as a list of tuples. The f-string formatting (:<4, :<25) left-aligns each value within a fixed-width column so the output looks like a table.

  • Save add_data.py.
Run and verify
  • Run your script by executing this command in the VS Code terminal:
python add_data.py

You should see a formatted table showing all six items with their IDs, names, quantities, and locations.

What should I see?

Your terminal output should start with "Data inserted successfully!" followed by a table listing six items. The IDs should be 1 through 6, auto-assigned by PostgreSQL's SERIAL column.

Seeing a UniqueViolation error?

This happens if you run the script more than once. The categories table has a UNIQUE constraint on the name column, so inserting "Electronics" a second time fails.

To fix this, open the SQL Shell (psql), connect to inventory with \c inventory, then run DELETE FROM items; DELETE FROM categories; to clear the tables. Then run your script again.

help me fix a UniqueViolation error when inserting data with psycopg

Seeing an authentication error?

Make sure the password in your connection string matches the one you set during PostgreSQL installation. Check the password=your-password-here part of the string.

help me fix a PostgreSQL authentication error in Python

✔️ Awesome, I've got everything!

Great. Double check you've saved add_data.py and that your terminal output shows all six items.

ⓧ I'd like to double check the full code

Here is the complete add_data.py file. Compare your code against it:

import psycopg

with psycopg.connect("host=localhost dbname=inventory user=postgres password=your-password-here") as conn:
    with conn.cursor() as cur:
        # Define three categories for grouping items
        categories = [
            ("Electronics", "Gadgets, cables, and devices"),
            ("Books", "Physical books and textbooks"),
            ("Kitchen", "Cooking tools and appliances"),
        ]
        cur.executemany(
            "INSERT INTO categories (name, description) VALUES (%s, %s)",
            categories
        )

        # Define inventory items linked to categories by ID
        items = [
            ("USB-C Cable", 5, 1, "Desk drawer"),
            ("Python Crash Course", 1, 2, "Bookshelf"),
            ("Wireless Mouse", 2, 1, "Office"),
            ("Cast Iron Skillet", 1, 3, "Cabinet"),
            ("HDMI Cable", 3, 1, "TV stand"),
            ("Clean Code", 1, 2, "Bookshelf"),
        ]
        cur.executemany(
            "INSERT INTO items (name, quantity, category_id, location) VALUES (%s, %s, %s, %s)",
            items
        )

        # Save all changes to the database
        conn.commit()
        print("Data inserted successfully!")

        # Read back all items to confirm insertion worked
        cur.execute("SELECT id, name, quantity, location FROM items")
        rows = cur.fetchall()
        print("\nAll items in inventory:")
        print(f"{'ID':<4} {'Name':<25} {'Qty':<5} {'Location'}")
        print("-" * 50)
        for row in rows:
            print(f"{row[0]:<4} {row[1]:<25} {row[2]:<5} {row[3]}")

Your database now has real data in it. Next up, you'll learn how to update existing records and delete items you no longer need.

Update and Delete Records

In the last step, you populated your database with categories and items. Your inventory now has real data to work with.

A real inventory changes constantly. Items get restocked, moved to different locations, or removed entirely. In this step, you'll write Python code that modifies and removes records using SQL's UPDATE and DELETE statements.

In this step, get ready to:
  • Update an item's quantity and another item's location.
  • Delete an item from the inventory.
  • Verify all changes by displaying the updated inventory.
Update existing records

The UPDATE statement changes values in rows that already exist. You target specific rows with a WHERE clause so only matching records are affected.

  • Create a new file called update_delete.py in your project folder and add the following code:
import psycopg

with psycopg.connect("host=localhost dbname=inventory user=postgres password=your-password-here") as conn:
    with conn.cursor() as cur:
        # Increase USB-C Cable quantity by 3 (simulating restocking)
        cur.execute(
            "UPDATE items SET quantity = quantity + %s WHERE name = %s",
            (3, "USB-C Cable")
        )
        print("Updated USB-C Cable quantity.")

        # Move Wireless Mouse to a new location
        cur.execute(
            "UPDATE items SET location = %s WHERE name = %s",
            ("Home office desk", "Wireless Mouse")
        )
        print("Updated Wireless Mouse location.")

What does this code do?

The first UPDATE uses quantity + %s to add 3 to the existing value rather than replacing it outright. This is useful for restocking without needing to know the current count.

The second UPDATE replaces the location value entirely. The WHERE name = %s clause ensures only the Wireless Mouse row is affected.

  • Save update_delete.py.

You won't run this file yet. The next two sections add the DELETE and a verification query before you execute everything together.

Delete a record

The DELETE statement removes entire rows from a table. Like UPDATE, it uses a WHERE clause to target specific records.

  • In update_delete.py, find the line that says print("Updated Wireless Mouse location."), then add the following code below it:
        # Remove HDMI Cable from inventory
        cur.execute("DELETE FROM items WHERE name = %s", ("HDMI Cable",))
        print("Deleted HDMI Cable from inventory.")

What does this code do?

This deletes the HDMI Cable row entirely from the items table. The tuple ("HDMI Cable",) has a trailing comma because Python needs it to recognize a single-element tuple.

Always use WHERE with DELETE

Running DELETE FROM items without a WHERE clause would delete every row in the table. Always double-check your filter before executing a DELETE.

  • Save update_delete.py.
Verify all changes

To confirm your modifications worked, you'll commit the changes and run a SELECT query that displays the full updated inventory.

  • In update_delete.py, find the line that says print("Deleted HDMI Cable from inventory."), then add the following code below it:
        # Save all changes to the database
        conn.commit()

        # Display the updated inventory
        cur.execute("SELECT name, quantity, location FROM items ORDER BY name")
        rows = cur.fetchall()
        print("\nUpdated inventory:")
        print(f"{'Name':<25} {'Qty':<5} {'Location'}")
        print("-" * 45)
        for row in rows:
            print(f"{row[0]:<25} {row[1]:<5} {row[2]}")

What does this code do?

conn.commit() makes your UPDATE and DELETE changes permanent in the database. Without it, the changes would be rolled back when the connection closes.

The SELECT query then fetches all remaining items sorted alphabetically so you can confirm the quantity changed, the location changed, and the deleted item is gone.

  • Save update_delete.py.
  • Run your script by entering this command in the VS Code terminal:
python update_delete.py

You should see confirmation messages for each operation, followed by a formatted table showing five items (not six). USB-C Cable should show a quantity of 8, Wireless Mouse should show "Home office desk" as its location, and HDMI Cable should be gone.

Seeing an error or unexpected output?

  • If you see a syntax error, check that the trailing comma exists in ("HDMI Cable",). Without it, Python passes a string instead of a tuple.
  • If quantities or locations look unchanged, make sure conn.commit() appears after all your execute calls but before the SELECT.
  • If you see HDMI Cable still in the results, check that your DELETE line uses the exact string "HDMI Cable" with matching capitalization.

Help me debug my update_delete.py script

✔️ Awesome, I've got everything!

Great. Double check you've saved update_delete.py and your terminal output matches the expected results.

ⓧ I'd like to double check the full code

Here is the complete update_delete.py file:

import psycopg

with psycopg.connect("host=localhost dbname=inventory user=postgres password=your-password-here") as conn:
    with conn.cursor() as cur:
        cur.execute(
            "UPDATE items SET quantity = quantity + %s WHERE name = %s",
            (3, "USB-C Cable")
        )
        print("Updated USB-C Cable quantity.")

        cur.execute(
            "UPDATE items SET location = %s WHERE name = %s",
            ("Home office desk", "Wireless Mouse")
        )
        print("Updated Wireless Mouse location.")

        cur.execute("DELETE FROM items WHERE name = %s", ("HDMI Cable",))
        print("Deleted HDMI Cable from inventory.")

        conn.commit()

        cur.execute("SELECT name, quantity, location FROM items ORDER BY name")
        rows = cur.fetchall()
        print("\nUpdated inventory:")
        print(f"{'Name':<25} {'Qty':<5} {'Location'}")
        print("-" * 45)
        for row in rows:
            print(f"{row[0]:<25} {row[1]:<5} {row[2]}")

You now have full CRUD (Create, Read, Update, Delete) capabilities. Next up, you'll write more powerful queries that join your two tables together and generate summary reports.

Query with Filters, JOINs, and Aggregates

Your inventory database now has clean, accurate data after the updates and deletions you ran in the previous step.

Right now, every query you have written pulls from a single table. But the real power of a relational database comes from combining tables and summarizing data. In this step, you will write queries that join your two tables together, filter rows by condition, and generate summary reports using aggregate functions.

In this step, get ready to:
  • JOIN the items and categories tables to display full category names.
  • Filter results with WHERE and sort with ORDER BY.
  • Generate a summary report using GROUP BY and aggregate functions.
JOIN tables to show items with category names

Your items table stores a category_id number, but that number alone does not tell you which category an item belongs to. A JOIN combines rows from two tables based on a related column, letting you display the actual category name alongside each item.

  • Create a new file called query_data.py in VS Code.
  • Add the following code to connect to your database and run a JOIN query:
import psycopg

with psycopg.connect("host=localhost dbname=inventory user=postgres password=your-password-here") as conn:
    with conn.cursor() as cur:
        print("=== Items by Category (JOIN) ===")
        cur.execute("""
            SELECT items.name, items.quantity, categories.name AS category, items.location
            FROM items
            JOIN categories ON items.category_id = categories.id
            ORDER BY categories.name, items.name
        """)
        rows = cur.fetchall()
        print(f"{'Item':<25} {'Qty':<5} {'Category':<15} {'Location'}")
        print("-" * 60)
        for row in rows:
            print(f"{row[0]:<25} {row[1]:<5} {row[2]:<15} {row[3]}")

What does this code do?

The JOIN categories ON items.category_id = categories.id clause matches each item row with the category row that shares the same ID. This is how relational databases link data across tables.

The ORDER BY categories.name, items.name sorts results first by category, then alphabetically within each category.

  • Replace your-password-here with your postgres password.
  • Save query_data.py.
  • Run the script by entering this command in your VS Code terminal:
python query_data.py

You should see a formatted table showing all five items with their category names (Books, Electronics, Kitchen) instead of raw ID numbers.

Seeing a column does not exist error?

Make sure you typed categories.name (not category.name). The table name is plural: categories.

Also check that the ON clause uses items.category_id = categories.id with the correct column names from each table.

Help me debug my JOIN query

Why use JOIN instead of just storing the category name directly?

Storing a foreign key (the ID) instead of duplicating the category name in every row prevents inconsistencies. If you rename a category, you change it in one place and every item automatically reflects the update.

Filter with WHERE and sort with ORDER BY

A WHERE clause filters rows so only those matching a condition are returned. Combined with ORDER BY, you can ask precise questions like "which items do I have more than one of, ranked by quantity?"

  • In query_data.py, add the following code below the last print line of your JOIN query (still inside the with conn.cursor() as cur: block):
        print("\n=== Items with Multiple Units (WHERE) ===")
        cur.execute("""
            SELECT name, quantity, location
            FROM items
            WHERE quantity > 1
            ORDER BY quantity DESC
        """)
        rows = cur.fetchall()
        for row in rows:
            print(f"  {row[0]} - {row[1]} units at {row[2]}")

What does this query do?

The WHERE quantity > 1 clause excludes any items with just one unit. ORDER BY quantity DESC sorts the remaining items from highest quantity to lowest.

  • Save query_data.py and run it again:
python query_data.py

Below the JOIN output, you should now see a second section showing USB-C Cable (8 units) and Wireless Mouse (2 units).

Only seeing the JOIN results?

Check that the new code is indented to the same level as the JOIN query above it. It must be inside the with conn.cursor() as cur: block (8 spaces of indentation for the print and cur.execute lines).

Help me fix my WHERE query placement

Aggregate with GROUP BY

Aggregate functions like COUNT() and SUM() collapse multiple rows into a single summary value. Combined with GROUP BY, they produce per-group summaries. This is how you answer questions like "how many items are in each category?"

  • In query_data.py, add this final query block below the WHERE query (still inside the cursor block):
        print("\n=== Inventory Summary (GROUP BY) ===")
        cur.execute("""
            SELECT categories.name, COUNT(items.id) AS item_count, SUM(items.quantity) AS total_units
            FROM categories
            LEFT JOIN items ON categories.id = items.category_id
            GROUP BY categories.name
            ORDER BY total_units DESC
        """)
        rows = cur.fetchall()
        print(f"{'Category':<15} {'Items':<8} {'Total Units'}")
        print("-" * 35)
        for row in rows:
            print(f"{row[0]:<15} {row[1]:<8} {row[2]}")

What does this query do?

COUNT(items.id) counts how many items exist in each category. SUM(items.quantity) adds up all the quantities per category to give a total unit count.

The query uses LEFT JOIN instead of a regular JOIN so that categories with zero items still appear in the results. GROUP BY categories.name tells PostgreSQL to produce one summary row per category.

  • Save query_data.py and run it one final time:
python query_data.py

You should now see three sections of output: the JOIN table, the filtered items, and the summary report showing Electronics (2 items, 10 total units), Books (2 items, 2 total units), and Kitchen (1 item, 1 total unit).

Seeing NULL values in the summary?

If a category shows NULL for item_count or total_units, double check that the LEFT JOIN condition uses categories.id = items.category_id (not the other way around for the FROM table).

Also confirm you ran add_data.py and update_delete.py in earlier steps. The summary depends on the data those scripts inserted and modified.

Help me debug my GROUP BY query

✔️ Awesome, I've got everything!

Great. Double check you have saved query_data.py with all three query sections.

ⓧ I'd like to double check the full code

Here is the complete query_data.py file. Compare it against yours:

import psycopg

with psycopg.connect("host=localhost dbname=inventory user=postgres password=your-password-here") as conn:
    with conn.cursor() as cur:
        print("=== Items by Category (JOIN) ===")
        cur.execute("""
            SELECT items.name, items.quantity, categories.name AS category, items.location
            FROM items
            JOIN categories ON items.category_id = categories.id
            ORDER BY categories.name, items.name
        """)
        rows = cur.fetchall()
        print(f"{'Item':<25} {'Qty':<5} {'Category':<15} {'Location'}")
        print("-" * 60)
        for row in rows:
            print(f"{row[0]:<25} {row[1]:<5} {row[2]:<15} {row[3]}")

        print("\n=== Items with Multiple Units (WHERE) ===")
        cur.execute("""
            SELECT name, quantity, location
            FROM items
            WHERE quantity > 1
            ORDER BY quantity DESC
        """)
        rows = cur.fetchall()
        for row in rows:
            print(f"  {row[0]} - {row[1]} units at {row[2]}")

        print("\n=== Inventory Summary (GROUP BY) ===")
        cur.execute("""
            SELECT categories.name, COUNT(items.id) AS item_count, SUM(items.quantity) AS total_units
            FROM categories
            LEFT JOIN items ON categories.id = items.category_id
            GROUP BY categories.name
            ORDER BY total_units DESC
        """)
        rows = cur.fetchall()
        print(f"{'Category':<15} {'Items':<8} {'Total Units'}")
        print("-" * 35)
        for row in rows:
            print(f"{row[0]:<15} {row[1]:<8} {row[2]}")

You have gone from raw IDs to rich, readable reports that combine, filter, and summarize your inventory data. Next up, you will take on a Secret Mission to build a low stock alert system on top of what you have built.

Secret mission

Low Stock Alerts

Your inventory tracks what you have. But how do you know when something is running low? Build a low stock alert system that flags items below a minimum threshold you define.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely on your local machine, so there are no ongoing cloud costs. Decide whether to keep your resources for future practice, pause them, or delete everything.

Resources you used:

  • PostgreSQL 17 installation (server, pgAdmin 4, command line tools).
  • The inventory database and its tables (categories, items).
  • Python scripts (test_connection.py, create_tables.py, add_data.py, update_delete.py, query_data.py).
  • The psycopg Python package.

Keep everything running

No action needed. Choose this if you plan to keep practising SQL or want to build more features on top of your inventory database.

  • PostgreSQL continues running as a Windows service in the background.
  • Your inventory database and all its data remain intact.
  • Your Python scripts are ready to run again anytime.

Pause - I'll come back to this later

Stop the PostgreSQL service to free up memory. Your data stays on disk and is ready when you restart the service.

  • Press the Windows key and type Services. Open the Services app.
  • Find postgresql-x64-17 in the list.
  • Right-click it and select Stop.
  • To start it again later, right-click the same service and select Start.

Delete - I don't want to use this again

Remove all project resources and the PostgreSQL installation. Follow each step below.

  • Open the SQL Shell (psql) from your Start menu. Press Enter to accept defaults and enter your password.
  • Drop the inventory database by running:
DROP DATABASE inventory;
  • Type \q to exit psql.
  • Uninstall the psycopg package by running this in your VS Code terminal:
pip uninstall psycopg
  • Delete your project Python files. In your VS Code terminal, navigate to the folder containing your scripts and run:
del test_connection.py create_tables.py add_data.py update_delete.py query_data.py
  • Uninstall PostgreSQL. Open the Windows Control Panel.
  • Select Uninstall or Change a Program.
  • Find PostgreSQL 17 in the list, right-click it, and select Uninstall/Change.
  • Select Entire application and click Next. Follow the prompts to complete the removal.

What about the data directory?

The PostgreSQL uninstaller leaves the data directory intact to prevent accidental data loss. Since you already dropped the inventory database, this directory only contains system files.

You can safely delete the C:\Program Files\PostgreSQL\17\data folder after uninstalling if you want to reclaim disk space.

Nice Work!

Nice Work!

You built a working inventory system powered by PostgreSQL and Python.

You've learned how to:

  • Designed a relational schema with two tables linked by a foreign key.
  • Written Python scripts that perform full CRUD operations (Create, Read, Update, Delete) against a live database.
  • Queried your data with JOINs across tables, WHERE filters, ORDER BY sorting, and GROUP BY aggregate functions.
  • Secret Mission: Added a low stock alert system using ALTER TABLE and conditional queries.

Ready to quiz yourself?