api_integration.py

# -*- coding: utf-8 -*-
"""api_integration.ipynb

Automatically generated by Colab.

Original file is located at
    https://colab.research.google.com/drive/1XKadf2zhdg1XscicUq6lydwIk0jp51AJ

# **API Integration with Database (BONUS)**

## **Introduction**

This assignment demonstrates the use of Python to work with both local databases and external web APIs. The primary objective is to show an understanding of database creation, data import, querying, and file handling, along with the ability to make API calls, process JSON responses, and display results in a clear and readable format.

In the first part of the assignment, CSV files are uploaded and used to build a SQLite database containing customer, product, and order information. The second part focuses on interacting with a public API to retrieve country-level data based on user input. An optional advanced section extends this work by integrating database query results with API data, illustrating how internal datasets can be enriched with external information. Together, these components highlight practical skills in data management, API integration, user interaction, and structured program design.

## **Combining API Data with Database Queries**

This section demonstrates a more advanced integration by combining data retrieved from a local database with information fetched from an external API. The goal is to show how database query results can be enriched with real-time or external data to produce more informative outputs.

In this example, we read city-level data from a customer database, identify unique cities and their associated countries, and then use the REST Countries API to fetch additional country-level information. The final output displays customer counts by city alongside relevant country details, illustrating how API data and database queries can work together to provide deeper insights.

## **Upload CSV Files and Create Database**

In this step, we upload the CSV files into Google Colab and use them to create a SQLite database. Once the files are uploaded, we will load each CSV and save it as a table in the database so we can run SQL queries on it in later steps.

## **Upload the Customers CSV File**

First, upload the `customers (1).csv` file into Google Colab. This file will be used to create the **customers** table in our SQLite database.
"""

# Import Colab's file upload utility (lets us upload files from our computer)
from google.colab import files

# Give the user a clear instruction before the upload prompt appears
print("Please select the 'customers (1).csv' file")

# Open the file picker and upload the selected file into the Colab environment
uploaded = files.upload()

# Confirmation message so we know the upload completed
print("Customers file uploaded!")

"""The `customers (1).csv` file has been uploaded successfully and is now available in the Colab workspace. Next, we will create a SQLite database and load this CSV into a `customers` table so we can run SQL queries on it.

## **Upload the Products CSV File**

Next, upload the `products (1).csv` file. This file will later be loaded into the SQLite database as a **products** table for querying and integration.
"""

# Import Colab's file upload utility
from google.colab import files

# Prompt the user with a clear instruction before opening the file picker
print("Please select the 'products (1).csv' file")

# Upload the products CSV into the Colab environment
uploaded = files.upload()

# Confirmation message to verify the upload completed
print("Products file uploaded!")

"""The `products (1).csv` file has been uploaded successfully and is now available in the Colab environment. In the next step, this file will be loaded into the SQLite database as a `products` table alongside the customer data.

## **Upload the Orders CSV File**

Finally, upload the `orders (1).csv` file. This file will be used to create the **orders** table in the SQLite database and will allow us to connect customers with the products they purchased.
"""

# Import Colab's file upload utility
from google.colab import files

# Prompt the user with the correct filename to upload
print("Please select the 'orders (1).csv' file")

# Upload the orders CSV into the Colab environment
uploaded = files.upload()

# Confirmation message so we know the upload finished
print("Orders file uploaded!")

"""The `orders (1).csv` file has been uploaded successfully and is now available in the Colab workspace. Next, we will create a SQLite database and load all three CSV files into tables (`customers`, `products`, and `orders`) so we can run queries and integrate the results with API data.

## **Create the SQLite Database and Import CSV Data**

Now that all three CSV files are uploaded, we will create a SQLite database named `ecommerce.db` and set up three tables: **customers**, **products**, and **orders**. After creating the table schema (including primary keys, foreign keys, and basic validation rules), we will import the data from each CSV file into its corresponding table.

This step ensures the database is fully populated and ready for SQL queries and for the optional bonus integration where database results can be enriched using external API data.
"""

# Import required built-in libraries
import sqlite3        # SQLite database connection and SQL execution
import csv            # Reading CSV files
import urllib.request # Will be used later for API calls
import json           # Will be used later for parsing JSON responses

