Files, Data Formats, APIs and Databases

Introduction

Data science depends on moving data reliably between files, software systems, web services, and databases. A Python program may read a configuration file, ingest a CSV dataset, request current information from an API, transform records, and store the results in a relational database. These operations are often described as input/output (I/O), but professional data ingestion involves more than reading and writing: it requires correct paths, suitable encodings, validation, error handling, security, reproducibility, and awareness of data quality.

This chapter develops the practical foundations required to construct such workflows. Examples use Python’s standard library wherever possible. The same principles apply when working with specialist tools such as pandas, SQLAlchemy, requests, or cloud storage libraries.

Learning objectives

By the end of this chapter, you should be able to:

• read and write text files safely using context managers and explicit encodings;
• construct portable file paths with pathlib;
• process CSV and JSON data and distinguish their appropriate uses;
• explain serialization and work with binary files;
• consume web APIs using HTTP methods, parameters, headers, and JSON responses;
• connect Python to a relational database and execute parameterized SQL;
• design a reliable ingestion process with validation, logging, retries, and idempotency;
• identify security, privacy, ethical, and governance risks in data access.

1. Files as persistent data

A variable exists only while a program is running. A file provides persistence: data remains available after the program terminates and can be exchanged between programs. Files are organized by a filesystem, which usually represents locations as paths.

A path may be:

• absolute, such as /home/ana/data/sales.csv or C:\Users\Ana\data\sales.csv;
• relative, such as data/sales.csv, interpreted relative to the program’s current working directory;
• a directory path, identifying a folder rather than a file.

Relative paths are convenient but can produce errors when a program is launched from a different directory. pathlib.Path provides an operating-system-independent interface.

from pathlib import Path

project = Path(__file__).resolve().parent
input_file = project / "data" / "sales.csv"

if not input_file.exists():
    raise FileNotFoundError(input_file)

The / operator joins path components. It does not mean string concatenation, and it automatically uses the correct separator for the operating system. Useful methods include exists(), is_file(), is_dir(), name, suffix, parent, glob(), and mkdir().

output_dir = project / "output"
output_dir.mkdir(parents=True, exist_ok=True)

for path in (project / "data").glob("*.csv"):
    print(path.name)

A program should not assume that the current working directory is the project directory. For reusable applications, obtain paths from configuration, command-line arguments, or a documented project root.

Opening text files

Python’s open() function creates a file object. The mode specifies the operation:

Text mode should normally specify an encoding, commonly UTF-8. A context manager closes the file even if an exception occurs.

from pathlib import Path

path = Path("notes.txt")
path.write_text("First line\nSecond line\n", encoding="utf-8")

with path.open("r", encoding="utf-8") as file:
    for line in file:
        print(line.rstrip("\n"))

Reading an entire large file with read() can consume substantial memory. Iterating over the file processes one line at a time. readline() reads one line, while readlines() returns a list of lines. The newline character is part of a line returned by iteration, so rstrip("\n") removes it without removing other whitespace.

Text, encoding, and newline issues

Text is represented internally by Unicode code points, while an encoding converts those characters to bytes. UTF-8 is widely used because it represents Unicode text and is compatible with ASCII for ordinary English characters. An incorrect encoding can cause UnicodeDecodeError or silently corrupted data if a system applies an inappropriate fallback.

For portable text exchange:

1. choose and document an encoding, preferably UTF-8;
2. specify it when opening files;
3. handle newline conventions consistently;
4. avoid silently replacing undecodable characters unless data loss is acceptable.

2. CSV data

Comma-separated values (CSV) represent tabular data as rows and fields. The first row may contain column names. CSV is simple and interoperable, but it does not intrinsically store data types, constraints, relationships, or formatting. A value such as 00125 may be an identifier rather than the number 125; automatic conversion can therefore be harmful.

Python’s csv module correctly handles delimiters, quoted fields, embedded commas, and line endings better than manual string splitting.

import csv
from pathlib import Path

with Path("sales.csv").open("r", newline="", encoding="utf-8") as file:
    reader = csv.DictReader(file)
    for row in reader:
        product = row["product"]
        quantity = int(row["quantity"])
        print(product, quantity)

When writing CSV, newline="" is recommended so the CSV module controls line endings.

records = [
    {"product": "Notebook", "quantity": 4, "price": 3.50},
    {"product": "Pen", "quantity": 10, "price": 1.20},
]

