Do I need Postgres, or is SQLite enough for an odds database?

SQLite handles a single-writer odds poller comfortably into the tens of millions of rows. It is one file, zero setup, and ships with Python. Move to Postgres only when you need concurrent writers or a shared server. The schema and queries port over with almost no change.

How far back does OddsPapi free historical odds data go?

The /historical-odds endpoint returns the recorded price history for a fixture, which on a sample MLB game meant thousands of exchange price points and dozens of sportsbook points spanning the days before the match. Seed it once into your own database and that history is yours permanently, independent of provider retention.

How do I store more than three bookmakers of historical odds?

The /historical-odds endpoint caps at three bookmakers per call. Loop the call with different bookmaker groups and merge into the same table. INSERT OR IGNORE plus change-only logic dedupes automatically, so overlapping calls never create duplicate rows.

Can I store player props and Asian handicaps in the same database?

Yes. Drop the markets filter to capture every market on the fixture, and add a player_id column for props, since prop outcomes key the players dict by player id rather than the string zero. Look up market and outcome names from /markets?sportId=X and store them alongside the ids.

Build an Odds Database in Python: Store Live & Historical Odds in SQLite (Free API)

Q: Why store only price changes instead of every poll?

Bookmakers repeat the same price on most polls. Writing every poll bloats the table and slows queries without adding information. Change-only inserts keep one row per actual move, which is all a backtest, CLV audit, or line-movement chart needs.

Build an Odds Database in Python - OddsPapi API Blog

How To Guides July 29, 2026

Your odds API hands you a live snapshot. It does not hand you a memory. Refresh the endpoint and yesterday’s price is gone, the opening line you wanted for closing-line value never got recorded, and you cannot run GROUP BY against a JSON blob. Every backtest, every steam-move alert, every CLV audit starts with the same missing piece: your own historical store.

This guide builds that store. You will stand up a SQLite database, capture live odds from 350+ bookmakers into it, seed it with real price history from the free /historical-odds endpoint, and query line movement with plain SQL. No Postgres server, no ORM, no paid data vendor. Every code block below ran against the live OddsPapi API on a real MLB game before it went into this post.

Why a JSON feed is not a database

Most odds tutorials stop at “here is the price right now.” That works until you need to answer a question about the past. When did Pinnacle move off the opening number? Which book had the best closing price? What was the vig at the moment you placed the bet? A live feed cannot answer any of those, because it forgets everything the instant you make the next call.

Two common workarounds both fall short. Dumping each pull to a CSV file gives you a folder full of timestamps you cannot join or filter. Re-querying the historical endpoint every time you need old data burns calls and stays capped by whatever retention the provider offers. A local database fixes both: you own the data, you dedupe it, and you query it in milliseconds.

The old way	OddsPapi to SQLite
One CSV per pull, no way to join across time	One indexed table, query any window with SQL
Re-fetch history on every backtest run	Seed once from free `/historical-odds`, then read locally
Store every duplicate price the feed repeats	Change-only inserts keep the table lean
History window controlled by the vendor	Your archive grows forever on disk
No opening line, so no closing-line value	Opening and latest price per book in one query

The database is the boring infrastructure that every other tool sits on top of. Once it exists, your model backtester, your steam-move detector, and your CLV tracker all read from the same place instead of hammering the API.

Step 1: Get a key and confirm the feed

Grab a free API key. Authentication is a query parameter, not a header. One call to /sports confirms you are wired up.

import requests

API_KEY = "YOUR_API_KEY"
BASE_URL = "https://api.oddspapi.io/v4"

def api_get(path, **params):
    params["apiKey"] = API_KEY          # apiKey is a query param, never a header
    r = requests.get(f"{BASE_URL}{path}", params=params, timeout=30)
    r.raise_for_status()
    return r.json()

print(len(api_get("/sports")), "sports")          # 69
print(len(api_get("/bookmakers")), "bookmakers")   # 381

That returned 69 sports and 381 bookmakers on the live feed the day this was written. Those 381 books include sharps (Pinnacle, SBOBet), exchanges (Betfair, Polymarket, Kalshi), and every major US retail book, all keyed by the same schema you are about to store.

Step 2: Design the schema

Two tables are enough. One row per fixture, one row per price observation. The observation table carries a natural time series: the same (fixture, bookmaker, market, outcome) tuple appears many times, once per recorded_at timestamp.

import sqlite3

