How-to › Move data in and out

How to load a paginated API into a pandas DataFrame#

Walk every page of a list endpoint into one pandas DataFrame, build it once, and hold its row count to the API's total, with requests, dlt, or sdkgen py-data.

Audience
Data analyst
Level
beginner
Topic
Load API data into notebooks and DataFrames
Languages
Python
Verified

The notebook cell calls the sightings endpoint once and holds 10 of the 47 rows the API has. A loop that calls pd.concat on every page gets all 47, and at a thousand pages it spends most of its time copying the rows it already had. Neither frame tells you whether it holds every row.

What you get

You will end up with one DataFrame built from every page of a list endpoint, and a check that fails on a short frame or a repeated id. This is for you if you load API data into pandas and need the whole collection, not its first page.

Short answer

To load a paginated API into pandas, request each page until the response says there are no more, append its records to one Python list, and build the frame once with pd.DataFrame.from_records. Then compare len(df) with the total the API reports and check that no id repeats. dlt’s rest_api source and sdkgen’s py-data target walk the pages for you, and the same two checks still apply.

You will need

Python 3.12 or later, pandas, and a list endpoint whose paging parameters are documented. Verified 2026-11-21 against Python 3.13.18, pandas 3.1.1, requests 2.34.2, @voxgig/sdkgen 4.44.3, @hey-api/openapi-python 0.0.24, openapi-python-client 0.30.0, and dlt 1.32.0. The document, api.json, describes 47 bird sightings served ten to a page, with page and limit parameters and a body of data, total, has_more, and next_page. The total also arrives in an X-Total-Count header. Every script runs against server.py, a local stand-in that refuses any parameter the document does not declare.

Approaches compared

ApproachWhen it fitsWhat it costs youWhen to pick something else
dlt rest_api sourceYou want the rows kept in a local DuckDB file and loaded again on later runsA second tool, a DuckDB file beside the notebook, pipeline state elsewhere, and appends by defaultOne load into one frame, with nothing kept between runs
Hey API Python clientYou want Pydantic models for each page and an httpx client to send withIn 0.0.24 the list method takes no parameters, so the query and the loop are yoursThe generated list call has to page by itself
openapi-python-clientYou want typed attrs models and a sync() call that takes the page and limitNo paging code, and a 400 the document lists without a body comes back as NoneYou want the generator to follow the pages for you
requests plus DataFrame.from_recordsAny API, no generated client, and a loop short enough to readEvery paging rule, stop condition, and retry is code you write and testA generated client for the API already exists
sdkgen py-data targetThe API owner generates with sdkgen and publishes the py-data packageExists only where the owner ships it, pandas only, and the total comes from one headerThe vendor ships no py-data package, or reports its total only in the body

Voxgig maintains sdkgen, one of the five rows in the table, and this page does not rank it over the other four.

The requests loop is the most code and the least surprise: every rule is on the page. dlt and py-data own the paging, and each brings its own: dlt a DuckDB file and a state directory, py-data a package that exists only if the API owner publishes it. The two generated clients give typed pages and leave the walk to you.

Collect every page, then build the frame once

The loop asks for a page, appends its records to a list, and stops when has_more is false. The frame is built once, after the last page, and the total comes from the last response.

def requests_frame(base_url: str, session: requests.Session | None = None) -> tuple[pd.DataFrame, int]:
    """requests: collect every page's records in a list, then build the frame once."""
    session = session or requests.Session()
    records: list[dict] = []
    page, seen, total = 1, {1}, None
    while True:
        resp = session.get(f"{base_url}/sightings", params={"page": page, "limit": PAGE_SIZE}, timeout=10)
        resp.raise_for_status()
        body = resp.json()
        records.extend(body["data"])
        total = body["total"]
        if not body["has_more"]:
            break
        page = next_page(page, body.get("next_page"), seen)
    return pd.DataFrame.from_records(records), total

next_page raises when the server offers no page, the same page, or one it already sent, so a server that never advances stops the loop instead of hanging the cell. check_rows is the verification: it raises unless len(df) equals the total and every id appears once.

def check_rows(df: pd.DataFrame, total: int | None) -> pd.DataFrame:
    """Refuse a frame whose row count differs from the API's total, or that repeats an id."""
    if total is None:
        raise ShortFrame("the API reported no total, so the row count cannot be checked")
    if len(df) != total:
        raise ShortFrame(f"{len(df)} rows, but the API reported {total}")
    if not df["id"].is_unique:
        repeated = sorted(df.loc[df["id"].duplicated(), "id"])
        raise ShortFrame(f"{len(df)} rows match the total, but ids repeat: {repeated}")
    return df