with Path("clean_sales.csv").open("w", newline="", encoding="utf-8") as file:
    writer = csv.DictWriter(file, fieldnames=["product", "quantity", "price"])
    writer.writeheader()
    writer.writerows(records)

CSV processing should define how missing values, duplicate rows, invalid numbers, dates, and unexpected columns are treated. For data intended for analysis, preserve the original file and write a cleaned version rather than overwriting the source.

3. JSON data and serialization

JSON (JavaScript Object Notation) represents structured data using objects, arrays, strings, numbers, Boolean values, and null. Python’s corresponding structures are dictionaries, lists, strings, numbers, True or False, and None.

import json
from pathlib import Path

config = {
    "host": "localhost",
    "port": 5432,
    "features": ["sales", "inventory"],
    "debug": False
}

path = Path("config.json")
with path.open("w", encoding="utf-8") as file:
    json.dump(config, file, indent=2)

with path.open("r", encoding="utf-8") as file:
    loaded = json.load(file)

json.dumps() converts a Python object to a JSON string, while json.loads() parses a JSON string. dump() and load() operate on file objects. JSON is particularly suitable for nested records, configuration, and API responses. It is less suitable than a relational database for complex queries across very large datasets.

Not every Python object is directly JSON serializable. For example, a datetime, set, or custom class requires conversion to a supported representation. A common approach is to use an ISO 8601 string for dates and times.

from datetime import datetime, timezone

record = {"created_at": datetime.now(timezone.utc).isoformat()}

Deserialization must be treated as data input, not automatically trusted content. Validate required fields, types, allowed values, and limits after parsing.

Serialization and binary files

Serialization is the conversion of an in-memory object into a storable or transferable representation. Deserialization reconstructs an object from that representation. JSON is language-independent and relatively transparent. Binary formats are often more compact or faster, but may be language-specific or less inspectable.

Binary files must be opened with b in the mode, and operations produce bytes rather than strings.

from pathlib import Path

data = bytes([10, 20, 30, 255])
Path("sample.bin").write_bytes(data)
restored = Path("sample.bin").read_bytes()

Python’s pickle module can serialize many Python objects, but never unpickle data from an untrusted source: deserialization can execute malicious code. For exchange, prefer JSON, CSV, Parquet, or another documented, validated format. For binary data such as images, audio, or compressed archives, preserve the format specification and verify integrity where necessary.

4. Web APIs

An application programming interface (API) defines how software requests and exchanges data. A web API commonly uses HTTP. A client sends a request to a URL, possibly including query parameters, headers, and a body. The server returns a response containing a status code, headers, and content.

Common HTTP methods include:

Status-code families communicate broad outcomes: 2xx indicates success, 3xx redirection, 4xx a client-side problem, and 5xx a server-side problem. A client must still inspect the response body and schema; a successful HTTP response does not prove that the data is semantically correct.

Using the standard library:

from urllib.parse import urlencode
from urllib.request import Request, urlopen
import json

params = urlencode({"city": "London", "limit": 10})
request = Request(
    f"https://api.example.org/weather?{params}",
    headers={"Accept": "application/json", "User-Agent": "DataIngest/1.0"}
)

with urlopen(request, timeout=10) as response:
    if response.status != 200:
        raise RuntimeError(f"HTTP status: {response.status}")
    payload = json.load(response)

In production, the third-party requests package is often more convenient, but the same principles apply:

import requests

response = requests.get(
    "https://api.example.org/items",
    params={"page": 1, "limit": 100},
    timeout=10
)
response.raise_for_status()
data = response.json()

Reliable API consumption

A robust API client should:

• use a finite connection and read timeout;
• check status codes and validate the response schema;
• authenticate using documented mechanisms, never hard-coded secrets;
• respect rate limits and terms of service;
• handle pagination rather than assuming one response contains all records;
• retry transient failures such as timeouts and selected 5xx responses, using bounded exponential backoff;
• avoid retrying permanent failures such as invalid credentials without correction;
• record request time, endpoint, status, and a correlation or job identifier;
• protect sensitive response data in logs.

Pagination may be page-based, offset-based, or cursor-based. Cursor pagination is generally safer when records are changing because a cursor represents the server’s position in a result sequence. API schemas can change, so store the retrieval timestamp, source, version where available, and raw response when governance rules permit.

5. Relational databases and SQL

