使用Python的MySQLdb加载CSV至MySQL数据库无数据问题排查

Hey there! Let's figure out why your CSV data isn't showing up even though your table gets deleted and recreated successfully—this is a super common gotcha, so let's walk through the most probable reasons:

• You forgot to commit the transaction
  If your database connection uses transactions (either explicitly with BEGIN or because your connection has autocommit disabled), the loaded data won't be visible until you run a COMMIT command. For example, if you're using Python's mysql-connector library, make sure you call conn.commit() right after executing the data load statement. Without this, the data is only in a pending transaction and won't show up in queries.

• MySQL can't access your CSV file
  No error doesn't mean the file was found. Double-check these details:

  • If loading a local CSV, use LOAD DATA LOCAL INFILE instead of LOAD DATA INFILE (and ensure your MySQL server allows local file loads via the local_infile=1 configuration).
  • Use an absolute file path (like /Users/you/Documents/data.csv)—relative paths might not work because MySQL's working directory is different from your code's.
  • Verify the MySQL process has read permissions for the CSV file and its parent directory.

• CSV columns don't match the table structure
  Mismatched field count, order, or formatting can cause data to be silently ignored or inserted incorrectly. Make sure:

  • The number of columns in your CSV matches the number of fields in the table.
  • You specify the correct delimiter (e.g., FIELDS TERMINATED BY ',') and quote character (e.g., ENCLOSED BY '"') in your LOAD DATA statement to handle formatted CSV data properly.

• You didn't skip the CSV header row
  If your CSV starts with a header row (column names), those will be treated as data unless you tell MySQL to skip them. Add IGNORE 1 ROWS to your LOAD DATA statement to skip the first line.

• Data type mismatches are causing silent failures
  If your table has, say, an INT field but the CSV has text in that column, MySQL might skip those rows entirely (especially if your sql_mode isn't set to strict mode). After running your load command, run SHOW WARNINGS; to see if any rows were rejected. You can also enable strict mode with SET sql_mode='STRICT_TRANS_TABLES'; to force MySQL to throw an error instead of silently skipping bad data.

Here's an example of a properly formatted LOAD DATA statement that covers most of these bases:

LOAD DATA LOCAL INFILE '/absolute/path/to/your/data.csv'
INTO TABLE your_target_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Don't forget to commit the transaction after running this if your connection requires it!

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