Pulling JSON From APIs Into a Database Reliably in Python

Calling an API and putting the result in a table is a one-evening script. Keeping that script correct for a year is a different job, and the difference is in what happens on the bad days: the API returns 500 for ten minutes, the job is killed at three in the morning, a field gets renamed, the data changes while you read it, the rate limit tightens, someone reruns the job twice. Each of those has a known cure, and a loader that applies all of them is not much longer than one that applies none.

This guide puts the cures together. It starts with the properties a reliable loader needs, shows a complete small loader in Python and PostgreSQL, runs it through seven scenarios of the kind that break naive scripts, and then points to the shorter articles that go into each part: loading into jsonb, pagination, rate limits, upserts, raw storage, shape drift, nested data, incremental loads, a real GitHub example and retries.

What reliable means

A loader is reliable if it meets these seven conditions. Each is cheap to build in from the start and hard to add after an incident.

1. It keeps the raw response. Whatever the API returned is stored before it is interpreted, so a mistake in the mapping is fixed by reprocessing and not by calling the API again.

2. Its writes are idempotent. Running the same load twice leaves the same table as running it once. Keys and upserts do this.

3. It can resume. After a crash, it continues from where it stopped, or restarts harmlessly from the beginning.

4. It retries what is safe to retry, and nothing else, with backoff, a limit on tries and respect for the server's rate limit.

5. It notices when the data's shape changes, and stops before writing nonsense.

6. It loads what changed, not everything, once the data is large enough that a full load is slow, and it does not lose records at the edges of that process.

7. It reconciles, because incremental loads cannot see deletions.

None of this needs a framework. The loader below is about sixty lines of logic.

The pieces, in order

Fetch. A requests.Session with a function that retries transient failures. 429 waits for Retry-After. 5xx waits with exponential backoff and jitter. A client-side limiter spaces calls to 90% of the documented limit. Other 4xx responses raise at once.

Land. Each response body is stored in raw_responses with the endpoint, the parameters, the fetch time and a hash of the bytes. A unique constraint on the request and the hash makes re-fetching an unchanged page a no-op.

Check. A function reduces each record to a set of (key, type) pairs and compares it with the pairs the loader needs. A missing or retyped field stops the run, before anything is written for that page.

Write. One transaction per page. Each record is upserted with a guard: update only if the content hash differs and the record is not older than the one held. Parents before children, children replaced.

Remember. After all pages are committed, the largest updated_at seen becomes the cursor in sync_state. The next run starts from it, inclusively.

Reconcile. A separate, occasional pass fetches the ids, and deletes rows that no longer exist, with a limit on how much it will delete.

The loader

The core of the fetch function, retries and limiter included:

def get(session, path, params, tries=6):
    delay = 0.05
    for attempt in range(tries):
        throttle()                                   # space calls to 90% of the limit
        r = session.get(BASE + path, params=params)
        if r.status_code == 200:
            return r
        if r.status_code == 429:
            time.sleep(float(r.headers.get("Retry-After", 1)))
        elif r.status_code >= 500:
            time.sleep(delay + random.random() * delay); delay *= 2
        else:
            r.raise_for_status()                     # 400, 401, 404: do not retry
    raise RuntimeError(f"giving up on {path}")

and the loop that lands, checks, writes and remembers:

def run(path="/v1/products"):
    since = read_cursor()                            # None on the first run
    newest = since or ""
    token = None
    while True:
        params = {"limit": 100, **({"cursor": token} if token else {}), **({"updated_since": since} if since else {})}
        r = get(session, path, params)
        body = r.json()
        missing = EXPECTED - set().union(*(shape(rec) for rec in body["data"]))
        if missing:
            raise SystemExit(f"STOP: shape drift, missing {sorted(missing)}")
        store_raw(path, params, r.content, body)     # insert ... on conflict do nothing
        with conn.transaction():                     # one page, all or nothing
            for rec in body["data"]:
                upsert(rec)                          # guarded: hash differs and not older
                newest = max(newest, rec["updated_at"])
        token = body["next_cursor"]
        if not token:
            break
    save_cursor(newest)                              # only after every page is committed

The guard in upsert is the clause from upserting JSON API records into a table: WHERE lp.row_hash IS DISTINCT FROM EXCLUDED.row_hash AND EXCLUDED.updated_at >= lp.updated_at. And reconcile() is a short function that fetches ids, counts the stale rows, and refuses to delete if they exceed a tenth of the table.

Seven scenarios

