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]
