How to Import UTF-8 Encoded Data into a MySQL Table Using Python\'s LOAD DATA INFILE Command?

Importing UTF-8 Encoded Data into MySQL Table

When importing large CSV files with non-English characters into MySQL tables, it's essential to handle the UTF-8 encoding properly to prevent character corruption.

To load UTF-8 encoded data into a MySQL table using Python's LOAD DATA LOCAL INFILE command, follow these steps:

1. Set Character Set: Ensure that the target column's character set is explicitly set to UTF8 using the CHARACTER SET clause in your table creation or alteration query.
2. Specify UTF-8 Encoding: Include the CHARACTER SET UTF8 option in the LOAD DATA INFILE command to explicitly indicate the UTF-8 encoding of the data being imported.
3. Set Encoding Function (Optional): Depending on your data's specific encoding, you may need to specify an appropriate encoding function using the SET NAMES or SET CHARACTER SET statements before executing the LOAD DATA INFILE command.
4. Configure Field and Line Terminators (Optional): Define the field and line terminators used in your CSV file using the FIELDS TERMINATED BY and LINES TERMINATED BY options respectively.

For example, the following Python code demonstrates loading a CSV file with UTF-8 encoded data into a MySQL table:

<code class="python">import mysql.connector

conn = mysql.connector.connect(
    user='username',
    password='password',
    host='localhost',
    database='database'
)

cursor = conn.cursor()

# Create a table with a UTF-8 character set
query = """
CREATE TABLE IF NOT EXISTS utf8_table (
  id INT AUTO_INCREMENT PRIMARY KEY,
  data VARCHAR(255) CHARACTER SET utf8
);
"""
cursor.execute(query)
conn.commit()

# Load the UTF-8 encoded CSV file
query = """
LOAD DATA INFILE 'utf8_data.csv'
IGNORE INTO TABLE utf8_table
CHARACTER SET UTF8
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
"""
cursor.execute(query)
conn.commit()

cursor.close()
conn.close()</code>

By following these steps, you can successfully import UTF-8 encoded data into your MySQL table while preserving the non-English characters accurately.

The above is the detailed content of How to Import UTF-8 Encoded Data into a MySQL Table Using Python\'s LOAD DATA INFILE Command?. For more information, please follow other related articles on the PHP Chinese website!