def getDESCatalogs(goldDB = False,rows= None): if goldDB is False: cfile = '../Data/DES_Y1_S82.fits' ''' Note: This fits table is everything in Y1A1_COADD_OBJECTS between (300 < ra < 60) and (dec > -10) with i_auto < 21. ''' print "Getting fits catalog" data,thing = esutil.io.read_header(cfile,ext=1,rows=rows) else: cur = desdb.connect() #query = ''' # SELECT * # FROM # Y1A1_COADD_OBJECTS y1 # WHERE # ((y1.ra > 300) or (y1.ra < 60)) AND # (y1.dec > -10) AND # (y1.mag_auto_i < 21.5) AND # (COADD_OBJECTS_ID IN (SELECT COADD_OBJECTS_ID FROM nsevilla.y1a1_gold_1_0_1 n WHERE n.flags_badregion=0)) AND # rownum < 1000 # ''' query = ''' SELECT * FROM Y1A1_COADD_OBJECTS y1 WHERE ((y1.dec < 2.7) and (y1.dec > -1.9) and ((y1.ra > 316 ) or (y1.ra < 4.8))) and (COADD_OBJECTS_ID IN (SELECT COADD_OBJECTS_ID FROM nsevilla.y1a1_gold_1_0_1 n WHERE n.flags_badregion=0)) AND rownum < 1000 ''' data = cur.quick(query,array=True) return data
def DropTablesIfNeeded(RunConfig, indexstart, size, tiles, runlog): allbands = runbalrog.GetAllBands() cur = desdb.connect() user = cur.username arr = cur.quick("select table_name from dba_tables where owner='%s'" %(user.upper()), array=True) tables = arr['table_name'] kinds = ['truth', 'nosim', 'sim', 'des'] write = False test = 'balrog_%s_%s' %(RunConfig['dbname'], kinds[0]) exists = (test.upper() in tables) if not RunConfig['DBoverwrite']: if exists: if RunConfig['duplicate'] == 'replace': runlog.info("Replacing existing entries in DB which match this run's balrog_indexes, if necessary...") for i in range(len(tiles)): for kind in kinds[0:3]: tab = 'balrog_%s_%s' %(RunConfig['dbname'], kind) q = "delete from %s where tilename='%s' and balrog_index>=%i and balrog_index<%i"%(tab,tiles[0],indexstart[i],indexstart[i]+size[i]) arr = cur.quick(q, array=True) cur.commit() runlog.info("Done") if RunConfig['duplicate'] == 'error': runlog.info("Verifying no duplicate balrog_indexes...") for i in range(len(tiles)): arr = cur.quick("select balrog_index from %s where tilename='%s'"%(test,tiles[i]), array=True) this = np.arange(indexstart[i], indexstart[i]+size[i], 1) inboth = np.in1d(np.int64(this), np.int64(arr['balrog_index'])) if np.sum(inboth) > 0: open(RunConfig['dupfailfile'],'a').close() raise Exception("You are trying to add balrog_index(es) which already exist, and you've flagged duplicate=error. Killing the subjob") runlog.info("Ok") else: if RunConfig['isfirst']: write = True elif (not RunConfig['isfirst']): runlog.info("You gave DBoverwrite=True. I'm waiting for the first process, to ensure that the %s tables have been deleted."%(RunConfig['dbname'])) while True: if os.path.exists(RunConfig['failfile']): raise Exception("The first process failed before we can be sure that the DB is as we want it.") if os.path.exists(RunConfig['touchfile']): break else: write = True if exists: runlog.warning("You gave DBoverwrite=True, and %s exists. I'm going to remove tables.'"%(RunConfig['dbname'])) for kind in kinds: tab = 'balrog_%s_%s' %(RunConfig['dbname'], kind) cur.quick("DROP TABLE %s PURGE" %tab) runlog.warning("Deleted %s"%(tab)) open(RunConfig['touchfile'],'a').close() open(RunConfig['dupokfile'],'a').close() return write
def GetTileDefs(args, strtype='|S12'): #t = esutil.io.read(args.tiles)[args.tilecol][0:2] t = esutil.io.read(args.tiles)[args.tilecol] tindex = np.arange(len(t)) tiles = np.empty(len(t), dtype=[('tilename',strtype), ('index', np.int64)]) tiles['tilename'] = t.astype(strtype) tiles['index'] = np.arange(len(t)) if args.density is not None: for tile in tiles['tilename']: outdir = os.path.join(args.outdir, tile) if not os.path.exists(outdir): os.makedirs(outdir) cur = desdb.connect() q = "select urall, uraur, udecll, udecur, tilename from coaddtile order by udecll desc, urall asc" arr = cur.quick(q, array=True) dt = arr.dtype.descr dt[-1] = ('tilename',strtype) dt = np.dtype(dt) newarr = np.empty(len(arr), dtype=dt) for i in range(len(arr.dtype.names)): name = arr.dtype.names[i] if i == 4: newarr[name] = arr[name].astype(strtype) else: newarr[name] = arr[name] tiles = rec.join_by('tilename', newarr, tiles, usemask=False) tiles = np.sort(tiles, order='index') return tiles
def IndexDescribe(table, user=None): """ Get information about the indexing of a table. Parameters ---------- table (str) Name of the table user (str) Name of the user who owns the table. None does not specify a user. (A user is not needed if the table name is unique.) Returns ------- arr (structured array) Array with several columns [index_owner, index_name, table_owner, table_name, column_name, column_position, column_length, char_length, descend] """ cur = _desdb.connect() if user is not None: arr = cur.quick( "select * from dba_ind_columns where table_name='%s' and table_owner='%s'" % (table.upper(), user.upper()), array=True, ) else: arr = cur.quick("select * from dba_ind_columns where table_name='%s'" % (table.upper()), array=True) return arr
def ConstraintDescribe(table, user=None): """ Get information about the constraints of a table. Parameters ---------- table (str) Name of the table user (str) Name of the user who owns the table. None does not specify a user. (A user is not needed if the table name is unique.) Returns ------- arr (structured array) Array with columns [owner, constraint_name, table_name, column_name, position] """ cur = _desdb.connect() if user is not None: arr = cur.quick( "select * from user_cons_columns where table_name='%s' and owner='%s'" % (table.upper(), user.upper()), array=True, ) else: arr = cur.quick("select * from user_cons_columns where table_name='%s'" % (table.upper()), array=True) return arr
def DownloadImages(): """ Read from the database where are the images and PSF files at the database and downloads them into the working node. Then uncompress the files with funpack. """ df = desdb.files.DESFiles(fs='net') conn = desdb.connect() images = [] psfs = [] bands = [] for band_ in ['det']+__bands__: if band_ == 'det': d = conn.quick("SELECT c.run from coadd c, runtag rt where rt.run=c.run and c.tilename='%s' and rt.tag='%s' and c.band is null" % (__tilename__, __config__['data_release'].upper()), array=True) else: d = conn.quick("SELECT c.run from coadd c, runtag rt where rt.run=c.run and c.tilename='%s' and rt.tag='%s' and c.band='%s'" % (__tilename__, __config__['data_release'].upper(),band_), array=True) if len(d) == 0: continue img = df.url('coadd_image', coadd_run=d[0]['run'], tilename=__tilename__, band=band_) images.append( img ) psfs.append( img.replace('.fits.fz', '_psfcat.psf') ) bands.append( band_ ) for image_ in images: subprocess.call(['wget','--quiet','--no-check-certificate',image_,'-O',image_.split('/')[-1]]) subprocess.call(['funpack','-v','-D','-O',image_.split('/')[-1].replace('.fits.fz', '.fits'),image_.split('/')[-1]]) for psf_ in psfs: subprocess.call(['wget','--quiet','--no-check-certificate',psf_,'-O',psf_.split('/')[-1]]) return [ images, psfs, bands ]
def ColumnDescribe(table, user=None): """ Get the column names, as well as data type and precsion information about a DB table Parameters ---------- table (str) Name of the table user (str) Name of the user who owns the table. None does not specify a user. (A user is not needed if the table name is unique.) Returns ------- arr (structured array) Array with columns [column_name, data_type, data_precision, data_scale] """ cur = _desdb.connect() if user is not None: arr = cur.quick( "SELECT column_name, data_type, data_precision, data_scale, nullable, char_length from all_tab_cols where table_name='%s' and owner='%s' order by column_name" % (table.upper(), user.upper()), array=True, ) else: arr = cur.quick( "SELECT column_name, data_type, data_precision, data_scale, nullable, char_length from all_tab_cols where table_name='%s' order by column_name" % (table.upper()), array=True, ) return arr
def ConstraintDescribe(table, user=None): """ Get information about the constraints of a table. Parameters ---------- table (str) Name of the table user (str) Name of the user who owns the table. None does not specify a user. (A user is not needed if the table name is unique.) Returns ------- arr (structured array) Array with columns [owner, constraint_name, table_name, column_name, position] """ cur = _desdb.connect() if user is not None: arr = cur.quick( "select * from user_cons_columns where table_name='%s' and owner='%s'" % (table.upper(), user.upper()), array=True) else: arr = cur.quick( "select * from user_cons_columns where table_name='%s'" % (table.upper()), array=True) return arr
def IndexDescribe(table, user=None): """ Get information about the indexing of a table. Parameters ---------- table (str) Name of the table user (str) Name of the user who owns the table. None does not specify a user. (A user is not needed if the table name is unique.) Returns ------- arr (structured array) Array with several columns [index_owner, index_name, table_owner, table_name, column_name, column_position, column_length, char_length, descend] """ cur = _desdb.connect() if user is not None: arr = cur.quick( "select * from dba_ind_columns where table_name='%s' and table_owner='%s'" % (table.upper(), user.upper()), array=True) else: arr = cur.quick("select * from dba_ind_columns where table_name='%s'" % (table.upper()), array=True) return arr
def ColumnDescribe(table, user=None): """ Get the column names, as well as data type and precsion information about a DB table Parameters ---------- table (str) Name of the table user (str) Name of the user who owns the table. None does not specify a user. (A user is not needed if the table name is unique.) Returns ------- arr (structured array) Array with columns [column_name, data_type, data_precision, data_scale] """ cur = _desdb.connect() if user is not None: arr = cur.quick( "SELECT column_name, data_type, data_precision, data_scale, nullable, char_length from all_tab_cols where table_name='%s' and owner='%s' order by column_name" % (table.upper(), user.upper()), array=True) else: arr = cur.quick( "SELECT column_name, data_type, data_precision, data_scale, nullable, char_length from all_tab_cols where table_name='%s' order by column_name" % (table.upper()), array=True) return arr
def GetTileDefs(args, strtype='|S12'): #t = esutil.io.read(args.tiles)[args.tilecol][0:2] t = esutil.io.read(args.tiles)[args.tilecol] tindex = np.arange(len(t)) tiles = np.empty(len(t), dtype=[('tilename', strtype), ('index', np.int64)]) tiles['tilename'] = t.astype(strtype) tiles['index'] = np.arange(len(t)) if args.density is not None: for tile in tiles['tilename']: outdir = os.path.join(args.outdir, tile) if not os.path.exists(outdir): os.makedirs(outdir) cur = desdb.connect() q = "select urall, uraur, udecll, udecur, tilename from coaddtile order by udecll desc, urall asc" arr = cur.quick(q, array=True) dt = arr.dtype.descr dt[-1] = ('tilename', strtype) dt = np.dtype(dt) newarr = np.empty(len(arr), dtype=dt) for i in range(len(arr.dtype.names)): name = arr.dtype.names[i] if i == 4: newarr[name] = arr[name].astype(strtype) else: newarr[name] = arr[name] tiles = rec.join_by('tilename', newarr, tiles, usemask=False) tiles = np.sort(tiles, order='index') return tiles
def DoCount(ramin, ramax, decmin, decmax, hpindex, select): cur = desdb.connect() nonzero = np.zeros( len(hpindex), dtype=np.bool_) for i in range(len(hpindex)): q = "SELECT count(*) as count from balrog_%s_truth_%s where dec between %f and %f and ra between %f and %f" %(select['table'], select['bands'][0], decmin[i],decmax[i],ramin[i],ramax[i]) arr = cur.quick(q, array=True) if arr['count'][0] > 0: nonzero[i] = True return nonzero
def Work(args, rank): cur = desdb.connect() while True: cmd = MPI.COMM_WORLD.sendrecv([rank, 0], dest=0, source=0) if cmd==-1: break else: GetData(args, cmd, args.cols[0], args.cols[1], args.cols[2], cur, rank) MPI.COMM_WORLD.send([rank, -1], dest=0)
def QuerySFW(s, f, w, select, where): cur = desdb.connect() if where != None: for i in range(len(select['bands'])): ww = 'truth_%s.%s' %(select['bands'][i],where) w.insert(0, ww) selects = 'SELECT %s' %(', '.join(s)) froms = 'FROM %s' %(', '.join(f)) wheres = 'WHERE %s' %(' and '.join(w)) q = '%s %s %s' %(selects, froms, wheres) return cur.quick(q, array=True)
def GetBalrog(select, truthwhere='', simwhere=''): cur = desdb.connect() q = GetQuery(select, truthwhere, simwhere, kind='truth') truth = cur.quick(q, array=True) q = GetQuery(select, truthwhere, simwhere, kind='sim') sim = cur.quick(q, array=True) q = GetQuery(select, truthwhere, simwhere, kind='nosim') nosim = cur.quick(q, array=True) return [truth, sim, nosim]
def GetDES(select, where='', auto='coadd_objects_id'): t1 = time.time() cur = desdb.connect() ss = [] for band in select['bands']: for sel in select['sim']: if sel!='number_sex': ss.append('%s_%s' %(sel,band)) ss.append('%s as %s_%s'%(auto,auto,band)) ss = ', '.join(ss) q = """SELECT %s FROM %s %s"""%(ss, select['des'], where) des = cur.quick(q, array=True) print where, time.time()-t1 return des
def _add(tables, user='******'): cur = _desdb.connect() count = 0 for n in tables: q = """SELECT SUM(bytes), SUM(bytes) B from dba_extents where owner='%s' and segment_name='%s'""" % ( user.upper(), n) ''' all = cur.quick(q, array=True) count += all['b'][0] ''' try: all = cur.quick(q, array=True) count += all['b'][0] print n, ' ', all['b'][0] / _np.power(1024.0, 3), 'GB' except: pass print count / _np.power(1024.0, 3), 'GB'
def WaitExistence(RunConfig, runlog): cur = desdb.connect() user = cur.username donenum = 4 runlog.info('Making sure that the tables I need exist, and waiting for the first process if necessary...') while True: if os.path.exists(RunConfig['failfile']): raise Exception("The first process failed, Doesn't matter if DBs exist. Exiting.") count = 0 arr = cur.quick("select table_name from dba_tables where owner='%s'" %(user.upper()), array=True) tables = arr['table_name'] for kind in ['truth', 'nosim', 'sim', 'des']: tab = 'balrog_%s_%s' %(RunConfig['dbname'], kind) if (tab.upper() in tables): count += 1 if count==donenum: break runlog.info('Ok')
def Drop(tables): """ Delete tables from the DB. (You must own them.) Parameters ---------- tables (list) A list, where each entry is a (str) table name Returns ------- None """ cur = _desdb.connect() for table in tables: print table cur.quick("DROP TABLE %s PURGE" % table) cur.commit()
def Drop(tables): """ Delete tables from the DB. (You must own them.) Parameters ---------- tables (list) A list, where each entry is a (str) table name Returns ------- None """ cur = _desdb.connect() for table in tables: print table cur.quick("DROP TABLE %s PURGE" % table) cur.commit()
def GetFiles2(config): tiles = np.array(config['tiles'], dtype='|S12') df = desdb.files.DESFiles(fs='net') bands = runbalrog.PrependDet(config['run']) conn = desdb.connect() images = [] psfs = [] bs = [] skipped = [] usetiles = [] for i in range(len(tiles)): for j in range(len(bands)): band = bands[j] if band=='det': d = conn.quick("SELECT c.run from coadd c, runtag rt where rt.run=c.run and c.tilename='%s' and rt.tag='%s' and c.band is null" %(tiles[i], config['run']['release'].upper()), array=True ) else: d = conn.quick("SELECT c.run from coadd c, runtag rt where rt.run=c.run and c.tilename='%s' and rt.tag='%s' and c.band='%s'" %(tiles[i], config['run']['release'].upper(), band), array=True ) if len(d)==0: if band=='det': skipped.append(tiles[i]) break else: continue if j==0: usetiles.append(tiles[i]) psfs.append([]) images.append([]) bs.append([]) run = d[0]['run'] img = df.url('coadd_image', coadd_run=run, tilename=tiles[i], band=band) images[-1].append(img) psfs[-1].append(img.replace('.fits.fz', '_psfcat.psf')) bs[-1].append(band) return [images, psfs, usetiles, bs, skipped]
def _add(tables, user="******"): cur = _desdb.connect() count = 0 for n in tables: q = """SELECT SUM(bytes), SUM(bytes) B from dba_extents where owner='%s' and segment_name='%s'""" % ( user.upper(), n, ) """ all = cur.quick(q, array=True) count += all['b'][0] """ try: all = cur.quick(q, array=True) count += all["b"][0] print n, " ", all["b"][0] / _np.power(1024.0, 3), "GB" except: pass print count / _np.power(1024.0, 3), "GB"
def GetDESViaTileQuery(select, limit=None): cur = desdb.connect() arr = cur.quick('SELECT unique(tilename) from balrog_%s_truth_%s' %(select['table'],select['bands'][0]), array=True) tiles = arr['tilename'] if limit!=None: tiles = tiles[0:limit] print len(tiles), 'tiles' arr = [] for tile in tiles: where = "where tilename='%s'"%(tile) des = GetDES(select, where=where) arr.append(des) print 'stacking' for i in range(len(arr)): if i==0: out = arr[i] else: out = recfunctions.stack_arrays( (out, arr[i]), usemask=False) return out
def GetQuota(user=None): """ Get a user's DB quota (in GB). Parameters ---------- user (str) The username whose quota you want to return. None means use your personal username (read from the .netrc file). Returns ------- quota (float) The user's quota in GB. """ if user is None: user, pwd = _dbfunctions.retrieve_login(_dbfunctions.db_specs.db_host) cur = _desdb.connect() q = "SELECT USERNAME, TABLESPACE_NAME, MAX_BYTES from DBA_TS_QUOTAS WHERE USERNAME='******'" % (user.upper()) all = cur.quick(q, array=True) return all["max_bytes"][0] / _np.power(1024.0, 3)
def GetAllViaTileQuery(select, limit=None): cur = desdb.connect() if MPI.COMM_WORLD.Get_rank()==0: arr = cur.quick('SELECT unique(tilename) from balrog_%s_truth_%s' %(select['table'],select['bands'][0]), array=True) tiles = arr['tilename'] if limit!=None: tiles = tiles[0:limit] print len(tiles), 'tiles' else: tiles = None tiles = mpifunctions.Scatter(tiles) arr = [] for tile in tiles: t = time.time() where = "where tilename='%s'"%(tile) truth, sim, nosim = GetBalrog(select, truthwhere=where, simwhere=where) des = GetDES(select, where=where) arr.append([truth, sim, nosim, des]) print where, time.time() - t arr = MPI.COMM_WORLD.gather(arr, root=0) if MPI.COMM_WORLD.Get_rank()==0: newarr = [] for i in range(len(arr)): if len(arr[i])==0: continue for j in range(len(arr[i])): for k in range(len(arr[i][j])): if len(newarr) < len(arr[i][j]): newarr.append(arr[i][j][k]) else: newarr[k] = recfunctions.stack_arrays( (newarr[k], arr[i][j][k]), usemask=False) else: newarr = [None]*4 return newarr
def GetQuota(user=None): """ Get a user's DB quota (in GB). Parameters ---------- user (str) The username whose quota you want to return. None means use your personal username (read from the .netrc file). Returns ------- quota (float) The user's quota in GB. """ if user is None: user, pwd = _dbfunctions.retrieve_login(_dbfunctions.db_specs.db_host) cur = _desdb.connect() q = "SELECT USERNAME, TABLESPACE_NAME, MAX_BYTES from DBA_TS_QUOTAS WHERE USERNAME='******'" % ( user.upper()) all = cur.quick(q, array=True) return all['max_bytes'][0] / _np.power(1024., 3)
def TotalUsage(user=None): """ Find total DB usage for the user. Parameters ---------- user (str) The username whose usage you want to return. None means use your personal username (read from the .netrc file). Returns ------- usage (float) Usage in GB """ if user is None: user, pwd = _dbfunctions.retrieve_login(_dbfunctions.db_specs.db_host) cur = _desdb.connect() return cur.quick("""SELECT SUM(bytes) as b from dba_extents where owner='%s'""" % (user.upper()), array=True)["b"][ 0 ] / _np.power(1024.0, 3)
def TotalUsage(user=None): """ Find total DB usage for the user. Parameters ---------- user (str) The username whose usage you want to return. None means use your personal username (read from the .netrc file). Returns ------- usage (float) Usage in GB """ if user is None: user, pwd = _dbfunctions.retrieve_login(_dbfunctions.db_specs.db_host) cur = _desdb.connect() return cur.quick( """SELECT SUM(bytes) as b from dba_extents where owner='%s'""" % (user.upper()), array=True)['b'][0] / _np.power(1024.0, 3)
def get_cx_oracle_cursor(db_specs): c = desdb.connect() connection = cx_Oracle.connect( "%s/%s@(DESCRIPTION=(ADDRESS=(PROTOCOL=%s)(HOST=%s)(PORT=%s))(CONNECT_DATA=(SERVER=%s)(SERVICE_NAME=%s)))" %(c.username,c.password,db_specs['protocol'],db_specs['db_host'],db_specs['port'],db_specs['server'],db_specs['service_name']) ) cur = connection.cursor() return cur, connection
#!/usr/bin/env python import desdb import numpy as np import esutil if __name__ == "__main__": cur = desdb.connect() svtiles = esutil.io.read('../tiles/spt-sva1+y1a1-overlap-grizY.fits') bands = ['g', 'r', 'i', 'z', 'Y'] notcommon = {} s82 = {} for band in bands: s = cur.quick("SELECT c.run, c.tilename, c.ra, c.dec from coadd c, runtag rt where rt.run=c.run and rt.tag='SVA1_COADD_SPTE' and c.band='%s' ORDER BY c.dec DESC, c.ra ASC" %(band), array=True) y = cur.quick("SELECT c.run, c.tilename, c.ra, c.dec from coadd c, runtag rt where rt.run=c.run and rt.tag='Y1A1_COADD_SPT' and c.band='%s' ORDER BY c.dec DESC, c.ra ASC" %(band), array=True) #yonly = (-np.in1d(y['tilename'], svtiles['tilename'])) & (y['ra'] > 70) & (y['ra'] < 200) yonly = (np.in1d(y['tilename'], s['tilename'])) #& (y['ra'] > 70) & (y['ra'] < 200) notcommon[band] = y[yonly] tiles = notcommon[bands[0]]['tilename'] ras = notcommon[bands[0]]['ra'] decs = notcommon[bands[0]]['dec'] for band in bands[1:]: ts = notcommon[band]['tilename'] keep = np.in1d(tiles, ts) tiles = tiles[keep] ras = ras[keep]
def get_sqlldr_connection_info(db_specs): cur = desdb.connect() #return '%s/%s@"(DESCRIPTION=(ADDRESS=(PROTOCOL=%s)(HOST=%s)(PORT=%s))(CONNECT_DATA=(SERVER=%s)(SERVICE_NAME=%s)))"' %(cur.username,cur.password,db_specs['protocol'],db_specs['db_host'],db_specs['port'],db_specs['server'],db_specs['service_name']) return '%s/%s@"\(DESCRIPTION=\(ADDRESS=\(PROTOCOL=%s\)\(HOST=%s\)\(PORT=%s\)\)\(CONNECT_DATA=\(SERVER=%s\)\(SERVICE_NAME=%s\)\)\)"' %(cur.username,cur.password,db_specs['protocol'],db_specs['db_host'],db_specs['port'],db_specs['server'],db_specs['service_name'])
def NewWrite2DB2(bcats, labels, valids, RunConfig, BalrogConfig, DerivedConfig, required='i', missingfix='i'): singles = OneOnly() it = EnsureInt(DerivedConfig) create = False if it==-2: create = True cur = desdb.connect() cxcur, con = get_cx_oracle_cursor(DerivedConfig['db']) allbands = GetAllBands() creates = [] names = [] dobj = [] for j in range(len(allbands)): cats = bcats[j] for i in range(len(cats)): ext = 1 if labels[i]!='truth' and BalrogConfig['catfitstype']=='ldac': ext = 2 cat = cats[i] tablename = '%s.balrog_%s_%s' %(cur.username, RunConfig['dbname'], labels[i]) arr = NewMakeOracleFriendly(cats[i], ext, BalrogConfig, DerivedConfig, labels[i], RunConfig, missingfix=missingfix, create=create) noarr = False t = False if labels[i]=='truth': noarr = True t = True if create: creates, names = UpdateCreates(arr, tablename, creates, names, j, i, singles, allbands, RunConfig['db-columns'], required=required, truth=t) else: dobj = UpdateInserts(arr, tablename, noarr, j, i, allbands, dobj, singles, valids, RunConfig['db-columns'], required=required, truth=t) if create: for i in range(len(creates)): cur.quick(creates[i]) cur.quick("GRANT SELECT ON %s TO DES_READER" %names[i]) else: for i in range(len(dobj)): nums = [] for j in range(len(dobj[i]['num'])): n = ':%i' %(dobj[i]['num'][j]) nums.append(n) numstr = ', '.join(nums) namestr = ', '.join(dobj[i]['name']) newarr = zip(*dobj[i]['list']) tablename = '%s.balrog_%s_%s' %(cur.username, RunConfig['dbname'], labels[i]) istr = "insert into %s (%s) values (%s)" %(tablename, namestr, numstr) cxcur.prepare(istr) #print bcats, valids ''' try: cxcur.executemany(None, newarr) print 'good arr', newarr except: print 'bad arr', newarr ''' cxcur.executemany(None, newarr) con.commit() cxcur.close()
#!/usr/bin/env python import desdb import numpy as np import esutil if __name__ == "__main__": cur = desdb.connect() bands = ['g', 'r', 'i', 'z', 'Y'] release = 'SVA1_COADD_RXJ' tiles = {} for band in bands: y = cur.quick( "SELECT c.run, c.tilename, c.ra, c.dec from coadd c, runtag rt where rt.run=c.run and rt.tag='%s' and c.band='%s'ORDER BY c.dec DESC, c.ra ASC " % (release, band), array=True) tiles[band] = y['tilename'] stiles = tiles[bands[0]] for band in bands[1:]: sts = tiles[band] skeep = np.in1d(stiles, sts) stiles = stiles[skeep] data = np.zeros(len(stiles), dtype=[('tilename', '|S12')]) data['tilename'] = stiles esutil.io.write('%s-grizY.fits' % (release.lower()), data, clobber=True)
def GetCourseRange(select): cur = desdb.connect() arr = cur.quick("SELECT min(ra) as ramin, max(ra) as ramax, min(dec) as decmin, max(dec) as decmax FROM balrog_%s_truth_%s" %(select['table'],select['bands'][0]), array=True) return arr[0]