Databases, APIs and External Systems

Introduction and Context

Most useful Python applications do not operate in isolation. They read and write persistent data, communicate with web services, consume files produced by other systems, and exchange information with devices, cloud platforms, and enterprise applications. A machine-learning application may retrieve customer records from a relational database, call an external model-serving API, process JSON responses, and store predictions for later evaluation. These interactions create substantial value, but they also introduce reliability, security, privacy, and governance obligations.

This chapter develops practical foundations for connecting Python programs to relational databases, SQL systems, web APIs, and structured data sources. It emphasizes a systems perspective: an external system is not merely a source of data, but a component with its own interface, constraints, authentication model, failure modes, and ownership.

Learning Objectives

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

• explain how relational databases, SQL, APIs, and structured data formats work together;
• design basic tables and use Python database APIs safely;
• distinguish parameterized SQL from unsafe string construction;
• consume, validate, transform, and produce JSON and other structured data;
• design HTTP requests with appropriate authentication, timeouts, retries, and error handling;
• protect credentials and personal data throughout an application's lifecycle;
• analyze an integrated application involving a database and an external API.

1. External Systems as Software Boundaries

A boundary exists whenever a program communicates with a component controlled by a different process, team, or organization. Examples include a PostgreSQL server, a payment gateway, a weather API, a CSV file uploaded by a user, or a cloud object store. Boundaries are important because assumptions that are safe inside one program may fail outside it.

An external system can be analyzed through five questions:

1. What is the interface? The interface may be SQL, HTTP, a file format, or a message protocol.

2. What data contract applies? A contract specifies fields, types, required values, permitted ranges, and error representations.

3. How is access controlled? Authentication identifies a client; authorization determines what that client may do.

4. What can fail? Network interruptions, malformed data, unavailable services, timeouts, and partial updates must be considered.

5. Who is responsible for the data? Ownership affects privacy, retention, accuracy, and legal obligations.

A robust integration separates business logic from infrastructure code. For example, a function that calculates a shipping charge should not also construct raw SQL, parse HTTP headers, and print passwords. Clear separation improves testing, security review, and replacement of one external service with another.

2. Relational Databases and the Relational Model

A relational database stores data in relations, commonly represented as tables. A table contains rows, called tuples, and columns, called attributes. Each column has a defined data type and semantic meaning. A primary key uniquely identifies a row. A foreign key refers to a key in another table and expresses a relationship between entities.

Consider a university prediction service with the following conceptual entities:

• `students(student_id, name, email)`
• `courses(course_id, title)`
• `enrollments(student_id, course_id, semester)`
• `predictions(prediction_id, student_id, model_version, score, created_at)`

The many-to-many relationship between students and courses is represented by the `enrollments` table. This avoids storing a variable-length list of courses in one student row and permits the relationship itself to carry attributes such as semester.

2.1 Keys, Constraints, and Normalization

A primary key should be stable, unique, and non-null. A foreign key prevents references to nonexistent parent records when referential integrity is enforced. Other common constraints include:

• `NOT NULL`: a value is required;
• `UNIQUE`: duplicate values are prohibited;
• `CHECK`: a Boolean condition must hold;
• `DEFAULT`: a value is supplied when one is omitted.

Normalization is a design approach that reduces duplication and update anomalies. In a simplified interpretation, first normal form requires atomic values rather than repeating groups; second normal form addresses dependence on only part of a composite key; and third normal form reduces dependencies on non-key attributes. Normalization is not an absolute rule: analytical systems sometimes deliberately denormalize data to improve read performance. Such decisions should be measured and documented.

2.2 Transactions and Consistency

A transaction groups operations into an all-or-nothing unit. The standard ACID properties are:

• Atomicity: either all operations in a transaction take effect or none do.
• Consistency: committed data satisfies defined constraints and rules.
• Isolation: concurrent transactions do not produce impermissible intermediate effects.
• Durability: committed changes survive an appropriate system failure.