A relational database stores data in tables consisting of rows and columns. A primary key uniquely identifies each row. A foreign key references a key in another table and expresses a relationship. Constraints such as NOT NULL, UNIQUE, CHECK, and foreign-key rules help protect data integrity.

SQL is declarative: the programmer states what data is required, while the database engine determines how to execute the request. The main operations are often summarized as CRUD: create, read, update, and delete.

Example schema:

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    amount NUMERIC NOT NULL CHECK (amount >= 0),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

A join combines related rows:

SELECT c.name, SUM(o.amount) AS total_amount
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY total_amount DESC;

The JOIN condition is essential. An accidental missing condition can create a Cartesian product and produce severely inflated totals.

Python database access

SQLite is included with Python and is useful for local applications, teaching, and prototypes.

import sqlite3

with sqlite3.connect("sales.db") as connection:
    connection.execute("""
        CREATE TABLE IF NOT EXISTS sales (
            sale_id INTEGER PRIMARY KEY,
            product TEXT NOT NULL,
            quantity INTEGER NOT NULL CHECK (quantity > 0)
        )
    """)

    rows = [("Notebook", 4), ("Pen", 10)]
    connection.executemany(
        "INSERT INTO sales (product, quantity) VALUES (?, ?)",
        rows
    )
    connection.commit()

The question marks are parameter placeholders. Parameterized queries separate SQL code from data and prevent SQL injection. Never construct SQL by concatenating untrusted input:

# Safe value parameterization
product = "Pen"
with sqlite3.connect("sales.db") as connection:
    result = connection.execute(
        "SELECT sale_id, quantity FROM sales WHERE product = ?",
        (product,)
    )
    records = result.fetchall()

A transaction groups operations into an atomic unit. In the usual atomicity, consistency, isolation, and durability (ACID) model, a committed transaction becomes durable subject to the database system; a failed transaction can be rolled back. Use commit() deliberately and rollback() when handling failures in longer transaction blocks. Do not assume that a file-based database provides the same concurrency and operational capabilities as a managed server database.

6. Designing a reliable ingestion pipeline

A dependable ingestion process can be represented as:

Acquire → Preserve → Parse → Validate → Transform → Load → Verify → Report

Acquire and preserve

Obtain data from a file, API, or database using explicit source configuration. Preserve the original input when policy and storage allow. Record source location, retrieval time, software version, and a file checksum when reproducibility is important. A checksum is a fingerprint used to detect whether bytes changed; it is not encryption and does not prove that content is trustworthy.

Parse and validate

Parsing converts bytes or text into structures. Validation checks whether those structures meet expectations. Structural validation includes required columns, JSON fields, and data types. Semantic validation includes ranges, uniqueness, referential relationships, and business rules. For example, a quantity may be required to be an integer greater than zero, while an email field may need a documented format.

Rejecting every imperfect row may lose useful information; accepting everything may contaminate downstream analysis. A professional design defines quarantine behavior: invalid records are stored separately with an error reason, while valid records continue if that policy is acceptable.

Transform and load

Transformations should be explicit and reproducible. Normalize dates to a documented time zone, standardize units, and distinguish missing from zero. Load into staging tables first when the source is uncertain. Then use controlled SQL operations to move validated records into production tables.

An ingestion job should be idempotent where possible: running it more than once produces the same final state rather than duplicate records. Techniques include source-record identifiers, unique constraints, upserts, and recorded batch identifiers. Idempotency is especially important after a network failure, because a client may not know whether the server completed a request before the connection failed.

Verify and observe

Verification may include row counts, duplicate checks, null-rate checks, range checks, referential-integrity checks, and comparison with expected totals. Logging should include processing stages, counts, durations, and failures without exposing passwords, tokens, or unnecessary personal data. Alerts should distinguish a complete failure from a partial load or a quality warning.

7. Worked application: daily retail ingestion

Consider an illustrative retailer receiving a daily CSV export from stores and a JSON inventory feed from an internal API. The objective is to load validated sales into SQLite for a dashboard.

The input CSV contains sale_id, store_id, product_id, quantity, and sale_date. The ingestion design is:

1. construct the input path with Path;
2. read using UTF-8 and csv.DictReader;
3. require all five columns;
4. convert quantity to an integer and reject values less than or equal to zero;
5. validate the date against the organization’s documented date format;
6. insert valid rows with a parameterized statement;
7. rely on a unique sale_id constraint to prevent duplicate loading;
8. write rejected rows and reasons to a quarantine file;
9. commit only after the batch passes defined checks;
10. report source name, accepted count, rejected count, and database status.

