Forked from
MetObs / MetObsSite
44 commits behind the upstream repository.
-
David Hoese authoredDavid Hoese authored
dbget2.py 5.21 KiB
# routines to access data from the penthouse database
DB_SERVER = 'bora2.ssec.wisc.edu'
DB_NAME = 'rig'
DB_USER = 'rig'
METEOROLOGY_GROUP_ID=-1
ENGINEERING_GROUP_ID=-2
import pgdb
import re
# make a connection to the database
def connect():
return pgdb.connect('%s:%s:%s' % (DB_SERVER, DB_NAME, DB_USER))
# close the connection
def disconnect(con):
con.close()
# get a dataset
def get_dataset(symbol, res, start, end):
con = connect()
c = con.cursor()
c.execute("SELECT symbol_id FROM symbols WHERE name='%s'" % symbol)
symbol_id = c.fetchone()[0]
c.execute("SELECT stamp, value " +
"FROM data NATURAL JOIN samples " +
("WHERE symbol_id=%d " % symbol_id) +
("AND stamp >= '%s' AND stamp <= '%s' " % (start, end)) +
("AND resolution=%d") % res)
tmp = c.fetchall()
disconnect(con)
return tmp
# get a daraset with the specified units (not checked for errors)
def get_dataset_with_units(symbol, res, start, end, units):
con = connect()
c = con.cursor()
c.execute("SELECT symbol_id FROM symbols WHERE name='%s'" % symbol)
symbol_id = c.fetchone()[0]
c.execute(("SELECT stamp, convert_units(value, '%s') " % units) +
"FROM data NATURAL JOIN samples " +
("WHERE symbol_id=%d " % symbol_id) +
("AND stamp >= '%s' AND stamp <= '%s' " % (start, end)) +
("AND resolution=%d") % res)
tmp = c.fetchall()
disconnect(con)
return tmp
# makes a text file from a dataset - ready for gnuplot
def makefile(dataset, filename):
f = open(filename, 'w')
#dataset = map(lambda x: (x[0][0:x[0].index('.')], x[1]), dataset)
for row in dataset:
f.write(row[0] + ' ' + str(row[1]) + '\n')
return (dataset[0][0], dataset[-1][0])
# returns data for the specified symbol
def get_symbol_data(symbol):
sym_dict = {}
con = connect()
c = con.cursor()
c.execute("SELECT * FROM symbols WHERE name='%s'" % symbol)
vals = c.fetchone()
for i in range(len(c.description)):
sym_dict[c.description[i][0]] = vals[i]
disconnect(con)
return sym_dict
# gets all symbol data in a dictionary keyed by name
# dictionary entries are: (id, long name, units, description)
def get_all_symbol_data():
sym_data = {}
con = connect()
c = con.cursor()
c.execute("SELECT name, symbol_id, long_name, units, description " +
"FROM symbols")
for row in c.fetchall():
sym_data[row[0]] = tuple(row[1:])
disconnect(con)
return sym_data
# returns the names of the symbols
def get_symbol_names():
sym_list = []
con = connect()
c = con.cursor()
c.execute("SELECT s.name FROM symbols s, group_items g " +
" WHERE g.symbol_id=s.symbol_id AND g.group_id > -3" +
" ORDER BY g.group_id DESC, g.list_order")
for l in c.fetchall():
if l[0] != "TIME":
sym_list.append(l[0])
disconnect(con)
return sym_list
# returns long-name data
def get_long_names():
names = {}
con = connect()
c = con.cursor()
c.execute("SELECT name, long_name FROM symbols")
for name, long_name in c.fetchall():
names[name] = long_name
disconnect(con)
return names
# gets possible units for the given symbols
def get_alternate_units(units):
con = connect()
c = con.cursor()
c.execute("SELECT type FROM units WHERE label='%s'" % units)
type = c.fetchone()[0]
c.execute("SELECT label FROM units WHERE type='%s'" % type)
tmp = map(lambda x: x[0], c.fetchall())
disconnect(con)
return tmp
# returns the names of the symbols for meteorology
def get_met_symbol_names():
return get_group_symbol_names(METEOROLOGY_GROUP_ID)
# returns the names of the symbols for engineering sensors
def get_eng_symbol_names():
return get_group_symbol_names(ENGINEERING_GROUP_ID)
def get_group_symbol_names(gid):
sym_list = []
con = connect()
c = con.cursor()
c.execute("SELECT s.name FROM symbols s, group_items g " +
" WHERE g.symbol_id=s.symbol_id AND g.group_id=%d" % gid +
" ORDER BY g.list_order")
for l in c.fetchall():
if l[0] != "TIME":
sym_list.append(l[0])
disconnect(con)
return sym_list
# returns units data
def get_units():
units_dict = {}
con = connect()
c = con.cursor()
c.execute("SELECT name, units FROM symbols")
for name, units in c.fetchall():
units_dict[name] = units
disconnect(con)
return units_dict
# returns current conditions tuple: (time_stamp, value_dict)
def get_current_conds():
data = {}
con = connect()
c = con.cursor()
c.execute("SELECT sample_id, stamp FROM samples WHERE resolution=60 " +
"ORDER BY stamp DESC LIMIT 1")
sample_id, stamp = tuple(c.fetchone())
c.execute("SELECT symbol_id, value from data WHERE sample_id=%d" %
sample_id)
for row in c.fetchall():
data[row[0]] = row[1]
disconnect(con)
return (stamp, data)
# returns a dictionary with global values
def get_globals():
gd = {}
con = connect()
c = con.cursor()
c.execute('SELECT * FROM globals')
vals = c.fetchone()
for i in range(len(c.description)):
gd[c.description[i][0]] = vals[i]
disconnect(con)
return gd