如何用Python和boto3将Xlsx导入DynamoDB？旧代码失效求支招

Hey there, sorry to hear that outdated tutorial threw you for a loop! You definitely don't need three separate scripts to get your Excel data into DynamoDB—let's use a streamlined, modern approach with Python that handles everything in one go. Here's how to do it:

Step 1: Install Required Tools #

First, install the Python libraries we'll need for Excel processing and AWS interactions. Run this in your terminal:

pip install pandas boto3 openpyxl

• pandas: Makes reading Excel files a breeze
• boto3: The official AWS SDK for Python (works with the latest DynamoDB APIs)
• openpyxl: Required to read modern .xlsx files

Step 2: Single Script for Full Workflow #

Create a Python file (e.g., excel_to_dynamodb.py) with this code. It reads your Excel file, transforms the data into DynamoDB-compatible format, and uploads it in batches automatically:

import pandas as pd
import boto3
from botocore.exceptions import ClientError

def excel_to_dynamodb(excel_file_path, dynamodb_table_name):
    # Load Excel data (uses the first worksheet by default)
    df = pd.read_excel(excel_file_path, engine='openpyxl')
    
    # Initialize DynamoDB resource
    dynamodb = boto3.resource('dynamodb')
    table = dynamodb.Table(dynamodb_table_name)
    
    # Batch write items to DynamoDB (handles batch limits automatically)
    with table.batch_writer() as batch:
        for index, row in df.iterrows():
            # Convert row to a dictionary (DynamoDB accepts dict objects)
            item = row.to_dict()
            
            # Optional: Clean up data (handle empty values, date formats, etc.)
            # Example: Convert pandas datetime to ISO string if needed
            # for key, value in item.items():
            #     if pd.api.types.is_datetime64_any_dtype(value):
            #         item[key] = value.isoformat()
            
            try:
                batch.put_item(Item=item)
                print(f"Uploaded row {index+1}: {item}")
            except ClientError as e:
                print(f"Failed to upload row {index+1}: {e.response['Error']['Message']}")

# Replace these with your file path and table name
if __name__ == "__main__":
    EXCEL_FILE = "your_excel_data.xlsx"
    DYNAMODB_TABLE = "your-target-table-name"
    excel_to_dynamodb(EXCEL_FILE, DYNAMODB_TABLE)

Step 3: Configure AWS Credentials #

Make sure your local environment has AWS credentials set up so boto3 can access your DynamoDB table. You can do this by:

• Creating a ~/.aws/credentials file (on macOS/Linux) or C:\Users\<YourName>\.aws\credentials (Windows) with your AWS access key and secret key
• Or setting environment variables: AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY

Key Notes to Avoid Issues #

• Match Primary Keys: Ensure your Excel file has columns that match the partition key (and sort key, if your table uses one) of your DynamoDB table. Otherwise, uploads will fail.
• Handle Empty Values: DynamoDB doesn't allow empty string values for some data types. Use df = df.dropna() to remove rows with missing values, or fill them with defaults (e.g., df.fillna("N/A", inplace=True)).
• Batch Limits: The batch_writer automatically splits your data into valid batches (max 25 items or 16MB per batch) so you don't have to manage that manually.

Verify the Upload #

After running the script, head to the DynamoDB console, select your table, and check the "Explore table items" tab to confirm your data was uploaded correctly.

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