A simplified validation and insertion fragment is:

import csv
import sqlite3
from pathlib import Path

required = {"sale_id", "store_id", "product_id", "quantity", "sale_date"}
accepted = []
rejected = []

with Path("sales.csv").open(newline="", encoding="utf-8") as file:
    reader = csv.DictReader(file)
    if set(reader.fieldnames or []) != required:
        raise ValueError("Unexpected CSV schema")

    for line_number, row in enumerate(reader, start=2):
        try:
            quantity = int(row["quantity"])
            if quantity <= 0:
                raise ValueError("quantity must be positive")
            accepted.append((
                row["sale_id"], row["store_id"],
                row["product_id"], quantity, row["sale_date"]
            ))
        except (KeyError, TypeError, ValueError) as error:
            rejected.append({"line": line_number, "reason": str(error), "row": row})

with sqlite3.connect("retail.db") as connection:
    connection.executemany("""
        INSERT INTO sales (sale_id, store_id, product_id, quantity, sale_date)
        VALUES (?, ?, ?, ?, ?)
        ON CONFLICT(sale_id) DO NOTHING
    """, accepted)
    connection.commit()

In a production system, the schema would define appropriate data types and keys, dates would be parsed and normalized rather than merely copied, and quarantine records would be persisted. The important design principle is separation of acquisition, validation, and loading. This makes failures observable and prevents malformed input from being treated as trustworthy business data.

8. Security, governance, and ethical considerations

File and database operations can expose confidential information. API keys, database passwords, and personally identifiable information require protection. Store secrets in environment variables or a secrets manager rather than source code; restrict file and database permissions; use encrypted transport such as HTTPS; and apply least privilege so an ingestion account can perform only necessary operations.

Data governance includes ownership, retention, lineage, access control, quality standards, and deletion procedures. Keep only data necessary for the stated purpose. Respect applicable privacy and data-protection law, contractual restrictions, licensing terms, and API usage policies. Do not infer that technically accessible data is ethically or legally usable.

Logs and backups can themselves contain sensitive data. Redact tokens and minimize personal fields. When data is used to train or evaluate machine-learning systems, document provenance, consent or lawful basis where relevant, representativeness, and risks of re-identification or discriminatory outcomes.

9. Common errors and best practices

Frequent errors include using relative paths without documenting the working directory, omitting encodings, splitting CSV lines manually, loading unbounded API responses into memory, ignoring pagination, exposing secrets in logs, concatenating SQL values, and treating a successful HTTP response as proof of valid data.

Best practice is to:

• use pathlib and explicit encodings;
• preserve raw inputs and record provenance;
• validate schemas and business rules before loading;
• use context managers for files and database connections;
• use parameterized SQL and transactions;
• implement timeouts, bounded retries, and rate-limit compliance;
• design repeatable, idempotent jobs;
• separate raw, staged, rejected, and curated data;
• test malformed input, empty input, duplicate input, network failure, and schema change;
• monitor quality metrics, not only program exceptions.

Chapter Summary

Persistent data may be stored as text, CSV, JSON, or binary representations. pathlib provides portable path operations, while context managers ensure that files and database resources are closed safely. Explicit UTF-8 handling reduces encoding ambiguity. CSV is effective for simple tabular exchange, whereas JSON represents nested records and API payloads; neither should be accepted without validation. Binary serialization can be efficient, but unsafe deserialization mechanisms such as untrusted pickle data must be avoided.

Web APIs use HTTP requests and responses. Reliable clients apply timeouts, status checking, pagination, authentication protection, rate-limit compliance, and controlled retries. Relational databases organize data with tables, keys, constraints, joins, transactions, and SQL. Python database programs should use parameterized queries to prevent injection and should commit data deliberately.

A professional ingestion pipeline acquires, preserves, parses, validates, transforms, loads, verifies, and reports. Idempotency, provenance, quarantine handling, observability, least privilege, privacy, and data minimization make ingestion reliable and governable.

Key Terms

Path: A filesystem location identifying a file or directory.

Encoding: A rule for representing characters as bytes.

Context manager: A construct that guarantees setup and cleanup around an operation.

CSV: A delimited text format commonly used for tabular data exchange.

JSON: A structured, language-independent text format using objects and arrays.