python3 walk.py
  GET /v1/sightings?page=1&limit=10
  GET /v1/sightings?page=2&limit=10
  GET /v1/sightings?page=3&limit=10
  GET /v1/sightings?page=4&limit=10
  GET /v1/sightings?page=5&limit=10
rows in the frame: 47, total the API reported: 47
check_rows: ok, every id once

     id     species  count observed_on          site
0  s047     Lapwing      6  2026-04-17   Old orchard
1  s046  Kingfisher     11  2026-04-16  Estuary hide
2  s045   Goldfinch      4  2026-04-15     Reed beds

The walk took five requests for 47 rows. Each URL keeps the /v1 of the base URL, because the f-string appends the path rather than resolving it.

Drain the pages with the sdkgen py-data target

generate.sh scaffolds the project with the py target, the py-data target that wraps it, and the paging feature, then generates. Its output is committed under generated/.

(cd "$WORK" && npx --yes @voxgig/create-sdkgen@0.31.9 birdcount --def api.json \
  --target py --target py-data --feature paging > scaffold.txt 2>&1)
(cd "$WORK/birdcount-sdk/.sdk" && npm run generate > ../../generate.txt 2>&1)

The generated package exports data(), which returns a BirdcountData object with one method per entity. The method for the list is sightings(), plural, and it drains every page before it returns.

    ad = data(token="test-key", base_url=base_url,
              feature={"paging": {"active": True, "limit": PAGE_SIZE}})
    df = ad.sightings(quiet=True)
    # The paging feature records the last page's signals on the client;
    # totalCount is read from the X-Total-Count response header.
    total = ad.sdk._paging["last"]["totalCount"]
    return df, total
python3 sdkgen_walk.py
  GET /v1/sightings?limit=10&page=1
  GET /v1/sightings?limit=10&page=2
  GET /v1/sightings?limit=10&page=3
  GET /v1/sightings?limit=10&page=4
  GET /v1/sightings?limit=10&page=5
rows in the frame: 47, totalCount on the client: 47
check_rows: ok, every id once

count           Int64
id             string
observed_on    string
site           string
species        string

The drain follows next_page until has_more is false, and it never compares the rows with a total, so the check is still yours. The total lives on ad.sdk._paging, an attribute with a leading underscore that the features reference documents for the dynamically typed targets. The paging feature reads it from X-Total-Count. The model records body.total as the list’s page total, but no generated Python reads that entry. The newest release on npm, 4.45.0, writes the same paging feature and drain, which generated/NEWEST records.

Walk the same pages over the two other generated clients

Hey API’s Python generator wrote Pydantic models and an Sdk class, but its list_sightings takes no arguments, so it can only fetch the first page. The loop sends the query on the class’s httpx client and validates each page with the generated SightingPage.

    while True:
        query = ListSightingsQuery(page=page, limit=PAGE_SIZE)
        resp = sdk.client.get("/sightings", params=query.model_dump())
        resp.raise_for_status()
        result = SightingPage.model_validate(resp.json())
        records.extend(s.model_dump(mode="json") for s in result.data)
        if not result.has_more:
            break
        page = next_page(page, result.next_page, seen)

openapi-python-client writes a module per operation whose sync() takes page and limit, so its loop is the requests loop with typed pages and to_dict() per record. Neither generator wrote any paging code for this document, which a test asserts.

python3 generators.py
Hey API list_sightings(self)
Hey API: 5 requests, 47 rows, total 47, check_rows ok
  first: GET /v1/sightings?page=1&limit=10
  last:  GET /v1/sightings?page=5&limit=10
openapi-python-client: 5 requests, 47 rows, total 47, check_rows ok
  first: GET /v1/sightings?page=1&limit=10
  last:  GET /v1/sightings?page=5&limit=10
Hey API list_sightings() alone: 10 rows of 47, sent GET /v1/sightings

Hey API’s TypeScript generator marks paginated operations through parser.pagination.keywords; its Python documentation has no such page, and the package asks to be pinned to an exact version while in initial development.1

Load the pages with dlt

