ShopEasy: Web Scraping Data Pipeline & Price Intelligence Analytics

A production-grade ETL pipeline built for ShopEasy Retail Intelligence that automates data collection from books.toscrape.com, transforms it into clean, structured records, and loads it into a PostgreSQL database to enable pricing analytics and market trend monitoring.

Built for ShopEasy Retail Intelligence, this project replaces manual, inconsistent data collection with an automated pipeline that scrapes product pricing data from books.toscrape.com, cleans and loads it into PostgreSQL, and surfaces actionable pricing insights through SQL analytics — enabling faster, more reliable market decisions.

ShopEasy-Scraper-ETL-Pipeline/
├── python/
│   ├── __init__.py
│   ├── config.py          # Database credentials and scraper settings
│   ├── logger.py          # Logging setup (console + file)
│   ├── extract_books.py   # Scrapes books.toscrape.com in batches
│   ├── transform.py       # Cleans prices, ratings, removes bad rows
│   ├── load.py            # Inserts cleaned data into PostgreSQL
│   └── pipeline.py        # Runs all three steps in order
│
├── sql/
│   ├── create_table.py    # Run once to set up the database table
│   └── analytics.sql      # Price intelligence queries
│
├── data/
│   └── logs/
│       ├── app.log        # Pipeline activity log (auto-created)
│       └── scheduler.log  # Scheduled run history (auto-created)
│
├── run_pipeline.bat       # Windows Task Scheduler automation script
├── .env                   # Your database credentials (never commit this)
└── requirements.txt

• Python 3.11+
• PostgreSQL 14+

git clone https://github.com/samuelede/ShopEasy-Scraper-ETL-Pipeline.git
cd ShopEasy-Scraper-ETL-Pipeline

python -m venv venv

# macOS / Linux
source venv/bin/activate

# Windows
venv\Scripts\activate

pip install -r requirements.txt

Create a .env file in the project root:

DB_HOST=localhost
DB_PORT=5432
DB_NAME=shopeasy
DB_USER=postgres
DB_PASSWORD=your_password_here

SCRAPE_DELAY=1.5

Make sure the shopeasy database exists in PostgreSQL first, then run:

python sql/create_table.py

You should see:

Schema and table created successfully.

python python/pipeline.py

The pipeline scrapes and saves in batches of 20 books at a time:

Starting ETL Pipeline...
Found 50 categories

--- Batch 1 (20 books scraped) ---
    Cleaned: 20 records
    Saved to DB. Running total: 20 books

--- Batch 2 (20 books scraped) ---
    Cleaned: 20 records
    Saved to DB. Running total: 40 books

For a quick test run (~40 books), open pipeline.py and change:

extract_books_batched(batch_size=20, max_pages=2)

To stop the pipeline at any time: Ctrl + C

The run_pipeline.bat file automates the pipeline on a schedule without any manual intervention. Each run is logged to data/logs/scheduler.log.

Double-click run_pipeline.bat or run from terminal:

run_pipeline.bat

Confirm it completes and check data/logs/scheduler.log shows:

[DD/MM/YYYY HH:MM:SS] Pipeline starting...
[DD/MM/YYYY HH:MM:SS] Pipeline completed successfully.

Press Win + S and search for Task Scheduler, then open it.

Click Create Basic Task in the right panel and follow these steps:

Click Finish. Your pipeline will now run automatically every day at the time you set. To verify, find ShopEasy Pipeline in the Task Scheduler library and check the Next Run Time column.

type data\logs\scheduler.log

You will see a timestamped entry for every run:

[10/05/2026 06:00:01] Pipeline starting...
[10/05/2026 06:04:22] Pipeline completed successfully.
[11/05/2026 06:00:01] Pipeline starting...
[11/05/2026 06:04:19] Pipeline completed successfully.

The pipeline is safe to re-run. It will not create duplicate records.

• ON CONFLICT (product_id, source) DO NOTHING in load.py skips any book already in the database
• drop_duplicates() in transform.py removes duplicates within each batch

To also update existing records with fresh prices on each run:

# In load.py, change DO NOTHING to DO UPDATE:
ON CONFLICT (product_id, source) DO UPDATE SET
    price      = EXCLUDED.price,
    rating     = EXCLUDED.rating,
    in_stock   = EXCLUDED.in_stock,
    scraped_at = EXCLUDED.scraped_at

Once data has been loaded, run the full analytics script:

psql -U postgres -d shopeasy -f sql/analytics.sql

If you see this error on any query:

ERROR: character with byte sequence 0xc2 0x80 in encoding "UTF8"
has no equivalent in encoding "WIN1252"

This is a Windows terminal encoding issue. Fix it by setting the encoding inside psql before running the file:

psql -U postgres -d shopeasy

Then inside psql run these two commands:

\encoding UTF8
\i sql/analytics.sql

OR run once using this command:

psql -U postgres -d shopeasy -c "SET client_encoding TO 'UTF8';" -f sql/analytics.sql

The \encoding UTF8 command tells psql to read the file as UTF-8 instead of trying to convert it to Windows encoding. All queries will then run cleanly.

Alternatively, set the terminal encoding before connecting:

chcp 65001
psql -U postgres -d shopeasy -f sql/analytics.sql

To run a single query, open pgAdmin, paste it into the Query Tool and press F5.

Database : shopeasy
Schema   : books
Table    : books.books

CREATE TABLE books.books (
    product_id   VARCHAR      NOT NULL,
    product_name VARCHAR      NOT NULL,
    category     VARCHAR,
    price        FLOAT,
    rating       SMALLINT,
    in_stock     BOOLEAN DEFAULT TRUE,
    source       VARCHAR      NOT NULL,
    scraped_at   TIMESTAMPTZ  DEFAULT NOW(),

    PRIMARY KEY (product_id, source)
);

• Checks robots.txt before starting
• Discovers all 50 categories from the homepage sidebar
• Paginates through every listing page
• Visits each book's detail page to collect full data
• Yields batches as it goes rather than waiting until the end
• Waits SCRAPE_DELAY seconds between requests

• Strips £ symbols from prices and casts to float
• Converts word-based ratings ("Three") to integers (3)
• Converts availability strings to True/False
• Drops rows with unparseable prices
• Removes duplicates within each batch

• Inserts each batch into books.books immediately after transformation
• Uses ON CONFLICT DO NOTHING so re-running won't create duplicates

selenium
beautifulsoup4
requests
pandas
webdriver-manager
python-dotenv
sqlalchemy
psycopg2-binary

• The .env file should be added to .gitignore — never commit credentials
• books.toscrape.com is built specifically for scraping practice — no real data, no restrictions in robots.txt
• The price change over time query (Query 4 in analytics) becomes meaningful after the pipeline has run on at least two separate days