For example, transferring funds requires both a debit and a credit. Committing only one operation would create an invalid state. Python database drivers commonly expose `commit()` and `rollback()` operations. A program should commit deliberately and roll back when an operation fails.

Isolation is a concurrency property, not a guarantee that all transactions execute sequentially. Database systems offer isolation levels with different trade-offs between consistency and concurrency. Applications should use the strongest practical level for their requirements and understand the anomalies permitted by their database configuration.

3. SQL Through Python

SQL is a declarative language: the programmer states the desired result, while the database optimizer determines an execution strategy. Common categories include:

• DDL: schema definition, such as `CREATE TABLE` and `ALTER TABLE`;
• DML: data manipulation, such as `INSERT`, `UPDATE`, and `DELETE`;
• DQL: data retrieval, principally `SELECT`;
• DCL: permissions, such as `GRANT` and `REVOKE`;
• TCL: transaction control, such as `COMMIT` and `ROLLBACK`.

A Python database driver normally follows a connection-and-cursor model. The connection represents a session with the database; a cursor submits statements and retrieves results. The exact library differs by database, but the conceptual pattern is stable.

```python

import sqlite3

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

try:

connection.execute("""

CREATE TABLE IF NOT EXISTS predictions (

prediction_id INTEGER PRIMARY KEY,

student_id INTEGER NOT NULL,

model_version TEXT NOT NULL,

score REAL NOT NULL CHECK (score >= 0 AND score <= 1),

created_at TEXT NOT NULL

)

""")

connection.execute(

"""

INSERT INTO predictions

(student_id, model_version, score, created_at)

VALUES (?, ?, ?, ?)

""",

(42, "v2.1", 0.87, "2025-01-15T10:30:00Z")

)

connection.commit()

except Exception:

connection.rollback()

raise

finally:

connection.close()

```

SQLite uses `?` placeholders. Other drivers may use named or numbered placeholders, but the principle is the same: values are bound separately from the SQL statement.

3.1 Parameterized Queries and SQL Injection

SQL injection occurs when untrusted input changes the meaning of a SQL statement. Unsafe code may concatenate a username directly into a query:

```python

Unsafe: do not use

query = "SELECT * FROM users WHERE name = '" + name + "'"

```

If `name` contains SQL syntax, the resulting statement may return unauthorized records or perform unintended operations. Parameterized queries prevent this by sending the SQL structure and values separately. They are the primary defense against injection for data values.

```python

rows = connection.execute(

"SELECT student_id, name FROM students WHERE email = ?",

(email,)

).fetchall()

```

Parameters are not a universal substitute for validation. They generally cannot represent table names, column names, or SQL keywords. If a user must choose a sort column, use an allow-list mapping rather than inserting the input directly:

```python

allowed = {"name": "name", "score": "score"}

column = allowed.get(requested_column, "name")

query = f"SELECT name, score FROM results ORDER BY {column}"

```

The value is safe here because it is selected from trusted, fixed strings. Database accounts should also follow least privilege: an application that only reads data should not have permission to drop tables.

3.2 Query Results and Performance

A query can return one row, many rows, or no rows. The application should distinguish these cases rather than assuming a result exists. Fetching millions of rows into a list can exhaust memory; iteration or pagination is safer.

Indexes are additional data structures that accelerate searches on selected columns. They can improve reads but consume storage and slow inserts and updates. Indexes should be based on actual query patterns. An execution plan, obtained through database-specific tools such as `EXPLAIN`, helps determine whether an index is being used.

The N+1 query problem occurs when an application retrieves a list and then issues one additional query for each item. A join, batch query, or carefully designed data-access method often reduces the number of round trips. Optimization should be measured rather than assumed.

4. Structured Data: JSON, CSV, and Validation

Structured data has an explicit organization that permits programs to interpret it. JSON represents objects, arrays, strings, numbers, Boolean values, and null. It is widely used in APIs because it is readable and supported across languages.

