def test_conn(): c = form_connection.micro_conn("STEWARTIA") assert(type(c) != types.StringType)
def gather_methods_from_table(self): import form_connection as fc conn = fc.micro_conn("SHELDON") old_conn = fc.micro_conn("STEWARTIA") query_d = { "AIRTEMP": "MS04301", "RELHUM": "MS04302", "PRECIP": "MS04303", "WSPD_PRO": "MS04304", "SOLAR": "MS04305", "DEWPT": "MS04307", "VPD": "MS04308", "LYS": "MS04309", "NR": "MS04325", "WSPD_SNC": "MS04324", "SOILTEMP": "MS04321", "SOILWC": "MS04323", "PAR": "MS04322", } query_m = { "AIRTEMP": "MS00101", "RELHUM": "MS00102", "PRECIP": "MS00103", "WSPD_PRO": "MS00104", "SOLAR": "MS00105", "DEWPT": "MS00107", "VPD": "MS00108", "LYS": "MS00109", "NR": "MS00125", "WSPD_SNC": "MS00124", "SOILTEMP": "MS00121", "SOILWC": "MS00123", "PAR": "MS00122", } cursor = conn.cursor() old_cursor = old_conn.cursor() # collect the distinct probes from your server if self.server == "STEWARTIA": ## COMMENT THIS LINE IN FOR MS001 # query = "select distinct probe_code from fsdbdata.dbo." + query_m[self.attribute] ## COMMENT THIS LINE IN FOR MS043 query = "select distinct probe_code from fsdbdata.dbo." + query_d[self.attribute] old_cursor.execute(query) distinct_probes = [] for row in old_cursor: distinct_probes.append(str(row[0])) query_string = query_string = "', '".join(distinct_probes) elif self.server == "SHELDON": ## COMMENT THIS LINE IN FOR LTERLogger_new # query = "select distinct probe_code from lterlogger_new.dbo" + query_d[self.attribute] ## COMMENT THIS LINE IN FOR LTERLogger_pro query = "select distinct probe_code from lterlogger_pro.dbo" + query_d[self.attribute] cursor.execute(query) # append distinct probes to a list distinct_probes = [] for row in cursor: distinct_probes.append(str(row[0])) query_string = query_string = "', '".join(distinct_probes) else: pass # get the limited info from the lookup table query = ( "select probe_code, date_bgn, date_end, method_code, height, depth from LTERLogger_new.dbo.method_history_daily where probe_code in ('" + query_string + "') and date_end >= '" + self.startdate + "' order by date_bgn asc" ) cursor.execute(query) shortened_lookup = {} # gather the methods by populating a dictionary called "shortened lookup" for row in cursor: probe_code = str(row[0]) date_bgn = str(row[1]) date_end = str(row[2]) method_code = str(row[3]) height = int(row[4]) depth = str(row[5]) if probe_code not in shortened_lookup: shortened_lookup[probe_code] = { method_code: {"begin": date_bgn, "end": date_end, "height": height, "depth": depth} } elif probe_code in shortened_lookup: if method_code not in shortened_lookup[probe_code]: shortened_lookup[probe_code][method_code] = { "begin": date_bgn, "end": date_end, "height": height, "depth": depth, } elif method_code in shortened_lookup[probe_code]: print "the method code %s is already collected for %s between the dates of %s and %s" % ( method_code, probe_code, date_bgn, date_end, ) pass # iterate over the distinct probes in our set for each_probe in distinct_probes: # and over each of the methods for that probes for each_method in shortened_lookup[each_probe].keys(): # get that probe method start method_startdate = shortened_lookup[each_probe][each_method]["begin"] print method_startdate # get that method's end method_enddate = shortened_lookup[each_probe][each_method]["end"] # new query to update the lter logger pro new_query = ( "update LTERLogger_Pro.dbo." + query_d[self.attribute] + " set " + self.attribute + "_METHOD = '" + str(each_method) + "' where probe_code like '" + each_probe + "' and Date >= '" + method_startdate + "' and Date < '" + method_enddate + "'" ) print new_query cursor.execute(new_query) conn.commit() return shortened_lookup() else: print "nothing to commit!" return shortened_lookup
def update_the_db_methods(self): """ updates the daily db based on the methods in method_history_daily""" print ("Updating your heights, depths, and methods prior to insertion in the db!") shortened_lookup = {} # form a new connection (we need this because we need the conn object to commit) import form_connection as fc conn = fc.micro_conn("SHELDON") # get all the probe_codes we have and dates we have probe_codes_ordered = [x[6] for x in self.new_rows] dates_ordered = [x[7] for x in self.new_rows] # get the unique entries of probe codes probe_string = self.f5(probe_codes_ordered) # make it into a string that the query can injest query_string = "', '".join(probe_string) # get the limited info from the lookup table -- we only need to look up the relevant probes and dates query = ( "select probe_code, date_bgn, date_end, method_code, height, depth from LTERLogger_new.dbo.method_history_daily where probe_code in ('" + query_string + "') and date_end >= '" + datetime.datetime.strftime(self.Worker.daterange.dr[0], "%Y-%m-%d %H:%M:%S") + "' order by date_bgn asc" ) cur = conn.cursor() cur.execute(query) # gather the methods by populating a dictionary called "shortened lookup" for row in cur: probe_code = str(row[0]) date_bgn = str(row[1]) date_end = str(row[2]) method_code = str(row[3]) height = int(row[4]) depth = str(row[5]) if probe_code not in shortened_lookup: shortened_lookup[probe_code] = { method_code: {"begin": date_bgn, "end": date_end, "height": height, "depth": depth} } elif probe_code in shortened_lookup: if method_code not in shortened_lookup[probe_code]: shortened_lookup[probe_code][method_code] = { "begin": date_bgn, "end": date_end, "height": height, "depth": depth, } elif method_code in shortened_lookup[probe_code]: print "the method code %s is already collected for %s between the dates of %s and %s" % ( method_code, probe_code, date_bgn, date_end, ) pass # iterate over the rows you plan to insert for row in self.new_rows: # extract the date as a dt obj thisdate = datetime.datetime.strptime(row[7], "%Y-%m-%d %H:%M:%S") # check the length of the rows in the lookup table that share a key with the probe you are on try: get_length_of_rows = len(shortened_lookup[row[6]].keys()) except KeyError: print "the probe %s is not listed" % (row[6]) continue # if there's only one of those, than that's the only method, so accept it as correct if get_length_of_rows == 1: correct_method = shortened_lookup[row[6]].keys()[0] height_valid = shortened_lookup[row[6]][correct_method]["height"] if row[3] == correct_method and row[4] == height_valid: continue else: print "correcting the method to %s from %s" % (correct_method, row[3]) print "correcting the height to %s from %s" % (height_valid, row[4]) row[3] = correct_method row[4] = height_valid elif get_length_of_rows > 1: correct_method = [ x for x in shortened_lookup[row[6]].keys() if thisdate >= datetime.datetime.strptime(shortened_lookup[row[6]][x]["begin"], "%Y-%m-%d %H:%M:%S") and thisdate < datetime.datetime.strptime(shortened_lookup[row[6]][x]["end"], "%Y-%m-%d %H:%M:%S") ][0] height_valid = shortened_lookup[row[6]][correct_method]["height"] if row[3] == correct_method and row[4] == height_valid: continue else: print "correcting the method to %s from %s" % (correct_method, row[3]) print "correcting the height to %s from %s" % (height_valid, row[4]) row[3] = correct_method row[4] = height_valid elif get_length_of_rows < 1: print "the needed probe is not listed: %s" % (row[6]) pass return self.new_rows
def update_the_db(self): """ Updates LTER Logger Pro-- NOT LTER LOGGER NEW! -- currently as of 04-20-3015 its empty so I can't check it for pre-existing values without an error! """ print ("This will update the LTERLogger_Pro database") # form a new connection (we need this because we need the conn object to commit) import form_connection as fc conn = fc.micro_conn("SHELDON") # keep the tuples from each object. I.e. self might be an AirTemperature, a Sonic, etc. new_tuples = [tuple(x) for x in self.new_rows] cursor = conn.cursor() # get_the_column_names cursor.execute( "select column_name from LTERLogger_Pro.information_schema.columns where table_name like '" + self.table + "' and table_schema like 'dbo'" ) # create a list from those columns, which is used to generate the sql later. For net radiation we have one strange additional column in LTERLogger_pro that is manually removed here. nr = [] for row in cursor: nr.append(str(row[0])) if self.table != "MS04325": column_string = " ,".join(nr[:-1]) elif self.table == "MS04325": column_string = " ,".join(nr) # the simplest case of data such as airtemp, relhum, dewpoint, soil temp, and soil wc if self.table in ["MS04301", "MS04302", "MS04307", "MS04321", "MS04323"]: # 'MS043',21, site_code, method_code, int(height), "1D", probe_code, datetime.datetime.strftime(each_date,'%Y-%m-%d %H:%M:%S'), mean_valid_obs, daily_flag, max_valid_obs, max_flag[0], datetime.datetime.strftime(max_valid_time[0], '%H%M'), min_valid_obs, min_flag[0], datetime.datetime.strftime(min_valid_time[0], '%H%M'), "NA", self.server cursor.executemany( "insert into LTERLogger_Pro.dbo." + self.table + " (" + column_string + ") VALUES (%s, %d, %s, %s, %d, %s, %s, %s, %d, %s, %d, %s, %s, %d, %s, %s, %s, %s)", new_tuples, ) conn.commit() # vpd is complicated, there is a long string for some older data and a short string for some newer data. elif self.table in "MS04308": print "processing vpd!" print "structure in LTERLogger_pro is: DBCODE, ENTITY, SITECODE, VPD_METHOD, HEIGHT, QC_LEVEL, PROBE_CODE, DATE, VPD_MEAN_DAY, VPD_MEAN_FLAG, VPD_MAX_DAY, VPD_MAX_FLAG, VPD_MAXTIME, VPD_MIN_DAY, VPD_MIN_FLAG, VPD_MINTIME, VAP_MEAN_DAY, VAP_MEAN_FLAG, VAP_MAX_DAY, VAP_MAX_FLAG, VAP_MIN_DAY, VAP_MIN_FLAG, EVENT_CODE, DB_TABLE" try: cursor.executemany( "insert into LTERLogger_Pro.dbo." + self.table + " (" + column_string + ") VALUES (%s,%d, %s, %s, %d, %s, %s, %s, %d, %s, %d, %s, %s, %d, %s, %s, %d, %s, %d, %s, %d, %s, %s, %s)", new_tuples, ) except Exception: print "First insertion attempt failed, trying a longer insertion for VPD et al." try: cursor.executemany( "insert into LTERLogger_Pro.dbo." + self.table + " (" + column_string + ") VALUES (%s,%d, %s, %s, %d, %s, %s, %s, %d, %s, %d, %s, %s, %d, %s, %s, %d, %s, %d, %s, %d, %s, %d, %s, %d, %s,%d, %s, %s, %s)", new_tuples, ) except Exception: print "Second insertion attempt failed, trying an even longer insertion for VPD et al." try: cursor.executemany( "insert into LTERLogger_Pro.dbo." + self.table + " (" + column_string + ") VALUES (%s, %d, %s, %s, %d, %s, %s, %s, %d, %s, %d, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", new_tuples, ) except Exception: print "Please edit the database insertion string for VPD in smashBosses. Check information schema.columns and the first row in MS04308" conn.commit() elif self.table in "MS04305": # DBCODE, ENTITY, SITECODE, SOLAR_METHOD, HEIGHT, QC_LEVEL, PROBE_CODE, DATE , SOLAR_TOT_DAY, SOLAR_TOT_FLAG, SOLAR_MEAN_DAY, SOLAR_MEAN_FLAG, SOLAR_MAX_DAY SOLAR_MAX_FLAG, SOLAR_MAXTIME, EVENT_CODE, DB_TABLE, ID for each_tuple in new_tuples: print each_tuple cursor.executemany( "insert into LTERLogger_Pro.dbo." + self.table + " (" + column_string + ") VALUES (%s, %d, %s, %s, %d, %s, %s, %s, %d, %s, %d, %s, %d, %s, %s, %s, %s)", new_tuples, ) conn.commit() elif self.table in "MS04303": # fixed to not have two outputs. # 'MS043', 3, 'PRIMET', 'PPT108', 100, '2D', 'PPTPRI01', '2015-01-01 00:00:00', 0.0, 'A', 'NA', 'SHELDON_LTERLogger_PRO_MS04313' cursor.executemany( "insert into LTERLogger_Pro.dbo." + self.table + " (" + column_string + ") VALUES( %s, %d, %s, %s, %d, %s, %s, %s, %d, %s, %s, %s)", new_tuples, ) conn.commit() elif self.table in "MS04309": cursor.executemany( "insert into LTERLogger_Pro.dbo." + self.table + " (" + column_string + ") VALUES( %s, %d, %s, %s, %s, %s, %s, %d, %s, %d , %s, %s, %s)", new_tuples, ) conn.commit() elif self.table in "MS04322": cursor.executemany( "insert into LTERLogger_Pro.dbo." + self.table + " (" + column_string + ") VALUES (%s, %d, %s, %s, %d, %s, %s, %s, %d, %s, %d, %s, %d, %s, %s)", new_tuples, ) conn.commit() elif self.table in "MS04325": cursor.executemany( "insert into LTERLogger_Pro.dbo." + self.table + " (" + column_string + ") VALUES (%s, %d, %s, %s, %d, %s, %s, %s, %d, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", new_tuples, ) conn.commit() elif self.table in "MS04304": cursor.executemany( "insert into LTERLogger_Pro.dbo." + self.table + " (" + column_string + ") VALUES(%s, %d, %s, %s, %d, %s, %s, %s, %d, %s, %d, %s, %d,%s ,%s, %d, %s, %d, %s, %d, %s, %d, %s, %d, %s, %d, %s, %d, %s, %d, %s, %d, %s, %d, %s, %s, %s)", new_tuples, ) conn.commit() elif self.table in "MS04334": cursor.executemany( "insert into LTERLogger_Pro.dbo." + self.table + " (" + column_string + ") VALUES( %s, %d, %s, %s, %d, %s, %s, %s, %d, %s, %d, %s, %d, %s, %d, %s, %d, %s, %d, %s, %d, %s, %d, %s, %d %s, %d, %s, %s, %s)", new_tuples, ) conn.commit()
def update_the_db_methods(self): """ updates the daily db based on the methods in method_history_daily""" print( "Updating your heights, depths, and methods prior to insertion in the db!" ) shortened_lookup = {} # form a new connection (we need this because we need the conn object to commit) import form_connection as fc conn = fc.micro_conn('SHELDON') # get all the probe_codes we have and dates we have probe_codes_ordered = [x[6] for x in self.new_rows] dates_ordered = [x[7] for x in self.new_rows] # get the unique entries of probe codes probe_string = self.f5(probe_codes_ordered) # make it into a string that the query can injest query_string = "\', \'".join(probe_string) # get the limited info from the lookup table -- we only need to look up the relevant probes and dates query = "select probe_code, date_bgn, date_end, method_code, height, depth from LTERLogger_new.dbo.method_history_daily where probe_code in (\'" + query_string + "\') and date_end >= \'" + datetime.datetime.strftime( self.Worker.daterange.dr[0], '%Y-%m-%d %H:%M:%S') + "\' order by date_bgn asc" cur = conn.cursor() cur.execute(query) # gather the methods by populating a dictionary called "shortened lookup" for row in cur: probe_code = str(row[0]) date_bgn = str(row[1]) date_end = str(row[2]) method_code = str(row[3]) height = int(row[4]) depth = str(row[5]) if probe_code not in shortened_lookup: shortened_lookup[probe_code] = { method_code: { 'begin': date_bgn, 'end': date_end, 'height': height, 'depth': depth } } elif probe_code in shortened_lookup: if method_code not in shortened_lookup[probe_code]: shortened_lookup[probe_code][method_code] = { 'begin': date_bgn, 'end': date_end, 'height': height, 'depth': depth } elif method_code in shortened_lookup[probe_code]: print "the method code %s is already collected for %s between the dates of %s and %s" % ( method_code, probe_code, date_bgn, date_end) pass # iterate over the rows you plan to insert for row in self.new_rows: # extract the date as a dt obj thisdate = datetime.datetime.strptime(row[7], '%Y-%m-%d %H:%M:%S') # check the length of the rows in the lookup table that share a key with the probe you are on try: get_length_of_rows = len(shortened_lookup[row[6]].keys()) except KeyError: print "the probe %s is not listed" % (row[6]) continue # if there's only one of those, than that's the only method, so accept it as correct if get_length_of_rows == 1: correct_method = shortened_lookup[row[6]].keys()[0] height_valid = shortened_lookup[ row[6]][correct_method]['height'] if row[3] == correct_method and row[4] == height_valid: continue else: print "correcting the method to %s from %s" % ( correct_method, row[3]) print "correcting the height to %s from %s" % ( height_valid, row[4]) row[3] = correct_method row[4] = height_valid elif get_length_of_rows > 1: correct_method = [ x for x in shortened_lookup[row[6]].keys() if thisdate >= datetime.datetime.strptime( shortened_lookup[row[6]][x]['begin'], '%Y-%m-%d %H:%M:%S') and thisdate < datetime.datetime. strptime(shortened_lookup[row[6]][x]['end'], '%Y-%m-%d %H:%M:%S') ][0] height_valid = shortened_lookup[ row[6]][correct_method]['height'] if row[3] == correct_method and row[4] == height_valid: continue else: print "correcting the method to %s from %s" % ( correct_method, row[3]) print "correcting the height to %s from %s" % ( height_valid, row[4]) row[3] = correct_method row[4] = height_valid elif get_length_of_rows < 1: print "the needed probe is not listed: %s" % (row[6]) pass return self.new_rows
def gather_methods_from_table(self): import form_connection as fc conn = fc.micro_conn('SHELDON') old_conn = fc.micro_conn('STEWARTIA') query_d = { 'AIRTEMP': 'MS04301', 'RELHUM': 'MS04302', 'PRECIP': 'MS04303', 'WSPD_PRO': 'MS04304', 'SOLAR': 'MS04305', 'DEWPT': 'MS04307', 'VPD': 'MS04308', 'LYS': 'MS04309', 'NR': 'MS04325', 'WSPD_SNC': 'MS04324', 'SOILTEMP': 'MS04321', 'SOILWC': 'MS04323', 'PAR': 'MS04322' } query_m = { 'AIRTEMP': 'MS00101', 'RELHUM': 'MS00102', 'PRECIP': 'MS00103', 'WSPD_PRO': 'MS00104', 'SOLAR': 'MS00105', 'DEWPT': 'MS00107', 'VPD': 'MS00108', 'LYS': 'MS00109', 'NR': 'MS00125', 'WSPD_SNC': 'MS00124', 'SOILTEMP': 'MS00121', 'SOILWC': 'MS00123', 'PAR': 'MS00122' } cursor = conn.cursor() old_cursor = old_conn.cursor() # collect the distinct probes from your server if self.server == "STEWARTIA": ## COMMENT THIS LINE IN FOR MS001 # query = "select distinct probe_code from fsdbdata.dbo." + query_m[self.attribute] ## COMMENT THIS LINE IN FOR MS043 query = "select distinct probe_code from fsdbdata.dbo." + query_d[ self.attribute] old_cursor.execute(query) distinct_probes = [] for row in old_cursor: distinct_probes.append(str(row[0])) query_string = query_string = "\', \'".join(distinct_probes) elif self.server == "SHELDON": ## COMMENT THIS LINE IN FOR LTERLogger_new #query = "select distinct probe_code from lterlogger_new.dbo" + query_d[self.attribute] ## COMMENT THIS LINE IN FOR LTERLogger_pro query = "select distinct probe_code from lterlogger_pro.dbo" + query_d[ self.attribute] cursor.execute(query) # append distinct probes to a list distinct_probes = [] for row in cursor: distinct_probes.append(str(row[0])) query_string = query_string = "\', \'".join(distinct_probes) else: pass # get the limited info from the lookup table query = "select probe_code, date_bgn, date_end, method_code, height, depth from LTERLogger_new.dbo.method_history_daily where probe_code in (\'" + query_string + "\') and date_end >= \'" + self.startdate + "\' order by date_bgn asc" cursor.execute(query) shortened_lookup = {} # gather the methods by populating a dictionary called "shortened lookup" for row in cursor: probe_code = str(row[0]) date_bgn = str(row[1]) date_end = str(row[2]) method_code = str(row[3]) height = int(row[4]) depth = str(row[5]) if probe_code not in shortened_lookup: shortened_lookup[probe_code] = { method_code: { 'begin': date_bgn, 'end': date_end, 'height': height, 'depth': depth } } elif probe_code in shortened_lookup: if method_code not in shortened_lookup[probe_code]: shortened_lookup[probe_code][method_code] = { 'begin': date_bgn, 'end': date_end, 'height': height, 'depth': depth } elif method_code in shortened_lookup[probe_code]: print "the method code %s is already collected for %s between the dates of %s and %s" % ( method_code, probe_code, date_bgn, date_end) pass # iterate over the distinct probes in our set for each_probe in distinct_probes: # and over each of the methods for that probes for each_method in shortened_lookup[each_probe].keys(): # get that probe method start method_startdate = shortened_lookup[each_probe][each_method][ 'begin'] print method_startdate # get that method's end method_enddate = shortened_lookup[each_probe][each_method][ 'end'] # new query to update the lter logger pro new_query = "update LTERLogger_Pro.dbo." + query_d[ self. attribute] + " set " + self.attribute + "_METHOD = \'" + str( each_method ) + "\' where probe_code like \'" + each_probe + "\' and Date >= \'" + method_startdate + "\' and Date < \'" + method_enddate + "\'" print new_query cursor.execute(new_query) conn.commit() return shortened_lookup() else: print "nothing to commit!" return shortened_lookup
def update_the_db(self): """ Updates LTER Logger Pro-- NOT LTER LOGGER NEW! -- currently as of 04-20-3015 its empty so I can't check it for pre-existing values without an error! """ print("This will update the LTERLogger_Pro database") # form a new connection (we need this because we need the conn object to commit) import form_connection as fc conn = fc.micro_conn('SHELDON') # keep the tuples from each object. I.e. self might be an AirTemperature, a Sonic, etc. new_tuples = [tuple(x) for x in self.new_rows] cursor = conn.cursor() # get_the_column_names cursor.execute( "select column_name from LTERLogger_Pro.information_schema.columns where table_name like \'" + self.table + "\' and table_schema like 'dbo'") # create a list from those columns, which is used to generate the sql later. For net radiation we have one strange additional column in LTERLogger_pro that is manually removed here. nr = [] for row in cursor: nr.append(str(row[0])) if self.table != "MS04325": column_string = " ,".join(nr[:-1]) elif self.table == "MS04325": column_string = " ,".join(nr) # the simplest case of data such as airtemp, relhum, dewpoint, soil temp, and soil wc if self.table in [ "MS04301", "MS04302", "MS04307", "MS04321", "MS04323" ]: # 'MS043',21, site_code, method_code, int(height), "1D", probe_code, datetime.datetime.strftime(each_date,'%Y-%m-%d %H:%M:%S'), mean_valid_obs, daily_flag, max_valid_obs, max_flag[0], datetime.datetime.strftime(max_valid_time[0], '%H%M'), min_valid_obs, min_flag[0], datetime.datetime.strftime(min_valid_time[0], '%H%M'), "NA", self.server cursor.executemany( "insert into LTERLogger_Pro.dbo." + self.table + " (" + column_string + ") VALUES (%s, %d, %s, %s, %d, %s, %s, %s, %d, %s, %d, %s, %s, %d, %s, %s, %s, %s)", new_tuples) conn.commit() # vpd is complicated, there is a long string for some older data and a short string for some newer data. elif self.table in "MS04308": print "processing vpd!" print "structure in LTERLogger_pro is: DBCODE, ENTITY, SITECODE, VPD_METHOD, HEIGHT, QC_LEVEL, PROBE_CODE, DATE, VPD_MEAN_DAY, VPD_MEAN_FLAG, VPD_MAX_DAY, VPD_MAX_FLAG, VPD_MAXTIME, VPD_MIN_DAY, VPD_MIN_FLAG, VPD_MINTIME, VAP_MEAN_DAY, VAP_MEAN_FLAG, VAP_MAX_DAY, VAP_MAX_FLAG, VAP_MIN_DAY, VAP_MIN_FLAG, EVENT_CODE, DB_TABLE" try: cursor.executemany( "insert into LTERLogger_Pro.dbo." + self.table + " (" + column_string + ") VALUES (%s,%d, %s, %s, %d, %s, %s, %s, %d, %s, %d, %s, %s, %d, %s, %s, %d, %s, %d, %s, %d, %s, %s, %s)", new_tuples) except Exception: print "First insertion attempt failed, trying a longer insertion for VPD et al." try: cursor.executemany( "insert into LTERLogger_Pro.dbo." + self.table + " (" + column_string + ") VALUES (%s,%d, %s, %s, %d, %s, %s, %s, %d, %s, %d, %s, %s, %d, %s, %s, %d, %s, %d, %s, %d, %s, %d, %s, %d, %s,%d, %s, %s, %s)", new_tuples) except Exception: print "Second insertion attempt failed, trying an even longer insertion for VPD et al." try: cursor.executemany( "insert into LTERLogger_Pro.dbo." + self.table + " (" + column_string + ") VALUES (%s, %d, %s, %s, %d, %s, %s, %s, %d, %s, %d, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", new_tuples) except Exception: print "Please edit the database insertion string for VPD in smashBosses. Check information schema.columns and the first row in MS04308" conn.commit() elif self.table in "MS04305": # DBCODE, ENTITY, SITECODE, SOLAR_METHOD, HEIGHT, QC_LEVEL, PROBE_CODE, DATE , SOLAR_TOT_DAY, SOLAR_TOT_FLAG, SOLAR_MEAN_DAY, SOLAR_MEAN_FLAG, SOLAR_MAX_DAY SOLAR_MAX_FLAG, SOLAR_MAXTIME, EVENT_CODE, DB_TABLE, ID for each_tuple in new_tuples: print each_tuple cursor.executemany( "insert into LTERLogger_Pro.dbo." + self.table + " (" + column_string + ") VALUES (%s, %d, %s, %s, %d, %s, %s, %s, %d, %s, %d, %s, %d, %s, %s, %s, %s)", new_tuples) conn.commit() elif self.table in "MS04303": # fixed to not have two outputs. # 'MS043', 3, 'PRIMET', 'PPT108', 100, '2D', 'PPTPRI01', '2015-01-01 00:00:00', 0.0, 'A', 'NA', 'SHELDON_LTERLogger_PRO_MS04313' cursor.executemany( "insert into LTERLogger_Pro.dbo." + self.table + " (" + column_string + ") VALUES( %s, %d, %s, %s, %d, %s, %s, %s, %d, %s, %s, %s)", new_tuples) conn.commit() elif self.table in "MS04309": cursor.executemany( "insert into LTERLogger_Pro.dbo." + self.table + " (" + column_string + ") VALUES( %s, %d, %s, %s, %s, %s, %s, %d, %s, %d , %s, %s, %s)", new_tuples) conn.commit() elif self.table in "MS04322": cursor.executemany( "insert into LTERLogger_Pro.dbo." + self.table + " (" + column_string + ") VALUES (%s, %d, %s, %s, %d, %s, %s, %s, %d, %s, %d, %s, %d, %s, %s)", new_tuples) conn.commit() elif self.table in "MS04325": cursor.executemany( "insert into LTERLogger_Pro.dbo." + self.table + " (" + column_string + ") VALUES (%s, %d, %s, %s, %d, %s, %s, %s, %d, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", new_tuples) conn.commit() elif self.table in "MS04304": cursor.executemany( "insert into LTERLogger_Pro.dbo." + self.table + " (" + column_string + ") VALUES(%s, %d, %s, %s, %d, %s, %s, %s, %d, %s, %d, %s, %d,%s ,%s, %d, %s, %d, %s, %d, %s, %d, %s, %d, %s, %d, %s, %d, %s, %d, %s, %d, %s, %d, %s, %s, %s)", new_tuples) conn.commit() elif self.table in "MS04334": cursor.executemany( "insert into LTERLogger_Pro.dbo." + self.table + " (" + column_string + ") VALUES( %s, %d, %s, %s, %d, %s, %s, %s, %d, %s, %d, %s, %d, %s, %d, %s, %d, %s, %d, %s, %d, %s, %d, %s, %d %s, %d, %s, %s, %s)", new_tuples) conn.commit()
def gather_methods_from_table(self): import form_connection as fc conn = fc.micro_conn('SHELDON') old_conn = fc.micro_conn('STEWARTIA') query_d = {'AIRTEMP': 'MS04301', 'RELHUM': 'MS04302', 'PRECIP': 'MS04303', 'WSPD_PRO': 'MS04304', 'SOLAR': 'MS04305', 'DEWPT': 'MS04307', 'VPD': 'MS04308', 'LYS': 'MS04309', 'NR': 'MS04325', 'WSPD_SNC': 'MS04324', 'SOILTEMP': 'MS04321', 'SOILWC': 'MS04323', 'PAR': 'MS04322'} query_m = {'AIRTEMP': 'MS00101', 'RELHUM': 'MS00102', 'PRECIP': 'MS00103', 'WSPD_PRO': 'MS00104', 'SOLAR': 'MS00105', 'DEWPT': 'MS00107', 'VPD': 'MS00108', 'LYS': 'MS00109', 'NR': 'MS00125', 'WSPD_SNC': 'MS00124', 'SOILTEMP': 'MS00121', 'SOILWC': 'MS00123', 'PAR': 'MS00122'} cursor = conn.cursor() old_cursor = old_conn.cursor() # collect the distinct probes from your server if self.server == "STEWARTIA": ## COMMENT THIS LINE IN FOR MS001 # query = "select distinct probe_code from fsdbdata.dbo." + query_m[self.attribute] ## COMMENT THIS LINE IN FOR MS043 query = "select distinct probe_code from fsdbdata.dbo." + query_d[self.attribute] old_cursor.execute(query) distinct_probes = [] for row in old_cursor: distinct_probes.append(str(row[0])) query_string = query_string = "\', \'".join(distinct_probes) elif self.server == "SHELDON": ## COMMENT THIS LINE IN FOR LTERLogger_new #query = "select distinct probe_code from lterlogger_new.dbo" + query_d[self.attribute] ## COMMENT THIS LINE IN FOR LTERLogger_pro try: query = "select distinct probe_code from lterlogger_pro.dbo" + query_d[self.attribute] cursor.execute(query) except Exception: if query_d[self.attribute] == "MS00121": query = "select distinct probe_code from lterlogger_pro.dbo.MS04321" cursor.execute(query) else: print("you are looking for a table on sheldon we do not have a method for yet. Please add this method.") # append distinct probes to a list distinct_probes = [] for row in cursor: distinct_probes.append(str(row[0])) query_string = query_string = "\', \'".join(distinct_probes) else: pass # get the limited info from the lookup table query = "select probe_code, date_bgn, date_end, method_code, height, depth from LTERLogger_new.dbo.method_history_daily where probe_code in (\'" + query_string +"\') and date_end >= \'" + self.startdate + "\' order by date_bgn asc" cursor.execute(query) shortened_lookup = {} # gather the methods by populating a dictionary called "shortened lookup" for row in cursor: probe_code = str(row[0]) date_bgn = str(row[1]) date_end = str(row[2]) method_code = str(row[3]) height = int(row[4]) depth = str(row[5]) if probe_code not in shortened_lookup: shortened_lookup[probe_code] = {method_code: {'begin': date_bgn, 'end': date_end, 'height': height, 'depth': depth}} elif probe_code in shortened_lookup: if method_code not in shortened_lookup[probe_code]: shortened_lookup[probe_code][method_code] = {'begin': date_bgn, 'end': date_end, 'height': height, 'depth': depth} elif method_code in shortened_lookup[probe_code]: print "the method code %s is already collected for %s between the dates of %s and %s" %(method_code, probe_code, date_bgn, date_end) pass # iterate over the distinct probes in our set for each_probe in distinct_probes: # and over each of the methods for that probes for each_method in shortened_lookup[each_probe].keys(): # get that probe method start method_startdate = shortened_lookup[each_probe][each_method]['begin'] print method_startdate # get that method's end method_enddate = shortened_lookup[each_probe][each_method]['end'] # new query to update the lter logger pro new_query = "update LTERLogger_Pro.dbo." + query_d[self.attribute] + " set " + self.attribute + "_METHOD = \'" + str(each_method) + "\' where probe_code like \'" + each_probe + "\' and Date >= \'" + method_startdate + "\' and Date < \'" + method_enddate + "\'" print new_query cursor.execute(new_query) conn.commit() return shortened_lookup() else: print "nothing to commit!" return shortened_lookup