使用pyodbc循环插入遇SQL错误：提取JSON及RDFA数据代码排查

Let's break down the issues in your code and fix them step by step:

Key Issues Identified #

• Invalid String Concatenation in SQL Query: The line cursor.execute("INSERT INTO RdfaEFs(rdfa) VALUES ('"data"')") has invalid Python syntax. You can't directly embed a variable inside a string like this—it will throw a syntax error before even reaching the SQL execution.
• Inserting a Raw Dictionary into SQL: The data variable from extruct.extract() is a Python dictionary containing multiple structured data types (RDFA, microdata, etc.). You can't insert a dictionary directly into a SQL column; you need to serialize it to a string format like JSON.
• Unsafe SQL Query Construction: Even if you fixed the string concatenation, inserting values directly into the query string exposes you to SQL injection attacks and can cause syntax errors if the data contains single quotes or special characters. Always use parameterized queries.
• Missing Error Handling: There's no handling for failed HTTP requests (e.g., broken URLs, timeouts) or database errors, which will crash your script unexpectedly.

Corrected Code #

Here's the fixed version with all issues addressed:

import pyodbc
import requests
import json
from pprint import pprint
import extruct

# Establish database connection
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost\SQLEXPRESS;DATABASE=WebCrawler; Trusted_Connection=yes')
cursor = cnxn.cursor()

try:
    # Fetch URLs from the database
    cursor.execute("select Id, url from WebCrawlerEFs")
    rows = cursor.fetchall()

    for row in rows:
        print(f"{row.Id}, {row.url}")
        try:
            # Fetch the URL content with timeout and error handling
            r = requests.get(row.url, timeout=10)
            r.raise_for_status()  # Raise error for HTTP status codes >=400

            # Extract structured data
            data = extruct.extract(r.text, r.url)
            # Get only the RDFA portion (adjust if you need other data types)
            rdfa_data = data.get('rdfa', [])
            # Convert RDFA data to JSON string for SQL insertion
            rdfa_json = json.dumps(rdfa_data)

            # Use parameterized query to avoid SQL injection and syntax issues
            cursor.execute("INSERT INTO RdfaEFs(rdfa) VALUES (?)", (rdfa_json,))
            cnxn.commit()  # Commit after each successful insert to preserve data

        except requests.exceptions.RequestException as e:
            print(f"Failed to fetch {row.url}: {str(e)}")
            cnxn.rollback()  # Rollback on request error
        except Exception as e:
            print(f"Error processing ID {row.Id}: {str(e)}")
            cnxn.rollback()  # Rollback on database/processing error

finally:
    # Ensure connection is closed even if an error occurs
    cursor.close()
    cnxn.close()

Explanation of Fixes #

• Parameterized Queries: Using ? as a placeholder and passing the value as a tuple to cursor.execute() ensures safe insertion and avoids SQL injection.
• JSON Serialization: json.dumps(rdfa_data) converts the RDFA list/dictionary into a valid JSON string that can be stored in a SQL column (make sure your rdfa column is of type NVARCHAR(MAX) or similar to accommodate the JSON data).
• Error Handling: Wrapped HTTP requests and database operations in try-except blocks to catch and log errors without crashing the entire script.
• Resource Management: Added a finally block to ensure the database connection and cursor are closed properly.
• Targeted Data Extraction: We now extract only the rdfa key from the extruct output, which is what you intended to insert (the original code was trying to insert all extracted data types).

内容的提问来源于stack exchange，提问作者Oguz Berkay Yerdelen