I ran the loader against the mock API with 20% of requests failing and a limit of 20 requests per second, and took the log after each scenario.

1. Killed on page 20. The first run was stopped after 20 pages. The table held 2,000 rows. Those 20 pages had cost 25 calls: 5 were retries of 500 responses. The cursor was not saved, since the run had not finished, which is the correct behavior.

2. Restarted. The second run began again from the start. It made 66 calls for 51 pages (15 were retries), took 4.4 s, fetched 5,000 rows and inserted 3,000, with 0 updates. The first 2,000 rows were fetched again and the guarded upsert wrote nothing for them. The table held 5,000 rows with 5,000 distinct ids, and 51 raw responses. A restart was harmless, so no resume logic was required for correctness; resuming would only have saved calls.

3. Nothing changed. An incremental run fetched 1 record in 1 call (the boundary record), inserted and updated 0. The cursor query with updated_since returned only the last record, because the inclusive comparison always returns the record at the cursor.

4. Fifty inserts, fifty changes. The incremental run fetched 101 records in 3 calls (one a retry) and wrote 50 inserts and 50 updates. The table went to 5,050 rows.

5. Again. Fetched 100 (the records sharing the last second), wrote 0. This is the repeat of the boundary: harmless, because the guard sees identical hashes.

6. Fifty deletions. The incremental run fetched 50 records and wrote nothing, and the table kept all 5,050 rows. The reconcile pass fetched the ids (5,000), found 50 rows missing from the API and deleted them. It would have refused to delete more than 505.

7. The API ships v2. Pointed at the renamed endpoint, the run stopped on page 1 with STOP: shape drift on page 1: missing [('price', 'float'), ('supplier', 'object')]. The table still had its 5,000 rows, with no partial write.

Each scenario was a bug in an earlier version of my own loader or one I have seen in someone else's. They are worth running against any loader you depend on, with a mock like this one, since you cannot make a real API misbehave on demand.

Each concern in depth

Landing JSON in a table. The simplest design is a table of (id, doc jsonb). Loading 5,000 records took 0.99 s one insert at a time, 0.07 s with executemany in a transaction and 0.02 s with COPY, and the second run of a plain insert failed on the primary key. Loading an API response into PostgreSQL as JSONB covers it, with a typed view over the raw documents.

Pagination. Page numbers shift when data changes during the crawl: 50 inserts at the top produced 50 duplicates, and 50 deletions skipped 50 live rows. A cursor lost none. Saving each page atomically makes the crawl resumable (paginating through a JSON API and saving every page).

Rate limits. The ceiling is the limit: 500 calls at 20 per second cannot take less than 25 s. Retrying at once wasted 1,469 of 1,969 requests, threads did not help, a client limiter sent no rejected requests, and bigger pages cut 500 calls to 5 (handling rate limits when loading JSON from an API).

Upserts. An unguarded ON CONFLICT DO UPDATE rewrote 5,000 unchanged rows and left 5,000 dead tuples; the guarded one wrote none, and also refused a stale copy that overwrote a newer price in the unguarded case.

Raw storage. 962 KB of responses took 222 KB as jsonb, an unchanged re-fetch stored nothing, and a mapping bug was fixed in 0.03 s from the stored responses (storing raw API responses before you transform them).

Shape drift. A signature of (path, type) pairs found a v2 rename and a type change that started on page 30 of a crawl, and a JSON Schema found the same ones (detecting when an API changes its JSON shape).

Nested data. Orders with customers and lines load into three tables, with children replaced in the same transaction as their parent, or removed lines stay behind (loading nested API data into parent and child tables).

Incremental loads. A strict greater-than comparison against the saved cursor missed all 5 records that arrived in the boundary second, an inclusive one found them, and deletions needed a reconcile pass (incremental loads from a JSON API using a cursor).

A real API. Three unauthenticated requests brought 300 GitHub issues into SQLite, 72 of them pull requests that the issues endpoint includes, with rate limit headers, a Link header and an ETag to read (loading GitHub API data into SQLite for analysis).

Retries. A rerun of a plain-insert loader after a crash on page 30 left 7,900 rows for 5,000 products; a POST that timed out after committing created 3 events without an idempotency key and 1 with one (retrying failed API calls without duplicating rows).

Choosing the database

The pattern does not depend on PostgreSQL, and the choice of target changes the details. PostgreSQL is the default for a shared, long-lived loader: real transactions, jsonb, ON CONFLICT, advisory locks, roles, and a server that outlives the job. Its cost is that you run a server.

