Skip to content

Collecting large tables safely

These tables are large and the page size is small. This page is about getting all of a table without losing rows to pagination, and about knowing what a download will cost before starting it.

import math
import transferegovpy as tg

Measure first

The eighty-six tables hold about 19 million rows between them, spread very unevenly — from none at all in especiais/orgaos_analises_pendentes_especiais to 5,052,521 in discricionarias/plano_aplicado_detalhado.

sizes = tg.tables(counts=True)
sizes.sort_values("rows", ascending=False).head()

That call makes eighty-six requests, and caches them. For a single table:

tg.count("fundoafundo", "gestao_financeira_lancamentos")
#> 1160094

count() takes the same filters as get(), so you can size the thing you actually want rather than the whole table:

tg.count("parcerias", "proposta", sg_uf_recebedor="PE")

What a page costs

Each module caps a page at its own size: 200 rows in discricionarias, especiais and parcerias, 1000 in fundoafundo and ted. modules() reports it, and page_size defaults to it. Unlike some APIs, these do not silently truncate a larger request — one row over the cap is a 422, and the package refuses it before sending:

tg.get("parcerias", "proposta", page_size=201)
#> ValueError: page_size must be a whole number between 1 and 200.

So the arithmetic is simple and worth doing, and the cap matters as much as the size. The largest table, discricionarias/plano_aplicado_detalhado, is ceil(5052521 / 200) = 25,263 requests — at the default throttle of sixty a minute, about seven hours. fundoafundo/gestao_financeira_lancamentos has 1.2 million rows but lives in a module that serves pages of 1000, so it takes 1,161 requests, about twenty minutes.

rows = tg.count("discricionarias", "plano_aplicado_detalhado")
math.ceil(rows / 200)
#> 25263

If you genuinely need a table that size, consider whether a filter narrows it first, and raise the throttle deliberately rather than by accident:

tg.configure(requests_per_minute=120)

Limits and offsets count rows

limit and offset are in rows, not pages, whatever page_size is set to.

tg.get("especiais", "meta_especiais", limit=450)

That is three requests: 200, 200, 50 — the last page is trimmed to the limit. An offset that falls inside a page is handled by fetching the page it lands in and dropping the rows before it:

tg.get("especiais", "meta_especiais", limit=100, offset=137, page_size=60)

math.inf collects everything that matches:

tg.get("especiais", "programas_especiais", limit=math.inf)

Checking what you got

Every result carries the pagination state the API reported:

metas = tg.get("especiais", "meta_especiais", limit=450)

tg.metadata(metas)
#> {'module': 'especiais', 'table': 'meta_especiais', 'total_rows': 156193.0,
#>  'rows_returned': 450, 'pages': 3, ...}

total_rows is how many rows matched, rows_returned how many you have. If collection ends short of what the API said it would return, that is an IncompleteResultWarning rather than a silent truncation.

Why the row order is safe to rely on

These APIs publish no ordering parameter. Page two is simply "page two", and whether that is a well-defined thing depends on the server keeping a stable order between requests — which nothing in the documentation promises.

Postgres makes no such promise in general: a query without ORDER BY may return rows in a different order between executions, and under page-based pagination that means page two can repeat rows from page one and skip others entirely. A row count would not reveal it. Two pages of 200 that overlap by 40 rows still add up to 400.

So it was tested rather than assumed. The check is to fetch the same rows at two different page sizes and compare them as sequences:

def strip(frame):
    out = frame.reset_index(drop=True).copy()
    out.attrs = {}
    return out

big = tg.get("especiais", "meta_especiais", limit=450, page_size=200)
small = tg.get("especiais", "meta_especiais", limit=450, page_size=50)

strip(big).equals(strip(small))
#> True

Three requests and nine requests, cutting the same 450 rows at different boundaries, produce the same rows in the same order. That is what rules out both overlap and skipping.

The same comparison holds 100,000 rows deep, across repeated calls, on tables with no natural key, and on tables with nested columns. tests/test_live.py re-runs all of it against the real services, so a change upstream shows up as a failing test rather than as quietly wrong data.

Caching

Responses are cached for an hour, so re-running a collection during a session costs nothing:

first = tg.get("especiais", "meta_especiais", limit=450)
again = tg.get("especiais", "meta_especiais", limit=450)

tg.metadata(again)["cached"]
#> True

The default cache lives in a temporary directory, so nothing is written outside the session unless you ask. For a long collection you will want it to survive:

tg.cache_dir("~/.cache/transferegovpy")

The APIs send no ETag, Cache-Control or Last-Modified, so HTTP caching would store nothing — this cache is the package's own. Use updated_at() to decide when a cached copy is stale.

A pattern for very large tables

For anything in the hundreds of thousands, collect in slices and write each one out, so an interrupted run does not start over:

import pathlib

total = tg.count("fundoafundo", "gestao_financeira_lancamentos")
slice_size = 20_000

for start in range(0, total, slice_size):
    path = pathlib.Path(f"lancamentos-{start:08d}.parquet")
    if path.exists():
        continue

    rows = tg.get(
        "fundoafundo", "gestao_financeira_lancamentos",
        limit=slice_size, offset=start,
    )
    rows.to_parquet(path)

Because the order is stable, the slices reassemble into the whole table without gaps or repeats.