Python用xlrd遍历Excel行列生成键值对并循环执行HTTP请求

Alright, let's walk through how to build this logic from scratch. I'll cover generating the required JSON structures, looping through your datasets, and handling the conditional GET/PUT/POST flow based on your parameters.

Step 1: Import Required Libraries #

First, grab the tools we'll need for Excel reading, HTTP requests, and JSON handling:

import xlrd
import requests
import json
from itertools import product  # For creating all combinations of your datasets

Step 2: Define Your Parameters #

Let's set up the exact parameters you provided (I'll note the opinion typo you mentioned later for the request logic):

host = 'your-api-endpoint-here'  # Replace with your actual API host URL
opinion = 'do'  # You specified checking for 'd0' to trigger request logic
columns = {"a1", "a2", "a3"}
rows = {"b1", "b2", "b3"}
mynums = (1234, 4562, 8521, 6592)
key = "k"
value_key = "V"  # Renamed to avoid clashing with Python's built-in 'value' term

Step 3: Generate Target JSON Structures #

Since you need to loop through columns, rows, and mynums, we'll use itertools.product to create every possible combination of these values. For each combo, we'll build the JSON object you specified:

# Create all possible combinations of columns, rows, and mynums
all_combinations = product(columns, rows, mynums)

# Build a list of JSON objects
json_outputs = []
for col_val, row_val, num in all_combinations:
    json_obj = {
        key: col_val,
        value_key: row_val,
        "number": num  # Added since you asked to loop through mynums; remove if not needed
    }
    json_outputs.append(json_obj)

# Example entry in json_outputs: {"k": "a1", "V": "b1", "number": 1234}

Step 4: Handle Conditional HTTP Requests #

Next, let's build a function to handle the GET/PUT/POST logic when opinion equals 'd0'. I'll use placeholder API paths—you'll need to adjust these to match your actual API's requirements:

def process_api_requests(host, json_obj):
    # Build GET request URL to check if data exists (customize for your API)
    check_url = f"{host}/verify-data?{key}={json_obj[key]}&{value_key}={json_obj[value_key]}"
    
    try:
        # Send GET request to validate data existence
        get_response = requests.get(check_url)
        get_response.raise_for_status()  # Trigger error for HTTP status codes >=400
        
        # Assume your API returns {"exists": True/False} to indicate data presence
        if get_response.json().get("exists"):
            # Data exists: send PUT request to update
            update_url = f"{host}/update-data"
            put_response = requests.put(update_url, json=json_obj)
            put_response.raise_for_status()
            print(f"✅ PUT successful for {json_obj}: {put_response.status_code}")
        else:
            # Data doesn't exist: send POST request to create
            create_url = f"{host}/create-data"
            post_response = requests.post(create_url, json=json_obj)
            post_response.raise_for_status()
            print(f"✅ POST successful for {json_obj}: {post_response.status_code}")
    
    except requests.exceptions.RequestException as e:
        print(f"❌ Request failed for {json_obj}: {str(e)}")

# Run the request logic only if opinion matches 'd0'
if opinion == 'd0':
    for item in json_outputs:
        process_api_requests(host, item)
else:
    print(f"Opinion '{opinion}' doesn't trigger HTTP requests.")

Step 5: Integrate Excel Data Reading (If Needed) #

You said you're already using xlrd to fetch columns and rows, but here's a quick example to tie it all together in case you need to extract those values directly from an Excel file:

# Open the Excel workbook (supports .xls files only; use openpyxl for .xlsx)
workbook = xlrd.open_workbook('your-data-file.xls')
target_sheet = workbook.sheet_by_index(0)  # Select the first sheet

# Extract column values (e.g., from column A, skip header row)
columns = set(target_sheet.col_values(0)[1:])

# Extract row values (e.g., from row 1, skip header column)
rows = set(target_sheet.row_values(1)[1:])

Key Notes: #

• API Customization: The URL paths, request parameters, and response checks are placeholders—adjust them to match your API's actual structure and behavior.
• xlrd Limitation: Remember xlrd only works with .xls files. If you're using .xlsx, switch to openpyxl instead.
• Error Handling: The try/except blocks catch network errors and HTTP issues—expand them to handle specific error cases (like invalid JSON responses) if needed.

内容的提问来源于stack exchange，提问作者rohit rocckz