Serialization: Conversion of an in-memory object into a storable or transferable representation.

Deserialization: Reconstruction of an object from a serialized representation.

Binary file: A file whose content is handled as bytes rather than decoded text.

API: A defined interface through which software systems communicate.

HTTP: The protocol commonly used for web requests and responses.

Pagination: Dividing a large API result into multiple responses.

Relational database: A database that organizes related data into tables.

Primary key: A column or set of columns that uniquely identifies a row.

Foreign key: A column or set of columns referencing a key in another table.

Transaction: A logical unit of database work that is committed or rolled back.

Parameterized query: SQL containing placeholders whose values are supplied separately.

Schema: The expected structure, fields, types, and constraints of data.

Idempotency: The property that repeating an operation produces the same intended final state.

Data lineage: A record of data origin, transformations, and movement.

Quarantine: Separate storage for records that fail validation.

Review Questions

Short Answer Questions

1. Why should a program specify an encoding when opening a text file?
2. What is the difference between json.load() and json.loads()?
3. What purpose does a primary key serve in a relational table?
4. Why are parameterized SQL queries safer than string concatenation?
5. Name two controls that make an API client more reliable.

Long Answer Questions

1. Explain how pathlib and context managers improve the portability and safety of file-processing programs.
2. Compare CSV and JSON in terms of structure, data types, human readability, and typical applications.
3. Describe the stages of a reliable data ingestion pipeline and explain why validation should precede loading.
4. Explain database transactions, constraints, and joins using a retail example.
5. Discuss the security and governance measures required when an ingestion process handles personal data and API credentials.

Analytical / Application Questions

1. A daily API job sometimes times out after the server has received the request. Design an approach that avoids both data loss and duplicate records.
2. A CSV contains 98,000 valid rows and 2,000 rows with invalid quantities, missing identifiers, or malformed dates. Propose a quarantine and reporting strategy, and justify whether the entire batch should be rejected.
3. A dashboard reports sales totals twice as large as expected after a database load. Identify likely join or ingestion causes and describe the SQL and data-quality checks you would perform.

Multiple Choice Questions with Answers

Question 1

Which Python object is designed for portable filesystem path operations?

A. pathlib.Path

B. csv.Reader

C. json.Object

D. sqlite3.Cursor

Correct Answer: A. pathlib.Path

Question 2

Which method parses a JSON string into a Python object?

A. json.dump()

B. json.loads()

C. json.write()

D. json.parse_file()

Correct Answer: B. json.loads()

Question 3

Why is newline="" commonly used when opening a CSV file for Python’s csv module?

A. It encrypts the file

B. It prevents all blank records

C. It allows the CSV module to control newline handling

D. It converts CSV into JSON

Correct Answer: C. It allows the CSV module to control newline handling

Question 4

Which practice most directly prevents SQL injection when inserting user-supplied values?

A. Concatenating values into SQL strings

B. Using parameterized queries

C. Printing the SQL before execution

D. Saving SQL in a CSV file

Correct Answer: B. Using parameterized queries

Question 5

What does a 4xx HTTP status code generally indicate?

A. A successful request

B. A redirection

C. A client-side request problem

D. A database commit

Correct Answer: C. A client-side request problem

Question 6

Which property means that repeating an ingestion operation does not create unintended duplicate results?

A. Pagination

B. Idempotency

C. Serialization

D. Encoding

Correct Answer: B. Idempotency

Question 7

Which statement about pickle is correct?

A. It is always safe for untrusted input

B. It only stores plain text

C. Untrusted pickle data must not be deserialized

D. It is the standard format for relational joins

Correct Answer: C. Untrusted pickle data must not be deserialized

Question 8

What is the main role of a foreign key?

A. To compress a table

B. To reference a key in another table

C. To encrypt a database password

D. To define an API timeout

Correct Answer: B. To reference a key in another table

Question 9

Which sequence best represents a reliable ingestion workflow?

A. Load, acquire, validate, preserve

B. Acquire, parse, validate, load, verify

C. Delete, transform, acquire, report

D. Serialize, delete, load, authenticate

Correct Answer: B. Acquire, parse, validate, load, verify

Question 10

Which control is most appropriate for protecting an API secret?

A. Hard-code it in a public source file

B. Write it into every log message

C. Store it in a secrets manager or protected environment configuration

D. Include it in a CSV column

Correct Answer: C. Store it in a secrets manager or protected environment configuration