# Path to the SQLite database file
DB_PATH = "ecommerce.db"


def create_database(db_path):
    """
    Create a SQLite database with customers, products, and orders tables.
    Existing tables are dropped so the script can be re-run cleanly.

    Args:
        db_path (str): Path to the SQLite database file
    """
    # Connect to the database (creates the file if it does not exist)
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()

        # Enable foreign key constraints
        cursor.execute("PRAGMA foreign_keys = ON;")

        # Drop existing tables to avoid conflicts
        cursor.execute("DROP TABLE IF EXISTS orders;")
        cursor.execute("DROP TABLE IF EXISTS products;")
        cursor.execute("DROP TABLE IF EXISTS customers;")

        # Create customers table
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS customers (
                customer_id INTEGER PRIMARY KEY,
                name TEXT NOT NULL,
                email TEXT NOT NULL UNIQUE,
                city TEXT NOT NULL,
                join_date DATE NOT NULL
            )
        """)

        # Create products table
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS products (
                product_id INTEGER PRIMARY KEY,
                name TEXT NOT NULL,
                category TEXT NOT NULL,
                price REAL NOT NULL CHECK(price >= 0),
                cost REAL NOT NULL CHECK(cost >= 0)
            )
        """)

        # Create orders table with foreign key relationships
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS orders (
                order_id INTEGER PRIMARY KEY,
                customer_id INTEGER NOT NULL,
                product_id INTEGER NOT NULL,
                quantity INTEGER NOT NULL CHECK(quantity > 0),
                order_date DATE NOT NULL,
                FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
                FOREIGN KEY (product_id) REFERENCES products(product_id)
            )
        """)

        # Save changes to the database
        conn.commit()

    print("Database and tables created successfully")


def import_csv_to_db(db_path, csv_file, table_name):
    """
    Import data from a CSV file into a specified SQLite table.

    The CSV column order must match the table schema exactly.

    Args:
        db_path (str): Path to the SQLite database file
        csv_file (str): Name of the CSV file to import
        table_name (str): Target table name in the database
    """
    rows_imported = 0

    # Connect to the database
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()

        # Open and read the CSV file
        with open(csv_file, "r", newline="", encoding="utf-8") as file:
            csv_reader = csv.reader(file)

            # Read header row to determine number of columns
            header = next(csv_reader)
            placeholders = ", ".join(["?" for _ in range(len(header))])

            # Build the INSERT statement dynamically
            insert_sql = f"INSERT INTO {table_name} VALUES ({placeholders})"

            # Insert each row into the table
            for row in csv_reader:
                cursor.execute(insert_sql, row)
                rows_imported += 1

        # Save changes to the database
        conn.commit()

    print(f"Imported {rows_imported} rows into '{table_name}'")


# Run the database setup and CSV imports
print("Setting up database")

create_database(DB_PATH)
import_csv_to_db(DB_PATH, "customers (1).csv", "customers")
import_csv_to_db(DB_PATH, "products (1).csv", "products")
import_csv_to_db(DB_PATH, "orders (1).csv", "orders")

print("Database setup complete")

"""The database setup completed successfully, and all three tables were populated with data from the uploaded CSV files. With the database now ready, we can proceed to query the data and integrate the results with external API information.

## **Get Cities from the Database**

In this step, we query the SQLite database to identify the unique cities listed in the `customers` table and count how many customers belong to each city. This summary is important for integration because it provides a city-level view of our customer base that we can later enrich using external API data.

The function below returns a list of `(city, customer_count)` pairs, sorted from the highest to lowest customer count, so we can quickly see where most customers are located.
"""

def get_cities_from_database(db_path):
    """
    Get unique cities and customer counts from the customers table.

    Args:
        db_path (str): Path to the SQLite database file

    Returns:
        list[tuple]: A list of (city, customer_count) tuples
    """
    # Connect to the SQLite database
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()

        # SQL query to group customers by city and count customers in each city
        results = cursor.execute("""
            SELECT city, COUNT(*) as customer_count
            FROM customers
            GROUP BY city
            ORDER BY customer_count DESC
        """).fetchall()

    # Return list of (city, count)
    return results


# Test the function by printing city counts from the database
print("Cities in our database:")
print("-" * 40)

# Call the function and store results
cities = get_cities_from_database(DB_PATH)

# Loop through results and display them in a readable format
for city, count in cities:
    print(f"  {city}: {count} customers")

"""The query ran successfully and returned the list of unique customer cities along with their customer counts. Next, we will use these city results to map each city to a country and then fetch country details from the REST Countries API.

