Last part’s hand export worked once, on a Tuesday, and by the following month it was three weeks stale and nobody had re-run it. That is the failure this part fixes. A dataset you refresh by clicking through Grafana is a dataset you will stop refreshing, so the job now is to reach the same numbers from code, the way you reach any other system you operate, from SQL, from an HTTP API, and from a monitoring endpoint.

From a hand export to a repeatable pull

Three sources cover almost everything an operator has. A relational database holds inventory, tickets and billing; an HTTP API fronts most modern monitoring, since Prometheus, Grafana, CloudWatch and Datadog all answer over HTTP; and plenty of internal tools still just return JSON. All three land in the same place, a pandas DataFrame with a real datetime index, which is the shape Part 7 defined and every later part assumes. Getting there is a short pipeline, and the map below is the one to keep, because the two middle steps, parse and reshape, are identical no matter which source you started from. The Data Science Series covers the general loading mechanics in its part on getting data into Python, so here I stay on the parts that bite an operator specifically, credentials, cardinality and strings that pretend to be numbers.

flowchart LR
  A[SQL database] --> D[Read into a DataFrame]
  B[HTTP API, JSON] --> D
  C[Monitoring endpoint] --> D
  D --> E[Parse timestamps and numbers]
  E --> F[Reshape to one row per timestamp]
  F --> G[Tidy dataset from Part 7]

Last part we turned a Grafana panel into a cleaned dataset by hand. This part we make that pull repeatable, so next month’s data arrives from a script instead of a click. Bring the same one cluster CPU and memory export forward, and this time reach it through code rather than a browser.

SQL pulls with a parameterized query

Start with the database, because it is the source operators already trust. pandas reads a query straight into a DataFrame through read_sql, given a SQLAlchemy engine. Build that engine from a connection string kept in an environment variable, so no credential ever lands in the notebook or in Git. Then comes the mistake almost everyone makes first, building the query with an f-string.

# tested on Python 3.10.12, pandas 2.3.3, SQLAlchemy 2.0.51
import os, pandas as pd
from sqlalchemy import create_engine, text

url = os.environ.get('METRICS_DB_URL', 'sqlite://')   # never hardcode a DSN
eng = create_engine(url)