```python

import json

text = '{"student_id": 42, "score": 0.87, "passed": true}'

data = json.loads(text)

if not isinstance(data, dict):

raise ValueError("Expected a JSON object")

```

`json.loads()` parses a string; `json.dumps()` serializes a Python object. Parsing is not validation. A syntactically valid JSON object may still lack required fields or contain an invalid score.

```python

def validate_prediction(payload):

required = {"student_id", "score", "model_version"}

if not required.issubset(payload):

raise ValueError("Missing required field")

if not isinstance(payload["student_id"], int):

raise ValueError("student_id must be an integer")

if not isinstance(payload["score"], (int, float)):

raise ValueError("score must be numeric")

if not 0 <= payload["score"] <= 1:

raise ValueError("score must lie between 0 and 1")

return payload

```

In production, schema libraries such as Pydantic or JSON Schema validators can express these rules more systematically. Validation should occur at trust boundaries, both for incoming requests and for responses from external services. It should address type, presence, range, length, encoding, and permitted values.

CSV is convenient for tabular exchange but has ambiguities involving delimiters, quoting, line endings, character encodings, and missing values. Python's `csv` module is preferable to manually splitting lines. A CSV import should define an expected header, reject or quarantine malformed rows, and record the source and import time.

5. HTTP APIs and Service Integration

An application programming interface (API) is a contract through which software exposes operations or data. A REST-style web API commonly uses HTTP methods:

• `GET` retrieves a resource;
• `POST` creates a resource or requests an action;
• `PUT` replaces a resource;
• `PATCH` partially updates a resource;
• `DELETE` removes a resource.

HTTP status codes communicate broad outcomes. Successful responses are generally in the 200 range; client errors in the 400 range include unauthorized access (`401`), forbidden access (`403`), and not found (`404`); server errors in the 500 range indicate a failure at the service or an intermediary. A client should inspect both status code and response body, since APIs often provide useful error details.

A minimal request using the widely used `requests` package is:

```python

import requests

response = requests.get(

"https://api.example.org/weather",

params={"city": "Leeds"},

timeout=(3.05, 10)

)

response.raise_for_status()

data = response.json()

```

A timeout is essential. Without one, a program may wait indefinitely for a network operation. A tuple can specify connection and read timeouts when supported by the client library.

5.1 Authentication and Authorization

Common API authentication approaches include API keys, bearer tokens, OAuth 2.0 access tokens, mutual TLS, and signed requests. A key or token should be treated as a secret. It should not be embedded in source code, committed to version control, printed in logs, or placed in a client-side application where users can extract it.

```python

import os

import requests

api_key = os.environ["WEATHER_API_KEY"]

response = requests.get(

"https://api.example.org/weather",

headers={"Authorization": f"Bearer {api_key}"},

timeout=10

)

response.raise_for_status()

```

Environment variables are better than source-code literals for local development, but production systems commonly use a dedicated secret manager with access auditing, rotation, and restricted permissions. Secrets should be rotated when exposure is suspected.

Authentication answers “who are you?” Authorization answers “what may you do?” A token can be valid but insufficient for a requested operation. Applications should request the narrowest practical scope and avoid placing tokens in URLs, where they may appear in browser history and proxy logs.

5.2 Reliability: Retries, Idempotency, and Rate Limits

Network calls fail transiently. A retry may be reasonable for a connection reset, timeout, or selected server error, but not for every failure. Retrying a request that creates a payment or order can duplicate the operation. Idempotent operations can be repeated with the same intended effect; `GET` is conventionally idempotent, while `POST` is not necessarily so.

For non-idempotent operations, an API may support an idempotency key. The client sends a unique key, and the server ensures that repeated submissions with that key are treated as one logical request. Retries should use bounded exponential backoff and a maximum attempt count. Rate-limit responses, often represented by status `429`, should be handled according to the provider's documented policy, including any `Retry-After` value.

