Files, Databases, APIs and Data Acquisition

Introduction

Data science depends not only on analytical methods but also on obtaining trustworthy data. Data may be stored in local text files, spreadsheets, relational databases, cloud platforms, web services, or public websites. Each source has different structures, access mechanisms, quality risks, performance characteristics, and legal obligations. A data scientist therefore needs more than the ability to call a Python function: they must understand how data is represented, transferred, validated, secured, documented, and prepared for analysis.

This chapter develops a practical acquisition pipeline using Python. It covers text, CSV, JSON and Excel files; SQL databases; REST APIs; and web data. It also examines validation, privacy, authentication, access control, provenance, error handling, and responsible use.

Learning Objectives

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

• read and write common file formats with Python;
• distinguish structured, semi-structured and unstructured data;
• query relational databases safely and efficiently;
• consume REST APIs using appropriate request methods and authentication;
• acquire web data responsibly;
• validate schema, types, ranges, uniqueness and completeness;
• design acquisition workflows with logging, retries and reproducibility;
• apply privacy, security, governance and access-control principles;
• evaluate the limitations and risks of different data sources.

1. The Data-Acquisition Pipeline

Data acquisition is the controlled process of obtaining data from a source and making it available for analysis. A professional pipeline normally includes:

1. Source identification: determine who owns the data, what it represents, and whether its use is permitted.
2. Connection and extraction: access the source using a file reader, database driver, API client, or web technique.
3. Staging: preserve the received data in its original or near-original form.
4. Validation: check structure, types, completeness, ranges, uniqueness and relationships.
5. Transformation: convert data into a suitable analytical format.
6. Documentation: record source, time, parameters, code version and assumptions.
7. Secure storage and access: restrict access according to role and sensitivity.
8. Monitoring and maintenance: detect schema changes, failures and quality deterioration.

A useful principle is to separate raw, validated, and curated data. Raw data should generally be immutable. Validated data has passed structural and quality checks. Curated data has been transformed for a particular analytical purpose.

Data sources can be classified as follows:

The choice of source affects reliability. A governed database may provide stronger consistency than an unofficial web page, while an API may provide more current data than a monthly spreadsheet. No source is automatically authoritative merely because it is technically convenient.

2. Text and Delimited Files

Plain-text files contain characters encoded using a character set such as UTF-8. They may contain prose, logs, or records separated by delimiters. A delimited file represents fields using a separator such as a comma, tab, semicolon, or pipe.

2.1 Reading and writing text

Python's open() function provides basic file access. A context manager closes the file even if an exception occurs.

from pathlib import Path

path = Path("data/notes.txt")

with path.open("r", encoding="utf-8") as file:
    text = file.read()

with path.open("w", encoding="utf-8") as file:
    file.write("Acquisition completed\n")

The mode r reads, w replaces an existing file, a appends, and x creates a new file only if it does not already exist. Explicit encoding is important because relying on an operating system's default can produce different results across machines.

For large files, process one line at a time rather than loading everything into memory:

with open("events.log", encoding="utf-8") as file:
    for line_number, line in enumerate(file, start=1):
        line = line.rstrip("\n")
        if line:
            print(line_number, line)

This approach has memory approximately proportional to the longest line rather than to the entire file. However, a line-oriented approach is appropriate only when records are line-based.

2.2 CSV files

CSV means comma-separated values, although real files may use other delimiters. A robust CSV reader handles quoted delimiters, embedded line breaks, and escaped quotation marks.

import csv

with open("customers.csv", newline="", encoding="utf-8") as file:
    reader = csv.DictReader(file)
    rows = list(reader)

print(reader.fieldnames)
print(rows[0])

For analytical work, pandas is convenient:

import pandas as pd

customers = pd.read_csv(
    "customers.csv",
    dtype={"customer_id": "string"},
    parse_dates=["registered_at"],
    na_values=["", "NA", "N/A", "unknown"]
)

