load-to-mysql

name	load-to-mysql
description	Use when you need to load a taxonomy into MySQL or MariaDB — create table with charset/collation, load data, support upsert/replace/append modes.

Load Taxonomy to MySQL

Load a taxonomy into MySQL or MariaDB with proper charset/collation, DDL generation, and load modes.

When to use

• "Load this into my MySQL database"
• "I want the taxonomy in MySQL with utf8mb4"
• "Upsert the data into the MySQL table"

Inputs to gather

• Connection: DSN (mysql+pymysql://user:pass@host:port/dbname) or env vars (MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD, MYSQL_DATABASE, MYSQL_PORT).
• Table name: What to call the table.
• Taxonomy file path: Absolute path to CSV or auto-discover from data/<name>/<name>.csv.
• Load mode: replace, upsert, append.
• Charset/collation: Default utf8mb4 / utf8mb4_unicode_ci; allow override.

Procedure

1. Generate DDL: Infer column types:
   • code → VARCHAR(32) or VARCHAR(64), PRIMARY KEY.
   • label / name → VARCHAR(255) NOT NULL.
   • Numeric → INT, BIGINT, or DECIMAL as appropriate.
   • metadata → JSON (MySQL 5.7+).
   • parent_code → VARCHAR(32) with FOREIGN KEY constraint.
   • Timestamps → TIMESTAMP or DATETIME.
   • End all VARCHAR columns with CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci.
   • Full table definition: CREATE TABLE <table> (…) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2. Show user the DDL for approval.
3. Ensure local_infile=1: Check SHOW VARIABLES LIKE 'local_infile'. If off, require user to enable it (session or config); note this is a security surface.
4. Handle load mode:
   • replace: TRUNCATE TABLE <table>; LOAD DATA LOCAL INFILE '<csv_path>' INTO TABLE <table> FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;
   • upsert: Use LOAD DATA … ON DUPLICATE KEY UPDATE col1=VALUES(col1), col2=VALUES(col2), …; (MySQL 8.0.19+) or row-by-row INSERT … ON DUPLICATE KEY UPDATE via Python.
   • append: Direct LOAD DATA LOCAL INFILE without TRUNCATE.
5. For hierarchical: Disable FK checks with SET FOREIGN_KEY_CHECKS=0; before load, re-enable after (to handle parent-before-child ordering).
6. Log the operation: Write to state/loads/<timestamp>-<database>.<table>.log with timestamp, mode, row count, status.

Output / side effects

• MySQL table created/updated with data loaded.
• Load logged to state/loads/<timestamp>-<database>.<table>.log.

Safety / constraints

• local_infile: Must be enabled; clarify this with user (security implication).
• No credentials in logs: Use env vars; never write passwords to log files.
• Charset: Strongly recommend utf8mb4 for user-facing labels; SQL will specify charset on all VARCHAR columns.
• Foreign key order: Hierarchical loads require FK check disable or topological ordering.