Circuit breakers, caching, queues, and fallback responses are additional resilience patterns. They should not hide persistent failures indefinitely: monitoring and alerting are required.

6. An Integrated Illustrative Case: Student Risk Service

The following fictional case illustrates an end-to-end design.

Background and Problem

A university wants to identify students who may benefit from academic support. Enrollment and assessment data are stored in a relational database. A separate machine-learning service exposes a protected HTTP endpoint that returns a risk score. The university must avoid exposing unnecessary personal data and must maintain an audit trail.

Analysis

The integration has several boundaries: the database, the model API, and the internal web application. The design must address schema constraints, input validation, authentication, network failure, privacy, and consistency. A student identifier is preferable to sending a name or email to the model service. The model response must be checked rather than trusted blindly.

Approach and Solution

A service performs these steps:

1. Authenticate the requesting staff member and verify authorization for the relevant students.

2. Validate the request: student identifiers must be integers, the list must have a defined maximum length, and the requested model version must be allow-listed.

3. Read the minimum necessary features from the database using a parameterized query.

4. Send pseudonymized features to the model API over HTTPS, with a short timeout and a correlation identifier.

5. Validate the returned JSON against a schema. A risk score must be numeric and within its documented range.

6. Store the score, model version, timestamp, and data-provenance information in a transaction.

7. Return a response that excludes internal credentials and unnecessary sensitive fields.

A simplified database write might be:

```python

connection.execute(

"""

INSERT INTO predictions

(student_id, model_version, score, created_at)

VALUES (?, ?, ?, CURRENT_TIMESTAMP)

""",

(student_id, model_version, score)

)

connection.commit()

```

If the API response is malformed, the service must not write an apparently valid prediction. If the database commit fails after the model call succeeds, the operation should be marked for retry or reconciliation rather than silently discarded. A correlation ID links logs across systems without recording the complete student payload.

Outcome

The design creates reproducible prediction records, restricts access, and makes failures observable. It also supports later evaluation because the model version and timestamp are stored with each prediction. The service does not claim that a risk score is a fact about a student; it is a model output requiring appropriate human interpretation.

Learning Points

The case demonstrates that integration quality depends on more than successful code execution. Data minimization, validation, transactional storage, controlled retries, and auditability are part of the application design. In an AI context, provenance and model versioning are especially important for monitoring drift and investigating adverse outcomes.

7. Security, Privacy, and Governance

Security should be designed in layers:

• use HTTPS and certificate validation for network communication;
• apply least privilege to database and API accounts;
• validate and constrain all untrusted input;
• use parameterized SQL;
• store secrets in managed systems and rotate them;
• redact tokens, passwords, and sensitive payloads from logs;
• patch dependencies and scan them for known vulnerabilities;
• monitor unusual access and failed authentication;
• maintain backups and test restoration.

Privacy includes more than confidentiality. Personal data should be collected and processed for a defined purpose, retained only as long as necessary, and made accessible only to authorized users. Depending on jurisdiction and context, laws such as the General Data Protection Regulation may impose requirements concerning lawful processing, transparency, access, correction, deletion, and data protection impact assessments. Organizations must obtain expert legal and policy guidance rather than treating technical controls as a complete compliance solution.

Data sent to a third-party API may cross organizational or national boundaries. Before integration, teams should examine the provider's retention policy, subprocessors, breach obligations, training use, service availability, and contract terms. Sensitive data should be minimized, pseudonymized where practical, and encrypted in transit and at rest.

For AI and machine-learning systems, governance additionally includes dataset provenance, model documentation, bias assessment, human oversight, explainability appropriate to the use case, and monitoring for performance degradation. A secure API can still produce harmful outcomes if the underlying data or decision process is inappropriate.

8. Testing and Operational Practice

Integration tests exercise a real or realistically isolated external component. Unit tests should also exist for business rules without requiring a live database or API. Mocking can make tests fast, but excessive mocking may conceal differences from the real service. A balanced strategy includes unit tests, contract tests, integration tests, and controlled end-to-end tests.