Identifiers should often be read as strings. If an account number such as 001245 is inferred as an integer, leading zeros are lost. Similarly, a product code may look numeric but should not be treated as a measurable quantity.

Writing data should specify whether an index is part of the output:

customers.to_csv("customers_clean.csv", index=False, encoding="utf-8")

Common CSV problems include inconsistent column counts, mixed date formats, unexpected delimiters, duplicated headers, malformed quotation, and locale-specific decimal marks. These should be detected rather than silently corrected.

3. JSON and Excel Data

3.1 JSON

JSON is a text-based format representing objects, arrays, strings, numbers, Boolean values and null. It is especially common in APIs.

import json

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

print(config["source"])

A JSON object corresponds naturally to a Python dictionary, while a JSON array corresponds to a list. Nested structures require careful interpretation:

payload = {
    "city": "Leeds",
    "measurements": [
        {"date": "2025-01-01", "temperature": 7.2},
        {"date": "2025-01-02", "temperature": 6.8}
    ]
}

The measurements can be normalised for tabular analysis:

import pandas as pd

measurements = pd.json_normalize(payload, record_path="measurements")

JSON does not guarantee a fixed schema. One response may omit a field that appears in another, or a field may change from a number to a string. Validation is therefore essential.

3.2 Excel workbooks

Excel files may contain multiple worksheets, formulas, merged cells, formatting, hidden rows and manually edited values. They are useful for human-oriented reporting but can be risky as a system-of-record format.

import pandas as pd

sales = pd.read_excel(
    "sales.xlsx",
    sheet_name="Transactions",
    dtype={"invoice_id": "string"}
)

workbook = pd.ExcelFile("sales.xlsx")
print(workbook.sheet_names)

When importing Excel, inspect the first rows, worksheet names, data types and blank regions. Do not assume that the visual appearance of a worksheet defines its machine-readable structure. A title placed above the header may require skiprows; merged cells may require manual restructuring.

4. Validation and Data Quality

Acquiring data is not equivalent to acquiring usable data. Validation tests whether data satisfies explicit expectations. A validation specification should define:

• required columns and their data types;
• permitted missingness;
• unique-key requirements;
• valid ranges and categories;
• date and time conventions;
• relationships between fields;
• acceptable record counts;
• source and freshness requirements.

A compact validation example is:

required = {"customer_id", "email", "age", "registered_at"}
missing_columns = required - set(customers.columns)
if missing_columns:
    raise ValueError(f"Missing columns: {missing_columns}")

if customers["customer_id"].isna().any():
    raise ValueError("Customer identifiers cannot be missing")

if customers["customer_id"].duplicated().any():
    raise ValueError("Customer identifiers must be unique")

invalid_age = ~customers["age"].between(13, 120, inclusive="both")
if invalid_age.any():
    raise ValueError("Age contains invalid values")

Validation rules must reflect domain knowledge. A missing value may be invalid for a primary key but acceptable for a secondary telephone number. A negative temperature may be valid, whereas a negative quantity sold may not be.

Important dimensions of data quality include:

Validation should produce an auditable result. Instead of merely deleting invalid records, retain rejected records with a reason code where appropriate. Silent correction can conceal systematic errors.

5. Relational Databases and SQL

A relational database stores data in tables connected through keys. A primary key identifies a row within a table. A foreign key refers to a primary key in another table. Constraints, transactions and indexes support integrity and performance.

A typical relationship may contain a customers table and an orders table. One customer can have many orders, represented by orders.customer_id referencing customers.customer_id.

5.1 Querying SQLite with Python

SQLite is useful for local analytical applications and teaching because it stores a database in a file.

import sqlite3
import pandas as pd

connection = sqlite3.connect("retail.db")

query = """
SELECT customer_id, order_date, total_amount
FROM orders
WHERE order_date >= ?
"""

orders = pd.read_sql_query(
    query,
    connection,
    params=("2025-01-01",)
)

connection.close()

The question mark is a parameter placeholder. Parameterisation is essential because it separates SQL instructions from user-supplied values and reduces SQL-injection risk. This is unsafe:

