PILY CT
ALL PROJECTS

Quotes Scraper.

A Python ETL that scrapes 100 quotes and 50 author bios from Quotes to Scrape, joins them and loads one PostgreSQL table, with mocked unit tests.

ROLE
Data Engineer
TEAM
Solo
DATE
Jul 2024 to Aug 2024
TRACK
Data
STACK
Python, Requests, BeautifulSoup, pandas, SQLAlchemy, PostgreSQL, pytest

FIG. 03A ARCHITECTURE

  • Quote pages /page/N, external. Connects to Requests + BeautifulSoup (HTML).
  • Author pages /author/slug, external. Connects to Requests + BeautifulSoup (HTML).
  • Requests + BeautifulSoup, backend. Connects to pandas DataFrame (DICTS). See note 3.
  • pandas DataFrame, backend. Connects to PostgreSQL (TO_SQL). See note 2.
  • PostgreSQL, storage. See note 1.

NOTES

  1. Decision

    I loaded everything into one flat table with a full refresh (to_sql, if_exists='replace'). The source is small and static, so re-running the pipeline is idempotent and needs no merge logic.

  2. Trade-off

    The table is denormalized. Tags are a comma-separated string and author bios repeat on every quote, so filtering by tag means parsing strings, and each run overwrites the last with no history.

  3. What I got wrong

    I fetched the author page once per quote, which is 111 requests for 11 pages and 50 authors when 61 would do. I also rebuilt author URLs from names with unidecode instead of following the page's own (about) link. A dict cache plus the real href fixes both.

RESULTS

QUOTES LOADED PER RUN
100
AUTHOR BIOS JOINED IN
50
UNIT TESTS, HTTP AND DB MOCKED
5
NEXT PROJECTEVAPP

PROJECT 06 / 08