Test cases should include valid data, missing fields, incorrect types, boundary values, duplicate requests, expired credentials, timeouts, malformed responses, rate limits, and database constraint violations. Test environments must use synthetic or properly protected data.

Observability consists of logs, metrics, and traces. Useful metrics include request latency, error rate, timeout count, retry count, database query duration, and validation failures. Logs should contain event type, service name, correlation ID, and outcome, but not raw secrets or unnecessary personal data. Alerts should be actionable and should distinguish an isolated failure from a systemic outage.

Configuration should be externalized. Development, testing, and production environments should use different credentials and endpoints. Schema migrations should be versioned and reversible where feasible. Dependency versions should be controlled, and changes to external API contracts should be monitored.

9. Limitations and Contemporary Developments

External integrations remain vulnerable to vendor outages, breaking changes, undocumented behavior, network partitions, and data-quality problems. A database does not automatically make data correct, and an API response does not automatically make information trustworthy. Distributed systems also face consistency and coordination challenges: two systems may temporarily disagree, and a successful request may not mean that every downstream component has completed its work.

Contemporary systems increasingly use asynchronous messaging, event streaming, managed databases, serverless functions, and API gateways. These technologies can improve scalability and decoupling, but they introduce new concerns involving ordering, duplicate messages, eventual consistency, schema evolution, and operational complexity. Structured schema registries and contract testing help manage change.

AI applications increasingly use retrieval services, vector databases, model APIs, and tool-calling interfaces. These remain external-system integrations and require the same principles: authenticate tools, validate outputs, limit permissions, protect private context, and treat model-generated content as untrusted input. A model should not be allowed to construct arbitrary SQL or invoke unrestricted administrative APIs.

Best-Practice Checklist

Before deploying a Python integration, ask:

• Are all external inputs validated for type, range, size, and permitted values?
• Are SQL values parameterized and database permissions minimized?
• Are connection and read timeouts configured?
• Are retries limited and safe for the operation's idempotency properties?
• Are API responses checked for status, schema, and semantic validity?
• Are credentials outside source control and managed for rotation?
• Are sensitive data, retention, and third-party processing documented?
• Are transactions used for logically atomic database changes?
• Are failures observable without exposing confidential information?
• Are tests available for outages, malformed data, and duplicate requests?

Chapter Summary

Python applications depend on databases, APIs, files, and other external systems to provide persistent data and services. Relational design uses tables, keys, constraints, normalization, and transactions to preserve structure and integrity. Python database drivers support connections, cursors, parameterized queries, commits, and rollbacks; parameterization is a central defense against SQL injection.

JSON and CSV provide structured exchange formats, but parsing is not equivalent to validation. Web API clients must handle HTTP methods, status codes, authentication, authorization, timeouts, retries, rate limits, idempotency, and malformed responses. Secrets require secure storage and rotation, while privacy requires minimization, purpose limitation, access control, retention management, and careful evaluation of third-party processing.

Reliable integration is an architectural and governance problem as well as a programming task. Testing, observability, least privilege, transaction design, provenance, and model versioning are particularly important in AI and machine-learning applications.

Key Terms

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

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

Foreign key: A field that references a key in another table.

Constraint: A rule enforced by a database on permitted data.

Normalization: Database design that reduces unnecessary duplication and anomalies.

Transaction: A group of database operations treated as one logical unit.

ACID: Atomicity, consistency, isolation, and durability properties of transactions.

SQL injection: An attack in which input changes the intended meaning of a SQL statement.

Parameterized query: A query whose values are bound separately from its SQL structure.

API: A defined interface through which software components communicate.

JSON: A text-based structured data format based on objects and arrays.

Schema: A formal description of data structure, fields, types, and constraints.

Authentication: The process of establishing an identity.

Authorization: The process of determining permitted actions.

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

