--- title: "How to query a REST API in Python" date: "2026-06-15" author: "Al Brown" category: "Engineering" excerpt: "Read a JSON API response into a DataFrame with chDB. Use the pandas API you already know to filter and aggregate the response, running on ClickHouse's engine with no server to start." --- # How to query a REST API in Python To query a REST API in Python, use [chDB](/resources/engineering/what-is-chdb) to read the JSON response directly into a DataFrame: ```python from chdb.datastore import DataStore df = DataStore.from_url("https://api.example.com/orders", format="JSONEachRow") ``` `df` supports the pandas API you already use (indexing, filtering, `groupby`, `merge`), but the work runs on ClickHouse's engine instead of pandas, so it stays fast as files grow. There is no server to start and no separate load step. ```bash pip install chdb ``` Prefer the command line? See [how to query a REST API with SQL](/resources/engineering/query-rest-api-with-sql) to do this with clickhouse local. ## Read a JSON API response into a DataFrame The companion example serves sample data over Python's built-in `http.server`. That stand-in for a real API shows the exact call you would make against any live JSON endpoint. ```python from chdb.datastore import DataStore df = DataStore.from_url("http://127.0.0.1:8731/orders.json", format="JSONEachRow") print(df) print(df.dtypes) ``` ```text id country status amount labels 0 0 GB open 10.99 [emea, gold] 1 1 AU closed 12.36 [apac, silver] 2 2 IN pending 13.73 [amer, bronze] 3 3 US open 15.10 [emea, gold] 4 4 DE closed 16.47 [apac, silver] id int64 country str status str amount float64 labels object dtype: object ``` chDB infers the schema from the response: `id` comes back as an integer, `amount` as a float. The nested `labels` field comes back as an object column (a numpy array per row when you materialize). You declared nothing. One thing is different from a regular DataFrame: `df` is not the response loaded into memory. It is a lazy, ClickHouse-backed object that records what you asked for and runs on ClickHouse's engine when you materialize a result, whether you print it, take `len()`, or call `.to_pandas()`. That is why it stays fast on large responses. ## Filter and aggregate the way you already do The pandas you write does not change. Filter with a boolean mask, group, and aggregate: ```python from chdb.datastore import DataStore df = DataStore.from_url("http://127.0.0.1:8731/orders.json", format="JSONEachRow") revenue = ( df[df["status"] != "closed"] .groupby("country")["amount"] .sum() .sort_values(ascending=False) ) print(revenue.to_pandas()) ``` ```text country US 15.10 IN 13.73 GB 10.99 Name: amount, dtype: float64 ``` Same syntax as pandas, same result. chDB compiles the whole chain into one optimized query and only materializes what you asked for, rather than pulling the full response into memory first. Joins with `merge`, computed columns with `assign`, and the `.str` and `.dt` accessors all work the same way. ## Hand off to real pandas when you need it When a library downstream needs an actual pandas DataFrame (scikit-learn, a plotting call, anything that mutates in place), call `.to_pandas()` to materialize one: ```python df = DataStore.from_url("http://127.0.0.1:8731/orders.json", format="JSONEachRow") pdf = df.to_pandas() # a real pandas.DataFrame, in memory ``` The usual pattern is to do the heavy filtering and aggregation on the chDB object first, then `.to_pandas()` the small result and continue in ordinary pandas. ## Multiple pages: read each URL, then concat When an API paginates across predictable URLs, read each page into a DataFrame and concat them: ```python import pandas as pd from chdb.datastore import DataStore page1 = DataStore.from_url("http://127.0.0.1:8731/orders_page1.json", format="JSONEachRow").to_pandas() page2 = DataStore.from_url("http://127.0.0.1:8731/orders_page2.json", format="JSONEachRow").to_pandas() pages = pd.concat([page1, page2], ignore_index=True) print(pages.groupby("country")["amount"].sum().sort_values(ascending=False)) ``` ```text country IN 1.82 US 1.06 AU 0.91 DE 0.53 GB 0.00 Name: amount, dtype: float64 ``` ## Is it faster than requests + json? On a large response, yes. The same filter-and-aggregate over a 2M-row (~120 MB) JSON endpoint, served on localhost so network latency is out of the picture, best-of-3 with a warm cache on an Apple M4 Pro (14 cores, 24 GB RAM, macOS): ```text requests + json + manual agg: 2.067s DataStore.from_url (chDB): 0.162s speedup: 12.8x ``` About 12-13x faster here. The gap is fetch-plus-parse-plus-aggregate cost: chDB streams the response and aggregates as it goes, while the Python loop decodes every line into objects before it can do anything with them. Against a real API the network round trip adds equally to both sides, so it does not change the ratio; it just raises the floor. The back-to-back ratio is the robust claim; absolute times shift with concurrent load. On a tiny response the comparison can flip, because chDB pays a small fixed per-query cost that dominates when there is barely any data. Reach for `DataStore.from_url` when the payload is large enough that parsing and aggregating it is the real work. Hardware: Apple M4 Pro (14 cores, 24 GB RAM, macOS); chDB 4.1.8, Python 3.14; best-of-3, warm. ## Works in Jupyter Printing a chDB object renders it as a table in a notebook, and `.to_pandas()` feeds straight into `.plot()`, joins, or any pandas code. The companion folder ships a `run.ipynb` you can open and run cell by cell. ## Run it yourself The complete, runnable example is here, with `generate.sh` to create the sample JSON files, plus `run.ipynb` and a `run.py` mirror containing the exact code above: [github.com/ClickHouse/examples/tree/main/local-analytics/chdb-rest-api](https://github.com/ClickHouse/examples/tree/main/local-analytics/chdb-rest-api) Working with JSON from other sources? [Read a JSON file in Python](/resources/engineering/read-json-file-python) and [read a JSON Lines file in Python](/resources/engineering/read-jsonl-file-python) use the same pandas drop-in pattern; only the reader changes. The same code scales from a local script to [ClickHouse Cloud](https://clickhouse.com/cloud) with no rewrite.