## **Map Cities to Countries**

The REST Countries API requires a country name as input, but our database contains city names. To connect the database results with the API, we first create a simple city-to-country mapping. Since the cities in this dataset are U.S. cities, we map each city to **United States**.

This step acts as a bridge between the database and the API. The helper function below returns the correct country for a given city, and it includes a default value to prevent errors if a city is not listed in the mapping.
"""

# City-to-country mapping for this dataset
# All cities in our customers table are U.S. cities, so they map to "United States of America"
CITY_COUNTRY_MAP = {
    "Boston": "United States of America",
    "New York": "United States of America",
    "Chicago": "United States of America",
    "San Francisco": "United States of America",
    "Los Angeles": "United States of America",
    "Houston": "United States of America",
    "Phoenix": "United States of America",
    "Philadelphia": "United States of America",
    "San Antonio": "United States of America",
    "San Diego": "United States of America",
    "Dallas": "United States of America",
    "San Jose": "United States of America"
}

def get_country_for_city(city):
    """
    Return the country name for a given city.

    Args:
        city (str): City name from the database

    Returns:
        str: Country name (defaults to 'United States of America' if the city is not found)
    """
    # Look up the city in the mapping; if not found, default to the USA
    return CITY_COUNTRY_MAP.get(city, "United States of America")


# Confirmation message so we know this cell ran successfully
print("City to Country mapping created!")

"""The city-to-country mapping has been set up successfully and will be used to translate city names into country names required by the API. In the next step, we will call the REST Countries API to retrieve country-level information and combine it with the database results.

## **Create the API Function**

In this step, we define a function that fetches country-level information from the REST Countries API. This function takes a country name (for example, “United States”), sends an API request, and parses the JSON response into a Python dictionary.

To keep the integration simple and reliable, the function extracts only a few key fields that are useful for enrichment such as the country name, capital, population, region, languages, currency, and flag. Basic error handling is included so the program fails gracefully if the API request is unsuccessful or the input is invalid.
"""

import urllib.parse

def fetch_country_data(country_name):
    try:
        # Encode spaces and special characters safely for a URL
        country_encoded = urllib.parse.quote(country_name.strip())

        # Use the encoded country in the API URL
        url = f"https://restcountries.com/v3.1/name/{country_encoded}"

        with urllib.request.urlopen(url) as response:
            data = json.loads(response.read().decode("utf-8"))

        country = data[0]

        name = country.get("name", {}).get("common", "N/A")
        capital = country.get("capital", ["N/A"])[0]
        population = country.get("population", 0)
        region = country.get("region", "N/A")

        languages_dict = country.get("languages", {})
        languages = ", ".join(languages_dict.values()) if languages_dict else "N/A"

        currencies_dict = country.get("currencies", {})
        currency = "N/A"
        for code, info in currencies_dict.items():
            currency = f"{info.get('name', '')} ({info.get('symbol', '')})".strip()
            break

        flag = country.get("flag", "")

        return {
            "name": name,
            "capital": capital,
            "population": population,
            "region": region,
            "languages": languages,
            "currency": currency,
            "flag": flag
        }

    except Exception as e:
        print(f"API Error: {e}")
        return None

"""The API function has been created successfully and is ready to retrieve country details from the REST Countries API. In the next step, we will loop through the cities from our database, map each city to its country, and use this function to fetch country information for the combined output.

## **Integration Function**

In this step, we create the function that combines database query results with API data. The function first pulls the list of cities and their customer counts from the SQLite database, then maps each city to a country name, and finally fetches country information using the REST Countries API.

