Python sample script to import not-normalised CSV data into MySQL tables, resolving FK values in memory

launch.sh

# create mysql_latest container
    docker run --name mysql57_abc \
        -e MYSQL_ROOT_PASSWORD=pass \
        -e MYSQL_DATABASE=abc \
        -e MYSQL_USER=abc \
        -e MYSQL_PASSWORD=abc \
        -p 3306:3306 \
       -d mysql:5.7 
    # launch if already created
    docker start mysql57_abc

    # create schema
    docker exec -i mysql57 mysql -uabc -pabc abc < scripts/schema.sql

    # install lib if not presemt
    pip install mysql-connector
    
    # import CSV (python script tested with v2.7)
    ./import.py

mysql_import_from_not_normalised_csv.py

#!/usr/local/bin/python
import csv
import mysql.connector
import sys

def csvLinesSkipFirst(csvPath):
    csv_data = csv.reader(file(csvPath))
    next(csv_data)
    return csv_data

db = mysql.connector.connect(
    host='***',
    user='***',
    passwd='***',
    db='***'
)
cursor = db.cursor()

# conditional truncate
if '--truncate' in sys.argv:
    truncate = True
else:
    truncate = 'y' == raw_input("Truncate tables before inserting ? [y/n]").lower()

if truncate:
    cursor.execute("SET FOREIGN_KEY_CHECKS = 0; ")
    cursor.execute("truncate table orders; ")
    cursor.execute("truncate table events; ")
    cursor.execute("truncate table customers; ")
    cursor.execute("truncate table offers; ")
    cursor.execute("SET FOREIGN_KEY_CHECKS = 1;")
    db.commit()
    print('Tables truncated')

# Customers
customers_name_to_id = {}
for row in csvLinesSkipFirst('data/customers.csv'):
    cursor.execute('INSERT INTO customers (name, credit_balance) VALUES(%s, %s)', row)
    customers_name_to_id[row[0].strip().lower()] = cursor.lastrowid

# Offers
offer_name_to_id = {}
for row in csvLinesSkipFirst('data/offers.csv'):
    cursor.execute('INSERT INTO offers (name, price) VALUES(%s, %s)', row)
    offer_name_to_id[row[0].strip().lower()] = cursor.lastrowid

# events
events_name_to_id = {}
for row in csvLinesSkipFirst('data/orders.csv'):
    current_event_name = row[4]
    if current_event_name.strip().lower() not in events_name_to_id:
        cursor.execute('INSERT INTO events (event_name) VALUES(%s)', [current_event_name])
        events_name_to_id[current_event_name.strip().lower()] = cursor.lastrowid

print("Customers: " + str(customers_name_to_id))
print("Offers: " + str(offer_name_to_id))
print("Events: " + str(events_name_to_id))

# orders
for row in csvLinesSkipFirst('data/orders.csv'):
    try:
        customer_id = int(customers_name_to_id[row[0].strip().lower()])
        offer_id = int(offer_name_to_id[row[2].strip().lower()])
        event_id = int(events_name_to_id[row[4].strip().lower()])
        insertRow = (customer_id, offer_id, int(row[3]), event_id)
        print (insertRow)
        cursor.execute(
            "INSERT INTO orders "
            "(customer_id, offer_id, quantity, event_id) "
            "VALUES"
            "(%s, %s, %s, %s)",
            insertRow
        )
        db.commit()
        # sys.exit()
    except KeyError as e:
        print "Order add skipped. missing key. " + repr(e)
    except Exception as e:
        print "Order add skipped. Details:" + repr(e)
        sys.exit()


db.commit()
cursor.close()

print("Done")

schema.sql

DROP TABLE IF EXISTS `customers`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `customers` (
  `id` int(10)  NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `credit_balance` decimal(8,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `events`
--

DROP TABLE IF EXISTS `events`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `events` (
  `id` int(10)  NOT NULL AUTO_INCREMENT,
  `event_name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `offers`
--

DROP TABLE IF EXISTS `offers`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `offers` (
  `id` int(10)  NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `price` decimal(8,2)  DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `orders`
--

DROP TABLE IF EXISTS `orders`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `orders` (
  `id` int(10)  NOT NULL AUTO_INCREMENT,
  `customer_id` int(10) NOT NULL,
  `offer_id` int(10) NOT NULL,
  `quantity` int(10) DEFAULT NULL,
  `event_id` int(10) NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `customer_id_idx` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `event_id_idx` FOREIGN KEY (`event_id`) REFERENCES `events` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `offer_id_idx` FOREIGN KEY (`offer_id`) REFERENCES `offers` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_c