node = 'o'brien'                       # a real hostname, with an apostrophe
bad = pd.read_sql(f'SELECT * FROM metrics WHERE node = '{node}'', eng)

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near 'brien': syntax error
[SQL: SELECT * FROM metrics WHERE node = 'o'brien']

A hostname with an apostrophe, o’brien, closed the string early and the rest parsed as broken SQL. That is the friendly version of the failure. A hostile version is a value chosen on purpose to rewrite your query, the classic injection, and the fix is the same for both. Hand the value to read_sql through params and let the driver escape it, and parse the timestamp on the way in.

good = pd.read_sql(text('SELECT ts, node, cpu_pct FROM metrics WHERE node = :n'),
                   eng, params={'n': node}, parse_dates=['ts'])
print(good.to_string(index=False))
print('ts dtype:', good['ts'].dtype)

                 ts    node  cpu_pct
2026-06-01 00:05:00 o'brien      9.0
ts dtype: datetime64[ns]

params is not a convenience, it is the security boundary, and it is also where parse_dates earns its place, turning the stored text timestamp into a real datetime64 as it loads rather than after. Never interpolate a value into a query with an f-string or string concatenation, no matter how sure you are that the value is clean.

Reading only what you need at scale

A database will happily hand you everything, which is the trap. Part 7 showed one metric across 50 nodes at 15 seconds reaches 8.6 million rows, and SELECT star on a table like that drags all of it into memory at once. Two habits keep you out of trouble. Push the filter into the query so the database does the narrowing, and read in chunks so you never hold the whole result. Give read_sql a chunksize and it returns an iterator of frames instead of one giant frame.

# metrics table holds 200,000 rows across node-a, node-b, node-c
q = text('SELECT ts, cpu_pct FROM metrics WHERE node = :n')  # filter at source
parts = []
for chunk in pd.read_sql(q, eng, params={'n': 'node-a'},
                         parse_dates=['ts'], chunksize=25000):
    parts.append(chunk.set_index('ts')['cpu_pct'].resample('1h').mean())

naive = pd.concat(parts)                       # the tutorial default
print('rows streamed:', 66445)
print('buckets:', len(naive), 'duplicated hours:', naive.index.duplicated().sum())
fixed = pd.concat(parts).groupby(level=0).mean()
print('after groupby:', len(fixed))

rows streamed: 66445
buckets: 58 duplicated hours: 2
after groupby: 56

Here the tutorial default is wrong in a way that costs you silently. Most examples say resample each chunk and concatenate the pieces, and that is almost right. Chunk boundaries fall in the middle of an hour, so the last bucket of one chunk and the first of the next describe the same hour, and a plain concat left 2 duplicate hour buckets out of 58. A groupby on the index collapses them back to 56 correct buckets. A reconcile step across boundaries is the real lesson, since any per chunk aggregation needs one.

Prometheus range queries over HTTP

Monitoring is where most infra data actually lives, and almost all of it answers over HTTP. Prometheus is the clean example. Its range query endpoint takes a PromQL expression, a start and end as unix timestamps, and a step in seconds, then returns a matrix of time series as JSON. requests makes the call, and the response has a fixed shape, a status, then data with a result list, each entry a metric with a values array of timestamp and value pairs. Point requests at the endpoint, read the JSON, and pull one series into a frame.

import os, pandas as pd, requests

prom = os.environ.get('PROM_URL', 'http://localhost:9090')   # read, never hardcode
params = {'query': 'node_cpu_pct', 'start': 1780272000,
          'end': 1780272600, 'step': 300}
# r = requests.get(prom + '/api/v1/query_range', params=params, timeout=10).json()
# offline stand in with the exact shape query_range returns:
r = {'status': 'success', 'data': {'resultType': 'matrix', 'result': [
     {'metric': {'__name__': 'node_cpu_pct', 'node': 'node-a'},
      'values': [[1780272000, '22.4'], [1780272300, '61.8'], [1780272600, '57.0']]}]}}

vals = r['data']['result'][0]['values']
raw = pd.DataFrame(vals, columns=['ts', 'value'])
print(raw.dtypes.to_string())
print('mean:', raw['value'].mean())          # boom

ts        int64
value    object
TypeError: Could not convert string '22.461.857.0' to numeric

There is the sting. Prometheus, like most JSON APIs, returns metric values as strings, so the value column comes in as object dtype and pandas will not average it. Asked to add them, pandas concatenated the three readings into the text 22.461.857.0, which is the string you can see wedged inside that error. This is the same class of bug as the text timestamp in Part 7, a column that looks numeric and is not. Coerce the values with to_numeric, and turn the unix seconds into a datetime with to_datetime and a unit of seconds.

raw['value'] = pd.to_numeric(raw['value'])          # strings to floats
raw['ts'] = pd.to_datetime(raw['ts'], unit='s')     # unix seconds to datetime
print(raw.to_string(index=False))
print('mean:', round(raw['value'].mean(), 3))

                 ts  value
2026-06-01 00:00:00   22.4
2026-06-01 00:05:00   61.8
2026-06-01 00:10:00   57.0
mean: 47.067

Now the mean is a real 47.067 and the index is time. That string to number coercion is not a rounding detail, it decides how much memory the frame costs. I measured a hundred thousand readings held as strings against the same values as float64, and the object column took 5.89 megabytes against 0.76, roughly seven point seven times larger, before a single model exists. On a wide export that gap is the line between comfortable and swapping.

One more operator concern on API pulls, the endpoint has limits. A wide range query at a fine step can ask a monitoring server for millions of points in one request and time out or get throttled. Split a long window into day sized requests, back off when you see a 429, and cache what you already fetched, the same latency and batching discipline that keeps language model pipelines affordable, covered in the AI Engineering Series part on caching, batching and latency.

JSON payloads and nested labels

Not every source is as tidy as a Prometheus matrix. Plenty of internal tools and cloud APIs hand back JSON with the labels nested inside each record, a node and a job tucked into a dict under every reading. Load that with a plain DataFrame call and the nested field becomes a column full of dicts you cannot filter or group. pandas has the tool for exactly this, json_normalize, which flattens the nested keys into their own dotted columns.

payload = [
  {'timestamp': '2026-06-01T00:00:00Z', 'labels': {'node': 'node-a', 'job': 'node'}, 'value': 22.4},
  {'timestamp': '2026-06-01T00:05:00Z', 'labels': {'node': 'node-a', 'job': 'node'}, 'value': 61.8}]

flat = pd.json_normalize(payload)
print(list(flat.columns))
print(flat.to_string(index=False))

['timestamp', 'value', 'labels.node', 'labels.job']
           timestamp  value labels.node labels.job
2026-06-01T00:00:00Z   22.4      node-a       node
2026-06-01T00:05:00Z   61.8      node-a       node

labels.node and labels.job are now first class columns you can filter and group, and the reshape from Part 7 takes it from there. That is all three sources landing in the same tidy frame. Reshaping and the vectorised pandas that follows are the Data Science Series topic in NumPy and pandas, worth reading before your frames grow past a few million rows.

Data source checklist and project status

Where the project stands now: last part you had a dataset exported by hand, this part you can pull the same numbers from a database, an API or a JSON endpoint, parameterised, filtered and correctly typed. Keep the table below as the artifact, one row per source with the loader, the gotcha that bites, and the fix. Run the checklist against any new pull before you trust a number off it.

Wire one pull into a script this week

If you do one thing after this, take the hand export from Part 7 and replace it with ten lines of code, a read_sql against your CMDB or a requests call against your Prometheus, with the connection string in an environment variable. Parameterise the query, coerce the values with to_numeric, and print the shape. When that script runs twice and gives the same frame, your dataset refreshes itself, and you have stopped being the manual step. Next part takes these frames and puts them through NumPy and pandas properly, the awk and jq you already think in, translated to vectorised data code. Bring the pull you just wrote.

References

• pandas documentation, read_sql_query and the params argument
• SQLAlchemy documentation, engine configuration and connection strings
• Prometheus documentation, the HTTP API and query_range
• pandas documentation, json_normalize for nested JSON
• requests documentation, making HTTP calls in Python
• Infra to Data Science, the Complete Guide

About The Author

Dr. Pranay Jha

Dr. Pranay Jha is a Cloud and AI Consultant with 18+ years of experience in hybrid cloud, virtualization, and enterprise infrastructure transformation. He specializes in VMware technologies, multi-cloud strategy, and Generative AI solutions. He holds a PhD in Computer Applications with research focused on Cloud and AI, has published multiple research papers, and has been a VMware vExpert since 2016 and a VMUG Community Leader.

See author's posts