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
| Approach | When it fits | What it costs you | When to pick something else |
|---|---|---|---|
| dlt rest_api source | 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 | 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 | 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 | 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 | 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.
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.
Related how-tos
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
-
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
-
The same key name means two counts in one table of the rest_api reference. For the
offsetpaginator,total_pathis a JSONPath for the total number of items. Forpage_number, the next entry, it is the total number of pages. In dlt’s source, both constructors default it tototal. ↩︎ Back to text -
The advice is in pandas’ own reference entry for the function being misused. The notes of
pandas.concatadvise 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 singleconcatcall. ↩︎ Back to text