# Do not construct SQL by concatenating untrusted input
query = "SELECT * FROM users WHERE name = '" + name + "'"

For production systems, use a connection pool or context-managed connection appropriate to the database driver. Credentials should be stored in environment variables or a secret manager, not in source code.

5.2 SQL concepts for acquisition

A SELECT retrieves columns, WHERE filters rows, JOIN combines tables, GROUP BY aggregates records, and ORDER BY sorts results. For example:

SELECT c.customer_id,
       COUNT(o.order_id) AS order_count,
       SUM(o.total_amount) AS revenue
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.order_date >= ?
GROUP BY c.customer_id;

For large tables, do not retrieve every column and row when only a subset is needed. Select required columns, filter at the database, use indexes on frequently filtered or joined fields, and extract in chunks when necessary:

for chunk in pd.read_sql_query(
    "SELECT * FROM events WHERE event_date >= ?",
    connection,
    params=("2025-01-01",),
    chunksize=50_000
):
    process(chunk)

A transaction groups changes into a unit. The ACID properties—atomicity, consistency, isolation and durability—describe desirable transactional behaviour, although exact guarantees depend on the database engine and configuration. Analytical extraction is usually read-only, but acquisition code must still respect workload limits and permissions.

6. REST APIs

An API is a programmatic interface through which one system exposes data or operations. A REST-style API commonly uses HTTP methods:

HTTP status codes communicate outcomes. A 200 response generally indicates success, 201 creation, 400 a malformed request, 401 missing or invalid authentication, 403 insufficient permission, 404 absence of a resource, 429 rate limiting, and 5xx a server-side problem. Client code should not treat every response as valid data.

6.1 A robust GET request

import requests

response = requests.get(
    "https://api.example.org/v1/weather",
    params={"city": "Leeds", "days": 3},
    timeout=20
)
response.raise_for_status()
data = response.json()

Query parameters should be passed through params so that the library encodes them correctly. A timeout prevents a stalled request from blocking a pipeline indefinitely. raise_for_status() converts unsuccessful HTTP responses into exceptions.

6.2 Authentication and pagination

Common authentication methods include API keys, bearer tokens and OAuth 2.0. Tokens should be treated as confidential credentials. They should not be committed to source repositories or printed in logs.

Many APIs return only a page of results. A cursor-based example is:

import requests

headers = {"Authorization": f"Bearer {token}"}
records = []
url = "https://api.example.org/v1/items"
params = {"limit": 100}

while url:
    response = requests.get(url, headers=headers, params=params, timeout=20)
    response.raise_for_status()
    page = response.json()
    records.extend(page["items"])
    url = page.get("next")
    params = None

The exact pagination design varies. Some APIs use page numbers, offsets, links, or cursors. The extraction should record the endpoint, parameters, retrieval time and API version.

6.3 Rate limits, retries and idempotence

A rate limit protects an API from excessive traffic. A respectful client follows published limits and responds to 429 using the provider's guidance, often a Retry-After header. Retries should use bounded exponential backoff and should not blindly repeat operations that may create duplicate records.

A read-only GET is generally safe to retry, but safety depends on the API. A POST may create a new resource each time unless the API supports an idempotency key. Production pipelines should distinguish transient errors from permanent errors and record failed requests for later review.

7. Web Data Acquisition

Web data may be available through an official API, downloadable file, HTML table, or embedded script data. The preferred order is usually:

1. official API;
2. official downloadable dataset;
3. stable HTML table, where permitted;
4. more complex page parsing only when necessary.

HTML can be parsed with tools such as Beautiful Soup, but scraping is not simply a technical exercise. Before collecting data, check terms of service, robots guidance, copyright, licensing, authentication requirements, privacy implications and request frequency. Do not bypass access controls, CAPTCHAs or technical restrictions.

A simple table extraction may be possible with pandas:

import pandas as pd