Timeout: A limit on how long an operation may wait.

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

Secret manager: A system for securely storing, controlling, and rotating credentials.

Least privilege: Granting only the permissions required for a task.

Data minimization: Collecting or exposing no more personal data than necessary.

Review Questions

Short Answer Questions

1. What is the purpose of a foreign key in a relational database?

2. Why are parameterized SQL queries safer than string concatenation?

3. What is the difference between authentication and authorization?

4. Why should an HTTP client specify a timeout?

5. Why does successful JSON parsing not guarantee valid application data?

Long Answer Questions

1. Explain the ACID properties of transactions and illustrate why they matter in a multi-step update.

2. Describe a secure design for a Python program that reads from a database and calls an external API.

3. Compare normalization and denormalization, including situations in which each may be appropriate.

4. Explain how retries, idempotency keys, rate limits, and circuit breakers contribute to reliable API integration.

5. Discuss privacy and governance concerns when student data is sent to a third-party machine-learning service.

Analytical / Application Questions

1. A developer builds a query by concatenating a user-provided email address and gives the application database account full administrative privileges. Identify the risks and propose a redesign.

2. An API returns HTTP 200 but its `score` field is a string with value `"unknown"`. Explain how the client should respond and what should be recorded operationally.

3. Design a transaction and recovery strategy for a service that obtains a prediction from an API and stores it in a database, considering timeouts, duplicate requests, and database failure.

Multiple Choice Questions with Answers

Question 1

Which practice is the primary defense against SQL injection in values supplied by users?

A. Parameterized queries

B. Increasing database storage

C. Using longer table names

D. Disabling transactions

Correct Answer: A. Parameterized queries

Question 2

What does a database transaction's atomicity property require?

A. Queries always execute in alphabetical order

B. All operations succeed together or the transaction is undone

C. Only one user can access the database

D. Every query must use an index

Correct Answer: B. All operations succeed together or the transaction is undone

Question 3

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

A. 201

B. 301

C. 404

D. 429

Correct Answer: D. 429

Question 4

Why should an API client use a timeout?

A. To encrypt JSON automatically

B. To prevent indefinite waiting for a network operation

C. To make every request idempotent

D. To replace authentication

Correct Answer: B. To prevent indefinite waiting for a network operation

Question 5

Which statement correctly distinguishes authentication from authorization?

A. Authentication grants database indexes; authorization creates tables

B. Authentication identifies a client; authorization determines permitted actions

C. Authentication validates JSON; authorization parses CSV

D. Authentication retries requests; authorization sets timeouts

Correct Answer: B. Authentication identifies a client; authorization determines permitted actions

Question 6

What is the safest way to allow a user to choose a sort column in a SQL query?

A. Insert the raw user input into the query

B. Use an allow-list mapping to trusted column names

C. Encode the column name as JSON

D. Put the column name in a password field

Correct Answer: B. Use an allow-list mapping to trusted column names

Question 7

Which statement about JSON parsing and validation is correct?

A. Valid JSON always contains the required business fields

B. Parsing checks syntax, while validation checks application requirements

C. Validation is unnecessary when the response status is 200

D. JSON cannot represent numeric values

Correct Answer: B. Parsing checks syntax, while validation checks application requirements

Question 8

Which operation is generally idempotent by HTTP convention?

A. GET

B. POST

C. Sending an email

D. Charging a credit card

Correct Answer: A. GET

Question 9

Which practice best protects an API credential?

A. Commit it to a private source repository

B. Print it in every error log

C. Store it in a managed secret system and rotate it

D. Place it in a URL query parameter

Correct Answer: C. Store it in a managed secret system and rotate it

Question 10

What is the principle of least privilege?

A. Every service receives administrator access for convenience

B. A service receives only the permissions necessary for its task

C. Users must be denied all access permanently

D. Privileges are assigned randomly to improve security

Correct Answer: B. A service receives only the permissions necessary for its task