Python：如何通过API将数据按列写入CSV（解决仅存最后一行问题）

Hey there! Let's get this sorted out—you're absolutely right about the problem: every time you run open('AutoCSV.csv', "w") inside the loop, you're wiping the file clean and starting over. That's why only the last row sticks around. Let's fix this step by step, with beginner-friendly explanations.

What's Going Wrong #

The "w" (write) mode in Python's open() function overwrites the entire file every time you use it. Since you're doing this inside your inner loop, you're erasing all previous data before writing each new row—leaving only the very last one when the loop finishes.

The Fix: Open the File Once, Write All Rows #

Instead of opening/closing the file in every iteration, open it once before your loops start, and keep writing rows to it. We'll use a with statement too—it automatically handles closing the file, which is safer for beginners.

Here's the revised code, with extra improvements for robustness:

import requests
import csv

# Open the file once at the start (w mode creates/overwrites once, not every loop)
with open('AutoCSV.csv', "w", newline='') as myFile:
    # Initialize the CSV writer once
    writer = csv.writer(myFile)
    
    # Optional: Write a header row so your columns have labels
    writer.writerow(['deviceGuid', 'status'])
    
    # Loop through your API pages
    for x in range(0, 5):
        # Use f-strings for cleaner URL formatting (Python 3.6+)
        api_url = f"https://api.rainforestcloud.com/rest/device?networkName=Company&take=500&skip={x*500}"
        
        # Try/except to handle API request errors (prevents crashes)
        try:
            response = requests.get(api_url, headers=headers).json()
        except Exception as e:
            print(f"Failed to fetch page {x}: {e}")
            continue
        
        # Loop through each item in the API response (no hardcoding 499!)
        for device in response:
            # Extract the two values for your columns
            row_data = [device['deviceGuid'], device['status']]
            # Write the row to the CSV
            writer.writerow(row_data)

Key Improvements Explained #

• Single file open: The with block keeps the file open for all writes, so no more overwriting.
• F-strings for URLs: Easier to read and edit than concatenating strings with +.
• No hardcoded loop limits: Using for device in response avoids errors if the API returns fewer than 500 devices on the last page.
• Error handling: The try/except catches issues like network errors or invalid JSON, so your script doesn't crash halfway through.
• Newline parameter: newline='' prevents extra blank rows from appearing in CSV files on Windows (a common gotcha!).

About "Writing by Columns" #

CSV files are structured row-by-row, but the code above will create exactly what you need: a file with two columns (deviceGuid and status), where each row has one device's data. When you open this in Excel or Google Sheets, it'll display as two columns with ~2500 rows (5 pages × 500 devices).

Quick Testing Tip #

Before running the full 5 pages, change range(0,5) to range(0,1) to fetch just 500 devices. This lets you verify the CSV looks right without waiting for all requests to finish.

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