Python遍历字典键值对插入SQL数据库的实现求助

Got it! You're already halfway there—you’ve got the data pulled from Weight Gurus and your database connection set up. The missing piece is looping through each key-value pair in your dictionaries and inserting them as individual rows. Let’s fix that.

Modified Code with Row-by-Row Insert Logic #

import requests
import pyodbc

# Login and get auth token
data = {"email": "your_email@example.com", "password": "your_password"}
login_response = requests.post("https://api.weightgurus.com/v3/account/login", data=data)
login_json = login_response.json()

# Fetch scale data
data_response = requests.get(
    "https://api.weightgurus.com/v3/operation/",
    headers={
        "Authorization": f'Bearer {login_json["accessToken"]}',
        "Accept": "application/json, text/plain, */*",
    },
)
scale_data_json = data_response.json()

# Database connection
server = 'your_server_name'
database = 'your_database_name'
username = 'your_username'
password = 'your_password'
driver='{ODBC Driver 13 for SQL Server}'
cnxn = pyodbc.connect(f'DRIVER={driver};SERVER={server};PORT=1433;DATABASE={database};UID={username};PWD={password}')
cursor = cnxn.cursor()

# Customize this query to match your BodyComposition table structure
# Note: Your auto-increment ID column doesn't need to be included here—SQL Server handles it automatically
insert_query = """
INSERT INTO BodyComposition (OperationID, MetricName, MetricValue)
VALUES (?, ?, ?)
"""

try:
    # Loop through each top-level entry from the API
    for entry in scale_data_json["operations"]:
        # Grab the unique ID of the original entry (adjust the key if your entry uses a different ID field)
        operation_id = entry.get("id")
        if not operation_id:
            print(f"Skipping entry without a valid ID: {entry}")
            continue
        
        # Loop through each key-value pair in the entry
        for metric_name, metric_value in entry.items():
            # Skip the ID key if you don't want to insert it as a metric
            if metric_name == "id":
                continue
            
            # Handle null values to avoid insertion errors
            if metric_value is None:
                metric_value = None
            
            # Execute parameterized query (prevents SQL injection and handles data types)
            cursor.execute(insert_query, (operation_id, metric_name, metric_value))
    
    # Save all changes to the database
    cnxn.commit()
    print("All metric data inserted successfully!")

except Exception as e:
    # Roll back if any error occurs to avoid partial data insertion
    cnxn.rollback()
    print(f"Error inserting data: {str(e)}")

finally:
    # Clean up connections
    cursor.close()
    cnxn.close()

Key Details to Note #

1. Auto-Increment Primary Key: Your table's auto-increment ID field doesn't require any manual input—SQL Server will generate a unique ID for each new row automatically, so we don't include it in the INSERT statement.
2. Parameterized Queries: Using ? placeholders is critical here—it avoids SQL injection risks and lets pyodbc handle data type conversions between Python and SQL Server.
3. Associate Metrics to Original Entries: I added an OperationID column to link each metric row back to the original entry from Weight Gurus (assuming your entries have an id key). If you don't need this association, just remove the column from the query and omit the operation_id parameter in cursor.execute.
4. Error Handling: The try-except-finally block ensures that if any insertion fails, all changes are rolled back, and connections are properly closed to avoid resource leaks.
5. Skip Unwanted Keys: The check for metric_name == "id" lets you skip inserting the entry's ID as a metric row—adjust this if there are other keys you don't want to include.

Adapt to Your Table Structure #

If your BodyComposition table uses different column names (e.g., MetricType instead of MetricName), just update the INSERT query to match. For example, if you only need metric names and values:

INSERT INTO BodyComposition (MetricName, MetricValue)
VALUES (?, ?)

Then modify the cursor.execute line to cursor.execute(insert_query, (metric_name, metric_value)).

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