SCHEMA = """
CREATE TABLE IF NOT EXISTS fixtures (
    fixture_id   TEXT PRIMARY KEY,
    sport_id     INTEGER,
    home         TEXT,
    away         TEXT,
    start_time   TEXT
);
CREATE TABLE IF NOT EXISTS odds_snapshots (
    fixture_id   TEXT,
    bookmaker    TEXT,
    market_id    INTEGER,
    outcome_id   INTEGER,
    price        REAL,
    recorded_at  TEXT,
    UNIQUE(fixture_id, bookmaker, market_id, outcome_id, recorded_at)
);
CREATE INDEX IF NOT EXISTS idx_snap
    ON odds_snapshots(fixture_id, bookmaker, market_id, outcome_id, recorded_at);
"""

def get_conn(path="odds.db"):
    conn = sqlite3.connect(path)
    conn.executescript(SCHEMA)
    return conn

The UNIQUE constraint is doing real work. It lets you insert with INSERT OR IGNORE and never worry about writing the same price at the same timestamp twice. Store decimal odds as REAL; the feed already hands you decimal, American, and fractional formats, so you can add columns for those later without a converter.

Populate the fixtures table

Fixture names live on the /fixtures endpoint, not /odds. The odds response carries participant IDs only. Pull the schedule for a date range (max 10 days apart) and store the rows you care about.

def store_fixtures(conn, sport_id, date_from, date_to):
    fixtures = api_get("/fixtures", sportId=sport_id,
                       **{"from": date_from, "to": date_to})
    for fx in fixtures:
        if not fx.get("hasOdds"):        # only fixtures with a priced market
            continue
        conn.execute(
            "INSERT OR REPLACE INTO fixtures VALUES (?,?,?,?,?)",
            (fx["fixtureId"], fx.get("sportId"),
             fx.get("participant1Name"), fx.get("participant2Name"),
             fx.get("startTime")))
    conn.commit()

conn = get_conn()
store_fixtures(conn, 13, "2026-07-17", "2026-07-18")   # MLB, sportId 13

Only fixtures flagged hasOdds: true return a real odds payload, so filtering here saves you empty calls later. The worked example below runs on New York Yankees vs Los Angeles Dodgers, fixture id1300010963303845, which had 17 books on the board.

Step 3: Capture a live snapshot

The /odds response is deeply nested: bookmakerOdds → slug → markets → market_id → outcomes → outcome_id → players → "0" → price. Walk it into flat rows. Two defensive filters matter here, and both come from real payloads, not theory.

from datetime import datetime, timezone

def capture_live(conn, fixture_id, markets=("131",)):
    data = api_get("/odds", fixtureId=fixture_id)
    now = datetime.now(timezone.utc).isoformat()
    rows = []
    for slug, bookdata in data.get("bookmakerOdds", {}).items():
        for mid, market in bookdata.get("markets", {}).items():
            if markets and mid not in markets:      # keep the DB focused
                continue
            for oid, outcome in market.get("outcomes", {}).items():
                node = outcome.get("players", {}).get("0")
                if not node:                        # player-prop lineups are keyed by
                    continue                        # player id, not "0" -- skip them here
                price = node.get("price")
                if outcome.get("active") is False or not price:
                    continue                        # see the two gotchas below
                rows.append((fixture_id, slug, int(mid), int(oid), price, now))
    conn.executemany(
        "INSERT OR IGNORE INTO odds_snapshots VALUES (?,?,?,?,?,?)", rows)
    conn.commit()
    return len(rows)

Two gotchas the feed will throw at you:

The active flag is not always true. On a live pre-game pull of this fixture, every outcome shipped active: null alongside a perfectly valid price of 2.01. Filter on active is False, not on truthy active, or you will silently drop good prices and write an empty snapshot.
The players key is "0" for game lines only. On player-prop markets it is keyed by player id (like "1410115") with a playerName on each node. The node check above skips those so a game-line capture does not choke on a prop market.

Running that captured 26 rows in one call: 13 books that priced the moneyline (market 131), two outcomes each. Outcome 131 is the Yankees, 132 the Dodgers. Pinnacle sat at 2.01 / 1.917, Kalshi at 2.041, DraftKings at 1.95 / 1.87.

Step 4: Store only what changed

A book repeats the same price on most polls. If you write every poll you will 10x your row count for nothing. Insert a new row only when the price differs from the last one you stored for that outcome. This mirrors how the feed’s own changedAt field works, and it is the difference between a database and a log file.