SQLite is a good target for a loader on one machine, or for analysis of a bounded data set, as in the GitHub example. It has upserts (INSERT ... ON CONFLICT DO UPDATE), JSON functions, and a single file as the deliverable. Its trap is transactions. Fifty thousand single-row inserts to a file database in autocommit mode took 55 s in my test, because every statement waits for the disk. The same inserts inside one transaction took 0.08 s, and in WAL mode with synchronous=NORMAL and autocommit 3.65 s. Always wrap a page or a batch in one transaction. SQLite also allows one writer at a time, so parallel workers should queue their writes through one connection.

DuckDB suits analysis after loading: it reads the stored JSON files or the raw table directly and aggregates quickly. It is less comfortable as the target of many small upserts, since it is built for bulk operations, so land pages as files or batches and let it query them (analyzing JSON data with SQL). MySQL has the equivalent of the upsert (ON DUPLICATE KEY UPDATE) and JSON_TABLE, and the same design applies; check its default handling of backslashes in generated SQL if you ever write statements by hand (converting JSON to SQL INSERT statements).

Whatever you choose, keep the three layers: raw responses, typed tables and state. They have different lifetimes and different rules for deletion.

Types in API data

APIs are loose about types, and the loader is where you tighten them. Identifiers: keep as text unless you know they are small integers, since some APIs return the same id as a number in one endpoint and a string in another, and JSON numbers above 2^53 lose digits in many parsers (how JSON handles big numbers). Money: store as numeric with a declared scale, never as a float, and prefer integer minor units if the API offers them (price_cents in the v2 of my mock). Timestamps: parse to timestamptz and make the zone explicit; a string without an offset is a local time of unknown zone. Booleans arrive as true, "true", 1 or "yes", and the loader should accept the forms the API documents and reject the rest. Nulls and missing keys mean different things; decide which maps to NULL and which to a default, and write it down.

Put the conversions in one function per record type. Then a type surprise (a price that turns into a string on page 30, as in my drift test) fails in one place with a clear message, rather than in a cast at the bottom of a view. Convert strictly in the loader, and keep the lenient parse for the raw layer, where nothing is converted at all.

Overlapping runs and scheduling

Two runs of the same loader at the same time are a failure mode of their own. A cron job that takes longer than its interval starts a second copy while the first is still running, and the two fight over the cursor, the rate limit and the rows. With idempotent writes the data survives, and the cursor and the quota do not. Prevent it with a lock that the database releases for you: a PostgreSQL advisory lock, taken at the start of the run with pg_try_advisory_lock(key).

cur.execute("SELECT pg_try_advisory_lock(%s)", (4242,))
if not cur.fetchone()[0]:
    raise SystemExit("another run holds the lock")

I tested it with two connections. The first run got True, a second run started while the first was alive got False and exited, and after the first connection closed (as it would on a crash) the lock was free again and the next run got True. An advisory lock lives as long as the session, so a crashed loader cannot leave a stale lock behind, which a lock file or a row flag can. Schedule with a margin, set a timeout on the whole run, and alert when a run exceeds it.

Run times should also be in your control. A nightly job that starts at the same minute as other jobs hitting the same API competes for the rate limit. Spread the starts, and let the loader read the limit from configuration.

Backfills and changes to the mapping

The raw table pays for itself again when the requirements change. A new field is wanted in the products table. Add the column, write the new expression in the transform, and rebuild from the stored responses: INSERT ... SELECT over jsonb_array_elements rebuilt 5,050 rows from 100 stored responses in 0.03 s in my test. No API call, no rate limit, no waiting. Compare with the alternative, which is a full crawl at the API's pace. The same applies to a mapping bug, which is bound to happen: I truncated prices in a test, the total was 1,253,318.00 instead of 1,255,846.96, and a truncate and reload from the raw table fixed it in 0.03 s.

Keep the transform as one script that can run from scratch against the raw table, and treat the typed tables as disposable. Version the script with the loader. When the API adds fields you start to care about, the history of what the API returned is already there, back to the day the loader started. A transform that depends on state held only in the typed tables cannot be rebuilt, so avoid it.

Backfills of the source itself (you need data from before the loader existed) are a different problem: the cursor starts at a chosen date, or the API's export feature supplies the history. Run backfills with the same code path as normal runs, so that they get the same checks, and with a lower request rate than the production schedule.

How many calls will it take

Before building anything, do the arithmetic, because the call count decides the design. The number of requests is the number of records divided by the page size, and the time is that divided by the request rate. For the 5,000-record API:

For a million records the same arithmetic gives 100,000 calls at 10 per page (about 83 minutes at 20 per second), 10,000 calls at 100 per page (8 minutes) and 1,000 calls at 1,000 per page (50 s). These are products of the figures above, not measurements, and real APIs add latency per call, which dominates at large page sizes: a call that returns a megabyte may take a second. The point is that a 100-fold difference in page size is a 100-fold difference in run time and in exposure to failures, so check the maximum page size first, and then the fields you can leave out.

Daily limits change the question. If the provider allows 10,000 calls a day, a million records at 100 per page uses all of it. An incremental load that fetches only what changed turns that into a fraction. Do the arithmetic for both the first load and the steady state, and for the steady state at the busiest hour.

What I would check in a review

When reading a colleague's loader, these are the questions that find the problems:

• What happens if it is killed right now? Right after the cursor is saved, right before?

• What happens if it is run twice at once? Twice in a row?

• Is there a unique key on the target, and does the write use it?

• Does a page's write happen in one transaction, children and parents together?

• Which HTTP statuses are retried, how often, and what is the delay? Is Retry-After read?

• Is there a limit on retries and on total run time?

• What does it do when a field is missing? When a type changes? When the list is empty?

• Where is the raw response, and who can read it?

• How are deletions handled?

• What is logged, and would a silent failure be noticed?

• Where do the credentials come from, and do they appear in logs or stored requests?

The answers show quickly whether the loader is designed for the bad day. Most loaders I have seen fail the first two.

The first day of an API change

When the drift check fires, the work follows a sequence, and it is shorter if you rehearse it. Read the report: which fields, which pages, which share of records. Check the provider's changelog and status page. Look at a few raw responses in the raw table. Decide whether the load should have stopped (usually yes), and whether the change is a new version, a bug on their side or a gradual rollout (the page where the new shape first appeared helps here). Fix the mapping in the transform, test it on the stored responses from before and after the change, update the baseline, and rerun the load, which resumes from the saved cursor. If the change is a bug on the provider's side, report it with an example response from the raw table: it is the evidence they ask for.

Write down what happened and what the check cost you. A drift check that fires once a year and saves a day of cleaning pays for itself many times over, and the log of its firings is the best argument for keeping it.

Monitoring: what to log on every run

A loader that works today will break quietly some other day, so make it report enough to tell. One structured line per run: the source, the cursor it started from and the one it saved, the number of calls, retries and rejected requests, the number of records fetched, inserted, updated and deleted, the shape check result, the duration. Keep them in a table. Then alert on the changes, not the failures: a run that fetched 0 records for three days in a row, an update count ten times its average, a drift warning, a run that took twice as long as usual, a retry count that is climbing. The silent failures are the dangerous ones: a token that expired and now returns an empty list with a 200, or a filter that stopped matching.

Pair the log with the checks from each article: row count against the API's reported total when it gives one, distinct ids against rows, a money or quantity total against another source. These take seconds and catch what the logs cannot.

Credentials and data

Keep tokens out of code, out of the raw tables and out of logs (strip Authorization headers and any key in the URL before storing the request). Give the loader a database role that can write only its own tables. If responses contain personal data, the raw table is a second copy of it with its own retention duty: decide how long to keep bodies and delete on schedule. Respect the provider's terms on storing and redistributing their data, and on rate limits, which are part of the contract.

Testing a loader

The mock server is the point. A loader that has only met a healthy API has never been tested. The faults to inject, each a short test: a share of 500 responses; a rate limit; a timeout after the work was done (for POST); the server changing the data between pages; a deleted record; a renamed field; a type change partway; an empty page in the middle; a response that is not JSON (an HTML error page with a 200). For each, say what should happen (continue, stop, retry, alert) and assert it. Run them in CI. A hundred lines of mock is a small price, and every scenario above came from this list.

When to use a ready-made connector

Many sources with the same needs. If you load dozens of APIs, a connector framework or a managed ELT service already implements pagination, state, retries and schema handling. Writing the sixth loader by hand is worse than learning the tool.

The source is a common product. For large SaaS systems, maintained connectors track their API changes for you.

The volume is large. Hundreds of millions of records a day need parallel readers, queues and a warehouse that can ingest them; the structure above still applies and the implementation does not.

A bulk export exists. A full dump delivered as files is better than a million API calls.

For the background, see the PostgreSQL manual on INSERT ... ON CONFLICT and JSON types, the HTTP rules for retry-after and idempotent methods in RFC 9110, and the requests documentation on sessions. To look at a response, paste it into the JSON formatter.