Fetching Data from APIs and Databases

Not all data comes from files. This topic shows how to pull data from web APIs and SQL databases straight into Pandas.

a. Working with APIs Using requests

An API is a way for programs to talk to each other. Many websites offer APIs that return data, such as weather, stock prices, or public records. In Python, the requests library makes it simple to call an API.

import requests
url = "https://api.example.com/data"
response = requests.get(url, params={"city": "Chennai"}, timeout=10)
print(response.status_code)

The status code tells you if the call worked. A code of 200 means success. Codes like 404 mean not found, and 401 or 403 mean you do not have permission.

Many APIs need a key. Keep your key out of your code by storing it in an environment variable. Always check the API's documentation for limits on how often you can send requests.

b. Parsing JSON Responses

Most APIs return data in JSON format. The response.json() method turns it into Python dictionaries and lists, which you can work with right away.

data = response.json()
print(data.keys())

Look at the structure first. The data you want is often inside a nested key, like data["results"]. Pick that part and load it into a DataFrame.

df = pd.DataFrame(data["results"])

If the JSON is deeply nested, use pd.json_normalize() to flatten it into columns. Add error handling with response.raise_for_status() so that failed calls do not go unnoticed.

c. Connecting Python to SQL Databases

Companies store most of their data in SQL databases. Python can connect to them and run queries. The easiest way to practice is with SQLite, which is built into Python and needs no setup.

import sqlite3
conn = sqlite3.connect("shop.db")

For other databases like MySQL or PostgreSQL, you can use SQLAlchemy. It creates a connection from a URL and works with many database types.

from sqlalchemy import create_engine
engine = create_engine("sqlite:///shop.db")

Always keep passwords and connection details out of your code. Close the connection when you are done to free resources.

d. Loading Query Results into Pandas

The pd.read_sql() function runs a SQL query and returns the result as a DataFrame. This lets you combine the power of SQL for selecting data with Pandas for analysis.

query = "SELECT region, SUM(sales) AS total FROM orders GROUP BY region"
df = pd.read_sql(query, engine)

Let SQL do the heavy work of filtering and grouping, especially on large tables. Then bring only the needed rows into Pandas. This is faster and uses less memory.

You can also write a DataFrame back to a database using df.to_sql("table_name", engine, if_exists="replace", index=False). This is helpful for saving cleaned data for others to use.