掌握USDA食品数据API分页获取完整营养信息教程

import requests
import json
import pandas as pd

def get_all_usda_foods(query_term, api_key, page_size=200):
    """
    从USDA食品数据API分页获取所有符合查询条件的食品数据。

    参数:
    query_term (str): 查询的食品名称或关键词。
    api_key (str): USDA API的授权密钥。
    page_size (int): 每页返回的结果数量，最大为200。

    返回:
    list: 包含所有食品数据字典的列表，如果发生错误则返回None。
    """
    all_foods = []
    # 构造初始URL，设置每页大小
    base_url = f"https://api.nal.usda.gov/fdc/v1/foods/search?api_key={api_key}&query={query_term}&pageSize={page_size}"

    with requests.Session() as session:  # 使用requests.Session保持连接，提高效率
        try:
            # 第一次请求，获取总页数
            print(f"Fetching initial page for query: '{query_term}'")
            response = session.get(base_url, timeout=10)
            response.raise_for_status()  # 检查HTTP请求是否成功
            api_response = response.json()

            # 检查API响应结构，确保'foods'和'totalPages'存在
            if "foods" not in api_response or "totalPages" not in api_response:
                print("Error: 'foods' or 'totalPages' not found in initial API response.")
                return None

            all_foods.extend(api_response["foods"])
            total_pages = api_response["totalPages"]
            print(f"Total pages to fetch: {total_pages}")

            # 从第二页开始循环获取所有数据
            for page_num in range(2, total_pages + 1):
                print(f"Fetching page {page_num}/{total_pages}...")
                page_url = f"{base_url}&pageNumber={page_num}"
                page_response = session.get(page_url, timeout=10)
                page_response.raise_for_status()
                page_api_response = page_response.json()

                if "foods" in page_api_response:
                    all_foods.extend(page_api_response["foods"])
                else:
                    print(f"Warning: 'foods' not found in response for page {page_num}.")
                    break # 如果某一页没有'foods'，可能表示数据结束或API行为异常

        except requests.exceptions.HTTPError as http_err:
            print(f"HTTP error occurred: {http_err} - Status Code: {http_err.response.status_code}")
            return None
        except requests.exceptions.ConnectionError as conn_err:
            print(f"Connection error occurred: {conn_err}")
            return None
        except requests.exceptions.Timeout as timeout_err:
            print(f"Request timed out: {timeout_err}")
            return None
        except requests.exceptions.RequestException as req_err:
            print(f"An unexpected request error occurred: {req_err}")
            return None
        except json.JSONDecodeError as json_err:
            print(f"Failed to decode JSON response: {json_err}")
            print(f"Response content: {response.text[:500]}...") # 打印部分响应内容辅助调试
            return None
        except Exception as e:
            print(f"An unexpected error occurred: {e}")
            return None

    return all_foods

# 使用示例
if __name__ == "__main__":
    API_KEY = "YOUR_USDA_API_KEY" # 替换为你的USDA API密钥
    search_term = "raw fruit" # 示例查询词

    print(f"Starting data retrieval for '{search_term}'...")
    food_items_data = get_all_usda_foods(search_term, API_KEY)

    if food_items_data:
        print(f"\nSuccessfully retrieved {len(food_items_data)} food items.")

        # 将数据转换为Pandas DataFrame并导出到Excel
        processed_data = []
        for item in food_items_data:
            row = {"Description": item.get("description", "N/A")}
            for nutrient in item.get("foodNutrients", []):
                nutrient_name = nutrient.get("nutrientName")
                nutrient_value = nutrient.get("value")
                if nutrient_name and nutrient_value is not None:
                    row[nutrient_name] = nutrient_value
            processed_data.append(row)

        df = pd.DataFrame(processed_data)

        # 清理列名，处理重复营养素（如果存在）
        # 这里只是一个简单的处理，实际应用中可能需要更复杂的逻辑
        df = df.loc[:,~df.columns.duplicated()] 

        output_filename = f'{search_term.replace(" ", "_")}_nutritional_facts.xlsx'
        df.to_excel(output_filename, index=False)
        print(f"Data exported to {output_filename}")
    else:
        print("Failed to retrieve food data.")