基于Python从CSV生成动态INSERT SQL语句的技术实现问询

Nice start on your script for generating INSERT statements from CSV data! Let's refine it to make it more robust, efficient, and safe for real-world use. Here are key enhancements and a polished implementation:

Key Issues in the Original Code #

Before diving into the improved version, let's note what could be better:

• No automatic file cleanup (risk of leaving files open)
• All values are wrapped in quotes (numbers shouldn't be, empty values should use NULL instead of empty strings)
• No handling of special characters (like quotes inside values, which break SQL syntax)
• Single-row INSERTs are inefficient for large datasets
• No support for encoding (can cause garbled text with non-ASCII characters)

Polished Implementation #

import csv
import re

def escape_sql_value(value):
    # Handle empty values (convert to SQL NULL)
    if not value.strip():
        return "NULL"
    # Check if value is a number (int or float) to avoid wrapping in quotes
    if re.match(r'^-?\d+(\.\d+)?$', value):
        return value
    # Escape double quotes (standard for most SQL databases like MySQL, SQL Server)
    escaped_value = value.replace('"', '""')
    return f'"{escaped_value}"'

def generate_insert_statements(csv_path, table_name, batch_size=100):
    insert_statements = []
    # Use 'with' to auto-close the file when done
    with open(csv_path, 'r', encoding='utf-8') as open_file:
        csv_reader = csv.reader(open_file)
        header = next(csv_reader)
        # Wrap column names in backticks to avoid conflicts with SQL keywords
        headers = [f'`{col}`' for col in header]
        insert_prefix = f'INSERT INTO `{table_name}` ({", ".join(headers)}) VALUES '
        
        batch_values = []
        for row in csv_reader:
            # Process each value in the row
            processed_values = [escape_sql_value(val) for val in row]
            batch_values.append(f'({", ".join(processed_values)})')
            
            # Generate a batch INSERT when we hit the batch size
            if len(batch_values) >= batch_size:
                insert_statements.append(f'{insert_prefix}{", ".join(batch_values)};')
                batch_values = []
        
        # Add any remaining rows as a final batch
        if batch_values:
            insert_statements.append(f'{insert_prefix}{", ".join(batch_values)};')
    
    return insert_statements

# Example usage
if __name__ == "__main__":
    # Generate statements from your CSV
    sql_statements = generate_insert_statements('test.csv', 'Table')
    
    # Print statements to console
    for stmt in sql_statements:
        print(stmt)
    
    # Or write directly to a SQL file for easy execution
    with open('insert_data.sql', 'w', encoding='utf-8') as sql_file:
        sql_file.write('\n'.join(sql_statements))

What's Improved? #

• Automatic File Management: The with statement ensures the CSV file is closed properly, even if an error occurs.
• Smart Value Handling:
  • Empty values are converted to NULL (follows SQL best practices)
  • Numbers are left unquoted (avoids type issues in the database)
  • Special characters like quotes are escaped to prevent SQL syntax errors
• Batch Insert Optimization: Instead of one INSERT per row, we group rows into batches (default 100). This drastically speeds up database imports.
• Encoding Support: Explicitly using utf-8 ensures non-ASCII characters (like Chinese, accents) are handled correctly.
• Reusable Function: The code is wrapped in a function, making it easy to reuse across projects by just passing the CSV path and table name.

Extra Tips #

• Database-Specific Adjustments: If you're using PostgreSQL, modify the escape logic to use \ instead of doubling quotes (replace value.replace('"', '""') with value.replace('"', '\\"')).
• Large CSVs: For extremely large files, consider processing rows in chunks without storing all statements in memory (write directly to the SQL file as you process batches).
• CSV Formatting: The csv.reader handles standard CSV edge cases (like fields with commas wrapped in quotes) automatically, so you don't need to worry about breaking rows incorrectly.

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