tables = pd.read_html("https://example.org/statistics")
first_table = tables[0]

This can fail when a page is rendered dynamically by JavaScript, when table structure changes, or when visible text is not the authoritative dataset. Save the original response or a permitted snapshot, record the URL and retrieval time, and test the parser against schema changes.

8. Privacy, Security and Access Control

Data acquisition can create legal and ethical risk, particularly when data identifies or can reasonably be linked to individuals. Personal data may include names, email addresses, device identifiers, location histories, health information and combinations of seemingly harmless attributes.

Key principles include:

• Purpose limitation: collect data for a specified legitimate purpose.
• Data minimisation: acquire only what is necessary.
• Transparency: communicate how data is used where required.
• Retention limitation: delete or anonymise data when no longer needed.
• Accuracy: provide mechanisms for correction where applicable.
• Security: protect confidentiality, integrity and availability.
• Accountability: document decisions and demonstrate compliance.

Access control should follow least privilege: a user or process receives only the permissions required for its role. Role-based access control may distinguish analysts, engineers, administrators and auditors. Database users should normally have read-only access for extraction tasks. Sensitive fields can be masked, tokenised or removed before wider analytical use.

Encryption in transit is commonly provided through HTTPS or secure database connections. Encryption at rest protects stored files and databases. Neither replaces sound identity management, logging, patching and secure handling of credentials.

A useful distinction is between authentication—establishing who or what is requesting access—and authorisation—deciding what that identity is allowed to do. Logging should record access and important actions without exposing passwords, tokens or unnecessary personal information.

Legal requirements vary by jurisdiction and context. Organisations may need to consider data-protection legislation, contractual restrictions, sector regulations, intellectual-property rights and records-management rules. This chapter provides technical guidance, not legal advice.

9. Reproducibility and Data Provenance

A reproducible acquisition process enables another qualified person to understand and, where permitted, repeat the extraction. Provenance records should include:

• source name and owner;
• URL, database or file location;
• retrieval timestamp and time zone;
• query, endpoint and parameters;
• software and library versions;
• schema or API version;
• checksums for downloaded files where appropriate;
• validation results and rejected-record counts;
• transformations and assumptions;
• responsible person or service account.

A checksum is a compact value calculated from file contents. If the same algorithm and input produce different checksums, the file has changed. Checksums support integrity checking but do not prove that the content is accurate or safe.

Use deterministic filenames and directory conventions, for example raw/source_name/2025-03-01/data.csv. Preserve raw inputs when licensing and storage policy permit. Version code and validation rules. Do not rely on a manually downloaded file whose origin and retrieval history cannot be established.

10. Applied Case: Retail Inventory Acquisition

Background

This illustrative case concerns a retailer combining daily inventory files from stores, sales transactions from a SQL database, and product information from a supplier API. The objective is to identify stock shortages without exposing unnecessary customer information.

Problem

The store files use inconsistent column names and sometimes represent missing quantities as -. The database contains sales records, while the supplier API is paginated and limits requests to 100 calls per minute. Product identifiers must remain strings because some begin with zero.

Analysis

The team defines a target schema containing product_id, store_id, date, opening_stock, received, sold, and closing_stock. A domain rule states that, for a daily record, the following inventory balance should hold approximately:

Here, all variables represent units for one product, store and day. The identity applies when stock adjustments, returns, damage and transfers are either absent or represented explicitly. If those events exist, a more complete relationship is required:

The team must not label a record inaccurate merely because the simple equation fails if unrecorded adjustments are known to occur.

Approach and solution

The pipeline first copies each file to immutable raw storage and records its checksum. It maps supplier-specific column names to canonical names, reads product identifiers as strings, converts - to missing values, and validates dates and non-negative quantities. SQL extraction selects only the required fields and filters by date. The API client uses a bearer token stored in a secret manager, follows pagination, applies a timeout, and honours rate-limit responses.

