def stuff_runs(self, runs=None): """ Loop over runs and stuff them into the database table. If runs is not entered, they are gotten using the window runlist """ tm00 = time.time() # if not updating, make sure the table does not already exist if not self._update: pg = pgnumpy.connect(self._conninfo) if pg.table_exists(self._table): raise ValueError("Table '%s' already exists" % self._table) runs2use,reruns2use = self.matchruns(runs=runs) nrun=len(runs2use) srun = runs2use.argsort() irun=1 for i in srun: run = runs2use[i] rerun = reruns2use[i] stdout.write("run: %06i (%s/%s)\n" % (run,irun,nrun)) tm0 = time.time() for camcol in [1,2,3,4,5,6]: self.stuff_one(run,rerun,camcol) tm1=time.time() stdout.write(" Time: %s sec\n" % (tm1-tm0,) ) stdout.flush() # grant select privledges to everyone if irun == 1: grant_query = 'grant select on %s to public' % self._table stdout.write(' '+grant_query+'\n') pg.execute(grant_query) irun += 1 tm11 = time.time() stdout.write("Total time: ") esutil.misc.ptime(tm11-tm00)
def __init__(self, run_name, max_rmag=22.0, max_imag=21.0, minscore=0.1): self.maskname = 'tycho' self.run_name=run_name self.max_rmag = max_rmag self.max_imag = max_imag self.minscore=minscore self.flags = sdsspy.flags.Flags() win = sdsspy.window.Window() stdout.write("Loading run list\n") self.runs,reruns = win.runlist(minscore=self.minscore) stdout.write("connecting to boss database\n") self.pg = pgnumpy.connect('dbname=boss')
def create_meta_tables(self, subtypes=['','prim']): """ Before calling this you must create the primary view Note count will be a bigint """ photo_sweep = os.path.basename(self._photo_sweep) photo_resolve = os.path.basename(self._photo_resolve) pg = pgnumpy.connect(self._conninfo) for subtype in subtypes: query=""" CREATE TABLE {subtype}{type}_meta AS ( SELECT now() as creation_date, '{photo_sweep}'::varchar as photo_sweep, '{photo_resolve}'::varchar as photo_resolve, count(*) FROM {subtype}{type} ) """.format(type=self._type, subtype=subtype, photo_sweep=photo_sweep, photo_resolve=photo_resolve) stdout.write(query+'\n') pg.execute(query) grant_query = 'grant select on {subtype}{type}_meta to public' grant_query=grant_query.format(type=self._type, subtype=subtype) stdout.write(grant_query+'\n') pg.execute(grant_query)
def create_indices(self): """ Create all the indices in the _index_columns variable """ tm00 = time.time() pg = pgnumpy.connect(self._conninfo) for colspec in self._index_columns: stdout.write('-'*70 + '\n') if colspec == 'photoid': unique=True else: unique=False tm0 = time.time() pg.create_index(self._table, colspec, unique=unique) tm1 = time.time() esutil.misc.ptime(tm1-tm0) tm11 = time.time() stdout.write('-'*70 + '\n') stdout.write("Total time: ") esutil.misc.ptime(tm11-tm00)
def __init__(self): self._window = sdsspy.window.Window() self._pg=pgnumpy.connect('dbname=boss user=postgres')