4 Using a Coding Companion with Python

In this chapter, the focus shifts from using AI to help with SQL toward using a coding companion for Python-based data engineering work. The main idea is that Python opens up many more kinds of tasks than querying alone, including API calls, JSON parsing, and regex-based text processing. Because these workflows are often repetitive, messy, and prone to errors, they are especially well suited to AI assistance when the prompts are clear and the results are tested carefully.

The chapter first shows how AI can help with API interactions using simple public services like NumbersAPI. A coding companion can generate boilerplate code to fetch data, parse JSON, handle failures, add logging, and store results in a pandas DataFrame. It also demonstrates how iterative prompting improves the output, such as adding retry logic or using uploaded API documentation to guide the model toward more accurate request URLs and parameters. At the same time, the chapter warns that AI may overengineer solutions, misunderstand prompt context, or misread documentation, so developers must verify outputs and shape prompts to match their actual environment.

Next, the chapter explains how AI helps flatten nested JSON into analysis-ready tabular data and how to use regex for extracting and normalizing phone numbers. With JSONPlaceholder, the examples show how to safely pull shallow and deeply nested fields, combine values into cleaner columns, and handle missing data with chained lookups. For regex, the chapter walks through extracting U.S. phone numbers, converting them to a standard format, splitting them into structured fields, and even exporting them as JSON for document-oriented workflows. The chapter ends with a lab that applies these ideas to the Open Brewery DB API, reinforcing that AI is most useful as a drafting and refinement partner, while human review remains essential for correctness and robustness.

Interacting with an API: This diagram shows how a client constructs a full request URL by combining a base endpoint with query parameters that define the data request (e.g., filters, limits, and format). The server receives the request, handles tasks like authentication and data lookup, and returns a structured JSON response. This client-server pattern is central to data engineering workflows, where APIs often serve as the primary source of external or cloud-based data. Knowing how to shape requests and interpret responses is essential for working with modern data pipelines.

Result of the zero-shot prompt response against the NumbersAPI

Result of the one-shot prompt response against the NumbersAPI

Result of the API documentation enhanced response against the NumbersAPI

Result of the simple JSON unpacking response against the JSONPlaceholder API.

Result of the complex JSON unpacking response against the JSONPlaceholder API.

Structured breakdown of phone numbers using regex and pandas

Results for Lab Answers 1

Results for Lab Answers 2

Results for Lab Answers 3

FAQ