dlt’s rest_api source follows a paginator you declare and loads into DuckDB, and pipeline.dataset().sightings.df() reads the table back as a frame. record.sh ran it once, outside the gate, because dlt and DuckDB are large wheels; the record is committed. dlt reports anonymous usage by default, so dlt_load.py sets RUNTIME__DLTHUB_TELEMETRY to false. That is the environment form of the runtime.dlthub_telemetry option the telemetry page documents, and dlt telemetry printed Telemetry is DISABLED.

paginator = {"type": "page_number", "base_page": 1, "page_param": "page"}
if mode == "has_more":
    # The body's `total` counts sightings; dlt's total_path expects a count of pages.
    paginator.update({"total_path": None, "has_more_path": "has_more"})
grep -E '^(Telemetry|has_more|default|no_empty_stop)|ids repeated' records/dlt-run.txt
Telemetry is DISABLED
has_more, append: 5 requests, 47 rows in the table, API total 47
  ids repeated: 0
has_more, append: 5 requests, 94 rows in the table, API total 47
  ids repeated: 47
has_more, replace: 5 requests, 47 rows in the table, API total 47
  ids repeated: 0
default, append: 6 requests, 47 rows in the table, API total 47
  ids repeated: 0
no_empty_stop, append: 47 requests, 47 rows in the table, API total 47
  ids repeated: 0

The second run of the same cell left 94 rows, because write_disposition defaults to append and the script sets none; replace brought it back to 47. With total_path at its default, dlt reads the body’s total as a page count: the empty page 6 stopped the default run, and with stop_after_empty_page off it asked for all 47 pages.2 The DuckDB file landed in the working directory. The pipeline state went under /var/dlt/pipelines, because the run was as root; for other users it is ~/.dlt/pipelines.

Check it worked

test_pages.py runs every route against the stand-in, compares each frame’s rows with the total, checks that the four routes agree row for row, and reads the committed records.

python3 test_pages.py
test_a_deletion_mid_walk_passes_both_checks (__main__.Checks.test_a_deletion_mid_walk_passes_both_checks) ... ok
test_an_insert_mid_walk_matches_the_total_and_repeats_an_id (__main__.Checks.test_an_insert_mid_walk_matches_the_total_and_repeats_an_id) ... ok
test_check_rows_refuses_a_missing_total (__main__.Checks.test_check_rows_refuses_a_missing_total) ... ok
test_check_rows_refuses_a_short_frame (__main__.Checks.test_check_rows_refuses_a_short_frame) ... ok
test_next_page_refuses_a_server_that_does_not_advance (__main__.Checks.test_next_page_refuses_a_server_that_does_not_advance) ... ok
test_openapi_python_client_returns_none_on_a_documented_400 (__main__.Checks.test_openapi_python_client_returns_none_on_a_documented_400) ... ok
test_sdkgen_reads_no_total_from_the_body (__main__.Checks.test_sdkgen_reads_no_total_from_the_body) ... ok
test_concat_in_the_loop_writes_quadratically_many_rows (__main__.Records.test_concat_in_the_loop_writes_quadratically_many_rows) ... ok
test_neither_other_generator_wrote_paging_code (__main__.Records.test_neither_other_generator_wrote_paging_code) ... ok
test_the_dlt_record_holds_what_the_page_states (__main__.Records.test_the_dlt_record_holds_what_the_page_states) ... ok
test_the_model_records_the_body_total_and_no_generated_code_reads_it (__main__.Records.test_the_model_records_the_body_total_and_no_generated_code_reads_it) ... ok
test_the_newest_sdkgen_writes_the_same_paging_and_drain (__main__.Records.test_the_newest_sdkgen_writes_the_same_paging_and_drain) ... ok
test_the_timing_record_shows_the_loop_slower (__main__.Records.test_the_timing_record_shows_the_loop_slower) ... ok
test_all_routes_agree_on_the_rows (__main__.Routes.test_all_routes_agree_on_the_rows) ... ok
test_hey_api_list_method_takes_no_page (__main__.Routes.test_hey_api_list_method_takes_no_page) ... ok
test_hey_api_loop (__main__.Routes.test_hey_api_loop) ... ok
test_openapi_python_client_loop (__main__.Routes.test_openapi_python_client_loop) ... ok
test_requests_walks_every_page_and_builds_once (__main__.Routes.test_requests_walks_every_page_and_builds_once) ... ok
test_sdkgen_py_data_drains_every_page_and_reads_x_total_count (__main__.Routes.test_sdkgen_py_data_drains_every_page_and_reads_x_total_count) ... ok
test_a_proxy_in_the_environment_does_not_reach_the_stand_in (__main__.StandIn.test_a_proxy_in_the_environment_does_not_reach_the_stand_in) ... ok
test_refuses_what_the_document_does_not_allow (__main__.StandIn.test_refuses_what_the_document_does_not_allow) ... ok

