import csv
import pymysql

# you may have to install pymysql. The command is 'pip install pymysql'

# This script will load a file (sample.csv) into the database
# put the filename and path you want to load

load_path = 'C:/code/samples/ICES_biota.csv'
load_file = 'ICES_biota.csv'

connection = pymysql.connect(host='tesla.epa.gov',
                      port=3306,
                      user='your_username',
                      password='your_password',
                      db='prod_samples',
                      use_unicode=True,
                      charset="utf8")
cursor = connection.cursor()


# insert the file into the sources table
sql = "INSERT INTO prod_samples.source (filename) VALUES ('%s')" % load_file
cursor.execute(sql)
connection.commit()

# grab the id of the source just inserted to add to the sample table
sql = "SELECT id FROM source WHERE filename = '%s'" % load_file
cursor.execute(sql)
fk_source_id = cursor.fetchone()

# open up the csv - utf-8 encoding is super important here
reader = csv.DictReader(open(load_file, encoding='utf-8'))

# get the column names
column_names = reader.fieldnames

# read the rows and columns and insert the info into the database
for row in reader:
   for column in column_names:
      # only load the sample column if there is a value in it :)
      if row[column] != '':
         sql = "INSERT INTO sample (variable_name, variable_value, fk_source) VALUES ('%s', '%s', %i)" % \
              (column, row[column], fk_source_id[0])
         cursor.execute(sql)
         connection.commit()

connection.close()
print('done!')