使用PHP创建数据库并导入文本文件数据：仅插入一行问题排查

Hey Andrews, let's tackle this data insertion issue step by step. It sounds like you've got your database table set up correctly but only one row is making it into the database—super common when dealing with quirky text file formats, so let's break this down.

Looking at your sample format:

@ID::@ID/EM.PRDL.ELIGIBILITY.ID::EM.PRDL.ELIGIBILITY.ID/PRODUCT.ID::PRODUCT.ID/MIN.AGE::MIN.AGE/MIN.AGE.BRK::MIN.AGE.BRK/MAX.AGE::MAX.AGE/MAX.AGE.BRK::MAX.AGE.BRK/GENDER::GENDER/GENDER.BRK...

I’m guessing each line in eligibilitycheck.txt is a single record, with fields split by /, and each field follows a KEY::VALUE pattern (your example uses placeholders, but real data would have actual values like @ID::1001). The first thing to fix is making sure we parse each line correctly without breaking on edge cases.

These are the most likely culprits:

• Malformed line endings: Your file might use non-standard newlines (e.g., Windows \r\n vs. Linux \n) or be saved as a single giant line, so your code only reads one record.
• Unescaped special characters: If a value has single quotes, commas, or other SQL-reserved characters, it’ll break the insert statement and stop execution if you don’t handle errors.
• Brittle parsing logic: If some lines have missing fields or extra :: in values, your code might crash mid-process instead of skipping bad rows.
• Transaction misconfiguration: If you’re using a transaction that rolls back on any error, one bad row could wipe out all prior inserts (except the first if you committed early).

Let’s use Python + SQLite (adjust the DB driver for MySQL/PostgreSQL as needed) to parse and insert safely. This code handles errors, skips bad lines, and uses parameterized queries to avoid SQL injection and special character issues.

First, assume your table eligibility has columns mapped from your text fields (e.g., @ID → id, EM.PRDL.ELIGIBILITY.ID → eligibility_id, etc.):

import sqlite3

# Connect to your database
conn = sqlite3.connect('your_database.db')
cursor = conn.cursor()

def parse_record(line):
    """Turn a single text line into a dictionary of column-value pairs"""
    line = line.strip()
    if not line:
        return None
    
    field_pairs = line.split('/')
    record = {}
    for pair in field_pairs:
        # Split only on the first :: to handle values that might contain ::
        if '::' not in pair:
            print(f"Skipping invalid field: {pair}")
            continue
        key, value = pair.split('::', 1)
        # Clean key to match database column names (e.g., EM.PRDL.ELIGIBILITY.ID → eligibility_id)
        cleaned_key = key.lower().replace('.', '_').replace('@', '')
        record[cleaned_key] = value
    return record

# Process the text file
with open('eligibilitycheck.txt', 'r') as file:
    for line_num, line in enumerate(file, 1):
        try:
            record = parse_record(line)
            if not record:
                print(f"Skipping empty/invalid line {line_num}")
                continue
            
            # Use parameterized query to avoid SQL errors from special characters
            columns = ', '.join(record.keys())
            placeholders = ', '.join(['?' for _ in record.values()])
            insert_query = f"INSERT INTO eligibility ({columns}) VALUES ({placeholders})"
            
            cursor.execute(insert_query, tuple(record.values()))
            conn.commit()
            print(f"Successfully inserted line {line_num}")
        
        except Exception as e:
            conn.rollback()
            print(f"Failed to insert line {line_num}: {str(e)}")
            # Log bad lines for debugging
            with open('failed_records.log', 'a') as log:
                log.write(f"Line {line_num}: {line}\nError: {str(e)}\n\n")

conn.close()

Key Fixes in This Code: #

• Parameterized queries: Uses ? placeholders instead of string concatenation, so special characters like ' don’t break SQL syntax.
• Error handling: Wraps each insert in a try-except block, so one bad row won’t stop the entire process.
• Robust parsing: Splits only on the first :: to handle values that might contain the sequence, and skips invalid fields.
• Line-by-line processing: Reads the file line by line, even if line endings are wonky.

• Check line endings: Use a text editor like Notepad++ to view your file’s line endings (look for "Show All Characters" in the view menu).
• Validate parsed records: Add a print(record) after parsing to confirm each line maps to the correct columns.
• Check database logs: If using MySQL/PostgreSQL, check the server logs for specific SQL error messages—this will tell you exactly why a row failed.
• Test with a small sample: Copy 3-5 lines into a test file and run the code on that first to isolate issues.

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