To keep the integration efficient, the function uses a simple caching approach so that the same country is not requested multiple times. The final output is returned as a list of dictionaries, where each record contains the city, customer count, country name, and the associated country data retrieved from the API.
"""

def get_city_with_country_info(db_path):
    """
    Get cities from the database and enrich them with country information from the API.

    Args:
        db_path (str): Path to the SQLite database file

    Returns:
        list[dict]: List of dictionaries containing combined database and API data
    """
    # Step 1: Get city-level customer counts from the database
    cities = get_cities_from_database(db_path)

    # Step 2: Cache API results to avoid repeating the same API call
    # Key: country name, Value: country_data dictionary
    countries_fetched = {}

    # Step 3: Build a combined results list
    results = []

    # Loop through each city and its customer count
    for city, customer_count in cities:
        # Convert city into a country name (API needs a country, not a city)
        country_name = get_country_for_city(city)

        # Fetch country data from the API only once per country (cache results)
        if country_name not in countries_fetched:
            print(f"Fetching data for {country_name}...")
            country_data = fetch_country_data(country_name)
            countries_fetched[country_name] = country_data
        else:
            # Reuse cached data if we already fetched it
            country_data = countries_fetched[country_name]

        # Combine database data (city, customer_count) with API country data
        combined = {
            "city": city,
            "customer_count": customer_count,
            "country": country_name,
            "country_data": country_data
        }

        # Add combined record to results list
        results.append(combined)

    return results


# Confirmation message so we know this function cell ran successfully
print("get_city_with_country_info() function created!")

"""The integration function has been created successfully and now connects the database with external API data. In the next step, we will execute this function to retrieve and display the combined city-level customer information enriched with country details.

## **Display Function**

In this step, we create a display function to present the combined database and API results in a clear, readable format. Instead of showing raw dictionaries, this function prints each city with its customer count and the enriched country details retrieved from the REST Countries API.

At the end, the function also prints a short summary including the total number of cities returned and the total number of customers across all cities. This makes the output easy to interpret and helps confirm that the integration worked as expected.
"""

def display_integration_results(results):
    """
    Display combined database and API results in a readable format.

    Args:
        results (list[dict]): List of combined dictionaries returned by get_city_with_country_info()
    """
    # Print a header to make the output visually clear
    print("\n" + "=" * 70)
    print("  CUSTOMER CITIES WITH COUNTRY INFORMATION")
    print("  (Database + API Integration)")
    print("=" * 70)

    # Loop through each combined record (one per city)
    for item in results:
        city = item["city"]                      # City name from database
        count = item["customer_count"]           # Customer count from database
        country_data = item["country_data"]      # Country details from API

        # Display city-level data
        print(f"\n  City: {city}")
        print(f"  Customers: {count}")

        # Display API-enriched country details (only if API call succeeded)
        if country_data:
            print(f"  Country: {country_data['flag']} {country_data['name']}")
            print(f"  Capital: {country_data['capital']}")
            print(f"  Population: {country_data['population']:,}")
            print(f"  Region: {country_data['region']}")
            print(f"  Currency: {country_data['currency']}")

        # Separator line between cities
        print("  " + "-" * 50)

    # Compute and display a small summary at the end
    total_customers = sum(item["customer_count"] for item in results)
    total_cities = len(results)

    print("\n  SUMMARY:")
    print(f"  Total Cities: {total_cities}")
    print(f"  Total Customers: {total_customers}")
    print("=" * 70)


# Confirmation message so we know this function cell ran successfully
print("display_integration_results() function created!")

"""The display function has been created successfully and is ready to present the integrated results. In the next step, we will run the full integration workflow to fetch database data, enrich it using the API, and display the combined output.

## **Main Function**

In this final step, we create the main function that runs the complete database and API integration workflow. The function prints a short introduction, calls the integration function to combine database query results with REST Countries API data, and then displays the final enriched output using the display function.