Customer identifiers are not extracted because they are unnecessary for inventory analysis. Database credentials are read-only. Validation reports duplicate product-store-date keys, missing products, negative quantities and failed inventory balances. Rejected rows are retained separately with reason codes rather than silently discarded.

Outcome

The retailer obtains a daily inventory table with documented lineage and measurable quality. Analysts can distinguish genuine low-stock conditions from acquisition failures. The security design reduces exposure of customer data, while the validation report reveals stores that do not record transfers consistently.

Learning points

The case demonstrates that data acquisition is an engineered control process. File parsing, SQL, APIs, validation, privacy and provenance must be designed together. A technically successful download is not necessarily a reliable analytical input.

11. Common Failure Modes and Best Practices

Several recurring failures deserve particular attention:

• Assuming inferred types are correct: explicitly define types for identifiers, dates and categorical fields.
• Loading everything into memory: select columns, filter at source, stream or use chunks.
• Ignoring encoding and locale: specify encoding and examine decimal and date conventions.
• Building SQL with string concatenation: use parameterised queries.
• Treating HTTP 200 as proof of valid data: validate response structure and required fields.
• Ignoring pagination: compare retrieved counts with provider metadata where available.
• Retrying without limits: use bounded retries and respect rate limits.
• Logging secrets: redact tokens, passwords and personal data.
• Overwriting raw data: preserve original inputs and create new derived versions.
• Scraping without permission: review licensing, terms and access constraints.
• Failing silently: raise or record actionable errors and monitor pipeline metrics.

A mature acquisition system tests not only code but also assumptions. Contract tests can check that an API still returns expected fields. Unit tests can verify date parsing and validation rules. Integration tests can use a controlled database or mock service. Operational monitoring can track latency, row counts, null rates, duplicate rates and schema changes.

12. Contemporary Developments

Modern data platforms increasingly use object storage, data lakes, lakehouses, managed connectors and event streams. Columnar formats such as Parquet can improve analytical performance because they store values by column and support selective reading. Data contracts formally describe expected schemas and responsibilities between producers and consumers. Schema registries and automated quality checks help detect breaking changes.

AI-assisted tools can generate extraction code, map fields or identify anomalies, but generated code requires human review. An AI system may invent an endpoint, mishandle permissions, leak sensitive data into a prompt, or make an unjustified correction. Human accountability, test coverage and governance remain necessary.

Chapter Summary

Data acquisition is a lifecycle rather than a single download operation. Python can read text, CSV, JSON and Excel files; connect to SQL databases; call REST APIs; and assist with permitted web extraction. Each source has distinct structural and operational risks. Reliable workflows preserve raw data, validate schema and domain rules, use parameterised SQL, handle API status codes and pagination, respect rate limits, and document provenance.

Privacy and security must be integrated from the beginning. Authentication identifies a requester, while authorisation controls permitted actions. Least privilege, secret management, encryption, minimisation and retention controls reduce risk. Reproducibility requires recording sources, timestamps, parameters, versions, checksums and validation outcomes. The central professional principle is that convenient data is not automatically trustworthy, lawful or fit for purpose.

Key Terms

API: A programmatic interface through which software exchanges data or requests operations.

Authentication: Verification of the identity of a user, application or service.

Authorisation: Determination of which actions an authenticated identity may perform.

CSV: A delimited text format commonly used for tabular records.

Data provenance: Information describing a dataset’s origin, history and transformations.

Data validation: Testing whether data satisfies structural, type, domain and integrity rules.

Foreign key: A field referring to a key in another relational table.

JSON: A text format representing objects, arrays and primitive values.

Least privilege: Granting only the access necessary for a task.

Pagination: Dividing a large API result into multiple responses or pages.

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

Primary key: A field or combination of fields that uniquely identifies a row.

Rate limit: A restriction on the number or frequency of requests.

REST: An architectural style commonly using HTTP resources and methods.

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

Staging: Temporary or intermediate storage used before validation and transformation.

Structured data: Data organised according to a defined schema, often in rows and columns.

Tokenisation: Replacing sensitive values with controlled substitutes or tokens.

