如何解析接口返回的JSON数据并导入MySQL数据库？

Let's break down how to transform your JSON response into the MySQL row format you need, then insert those rows into your database step by step.

Step 1: Parse JSON into Structured Rows #

Your JSON's data array contains objects where each entry's dimensions list is ordered exactly to match your MySQL headers, and metrics holds the two numeric values. We can collect these values in sequence to form a single row per entry.

Here's how to adjust your parsing logic:

import json
import requests
import mysql.connector

def fetch_and_parse_data(url, params):
    # Fetch and validate the JSON response
    r = requests.get(url, params=params)
    r.raise_for_status()  # Throw error if request fails
    json_data = json.loads(r.text)
    
    parsed_rows = []
    for entry in json_data.get('data', []):
        # Collect dimension names in the order they appear in the JSON
        dimension_values = [dim.get('name') for dim in entry.get('dimensions', [])]
        # Append metrics values to complete the row
        full_row = dimension_values + entry.get('metrics', [])
        parsed_rows.append(full_row)
    
    return parsed_rows

Step 2: Insert Rows into MySQL #

Next, we'll connect to your MySQL database and insert the parsed rows using parameterized queries (this prevents SQL injection and handles null values correctly).

First, install the required MySQL connector package if you haven't already:

pip install mysql-connector-python

Then, add the insertion logic:

def insert_into_mysql(rows, db_config):
    # Define your MySQL table columns (matches your desired headers)
    columns = [
        "`ym:s:date`",
        "`ym:s:lastTrafficSource`",
        "`ym:s:deviceCategory`",
        "`ym:s:lastSearchEngine`",
        "`ym:s:regionCity`",
        "`ym:s:regionCountry`",
        "`ym:s:lastSearchPhrase`",
        "`ym:s:visits`",
        "`ym:s:pageviews`"
    ]
    
    # Create table if it doesn't exist (adjust data types to fit your needs)
    create_table_query = f"""
    CREATE TABLE IF NOT EXISTS your_table_name (
        {columns[0]} DATE,
        {columns[1]} VARCHAR(255),
        {columns[2]} VARCHAR(255),
        {columns[3]} VARCHAR(255),
        {columns[4]} VARCHAR(255),
        {columns[5]} VARCHAR(255),
        {columns[6]} TEXT,
        {columns[7]} DECIMAL(10,2),
        {columns[8]} DECIMAL(10,2)
    )
    """
    
    # Connect to MySQL and execute queries
    conn = mysql.connector.connect(**db_config)
    cursor = conn.cursor()
    
    try:
        # Create the table
        cursor.execute(create_table_query)
        
        # Prepare parameterized insert query
        insert_query = f"""
        INSERT INTO your_table_name ({', '.join(columns)})
        VALUES ({', '.join(['%s'] * len(columns))})
        """
        
        # Insert all rows in one batch (more efficient than single inserts)
        cursor.executemany(insert_query, rows)
        conn.commit()
        print(f"Successfully inserted {cursor.rowcount} rows.")
    except mysql.connector.Error as err:
        print(f"Error: {err}")
        conn.rollback()
    finally:
        cursor.close()
        conn.close()

# Example usage
if __name__ == "__main__":
    # Replace with your actual API details
    api_url = "your_api_endpoint_here"
    api_params = {"param1": "value1", "param2": "value2"}
    
    # Replace with your MySQL credentials
    db_config = {
        "host": "localhost",
        "user": "your_mysql_username",
        "password": "your_mysql_password",
        "database": "your_database_name"
    }
    
    # Fetch and parse the JSON data
    parsed_data = fetch_and_parse_data(api_url, api_params)
    
    # Insert into MySQL
    insert_into_mysql(parsed_data, db_config)

Key Details to Note: #

• Order Preservation: The dimension_values list maintains the exact order of dimensions from the JSON, ensuring values map to the correct MySQL columns.
• Null Handling: Using dim.get('name') safely returns None (which MySQL interprets as NULL) when the name key is missing or has a null value.
• Parameterized Queries: The %s placeholders protect against SQL injection and automatically handle data type conversions (like dates, strings, and numbers).
• Data Types: Adjust the CREATE TABLE data types (e.g., use INT instead of DECIMAL if metrics are whole numbers) to match your actual data.

Test the Parsing First #

If you want to verify the parsed rows match your desired format before inserting into MySQL, add this to the example usage:

for row in parsed_data:
    print(" // ".join(str(val) if val is not None else "null" for val in row))

This will print rows in the exact format you shared in your example.

内容的提问来源于stack exchange，提问作者袛屑懈褌褉懈泄 袙芯褉芯薪懈</think_never_used_51bce0c785ca2f68081bfa7d91973934>