Talk to a real database from Python

How an API works

An API (Application Programming Interface) is a way for one program to ask another program for something. A web API does it over the internet using HTTP, the same language your browser speaks.

What it actually looks like

This is the exact text that travels over the network when you ask for employee 10001, and the answer that comes back. Python's requests library writes and reads this text for you.

JSON in one minute

JSON is how the API writes data. It maps directly onto Python:

Quick start

pip install requests pandas

Your token

Except for /api/health and /api, every request must prove you're in the class. You do that with a token: a long secret string your teacher gives you. Send it in the Authorization header with the word Bearer and a space in front:

Authorization: Bearer YOUR_TOKEN

In Python, make the header once and pass it to every call:

headers = {"Authorization": f"Bearer {TOKEN}"}
response = requests.get(f"{BASE_URL}/api/departments", headers=headers)

Forget the header and you get 401 Unauthorized:

Methods = CRUD

Almost everything you do with data is one of four things: Create, Read, Update, Delete. Each has an HTTP method. If you know SQL, here's the match:

GET is safe: it never changes anything, so you can repeat it freely. POST, PUT, PATCH and DELETE change data.

Status codes

The first digit tells you the story: 2xx it worked, 4xx something is wrong with your request, 5xx something is wrong on the server.

Errors

Every error has the same shape, so your code can handle all of them the same way:

{
  "error": {
    "status": 404,
    "code": "not_found",
    "message": "No employee with id 1."
  }
}

code is for your program (it never changes); message is for you to read. For 422 errors there is also details, a list saying exactly which field is wrong and why. Here's a real one: the request below has a bad gender, an impossible date, and missing fields:

In Python, check the status before using the data:

response = requests.get(f"{BASE_URL}/api/employees/1", headers=headers)
if response.ok:  # True for any 2xx code
    print(response.json())
else:
    error = response.json()["error"]
    print("Problem:", response.status_code, error["message"])

Pages and filters

There are 300,024 employees: far too many for one response. Lists come back one page at a time:

So page 1 is ?limit=20&offset=0, page 2 is ?limit=20&offset=20, and so on. Every list answer tells you where you are:

{
  "total": 300024,      // matches in all pages
  "limit": 20,
  "offset": 0,
  "count": 20,          // items on this page
  "next": "https://employees-api.taila30d72.ts.net/api/employees?limit=20&offset=20",
  "previous": null,     // null = this is the first page
  "data": [ ... ]
}

Follow next until it is null to collect everything (see the tutorial). Filters are more query parameters, like ?gender=F&department_id=d005. In requests, pass a dict as params= and it builds the query string for you.

The data

Six tables, linked by ids. An employee can have many salaries and titles over the years, and can move between departments.

The data covers 1985 to 2002 and is a well-known sample database: the people are made up. Department ids look like d001–d009; employee ids run from 10001 to 499999, and new ones you create start at 500000.

Endpoints

Each endpoint below shows: what it does, its inputs, a Python sample you can copy, the real request and response it produced when we ran it, and what the sample printed. Samples that change data first create their own record, so they never touch your classmates' work.

Practice tutorials

Longer programs that combine several calls. Each one was run against the live API; the output shown is real.

Case study

Talk to an AI model with the OpenAI API

You now know how APIs work. OpenAI's API is the same idea: you send an HTTP request with a key and JSON, and get JSON back. The only difference is what answers: a large language model, gpt-4o-mini. By the end of this case study your Python program will let the model look things up in the Employees API by itself.

Same concepts you already know

Set up once

pip install openai requests pandas

export OPENAI_API_KEY="sk-..."

$env:OPENAI_API_KEY = "sk-..."

import os
os.environ["OPENAI_API_KEY"] = "sk-..."

The steps

Each step builds on the one before. Every sample was run for real; the output shown is what it printed. AI answers vary, so yours will be worded differently.

Try it yourself

1. In step 3, change the instructions so the model answers like a pirate, or in Uzbek. What changes, what doesn't?
2. In step 6, add a field "department" (which department should handle the comment) to the schema, and count comments per department with pandas.
3. In step 7, add a third tool list_department_employees(department_id) that calls /api/departments/{department_id}/employees, then ask: “Who are three people in Finance?”
4. Use step 8's cost formula: how much would labelling 10,000 comments with step 6 cost?
5. Make the tool loop print how many tokens the whole conversation used in total.

Rules and limits

Advanced: the machine-readable OpenAPI description is at /api/openapi.json, and an interactive Swagger page at /api/swagger.

Glossary

API

A set of rules for how programs ask each other for things.

Endpoint

One method + path the API answers, like GET /api/employees.

Client / Server

The client asks (your Python program); the server answers (this API).

HTTP

The language of the web: requests with methods, headers and bodies; responses with status codes.

Base URL

The start of every address: https://employees-api.taila30d72.ts.net.

Path parameter

A value inside the path, like the 10001 in /api/employees/10001.

Query parameter

A value after ? in the URL, like ?limit=5. Used for filters and pages.

Header

A name: value line sent with a request or response, like Content-Type: application/json.

Body / Payload

The data sent with a request (POST/PUT/PATCH) or response, written as JSON.

JSON

A text format for data with objects, arrays, strings, numbers, true/false and null.

Token

A secret string that proves who you are. Sent as Authorization: Bearer ….

Status code

A three-digit number in every response saying how the request went.

CRUD

Create, Read, Update, Delete: the four basic things you do with data.

Pagination

Splitting a long list into pages with limit and offset.

Rate limit

A cap on how many requests you may send per minute.

REST

A common style of web API: things are “resources” at URLs, and methods say what to do with them.

Examples on this page were recorded from the live API on .