# 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.

Source: https://voxgig.com/howto/paginated-api-into-pandas-dataframe

- Audience: data-analyst
- Level: beginner
- Languages: python
- Verified: 2026-11-21
- Published: 2026-11-21

## 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

| Approach | When it fits | What it costs you | When to pick something else |
| --- | --- | --- | --- |
| [dlt rest_api source](https://dlthub.com/docs/dlt-ecosystem/verified-sources/rest_api/basic) | You want the rows kept in a local DuckDB file and loaded again on later runs | A second tool, a DuckDB file beside the notebook, pipeline state elsewhere, and appends by default | One load into one frame, with nothing kept between runs |
| [Hey API Python client](https://heyapi.dev/docs/openapi/python/get-started) | You want Pydantic models for each page and an httpx client to send with | In 0.0.24 the list method takes no parameters, so the query and the loop are yours | The generated list call has to page by itself |
| [`openapi-python-client`](https://github.com/openapi-generators/openapi-python-client) | You want typed `attrs` models and a `sync()` call that takes the page and limit | No paging code, and a 400 the document lists without a body comes back as `None` | You want the generator to follow the pages for you |
| [requests plus `DataFrame.from_records`](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.from_records.html) | Any API, no generated client, and a loop short enough to read | Every paging rule, stop condition, and retry is code you write and test | A generated client for the API already exists |
| [sdkgen py-data target](https://github.com/voxgig/sdkgen) | The API owner generates with sdkgen and publishes the py-data package | Exists only where the owner ships it, pandas only, and the total comes from one header | The 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.

```py title="routes.py"
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.

```py title="routes.py"
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
```

```bash
python3 walk.py
```

```text output
  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](https://github.com/voxgig/sdkgen/blob/main/docs/reference/features.md),
then generates. Its output is committed under `generated/`.

```bash title="generate.sh"
(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.

```py title="routes.py"
    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
```

```bash
python3 sdkgen_walk.py
```

```text output
  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`.

```py title="routes.py"
    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.

```bash
python3 generators.py
```

```text output
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`](https://heyapi.dev/docs/openapi/typescript/configuration/parser);
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](https://dlthub.com/docs/general-usage/credentials/setup) of the
`runtime.dlthub_telemetry` option the [telemetry page](https://dlthub.com/docs/reference/telemetry)
documents, and `dlt telemetry` printed `Telemetry is DISABLED`.

```py title="dlt_load.py"
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"})
```

```bash
grep -E '^(Telemetry|has_more|default|no_empty_stop)|ids repeated' records/dlt-run.txt
```

```text output
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`](https://dlthub.com/docs/general-usage/resource) 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](https://dlthub.com/docs/general-usage/pipeline) 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.

```bash
python3 test_pages.py
```

```text output
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]

```bash
python3 pitfall.py
```

```text output
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](https://dlthub.com/docs/general-usage/incremental-loading). `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.

## Related how-tos

- [Turn typed SDK response objects into a pandas DataFrame](/howto/sdk-response-objects-into-a-pandas-dataframe)

- [Paginate an API with Python generators](/howto/paginate-an-api-with-python-generators)

- [Loop over every page of a REST collection](/howto/loop-over-every-page-of-a-collection)

- [Ship a DataFrame-first Python client for your analysts](/howto/dataframe-first-python-client-from-openapi)

- [Describe pagination so generators can follow it](/howto/describe-pagination-in-openapi-for-generators)

## 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.

[^1]: The [get started page](https://heyapi.dev/docs/openapi/python/get-started) says the package
is in initial development and links item 4 of [Semantic Versioning
2.0.0](https://semver.org/spec/v2.0.0.html). That item reserves major version zero for the stage
and says anything may change at any time. Hey API's [Python
page](https://heyapi.dev/codegen/openapi/python) offers sign-up for early access, although the
package is on npm at 0.0.24, the version this page ran.

[^2]: The same key name means two counts in one table of the [rest_api
reference](https://dlthub.com/docs/dlt-ecosystem/verified-sources/rest_api/basic). 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](https://github.com/dlt-hub/dlt/blob/devel/dlt/sources/helpers/rest_client/paginators.py),
both constructors default it to `total`.

[^3]: The advice is in pandas' own reference entry for the function being misused. The notes of
[`pandas.concat`](https://pandas.pydata.org/docs/reference/api/pandas.concat.html) 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.