content: import sys
  import os
  import datetime
  import pickle
  import numpy as np
  import matplotlib.pyplot as plt
  from pprint import pprint
  import sqlite3
  import calendar
  from datetime import datetime
  
  #id INTEGER PRIMARY KEY AUTOINCREMENT,
  #sp2b_file TEXT, 
  #file_index INT, 
  #instr TEXT,
  #instr_locn TEXT,
  #particle_type TEXT,		
  #particle_dia FLOAT,				
  #unix_ts_utc FLOAT,
  #actual_scat_amp FLOAT,
  #actual_peak_pos INT,
  #FF_scat_amp FLOAT,
  #FF_peak_pos INT,
  #FF_gauss_width FLOAT,
  #zeroX_to_peak FLOAT,
  #LF_scat_amp FLOAT,
  #incand_amp FLOAT,
  #lag_time_fit_to_incand FLOAT,
  #LF_baseline_pct_diff FLOAT,
  #rBC_mass_fg FLOAT,
  #coat_thickness_nm FLOAT,
  #coat_thickness_from_actual_scat_amp FLOAT
  #UNIQUE (sp2b_file, file_index, instr)
  
  
  #connect to database
  conn = sqlite3.connect('C:/projects/dbs/SP2_data.db')
  c = conn.cursor()
  c2 = conn.cursor()
  
  instrument = 'UBCSP2'
  instrument_locn = 'WHI'
  type_particle = 'incand'
  start_date = '20110105'
  end_date = '20120601'
  lookup_file = 'C:/Users/Sarah Hanna/Documents/Data/WHI long term record/coatings/lookup_tables/coating_lookup_table_WHI_2012_UBCSP2-nc(2p26,1p26).lupckl'
  rBC_density = 1.8 
  incand_sat = 3750
  
  lookup = open(lookup_file, 'r')
  lookup_table = pickle.load(lookup)
  lookup.close()
  
  c.execute('''SELECT * FROM SP2_coating_analysis''')
  names = [description[0] for description in c.description]
  pprint(names)
  
  
  begin_data = calendar.timegm(datetime.strptime(start_date,'%Y%m%d').timetuple())
  end_data = calendar.timegm(datetime.strptime(end_date,'%Y%m%d').timetuple())
  
  
  def get_rBC_mass(incand_pk_ht, year):
  	if year == 2012:
  		rBC_mass = 0.003043*incand_pk_ht + 0.24826 #AD corrected linear calibration for UBCSP2 at WHI 2012
  	if year == 2010:
  		rBC_mass = 0.01081*incand_pk_ht - 0.32619  #AD corrected linear calibration for ECSP2 at WHI 2010
  	return rBC_mass
  
  def get_coating_thickness(BC_VED,scat_amp,coating_lookup_table):
  	#get the coating thicknesses from the lookup table which is a dictionary of dictionaries, the 1st keyed with BC core size and the second being coating thicknesses keyed with calc scat amps                  
  	core_diameters = sorted(coating_lookup_table.keys())
  	prev_diameter = core_diameters[0]
  
  	for core_diameter in core_diameters:
  		if core_diameter > BC_VED:
  			core_dia_to_use = prev_diameter
  			break
  		prev_diameter = core_diameter
  
  	#now get the coating thickness for the scat_amp this is the coating thickness based on the raw scattering max
  	scattering_amps = sorted(coating_lookup_table[core_dia_to_use].keys())
  	prev_amp = scattering_amps[0]
  	for scattering_amp in scattering_amps:
  		if scat_amp < scattering_amp:
  			scat_amp_to_use = prev_amp
  			break
  		prev_amp = scattering_amp
  
  	scat_coating_thickness = coating_lookup_table[core_dia_to_use].get(scat_amp_to_use, np.nan) # returns value for the key, or none
  	return scat_coating_thickness
  
  LOG_EVERY_N = 10000
  
  i = 0
  for row in c.execute('''SELECT incand_amp, LF_scat_amp, unix_ts_utc, sp2b_file, file_index, instr FROM SP2_coating_analysis 
  WHERE instr=? and instr_locn=? and particle_type=? and incand_amp<? and unix_ts_utc>=? and unix_ts_utc<?''', 
  (instrument,instrument_locn,type_particle,incand_sat,begin_data,end_data)):
  	incand_amp = row[0]
  	LF_amp = row[1]
  	event_time = datetime.utcfromtimestamp(row[2])
  	file = row[3]
  	index = row[4]
  	instrt = row[5]
  	
  	rBC_mass = get_rBC_mass(incand_amp, event_time.year)
  	if rBC_mass >= 0.25:
  		rBC_VED = (((rBC_mass/(10**15*rBC_density))*6/3.14159)**(1/3.0))*10**7 #VED in nm with 10^15fg/g and 10^7nm/cm
  		coat_th = get_coating_thickness(rBC_VED,LF_amp,lookup_table)
  	else:
  		rBC_VED = None
  		coat_th = None
  	
  	
  	c2.execute('''UPDATE SP2_coating_analysis SET coat_thickness_from_actual_scat_amp=? WHERE sp2b_file=? and file_index=? and instr=?''', (coat_th, file,index,instrt))
  	
  	i+=1
  	if (i % LOG_EVERY_N) == 0:
  		print 'record: ', i
  	
  conn.commit()
  conn.close()
  
  
  
  
  