This main function serves as the entry point for the bonus demonstration and shows the full end-to-end process of reading from a SQLite database, fetching external API data, and presenting combined results in a clean format.
"""

def main():
    """Main function for the API + database integration demo."""

    # Print a clear title header for the bonus integration output
    print("=" * 70)
    print("  API INTEGRATION WITH DATABASE - BONUS DEMO")
    print("=" * 70)

    # Explain what the script is about (helps graders understand quickly)
    print("\nThis script demonstrates:")
    print("  1. Reading data from a SQLite database")
    print("  2. Fetching additional data from the REST Countries API")
    print("  3. Combining both data sources into one output")
    print("\n" + "-" * 70)

    # Run the integration workflow (database query + API enrichment)
    print("\nFetching and combining data...\n")
    results = get_city_with_country_info(DB_PATH)

    # Display the final integrated results in a readable format
    display_integration_results(results)

    # Final completion message
    print("\nAPI Integration demonstration complete!")


# Confirmation message so we know this cell ran successfully
print("main() function created for integration demo!")

"""All components of the integration workflow are now ready, including database queries, API calls, and formatted output. In the next step, we will run the `main()` function to execute the full integration and display the combined results.

## **Interactive Query**

In this step, we add an interactive feature that allows the user to explore the integrated data in more detail. Instead of displaying all cities at once, the program shows the list of available cities from the database and then prompts the user to select one city to look up.

Once a city is selected, the script retrieves the customer count for that city from the database and fetches the related country information from the REST Countries API. This provides a focused, user-driven way to validate and demonstrate the database and API integration.
"""

def interactive_city_lookup(db_path):
    """
    Interactive function to look up city information from the database
    and enrich it with country information from the API.
    """
    # Print a header for the interactive lookup section
    print("\n" + "=" * 50)
    print("  INTERACTIVE CITY LOOKUP")
    print("=" * 50)

    # Get list of available cities (and customer counts) from the database
    cities = get_cities_from_database(db_path)

    # Display available cities so the user knows what can be searched
    print("\nAvailable cities in database:")
    for city, count in cities:
        print(f"  - {city} ({count} customers)")

    # Ask the user which city they want to look up
    city_name = input("\nEnter city name to lookup: ")

    # Search for the city in the database results (case-insensitive match)
    city_found = None
    customer_count = 0

    for city, count in cities:
        if city.lower() == city_name.lower():
            city_found = city
            customer_count = count
            break

    # If the user enters a city not in the database, exit gracefully
    if not city_found:
        print(f"\nError: City '{city_name}' not found in database.")
        return

    # Map the city to its country (API requires country name)
    country_name = get_country_for_city(city_found)

    # Fetch country data from the REST Countries API
    print(f"\nFetching country data for {country_name}...")
    country_data = fetch_country_data(country_name)

    # Print the final results in a clean format
    print("\n" + "=" * 50)
    print(f"  RESULTS FOR: {city_found.upper()}")
    print("=" * 50)

    # Database information
    print("\n  FROM DATABASE:")
    print(f"    City: {city_found}")
    print(f"    Customers: {customer_count}")

    # API information (only if the API call succeeded)
    if country_data:
        print("\n  FROM API:")
        print(f"    Country: {country_data['flag']} {country_data['name']}")
        print(f"    Capital: {country_data['capital']}")
        print(f"    Population: {country_data['population']:,}")
        print(f"    Region: {country_data['region']}")
        print(f"    Languages: {country_data['languages']}")
        print(f"    Currency: {country_data['currency']}")

    # Footer line
    print("\n" + "=" * 50)


# Run the interactive lookup (user will be prompted for a city name)
interactive_city_lookup(DB_PATH)

"""This output shows a successful end-to-end integration between the SQLite database and the REST Countries API. The database provides the city-level customer information (Boston has 12 customers), and the API enriches this result with relevant country details such as capital, population, region, language, and currency. Together, this demonstrates how external data sources can add meaningful context to internal database records and make the results more informative for analysis or reporting.

## **Conclusion**

This assignment successfully demonstrates core Python skills related to database management and API interaction. By creating a SQLite database from CSV files, running SQL queries, and presenting structured results, the project shows a clear understanding of working with relational data and file-based data sources.

The API demonstration and the optional integration section further illustrate how external data can be fetched, parsed, and combined with internal database records to provide richer insights. Overall, the assignment highlights clean code organization, effective use of functions, basic error handling, and practical integration techniques that are commonly used in real-world data-driven applications.
"""