gistfile1.txt

#!/usr/bin/python

import sys
import csv
import MySQLdb

ftp_user = "fred"
ftp_pass = "secret2"
ftp_server = "ftp.domain.com"
delivery_name = "Test Delivery"

def get_id(db, cursor, table, col, row):
    cursor.execute("SELECT `id` FROM %s WHERE %s = '%s' LIMIT 1;" % (table, col, row))
    id = cursor.fetchone()

    if not id:
        cursor.execute("INSERT INTO %s (%s) VALUES (\"%s\");" % (table, col, row))
        db.commit()
        return cursor.lastrowid
    return id[0]


db = MySQLdb.connect(db="sat2_ingestion", user="root", passwd="asdfasdf", host="localhost")

c = db.cursor()

reader = csv.DictReader(sys.stdin)
for e in reader:
    country_id = get_id(db, c, "countries", "country_name", e["COUNTRY_1"])
    continent_id = get_id(db, c, "continents", "continent_name", e["CONTINEN_1"])
    landtype_id = get_id(db, c, "landtypes", "landtype_name", e["LAND_TYPE_"])
    platform_id = get_id(db, c, "platforms", "platform_name", e["PLATFORM"])

    stmt = """
    INSERT INTO cells (
        cell_name,
        country_id,
        continent_id,
        platform_id,
        landtype_id
    ) VALUES (
        "%s",
        "%d",
        "%d",
        "%d",
        "%d"
    ) ON DUPLICATE KEY UPDATE id = id;
    """ % (e["CELL_ID"], country_id, continent_id, platform_id, landtype_id)
    c.execute(stmt)
    c.execute("COMMIT;")

    stmt = """
    INSERT INTO accounts (
        user_name,
        password,
        server
    ) VALUES (
        "%s",
        "%s",
        "%s"
    ) ON DUPLICATE KEY UPDATE id = id;
    """ % (ftp_user, ftp_pass, ftp_server)
    c.execute(stmt)
    c.execute("COMMIT;")

    stmt = """
    INSERT INTO deliveries (
        delivery_name,
        account_id
    ) VALUES (
        "%s",
        (SELECT `id` FROM accounts WHERE user_name = "%s" AND server = "%s")
    ) ON DUPLICATE KEY UPDATE id = id;
    """ % (delivery_name, ftp_user, ftp_server)
    c.execute(stmt)
    c.execute("COMMIT;")

    stmt = """
    INSERT INTO tarfiles (
        filename,
        filesize,
        cell_id,
        delivery_id
    ) VALUES (
        "%s",
        "%s",
        (SELECT id FROM cells WHERE cell_name = "%s"),
        (SELECT id FROM deliveries WHERE delivery_name = "%s")
    );
    """ % (e["TAR_FILE"], e["TAR_SIZE"], e["CELL_ID"], delivery_name)
    c.execute(stmt)
    c.execute("COMMIT;")