def insert_if_changed(conn, fixture_id, slug, mid, oid, price, recorded_at):
    cur = conn.execute(
        "SELECT price FROM odds_snapshots "
        "WHERE fixture_id=? AND bookmaker=? AND market_id=? AND outcome_id=? "
        "ORDER BY recorded_at DESC LIMIT 1",
        (fixture_id, slug, mid, oid))
    last = cur.fetchone()
    if last and last[0] == price:
        return False                     # unchanged, skip
    conn.execute("INSERT OR IGNORE INTO odds_snapshots VALUES (?,?,?,?,?,?)",
                 (fixture_id, slug, mid, oid, price, recorded_at))
    return True

Change-only storage matters most on exchanges. When you seed this fixture in the next step, Kalshi alone returns 20,869 price points on the Yankees moneyline and 77,671 on the Dodgers side, because an order-book exchange re-quotes constantly. A sportsbook like Pinnacle returns 65 over the same window. Deduping on change turns the exchange firehose into the handful of moves you actually care about.

Step 5: Seed real history for free

Here is the kill shot most providers charge for. The /historical-odds endpoint hands back the full price history for a fixture at no cost on the free tier. Its shape differs from the live endpoint in one important way: the top-level key is bookmakers (not bookmakerOdds), and players["0"] is a list of snapshots, not a single dict. It accepts a maximum of three bookmakers per call, so loop if you want more.

def seed_history(conn, fixture_id, bookmakers, markets=("131",)):
    data = api_get("/historical-odds", fixtureId=fixture_id,
                   bookmakers=",".join(bookmakers))     # max 3 per call
    inserted = 0
    for slug, bookdata in data.get("bookmakers", {}).items():
        for mid, market in bookdata.get("markets", {}).items():
            if markets and mid not in markets:
                continue
            for oid, outcome in market.get("outcomes", {}).items():
                for snap in outcome.get("players", {}).get("0", []):   # a LIST
                    if snap.get("active") is False or not snap.get("price"):
                        continue
                    if insert_if_changed(conn, fixture_id, slug, int(mid),
                                         int(oid), snap["price"], snap["createdAt"]):
                        inserted += 1
    conn.commit()
    return inserted

seed_history(conn, "id1300010963303845", ["pinnacle", "draftkings", "fanduel"])

Each snapshot carries createdAt, price, limit, and active. After change-only deduping, Pinnacle’s 65 raw points collapse to 66 distinct price moves once merged with your live capture, DraftKings to 16, FanDuel to 10. Your database now holds a real time series that stretches back before you ever started polling.

Step 6: Query the database

This is the payoff. Every question a live feed cannot answer is now one SQL statement. These use window functions, so you need SQLite 3.25 or newer (any Python from 2019 onward ships it).

Line movement for one book

SELECT recorded_at, price
FROM odds_snapshots
WHERE fixture_id = 'id1300010963303845'
  AND bookmaker = 'pinnacle' AND market_id = 131 AND outcome_id = 131
ORDER BY recorded_at;

That returns 66 stored points tracing Pinnacle’s Yankees price from 2.0 at the open on July 16 to 2.01 near game time, with every wiggle in between preserved.

Opening vs latest price per book

Closing-line value needs the first and last price you observed. One query, no application code:

WITH ordered AS (
  SELECT bookmaker, price, recorded_at,
         ROW_NUMBER() OVER (PARTITION BY bookmaker ORDER BY recorded_at)      AS r_open,
         ROW_NUMBER() OVER (PARTITION BY bookmaker ORDER BY recorded_at DESC) AS r_last
  FROM odds_snapshots
  WHERE fixture_id = 'id1300010963303845' AND market_id = 131 AND outcome_id = 131)
SELECT o.bookmaker, o.price AS open_price, l.price AS latest_price
FROM ordered o JOIN ordered l ON o.bookmaker = l.bookmaker
WHERE o.r_open = 1 AND l.r_last = 1
ORDER BY o.bookmaker;

Bookmaker	Open (NYY)	Latest (NYY)
draftkings	1.90	1.95
fanduel	1.98	1.94
pinnacle	2.00	2.01

DraftKings drifted toward the Yankees, FanDuel away from them. That divergence is exactly the signal a CLV audit or a steam detector reads off your stored opening line.

Best price per outcome (line shopping from disk)

WITH latest AS (
  SELECT bookmaker, outcome_id, price,
         ROW_NUMBER() OVER (PARTITION BY bookmaker, outcome_id
                            ORDER BY recorded_at DESC) AS rn
  FROM odds_snapshots
  WHERE fixture_id = 'id1300010963303845' AND market_id = 131)
