{"id":3148,"date":"2026-07-29T10:00:00","date_gmt":"2026-07-29T10:00:00","guid":{"rendered":"https:\/\/oddspapi.io\/blog\/?p=3148"},"modified":"2026-07-17T19:15:30","modified_gmt":"2026-07-17T19:15:30","slug":"odds-database-python-sqlite","status":"publish","type":"post","link":"https:\/\/oddspapi.io\/blog\/odds-database-python-sqlite\/","title":{"rendered":"Build an Odds Database in Python: Store Live & Historical Odds in SQLite (Free API)"},"content":{"rendered":"

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<\/code> against a JSON blob. Every backtest, every steam-move alert, every CLV audit starts with the same missing piece: your own historical store.<\/p>\n

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<\/code> 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.<\/p>\n

Why a JSON feed is not a database<\/h2>\n

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.<\/p>\n

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.<\/p>\n

\n\n\n\n\n\n\n\n\n\n
The old way<\/th>\nOddsPapi to SQLite<\/th>\n<\/tr>\n<\/thead>\n
One CSV per pull, no way to join across time<\/td>\nOne indexed table, query any window with SQL<\/td>\n<\/tr>\n
Re-fetch history on every backtest run<\/td>\nSeed once from free \/historical-odds<\/code>, then read locally<\/td>\n<\/tr>\n
Store every duplicate price the feed repeats<\/td>\nChange-only inserts keep the table lean<\/td>\n<\/tr>\n
History window controlled by the vendor<\/td>\nYour archive grows forever on disk<\/td>\n<\/tr>\n
No opening line, so no closing-line value<\/td>\nOpening and latest price per book in one query<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/figure>\n

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

Step 1: Get a key and confirm the feed<\/h2>\n

Grab a free API key<\/a>. Authentication is a query parameter, not a header. One call to \/sports<\/code> confirms you are wired up.<\/p>\n

import requests\n\nAPI_KEY = \"YOUR_API_KEY\"\nBASE_URL = \"https:\/\/api.oddspapi.io\/v4\"\n\ndef api_get(path, **params):\n    params[\"apiKey\"] = API_KEY          # apiKey is a query param, never a header\n    r = requests.get(f\"{BASE_URL}{path}\", params=params, timeout=30)\n    r.raise_for_status()\n    return r.json()\n\nprint(len(api_get(\"\/sports\")), \"sports\")          # 69\nprint(len(api_get(\"\/bookmakers\")), \"bookmakers\")   # 381\n<\/code><\/pre>\n
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.<\/p>\n
Step 2: Design the schema<\/h2>\n
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)<\/code> tuple appears many times, once per recorded_at<\/code> timestamp.<\/p>\n
import sqlite3\n\nSCHEMA = \"\"\"\nCREATE TABLE IF NOT EXISTS fixtures (\n    fixture_id   TEXT PRIMARY KEY,\n    sport_id     INTEGER,\n    home         TEXT,\n    away         TEXT,\n    start_time   TEXT\n);\nCREATE TABLE IF NOT EXISTS odds_snapshots (\n    fixture_id   TEXT,\n    bookmaker    TEXT,\n    market_id    INTEGER,\n    outcome_id   INTEGER,\n    price        REAL,\n    recorded_at  TEXT,\n    UNIQUE(fixture_id, bookmaker, market_id, outcome_id, recorded_at)\n);\nCREATE INDEX IF NOT EXISTS idx_snap\n    ON odds_snapshots(fixture_id, bookmaker, market_id, outcome_id, recorded_at);\n\"\"\"\n\ndef get_conn(path=\"odds.db\"):\n    conn = sqlite3.connect(path)\n    conn.executescript(SCHEMA)\n    return conn\n<\/code><\/pre>\n
The UNIQUE<\/code> constraint is doing real work. It lets you insert with INSERT OR IGNORE<\/code> and never worry about writing the same price at the same timestamp twice. Store decimal odds as REAL<\/code>; the feed already hands you decimal, American, and fractional formats, so you can add columns for those later without a converter.<\/p>\n
Populate the fixtures table<\/h3>\n
Fixture names live on the \/fixtures<\/code> endpoint, not \/odds<\/code>. 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.<\/p>\n
def store_fixtures(conn, sport_id, date_from, date_to):\n    fixtures = api_get(\"\/fixtures\", sportId=sport_id,\n                       **{\"from\": date_from, \"to\": date_to})\n    for fx in fixtures:\n        if not fx.get(\"hasOdds\"):        # only fixtures with a priced market\n            continue\n        conn.execute(\n            \"INSERT OR REPLACE INTO fixtures VALUES (?,?,?,?,?)\",\n            (fx[\"fixtureId\"], fx.get(\"sportId\"),\n             fx.get(\"participant1Name\"), fx.get(\"participant2Name\"),\n             fx.get(\"startTime\")))\n    conn.commit()\n\nconn = get_conn()\nstore_fixtures(conn, 13, \"2026-07-17\", \"2026-07-18\")   # MLB, sportId 13\n<\/code><\/pre>\n
Only fixtures flagged hasOdds: true<\/code> 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<\/code>, which had 17 books on the board.<\/p>\n
Step 3: Capture a live snapshot<\/h2>\n
The \/odds<\/code> response is deeply nested: bookmakerOdds → slug → markets → market_id → outcomes → outcome_id → players → \"0\" → price<\/code>. Walk it into flat rows. Two defensive filters matter here, and both come from real payloads, not theory.<\/p>\n
from datetime import datetime, timezone\n\ndef capture_live(conn, fixture_id, markets=(\"131\",)):\n    data = api_get(\"\/odds\", fixtureId=fixture_id)\n    now = datetime.now(timezone.utc).isoformat()\n    rows = []\n    for slug, bookdata in data.get(\"bookmakerOdds\", {}).items():\n        for mid, market in bookdata.get(\"markets\", {}).items():\n            if markets and mid not in markets:      # keep the DB focused\n                continue\n            for oid, outcome in market.get(\"outcomes\", {}).items():\n                node = outcome.get(\"players\", {}).get(\"0\")\n                if not node:                        # player-prop lineups are keyed by\n                    continue                        # player id, not \"0\" -- skip them here\n                price = node.get(\"price\")\n                if outcome.get(\"active\") is False or not price:\n                    continue                        # see the two gotchas below\n                rows.append((fixture_id, slug, int(mid), int(oid), price, now))\n    conn.executemany(\n        \"INSERT OR IGNORE INTO odds_snapshots VALUES (?,?,?,?,?,?)\", rows)\n    conn.commit()\n    return len(rows)\n<\/code><\/pre>\n
Two gotchas the feed will throw at you:<\/p>\n
    \n
  • The active<\/code> flag is not always true<\/code>.<\/strong> On a live pre-game pull of this fixture, every outcome shipped active: null<\/code> alongside a perfectly valid price of 2.01. Filter on active is False<\/code>, not on truthy active<\/code>, or you will silently drop good prices and write an empty snapshot.<\/li>\n
  • The players<\/code> key is \"0\"<\/code> for game lines only.<\/strong> On player-prop markets it is keyed by player id (like \"1410115\"<\/code>) with a playerName<\/code> on each node. The node<\/code> check above skips those so a game-line capture does not choke on a prop market.<\/li>\n<\/ul>\n
    Running that captured 26 rows in one call: 13 books that priced the moneyline (market 131), two outcomes each. Outcome 131<\/code> is the Yankees, 132<\/code> the Dodgers. Pinnacle sat at 2.01 \/ 1.917, Kalshi at 2.041, DraftKings at 1.95 \/ 1.87.<\/p>\n
    Step 4: Store only what changed<\/h2>\n
    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<\/code> field works, and it is the difference between a database and a log file.<\/p>\n
    def insert_if_changed(conn, fixture_id, slug, mid, oid, price, recorded_at):\n    cur = conn.execute(\n        \"SELECT price FROM odds_snapshots \"\n        \"WHERE fixture_id=? AND bookmaker=? AND market_id=? AND outcome_id=? \"\n        \"ORDER BY recorded_at DESC LIMIT 1\",\n        (fixture_id, slug, mid, oid))\n    last = cur.fetchone()\n    if last and last[0] == price:\n        return False                     # unchanged, skip\n    conn.execute(\"INSERT OR IGNORE INTO odds_snapshots VALUES (?,?,?,?,?,?)\",\n                 (fixture_id, slug, mid, oid, price, recorded_at))\n    return True\n<\/code><\/pre>\n
    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.<\/p>\n
    Step 5: Seed real history for free<\/h2>\n
    Here is the kill shot most providers charge for. The \/historical-odds<\/code> 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<\/code> (not bookmakerOdds<\/code>), and players[\"0\"]<\/code> is a list<\/em> of snapshots, not a single dict. It accepts a maximum of three bookmakers per call, so loop if you want more.<\/p>\n
    def seed_history(conn, fixture_id, bookmakers, markets=(\"131\",)):\n    data = api_get(\"\/historical-odds\", fixtureId=fixture_id,\n                   bookmakers=\",\".join(bookmakers))     # max 3 per call\n    inserted = 0\n    for slug, bookdata in data.get(\"bookmakers\", {}).items():\n        for mid, market in bookdata.get(\"markets\", {}).items():\n            if markets and mid not in markets:\n                continue\n            for oid, outcome in market.get(\"outcomes\", {}).items():\n                for snap in outcome.get(\"players\", {}).get(\"0\", []):   # a LIST\n                    if snap.get(\"active\") is False or not snap.get(\"price\"):\n                        continue\n                    if insert_if_changed(conn, fixture_id, slug, int(mid),\n                                         int(oid), snap[\"price\"], snap[\"createdAt\"]):\n                        inserted += 1\n    conn.commit()\n    return inserted\n\nseed_history(conn, \"id1300010963303845\", [\"pinnacle\", \"draftkings\", \"fanduel\"])\n<\/code><\/pre>\n
    Each snapshot carries createdAt<\/code>, price<\/code>, limit<\/code>, and active<\/code>. 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.<\/p>\n
    Step 6: Query the database<\/h2>\n
    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).<\/p>\n
    Line movement for one book<\/h3>\n
    SELECT recorded_at, price\nFROM odds_snapshots\nWHERE fixture_id = 'id1300010963303845'\n  AND bookmaker = 'pinnacle' AND market_id = 131 AND outcome_id = 131\nORDER BY recorded_at;\n<\/code><\/pre>\n
    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.<\/p>\n
    Opening vs latest price per book<\/h3>\n
    Closing-line value needs the first and last price you observed. One query, no application code:<\/p>\n
    WITH ordered AS (\n  SELECT bookmaker, price, recorded_at,\n         ROW_NUMBER() OVER (PARTITION BY bookmaker ORDER BY recorded_at)      AS r_open,\n         ROW_NUMBER() OVER (PARTITION BY bookmaker ORDER BY recorded_at DESC) AS r_last\n  FROM odds_snapshots\n  WHERE fixture_id = 'id1300010963303845' AND market_id = 131 AND outcome_id = 131)\nSELECT o.bookmaker, o.price AS open_price, l.price AS latest_price\nFROM ordered o JOIN ordered l ON o.bookmaker = l.bookmaker\nWHERE o.r_open = 1 AND l.r_last = 1\nORDER BY o.bookmaker;\n<\/code><\/pre>\n
    \n\n\n\n\n\n\n\n
    Bookmaker<\/th>\nOpen (NYY)<\/th>\nLatest (NYY)<\/th>\n<\/tr>\n<\/thead>\n
    draftkings<\/td>\n1.90<\/td>\n1.95<\/td>\n<\/tr>\n
    fanduel<\/td>\n1.98<\/td>\n1.94<\/td>\n<\/tr>\n
    pinnacle<\/td>\n2.00<\/td>\n2.01<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/figure>\n
    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.<\/p>\n
    Best price per outcome (line shopping from disk)<\/h3>\n
    WITH latest AS (\n  SELECT bookmaker, outcome_id, price,\n         ROW_NUMBER() OVER (PARTITION BY bookmaker, outcome_id\n                            ORDER BY recorded_at DESC) AS rn\n  FROM odds_snapshots\n  WHERE fixture_id = 'id1300010963303845' AND market_id = 131)\nSELECT outcome_id, MAX(price) AS best_price\nFROM latest WHERE rn = 1 GROUP BY outcome_id;\n<\/code><\/pre>\n
    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.<\/p>\n
    De-vig the sharp line to a fair probability<\/h3>\n
    WITH latest AS (\n  SELECT outcome_id, price,\n         ROW_NUMBER() OVER (PARTITION BY outcome_id ORDER BY recorded_at DESC) rn\n  FROM odds_snapshots\n  WHERE fixture_id = 'id1300010963303845'\n    AND bookmaker = 'pinnacle' AND market_id = 131)\nSELECT outcome_id, price FROM latest WHERE rn = 1;\n<\/code><\/pre>\n
    # Pinnacle latest: NYY 2.01, LAD 1.917\nimp = {131: 1\/2.01, 132: 1\/1.917}\ntotal = sum(imp.values())\nprint(round((total - 1) * 100, 2), \"% vig\")            # 1.92 %\nfor oid, p in imp.items():\n    print(oid, \"fair prob\", round(p\/total, 4), \"fair odds\", round(total\/p, 4))\n# 131 fair prob 0.4882 fair odds 2.0485\n# 132 fair prob 0.5118 fair odds 1.9537\n<\/code><\/pre>\n
    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.<\/p>\n
    Step 7: Run it on a schedule<\/h2>\n
    The capture function is the whole loop. Point it at every fixture with hasOdds<\/code>, sleep to respect the roughly 0.88s per-endpoint cooldown on the free tier, and let it run under cron.<\/p>\n
    import time\n\ndef poll_once(conn, sport_id, date_from, date_to, markets=(\"131\",)):\n    store_fixtures(conn, sport_id, date_from, date_to)\n    fixture_ids = [r[0] for r in conn.execute(\n        \"SELECT fixture_id FROM fixtures WHERE sport_id=?\", (sport_id,))]\n    total = 0\n    for fid in fixture_ids:\n        try:\n            total += capture_live(conn, fid, markets)\n        except requests.HTTPError:\n            pass                          # fixture may have closed; keep going\n        time.sleep(1.0)                   # stay under the endpoint cooldown\n    return total\n\n# cron: *\/5 * * * *  python poll.py\nconn = get_conn()\nprint(poll_once(conn, 13, \"2026-07-17\", \"2026-07-18\"), \"rows this pass\")\n<\/code><\/pre>\n
    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<\/a>, 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.<\/p>\n
    From here the same tables feed everything else. Load a slice into pandas for analysis (live odds to DataFrame<\/a>), export a fixture to a spreadsheet (CSV and Excel export<\/a>), or point your model at it. You built the memory your API was missing.<\/p>\n
    Frequently asked questions<\/h2>\n
    Do I need Postgres, or is SQLite enough?<\/h3>\n
    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.<\/p>\n
    How far back does the free historical data go?<\/h3>\n
    The \/historical-odds<\/code> 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.<\/p>\n
    Why store only price changes instead of every poll?<\/h3>\n
    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.<\/p>\n
    How do I store more than three bookmakers of history?<\/h3>\n
    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<\/code> plus change-only logic dedupes automatically, so overlapping calls never create duplicate rows.<\/p>\n
    Can I store player props and Asian handicaps too?<\/h3>\n
    Yes. Drop the markets<\/code> filter to capture every market on the fixture, and add a player_id<\/code> column for props, since prop outcomes key players<\/code> by player id rather than \"0\"<\/code>. Look up human-readable market and outcome names from \/markets?sportId=X<\/code> and store them alongside the ids.<\/p>\n
    Start building your archive<\/h2>\n
    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<\/a> and start filling the table today.<\/p>\n