求助：使用Python将多CSV文件指定公共列导入MySQL

Hey there! Let's work through this CSV import challenge together. With 32 files that have unique fields but share 8 common columns, we can focus on extracting those consistent columns and loading them into MySQL smoothly—here are two reliable approaches:

First, you'll need to create a target table that matches your 8 common columns. Replace the column names and data types with your actual schema:

CREATE TABLE common_data (
    id INT PRIMARY KEY,
    full_name VARCHAR(255),
    email VARCHAR(255) UNIQUE,
    created_date DATE,
    last_updated DATETIME,
    account_status VARCHAR(50),
    user_role VARCHAR(50),
    country_code CHAR(2)
    -- Adjust these to your exact 8 columns
);

Since each CSV has different extra fields, you'll explicitly map the common columns and skip the rest using dummy variables (@dummy). For example, if a CSV has columns id, random_col1, full_name, random_col2, email, created_date, last_updated, random_col3, account_status, user_role, country_code, your load command would look like this:

LOAD DATA INFILE '/path/to/your/file.csv'
INTO TABLE common_data
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' -- Use this if your CSV values are quoted
LINES TERMINATED BY '\n'
IGNORE 1 ROWS -- Add this if your CSV has a header row
(id, @dummy1, full_name, @dummy2, email, created_date, last_updated, @dummy3, account_status, user_role, country_code);

To automate this for all 32 files, you can write a simple shell script to generate all the necessary SQL commands:

# Assume all CSVs are stored in /home/you/csv_files
for csv_file in /home/you/csv_files/*.csv; do
    # You'll need to adjust the column mapping inside the echo statement for each file
    # Alternatively, if you have a list of column positions for each file, you can script that too
    echo "LOAD DATA INFILE '$csv_file' INTO TABLE common_data FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 ROWS (id, @dummy, full_name, @dummy2, email, created_date, last_updated, account_status, @dummy3, user_role, country_code);" >> bulk_import.sql
done

Then run the generated SQL file with:

mysql -u your_mysql_user -p your_database_name < bulk_import.sql

This method is ideal if you don't want to manually map columns for each CSV—it uses pandas to automatically pull the common columns by name, regardless of their position in the file.

First, install the required packages:

pip install pandas mysql-connector-python

Then use this script (update the config values to match your setup):

import pandas as pd
import mysql.connector
from mysql.connector import Error
import os

# Define your 8 common column names exactly as they appear in the CSV headers
COMMON_COLS = ["id", "full_name", "email", "created_date", "last_updated", "account_status", "user_role", "country_code"]

# MySQL connection settings
DB_SETTINGS = {
    "host": "localhost",
    "database": "your_database_name",
    "user": "your_mysql_user",
    "password": "your_mysql_password"
}

def load_to_mysql(df):
    try:
        conn = mysql.connector.connect(**DB_SETTINGS)
        if conn.is_connected():
            cursor = conn.cursor()
            # Build the insert query
            col_str = ", ".join(COMMON_COLS)
            placeholder_str = ", ".join(["%s"] * len(COMMON_COLS))
            insert_query = f"INSERT INTO common_data ({col_str}) VALUES ({placeholder_str})"
            
            # Convert DataFrame rows to tuples
            data_rows = [tuple(row) for row in df[COMMON_COLS].values]
            cursor.executemany(insert_query, data_rows)
            conn.commit()
            print(f"Successfully inserted {cursor.rowcount} rows from the current CSV")
    except Error as e:
        print(f"Database error occurred: {e}")
    finally:
        if conn.is_connected():
            cursor.close()
            conn.close()

# Process all CSV files in the target directory
csv_directory = "/home/you/csv_files"
for filename in os.listdir(csv_directory):
    if filename.endswith(".csv"):
        file_path = os.path.join(csv_directory, filename)
        print(f"Processing file: {filename}")
        # Read only the common columns from the CSV
        df = pd.read_csv(file_path, usecols=COMMON_COLS)
        # Clean up any missing values (adjust based on your data needs)
        df = df.fillna("")  # Replace empty strings with appropriate values for numeric columns if needed
        # Load cleaned data to MySQL
        load_to_mysql(df)

• Enable Local Infile: If using LOAD DATA INFILE, make sure MySQL has local_infile=1 enabled (add this to your my.cnf file or connect with mysql --local-infile=1).
• Data Type Matching: Ensure your target table's column types match the data in your CSVs (e.g., date columns should use MySQL's DATE/DATETIME types, and your CSV dates should be in a compatible format like YYYY-MM-DD).
• Permissions: Your MySQL user needs FILE permission (for LOAD DATA INFILE) and INSERT permission on the target table.
• Test First: Always test with one CSV file before running bulk operations to catch any schema or data issues early.

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