SELECT outcome_id, MAX(price) AS best_price
FROM latest WHERE rn = 1 GROUP BY outcome_id;

Across the stored books, the best available Yankees price was 2.041 (Kalshi) and the best Dodgers price 1.961 (Polymarket). Both exchanges beat every retail book on this game. That is the best price on the board, which is a shopping result, not a value claim.

De-vig the sharp line to a fair probability

WITH latest AS (
  SELECT outcome_id, price,
         ROW_NUMBER() OVER (PARTITION BY outcome_id ORDER BY recorded_at DESC) rn
  FROM odds_snapshots
  WHERE fixture_id = 'id1300010963303845'
    AND bookmaker = 'pinnacle' AND market_id = 131)
SELECT outcome_id, price FROM latest WHERE rn = 1;

# Pinnacle latest: NYY 2.01, LAD 1.917
imp = {131: 1/2.01, 132: 1/1.917}
total = sum(imp.values())
print(round((total - 1) * 100, 2), "% vig")            # 1.92 %
for oid, p in imp.items():
    print(oid, "fair prob", round(p/total, 4), "fair odds", round(total/p, 4))
# 131 fair prob 0.4882 fair odds 2.0485
# 132 fair prob 0.5118 fair odds 1.9537

Pinnacle’s moneyline carried a 1.92% margin. Stripping it out puts the fair Yankees price at 2.0485 and the fair Dodgers price at 1.9537. Because you stored the whole time series, you can rerun this de-vig at any timestamp and watch the fair probability move, not just read it once and lose it.

Step 7: Run it on a schedule

The capture function is the whole loop. Point it at every fixture with hasOdds, sleep to respect the roughly 0.88s per-endpoint cooldown on the free tier, and let it run under cron.

import time

def poll_once(conn, sport_id, date_from, date_to, markets=("131",)):
    store_fixtures(conn, sport_id, date_from, date_to)
    fixture_ids = [r[0] for r in conn.execute(
        "SELECT fixture_id FROM fixtures WHERE sport_id=?", (sport_id,))]
    total = 0
    for fid in fixture_ids:
        try:
            total += capture_live(conn, fid, markets)
        except requests.HTTPError:
            pass                          # fixture may have closed; keep going
        time.sleep(1.0)                   # stay under the endpoint cooldown
    return total

# cron: */5 * * * *  python poll.py
conn = get_conn()
print(poll_once(conn, 13, "2026-07-17", "2026-07-18"), "rows this pass")

A five-minute cron interval is plenty for pre-game markets. If you need sub-second capture for live in-play trading, polling is the wrong tool: switch the capture step to the real-time WebSocket feed, which pushes every change instead of making you ask. The database schema and the change-only insert stay exactly the same; only the source of the price changes.

From here the same tables feed everything else. Load a slice into pandas for analysis (live odds to DataFrame), export a fixture to a spreadsheet (CSV and Excel export), or point your model at it. You built the memory your API was missing.

Frequently asked questions

Do I need Postgres, or is SQLite enough?

SQLite handles a single-writer odds poller comfortably into the tens of millions of rows. It is one file, zero setup, and ships with Python. Move to Postgres when you need concurrent writers or a shared server, not before. The schema and queries in this guide port over with almost no change.

How far back does the free historical data go?

The /historical-odds endpoint returns the recorded price history for a fixture, which on this MLB game meant thousands of exchange points and dozens of sportsbook points spanning the days before the match. Seed it once into your own database and that history is yours permanently, independent of any provider retention window.

Why store only price changes instead of every poll?

Books repeat the same price on most polls. Writing every poll bloats the table and slows every query without adding information. Change-only inserts keep one row per actual move, which is all a backtest, a CLV audit, or a line-movement chart needs.

How do I store more than three bookmakers of history?

The historical endpoint caps at three bookmakers per call. Loop the call with different bookmaker groups and merge the results into the same table. The INSERT OR IGNORE plus change-only logic dedupes automatically, so overlapping calls never create duplicate rows.

Can I store player props and Asian handicaps too?

Yes. Drop the markets filter to capture every market on the fixture, and add a player_id column for props, since prop outcomes key players by player id rather than "0". Look up human-readable market and outcome names from /markets?sportId=X and store them alongside the ids.

Start building your archive

The API gives you 350+ bookmakers, free historical odds, and a real-time WebSocket. This guide turns that into something you own: a queryable record of every price that ever printed. Stop scraping and stop re-fetching. Get your free API key and start filling the table today.