Web scraping: Programmatic extraction of information from web pages.

Review Questions

Short Answer Questions

1. Why should identifiers such as product codes often be read as strings rather than integers?
2. What is the difference between authentication and authorisation?
3. Why is parameterised SQL preferred to string concatenation?
4. State three dimensions of data quality.
5. Why are API pagination and rate limits important in an extraction pipeline?

Long Answer Questions

1. Explain a complete data-acquisition pipeline from source identification through monitoring. Include the role of raw, validated and curated data.
2. Compare CSV, JSON, Excel and relational databases in terms of structure, strengths and common risks.
3. Describe how a Python application should handle API authentication, timeouts, status codes, pagination and retries.
4. Explain how privacy, least privilege, encryption and data minimisation should influence the design of a data-acquisition system.
5. Discuss the importance of provenance and reproducibility in professional data science, including the metadata that should be recorded.

Analytical / Application Questions

1. A CSV file contains account numbers with leading zeros, dates in three formats and blank cells represented by N/A, - and unknown. Design a Python-based import and validation strategy, explaining which assumptions must be confirmed before transformation.
2. An API returns 100 records per page and provides a next link. After extraction, the expected total is 12,450 records but only 11,900 were received. Analyse possible causes and propose diagnostic steps.
3. A retailer wants to combine customer transactions with a public web dataset. Evaluate the technical, privacy, legal and governance questions that should be answered before the datasets are joined.

Multiple Choice Questions with Answers

Question 1

Which practice best prevents SQL injection when values come from users?

A. Concatenating values into an SQL string

B. Using parameterised queries

C. Printing the query before execution

D. Converting every value to uppercase

Correct Answer: B. Using parameterised queries

Question 2

Why should a product code such as 001245 usually be loaded as a string?

A. Strings always require less memory

B. Strings automatically validate the code

C. Reading it as an integer can remove leading zeros

D. Integers cannot be stored in CSV files

Correct Answer: C. Reading it as an integer can remove leading zeros

Question 3

Which HTTP status code commonly indicates that a client has exceeded an API rate limit?

A. 200

B. 201

C. 404

D. 429

Correct Answer: D. 429

Question 4

What is the main purpose of a foreign key?

A. To encrypt a database column

B. To link a row to a key in another table

C. To sort every table alphabetically

D. To replace all missing values

Correct Answer: B. To link a row to a key in another table

Question 5

Which property is most appropriate for an immutable raw-data layer?

A. It is overwritten after every transformation

B. It preserves the received input for audit and reproduction

C. It contains only manually corrected records

D. It stores passwords with the data

Correct Answer: B. It preserves the received input for audit and reproduction

Question 6

A Python API request uses response.raise_for_status(). What is its purpose?

A. To convert JSON into CSV

B. To retry every request indefinitely

C. To raise an exception for unsuccessful HTTP responses

D. To remove duplicate records

Correct Answer: C. To raise an exception for unsuccessful HTTP responses

Question 7

Which principle means that a process receives only the permissions necessary for its task?

A. Data minimisation

B. Least privilege

C. Pagination

D. Normalisation

Correct Answer: B. Least privilege

Question 8

Which validation rule is most directly concerned with duplicate customer identifiers?

A. Uniqueness

B. Timeliness

C. Encoding

D. Encryption

Correct Answer: A. Uniqueness

Question 9

What should normally be preferred before scraping a public web page?

A. Bypassing the website’s access controls

B. An official API or downloadable dataset

C. Sending requests as quickly as possible

D. Ignoring the terms of service

Correct Answer: B. An official API or downloadable dataset

Question 10

In the inventory identity closing_stock = opening_stock + received − sold, what assumption is required for the simple form to hold?

A. All products must have the same price

B. Transfers, adjustments and other stock changes are absent or represented separately

C. The database must use JSON

D. Every API response must contain exactly 100 records

Correct Answer: B. Transfers, adjustments and other stock changes are absent or represented separately