def get_graph_data(site, param): graph_data = [] conn = connect_db_socool() conn2 = connect_db_socool() socool_cursor = conn.cursor(MySQLdb.cursors.DictCursor) cursor2 = conn2.cursor(MySQLdb.cursors.DictCursor) #get deployed date deployed_query = "Select deployed, serial from sites where site = '%s'" % site socool_cursor.execute(deployed_query) for row in socool_cursor: deployed = row['deployed'] serial = row['serial'] data_query = "select %s, unix_timestamp(date) as epoch from opd_data where \ site = '%s' AND date > '%s' AND serial = '%s' order by epoch asc" % \ (param, site, deployed, serial) #get data cursor2.execute(data_query) result = cursor2.fetchall() for record in result: #multiply by 1000 to get ms for highcharts record['epoch'] = record['epoch']*1000 row = "[%s, %s]" % (record['epoch'], record[param]) print row graph_data.append(row) conn.close() conn2.close() cursor2.close() socool_cursor.close() return graph_data
def fetch_glider_data(): #images for data and kmz links data_img = 'chart.png' kmz_img = 'kml_icon.jpg' #Query DB for expanded headers conn = connect_db_socool() cursor = conn.cursor() headerQuery = 'SELECT expandedName from deploymentExpandedNames' try: cursor.execute(headerQuery) except MySQLdb.Error as e: print "Query failed. Error: %s" % e sys.exit() tableHeaders = [] for(header) in cursor: tableHeaders.append(header[0]) cursor.close() #get data data = [] cursor = conn.cursor() #we need to get unexpanded defs and build this string dataString = "vehicle,project,deployed,recovered,dayswet,battstart,\ battend,distance,data,kmz,plots" dataQuery = "SELECT %s from deployments ORDER BY \ deployed DESC" % dataString cursor.execute(dataQuery) for row in cursor: data.append(row) return data, tableHeaders
def get_sites(): sites = [] conn = connect_db_socool() cursor = conn.cursor(MySQLdb.cursors.DictCursor) #get opd status conn = connect_db_socool() cursor = conn.cursor(MySQLdb.cursors.DictCursor) #query for opd logs site_query = "select * from sites order by deployed desc" cursor.execute(site_query) result = cursor.fetchall() for line in result: sites.append(line) cursor.close() conn.close() return sites
def get_site(site): site_data = [] conn = connect_db_socool() cursor = conn.cursor(MySQLdb.cursors.DictCursor) site_query = "select * from sites where site = '%s'" % site cursor.execute(site_query) result = cursor.fetchall() for site in result: return site
def get_technology_data(): tech = [] conn = connect_db_socool() cursor = conn.cursor(MySQLdb.cursors.DictCursor) #query for inactive sites tech_query = "select caption, url, image from technology \ ORDER BY caption ASC" cursor.execute(tech_query) result = cursor.fetchall() for technology in result: tech.append(technology) return tech
def get_opd_data(): inactive_sites = [] conn = connect_db_socool() socool_cursor = conn.cursor(MySQLdb.cursors.DictCursor) site_cursor = conn.cursor(MySQLdb.cursors.DictCursor) #query for inactive sites inactive_query = "select site, latitude, longitude from sites where serial = 0"; socool_cursor.execute(inactive_query) result = socool_cursor.fetchall() for site in result: inactive_sites.append(site) active_sites = [] #conn = connect_db() #cursor = conn.cursor(MySQLdb.cursors.DictCursor) #active_cursor = conn.cursor(MySQLdb.cursors.DictCursor) #[TO DO] Need to add query to get all 'ACTIVE' serials here #and then do a for serial in cursor: query get_active_query = "select serial,operator,deployed,site \ from sites where status='active' and serial != '' ORDER BY serial ASC"; socool_cursor.execute(get_active_query) actives = socool_cursor.fetchall() for active in actives: active_data_query = " select voltage, pressure_drop, error_code, \ integration_time, water_vol, date,cdom_slope, intercept, similarity_index, \ target_model,sites.site, sites.serial, sites.operator, sites.deployed,\ sites.latitude, sites.longitude from opd_data,sites where \ opd_data.serial = '%d' and opd_data.serial = sites.serial and \ sites.status = 'active' order by date \ desc limit 1" % active['serial'] socool_cursor.execute(active_data_query) result = socool_cursor.fetchall() for site in result: #Date math to calculate days_wet current_date = date.today() days_wet = (current_date - site['deployed']) site['days_wet'] = days_wet.days site['deployed'] = site['deployed'].strftime('%Y-%m-%d') site['date'] = site['date'].strftime('%Y-%m-%d %H:%M') site['epoch'] = int(time.mktime(time.strptime(site['date'], '%Y-%m-%d %H:%M'))) site['time_delta'] = int(time.time() - site['epoch']) for key, value in site.iteritems(): if type(value) is LongType: site[key] = int(value) active_sites.append(site) socool_cursor.close() site_cursor.close() conn.close() return inactive_sites, active_sites
def get_gliderops_data(): ops = [] conn = connect_db_socool() cursor = conn.cursor(MySQLdb.cursors.DictCursor) #query for inactive sites ops_query = "select caption, url, image from glider_ops" cursor.execute(ops_query) result = cursor.fetchall() for op in result: ops.append(op) cursor.close() conn.close() return ops
def get_publications_data(): publication_types = [] conn = connect_db_socool() cursor = conn.cursor(MySQLdb.cursors.DictCursor) #query for inactive sites pub_query = "select caption, url, image from publication_types \ ORDER BY caption ASC" cursor.execute(pub_query) result = cursor.fetchall() for pub in result: publication_types.append(pub) cursor.close() conn.close() return publication_types
def get_home_apps(): home_apps = [] conn = connect_db_socool() cursor = conn.cursor(MySQLdb.cursors.DictCursor) #query for inactive sites home_app_query = "select title, url, image from home_apps \ ORDER BY display_order ASC" cursor.execute(home_app_query) result = cursor.fetchall() for app in result: home_apps.append(app) cursor.close() conn.close() return home_apps
def get_people_data(): staff = [] conn = connect_db_socool() cursor = conn.cursor(MySQLdb.cursors.DictCursor) #query for inactive sites staff_query = "select last, first, title, url, image from staff \ ORDER BY LAST ASC" cursor.execute(staff_query) result = cursor.fetchall() for person in result: staff.append(person) cursor.close() conn.close() return staff
def get_apps_data(): apps = [] conn = connect_db_socool() cursor = conn.cursor(MySQLdb.cursors.DictCursor) #query for inactive sites app_query = "select caption, url, image from apps \ ORDER BY caption ASC" cursor.execute(app_query) result = cursor.fetchall() for app in result: apps.append(app) cursor.close() conn.close() return apps
def get_techdocs(): tech_pubs = [] conn = connect_db_socool() cursor = conn.cursor(MySQLdb.cursors.DictCursor) #query for inactive sites pub_query = "select title, authors, url, date from techdocs \ ORDER BY date DESC" cursor.execute(pub_query) result = cursor.fetchall() for pub in result: tech_pubs.append(pub) cursor.close() conn.close() return tech_pubs
def get_presentations(): presentations = [] conn = connect_db_socool() cursor = conn.cursor(MySQLdb.cursors.DictCursor) #query for inactive sites pub_query = "select title, authors, url, event from presentations \ ORDER BY title ASC" cursor.execute(pub_query) result = cursor.fetchall() for pub in result: presentations.append(pub) cursor.close() conn.close() return presentations
def get_partners(): partners = [] conn = connect_db_socool() cursor = conn.cursor(MySQLdb.cursors.DictCursor) #query for inactive sites partner_query = "select partner, url, image from partners \ ORDER BY display_order ASC" cursor.execute(partner_query) result = cursor.fetchall() for partner in result: partners.append(partner) cursor.close() conn.close() return partners
def get_nav_links(): nav_links = [] conn = connect_db_socool() cursor = conn.cursor(MySQLdb.cursors.DictCursor) #query for inactive sites nav_query = "select title, url from nav_links \ ORDER BY display_order ASC" cursor.execute(nav_query) result = cursor.fetchall() for link in result: nav_links.append(link) cursor.close() conn.close() return nav_links
def get_netlog(): net_log = [] conn = connect_db_socool() cursor = conn.cursor(MySQLdb.cursors.DictCursor) #query for opd logs log_query = "select from_unixtime(epoch) as DATETIME, logline \ from opd_logs where ascii(logline) < 128 AND from_unixtime(epoch) > (NOW() - INTERVAL 7 DAY) ORDER BY epoch DESC" cursor.execute(log_query) result = cursor.fetchall() for line in result: net_log.append(line) cursor.close() conn.close() return net_log
def get_sop_docs(): sop_docs = [] conn = connect_db_socool() cursor = conn.cursor(MySQLdb.cursors.DictCursor) #query for inactive sites pub_query = "select title, url, revdate from sop_docs \ ORDER BY title ASC" cursor.execute(pub_query) result = cursor.fetchall() for doc in result: sop_docs.append(doc) cursor.close() conn.close() return sop_docs
def build_serial_list(): deployed = [] choices=[] db = connect_db_socool() cursor = db.cursor() query = "select serial from sites where serial != 0" cursor.execute(query) results = cursor.fetchall() for serial in results: deployed.append(serial[0]) cursor.close() db.close() for number in range(0,100): if number in deployed: next else: choices.append((number, number)) return choices
def get_opd_historical(interval): active_sites = [] conn = connect_db_socool() cursor = conn.cursor(MySQLdb.cursors.DictCursor) #[TO DO] Need to add query to get all 'ACTIVE' serials here #and then do a for serial in cursor: query if interval != 0: active_data_query = "select * from opd_data where date > \ NOW() - INTERVAL %d DAY order by date DESC" % interval else: active_data_query = "select * from opd_data order by date DESC" cursor.execute(active_data_query) result = cursor.fetchall() for record in result: for key, value in record.iteritems(): if type(value) is LongType: record[key] = int(value) active_sites.append(record) cursor.close() conn.close() return active_sites
def get_glider_data(): active_gliders = [] conn = connect_db_socool() socool_cursor =conn.cursor(MySQLdb.cursors.DictCursor) get_active_query = "select serial,operator,deployed from deployed_gliders \ ORDER BY serial ASC" socool_cursor.execute(get_active_query) actives = socool_cursor.fetchall() for active in actives: glider_query = "select operator, deployed_gliders.serial, deployed, \ sci_water_temp, horz_dist, dc_ratio, avg_speed, last_report, \ latitude, longitude from deployed_gliders,glider_data \ where deployed_gliders.serial = '%s' \ ORDER BY last_report DESC LIMIT 1;" % (active['serial']) socool_cursor.execute(glider_query) result = socool_cursor.fetchall() for glider in result: #Date math to calculate days_wet current_date = date.today() days_wet = (current_date - glider['deployed']) glider['days_wet'] = days_wet.days glider['deployed'] = glider['deployed'].strftime('%Y-%m-%d') glider['last_report'] = glider['last_report'].strftime('%Y-%m-%d %H:%M') glider['epoch'] = int(time.mktime(time.strptime(glider['last_report'], '%Y-%m-%d %H:%M'))) glider['time_delta'] = int(time.time() - glider['epoch']) for key, value in glider.iteritems(): if type(value) is LongType: glider[key] = int(value) active_gliders.append(glider) socool_cursor.close() conn.close() return active_gliders