Scratch ticket data,
automated end to end.
A Python scraper that pulls prize and retailer winner data from every active Texas Lottery scratch-off daily, loads it into a local PostgreSQL database, and surfaces it for analysis in DBeaver - replacing a manual copy-paste workflow that used to take hours.
Manual scraping at scale doesn't scale.
The Texas Lottery publishes prize and retailer data for every active scratch-off ticket across nine price points - $1 through $100. The core question worth answering: which retailers consistently sell the most top-prize winners? Getting there required visiting each ticket's page one by one.
Before
- Navigate to each denomination manually
- Click into every active ticket
- Copy prize table to spreadsheet
- Repeat for retailer winner page
- Save as CSV, import into DBeaver
- Run queries, repeat next week
After
- Cron job fires at 6 AM daily
- All 80+ tickets scraped automatically
- Prize and retailer data upserted
- New tables ready in DBeaver
- Historical backfill via uID estimator
- SQL queries answer questions instantly
Five steps from website to database.
The scraper runs as a single Python process. Each step feeds the next, with polite request delays to avoid hammering the server.
Index scan
Discover all active tickets
A single GET to the scratch-offs index page extracts every ticket uID and maps it to its denomination. The uID is the key to everything - it appears in both the detail URL and the retailer winner URL.
Detail page
Pull prize tables per ticket
For each uID, the detail page is fetched and parsed: game number, game name, prize amounts, prizes in game, and prizes claimed are extracted from the HTML table and written to scratch_prizes.
Retailer page
Recover retailer winner records
The retailer winner page is accessed directly by uID - no waiting for the site's hyperlink trigger. Every record includes date claimed, retailer name, address, city, zip, pack number, and ticket number.
Upsert
Write to PostgreSQL without duplicates
All three tables use ON CONFLICT DO NOTHING or DO UPDATE so daily re-runs are safe. Prize counts update in place; retailer records accumulate.
Schedule
Cron fires it every morning
A single crontab entry runs the script at 6 AM daily, logging output to a file. DBeaver connects to the same local PostgreSQL instance - refresh the connection and the data is there.
Three tables, one join to answer the core question.
The schema is normalized around the ticket uID. Joining scratch_tickets to scratch_retailer_winners on uid gives you retailer win counts by denomination in a single query.
Historical backfill via uID estimation
A companion script recovers data from retired games. Game numbers and uIDs move together but not in a fixed ratio - so the historical scanner uses piecewise linear interpolation across known anchor pairs to estimate a center uID, then probes a ±50 window around it.
game 2430 → uid 252701566
game 2669 → uid 252699542
# estimate for any target game number
t = (target − g1) / (g2 − g1)
uid_estimate = int(u1 + t × (u2 − u1))
probe_range = uid_estimate ± 50
404s return instantly, so probing 100 uIDs per game number is fast. Runs resume with --resume using a probe log table that tracks already-confirmed misses.
Questions the data can now answer.
Eight SQL queries ship with the project, covering the full range from simple leaderboards to prize-odds modeling.
Top retailers by total top prizes sold
All-time leaderboard across every game and denomination
Top retailers filtered by denomination
Which $5 ticket stores sell winners most often
Retailers by city
Regional breakdown of winning retailer concentration
Retailers across the most distinct games
Identifies consistent performers, not single-game flukes
Games with most unclaimed top prizes
Surfaces tickets with the best remaining odds by prize tier
Claim rate by denomination
How much of each price point's prize pool has been claimed
Recently active retailers (last 30 days)
Rolling window of current hot-streak locations
Full detail view
Three-table join for flat export or ad hoc inspection