如何优化Python脚本替换DBF文件中的字符字段为整数字段？

Great to hear your script works for small data—let's tweak it to handle that 2.5GB DBF file efficiently without choking on memory or speed. Here's a step-by-step optimized approach using the dbf library:

1. Avoid Loading the Entire Table Into Memory #

The biggest mistake with large DBFs is reading all records at once. Instead, iterate through the table row-by-row, which keeps memory usage low even for 9 million records:

import dbf

# Open source table in read-only mode (no full table load)
with dbf.Table("your_source_file.dbf") as source_table:
    # Define your target table structure: replace the 80-char field with an integer
    # Example: if original fields are [('id', 'N', 10), ('long_char', 'C', 80), ('other', 'C', 20)]
    target_fields = []
    for field in source_table.fields:
        if field.name != "long_char":
            target_fields.append(field)
    # Add the new integer field (length 2 is more than enough for 10 unique values)
    target_fields.append(("value_code", "N", 2))

2. Pre-Build the String-to-Integer Map Efficiently #

Since there are only ~10 unique strings, you don't need to scan the entire table to collect them. Stop as soon as you've found all unique values to save time:

value_map = {}
    current_code = 1
    for record in source_table:
        # Normalize values (strip whitespace, handle case consistency if needed)
        raw_val = record.long_char.strip()
        normalized_val = raw_val.upper()  # Optional: ensures "Apple" and "apple" map to the same code
        
        if normalized_val not in value_map:
            value_map[normalized_val] = current_code
            current_code += 1
            # Stop early once we've captured all 10 unique values
            if current_code > 10:
                break
    
    # Reset the source table pointer to start processing from the first row
    source_table.top()

3. Batch Writes to Minimize Disk IO #

Disk writes are the biggest bottleneck for large datasets—avoid writing one record at a time. Accumulate records in batches to reduce IO operations:

# Create and open the target table for writing
    with dbf.Table("optimized_dbf.dbf", target_fields) as target_table:
        batch_size = 1000  # Adjust based on your system's memory/disk speed
        batch = []
        
        for record in source_table:
            # Build the new record data
            new_record = []
            for field in source_table.fields:
                field_name = field.name
                if field_name == "long_char":
                    # Map the normalized string to its integer code
                    normalized_val = record.long_char.strip().upper()
                    new_record.append(value_map[normalized_val])
                else:
                    # Copy existing field values as-is
                    new_record.append(getattr(record, field_name))
            
            batch.append(tuple(new_record))
            
            # Write the batch when it reaches the desired size
            if len(batch) >= batch_size:
                target_table.append(batch)
                batch = []
        
        # Write any remaining records in the final partial batch
        if batch:
            target_table.append(batch)

4. Additional Pro Tips #

• Backup First: Always make a full copy of the original DBF before modifying it—data loss is a nightmare with large datasets.
• Validate Results: After conversion, spot-check a few rows to ensure integer codes map correctly to original strings, and verify the row count matches the source table.
• Tweak Batch Size: If you're still seeing slow performance, adjust batch_size (try 5000 or 20000) to find the sweet spot for your system.
• Handle Edge Cases: Add a default code (like 0) for unexpected values (e.g., empty strings) to prevent the script from crashing mid-process.

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