PHP环境下如何读取UTF-8文本文件逐行存入MySQL数据库

Let’s walk through this step by step using Python—it’s a straightforward choice for this kind of task thanks to its simple file handling and database integration tools.

Step 1: Read the UTF-8 Text File into an Array #

First, we’ll pull each line from your file, clean up extra whitespace (like newlines), and store the values in a list. We explicitly use utf-8 encoding to avoid garbled text from special characters.

# Read the UTF-8 file into a list of field values
field_values = []
try:
    with open('your_data_file.txt', 'r', encoding='utf-8') as file:
        # Strip whitespace and skip empty lines (optional but recommended)
        field_values = [line.strip() for line in file if line.strip()]
except FileNotFoundError:
    print("Error: The specified file was not found.")
except UnicodeDecodeError:
    print("Error: Could not decode the file as UTF-8. Double-check the file’s encoding.")

Step 2: Connect to MySQL Database #

We’ll use the mysql-connector-python library (install it first with pip install mysql-connector-python). Replace the placeholders below with your actual database credentials.

import mysql.connector
from mysql.connector import Error

# Database connection details
db_config = {
    'host': 'your_host',
    'database': 'your_database',
    'user': 'your_username',
    'password': 'your_password'
}

connection = None
try:
    # Establish connection
    connection = mysql.connector.connect(**db_config)
    if connection.is_connected():
        print("Successfully connected to MySQL database")
except Error as e:
    print(f"Error connecting to MySQL: {e}")

Step 3: Insert Data into MySQL #

Always use parameterized queries here—this prevents SQL injection attacks and avoids formatting issues with special characters. We’ll cover two common scenarios:

Scenario 1: Insert one row (each line = a field value) #

If your file lines map to columns in order (e.g., line 1 = column1, line2 = column2), adjust the query to match your table’s columns:

if connection and field_values:
    try:
        cursor = connection.cursor()
        # Replace with your table name and column names
        insert_query = """INSERT INTO your_table (column1, column2, column3) 
                          VALUES (%s, %s, %s)"""
        # Ensure the list length matches the number of %s placeholders
        cursor.execute(insert_query, tuple(field_values))
        connection.commit()
        print(f"Successfully inserted {cursor.rowcount} row into the database")
    except Error as e:
        print(f"Error inserting data: {e}")
        connection.rollback()
    finally:
        if cursor:
            cursor.close()

Scenario 2: Insert multiple rows (each line = a single field value) #

If each line is a value for a single column (e.g., each line is a new row in a table with one column):

if connection and field_values:
    try:
        cursor = connection.cursor()
        # Replace with your table name and column name
        insert_query = """INSERT INTO your_table (column_name) VALUES (%s)"""
        # Use executemany for bulk inserts (more efficient than individual queries)
        cursor.executemany(insert_query, [(value,) for value in field_values])
        connection.commit()
        print(f"Successfully inserted {cursor.rowcount} rows into the database")
    except Error as e:
        print(f"Error inserting data: {e}")
        connection.rollback()
    finally:
        if cursor:
            cursor.close()

Step 4: Clean Up the Connection #

Don’t forget to close the database connection when you’re done to free up resources:

finally:
    if connection and connection.is_connected():
        connection.close()
        print("MySQL connection closed")

Key Tips #

• Encoding: Always specify encoding='utf-8' when opening the file to handle non-ASCII characters correctly.
• Empty Lines: The if line.strip() check skips blank lines—remove it if you need to include empty field values.
• Error Handling: The try-except blocks catch common issues like missing files, connection failures, or insertion errors.

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