----------------------------------------------------------------------
Ran 21 tests

OK

The script exits 1 when any test fails, so a CI step that runs it fails with it.

When it goes wrong

The cell slows as the collection grows. pd.concat inside the loop writes the whole frame so far into a fresh one on every page, so the rows written grow with the square of the page count. Collect records in a list and build once.3

python3 pitfall.py
1. pd.concat inside the loop
   5 pages of 10: same frame, rows written 150 in the loop, 50 built once
   1000 pages of 50: same frame, rows written 25,025,000 in the loop, 50,000 built once
2. a sighting recorded after page 1 was served
   rows 48, total 48, newest sighting s048 in the frame: False
   check_rows: 48 rows match the total, but ids repeat: ['s038']
3. the newest sighting withdrawn after page 1 was served
   rows 46, total 46, withdrawn s047 in the frame: True, never fetched: ['s037']
   check_rows: ok, although the frame is not the collection
4. the same API without the X-Total-Count header
   py-data rows 47, totalCount None, while the body still says total 47
   check_rows: the API reported no total, so the row count cannot be checked
5. a page size the document refuses, through openapi-python-client
   status 400, sync() returned None, no exception raised

records/concat-timing.txt holds the timing record.sh took on the 1,000 pages, on the one machine it names: 1.350 s for the loop and 0.021 s for one build.

The row count matches and a row is missing. A sighting recorded mid-walk shifted every later page by one, so s038 came back twice and s048 never arrived. Page numbers move when a row is inserted ahead of them, and the id check is what catches it. A row withdrawn mid-walk passes both checks: s037 was skipped and the total fell by one with it. Only a cursor or a snapshot that the API offers closes that gap.

The sdkgen total is None. The API sends its total only in the body, which the paging feature does not read, so read the body’s total with one request of your own, as pitfall.py does. A sync() from openapi-python-client returns None on a documented 400, even with raise_on_unexpected_status on, so test the result’s type, as opc_frame does.

When not to do this

Do not load a whole collection into a notebook to answer a question one filter parameter would answer on the server. The walk costs one request per page and the frame holds every row in memory.

Do not run a dlt cell twice with the default write disposition and read the table as the collection. Set write_disposition to replace or merge, and keep the row check after the read.

Do not choose sdkgen’s py-data target for an API whose owner does not publish it; the requests loop here is the route there. Pick dlt when the rows should outlive the notebook and later runs should load only what changed. openapi-python-client suits a team that wants typed models with no second tool, and Hey API’s Pydantic models suit code that already validates with Pydantic.

Last verified

Verified 2026-11-21 against Node 22.23.5, Python 3.13.18, @voxgig/create-sdkgen 0.31.9, @voxgig/sdkgen 4.44.3, @voxgig/apidef 8.32.4, @hey-api/openapi-python 0.0.24, openapi-python-client 0.30.0, pandas 3.1.1, requests 2.34.2, dlt 1.32.0, and DuckDB 1.5.6. Every output block is what the command preceding it printed. The three generators ran once, in generate.sh, and their packages are committed under generated/. dlt ran once, in record.sh, and the gate reads its committed record.

Footnotes

  1. The get started page says the package is in initial development and links item 4 of Semantic Versioning 2.0.0. That item reserves major version zero for the stage and says anything may change at any time. Hey API’s Python page offers sign-up for early access, although the package is on npm at 0.0.24, the version this page ran. ↩︎ Back to text

  2. The same key name means two counts in one table of the rest_api reference. For the offset paginator, total_path is a JSONPath for the total number of items. For page_number, the next entry, it is the total number of pages. In dlt’s source, both constructors default it to total. ↩︎ Back to text

  3. The advice is in pandas’ own reference entry for the function being misused. The notes of pandas.concat advise against building DataFrames by adding single rows in a for loop. They say to build a list of rows and make a DataFrame with a single concat call. ↩︎ Back to text

Read this page as markdown · All how-to guides

Generate the client instead of writing it#

Retries, timeouts, pagination and auth are the same problems in every client. Voxgig generates them from your OpenAPI description, in 23 languages, from one model.

Listen to Fireside, the Voxgig podcast

Since 2018, Richard Rodger has talked with developer advocates, event organizers, and the people who build developer-facing companies about the specifics of the work.