def get_available_params(self):
''' Retrieve the available parameters to query on
Retrieves a list of all the available query parameters.
Used primarily for the web autocompletion.
Parameters:
Returns:
mykeys (list):
a list of all of the available queryable parameters
'''
if self.mode == 'local':
keys = list(self.marvinform._param_form_lookup.keys())
keys.sort()
rev = {v: k for k, v in self.marvinform._param_form_lookup._tableShortcuts.items()}
# simplify the spaxelprop list down to one set
mykeys = [k.split('.', 1)[-1] for k in keys if 'cleanspaxel' not in k]
mykeys = [k.replace(k.split('.')[0], 'spaxelprop') if 'spaxelprop'
in k else k for k in mykeys]
# replace table names with shortcut names
newkeys = [k.replace(k.split('.')[0], rev[k.split('.')[0]]) if k.split('.')[0] in rev.keys() else k for k in mykeys]
return newkeys
elif self.mode == 'remote':
# Get the query route
url = config.urlmap['api']['getparams']['url']
params = {'paramdisplay': 'all'}
try:
ii = Interaction(route=url, params=params)
except MarvinError as e:
raise MarvinError('API Query call to get params failed: {0}'.format(e))
else:
mykeys = ii.getData()
return mykeys
def _getIdleProcesses(self):
''' Get a list of all idle processes on server
This grabs a list of all processes in a state of
idle, or idle in transaction using pg_stat_activity
and returns the process id, the state, and the query
'''
if self.mode == 'local':
sql = ("select p.pid,p.state,p.query from pg_stat_activity as p \
where p.state ilike '%idle%';")
res = self.session.execute(sql)
procs = res.fetchall()
elif self.mode == 'remote':
# Fail if no route map initialized
if not config.urlmap:
raise MarvinError('No URL Map found. Cannot make remote call')
# Get the query route
url = config.urlmap['api']['cleanupqueries']['url']
params = {'task': 'getprocs', 'release': self._release}
try:
ii = Interaction(route=url, params=params)
except Exception as e:
raise MarvinError('API Query call failed: {0}'.format(e))
else:
procs = ii.getData()
return procs
def getSubset(self, start, limit=10):
''' Extracts a subset of results
Parameters:
start (int):
The starting index of your subset extraction
limit (int):
The limiting number of results to return.
Returns:
results (list):
A list of query results
Example:
>>> r = q.run()
>>> r.getSubset(0, 10)
>>> [(u'14-12', u'1901', -9999.0),
>>> (u'14-13', u'1902', -9999.0),
>>> (u'27-134', u'1901', -9999.0),
>>> (u'27-100', u'1902', -9999.0),
>>> (u'27-762', u'1901', -9999.0),
>>> (u'27-759', u'1902', -9999.0),
>>> (u'27-827', u'1901', -9999.0),
>>> (u'27-828', u'1902', -9999.0),
>>> (u'27-1170', u'1901', -9999.0),
>>> (u'27-1167', u'1902', -9999.0)]
'''
start = 0 if int(start) < 0 else int(start)
end = start + int(limit)
# if end > self.count:
# end = self.count
# start = end - int(limit)
self.start = start
self.end = end
self.chunk = limit
if self.mode == 'local':
self.results = self.query.slice(start, end).all()
elif self.mode == 'remote':
# Fail if no route map initialized
if not config.urlmap:
raise MarvinError('No URL Map found. Cannot make remote call')
# Get the query route
url = config.urlmap['api']['getsubset']['url']
params = {'searchfilter': self.searchfilter, 'params': self.returnparams,
'start': start, 'end': end, 'limit': self.limit,
'sort': self.sortcol, 'order': self.order}
try:
ii = Interaction(route=url, params=params)
except MarvinError as e:
raise MarvinError('API Query GetNext call failed: {0}'.format(e))
else:
self.results = ii.getData()
self._makeNamedTuple()
if self.returntype:
self.convertToTool()
return self.results
def _get_from_remote(self):
''' Get the keys from a remote source '''
from marvin.api.api import Interaction
from brain import bconfig
# if not a supported release, don't try to get params
if not is_supported_release(self.release):
return
# if not urlmap then exit
if not config.urlmap:
self._keys = []
return
# try to get the url
try:
url = config.urlmap['api']['getparams']['url']
except Exception as e:
warnings.warn(
'Cannot access Marvin API to get the full list of query parameters. '
'for the Query Datamodel. Only showing the best ones.',
MarvinUserWarning)
url = None
self._keys = []
#self._cleanup_keys()
# make the call
if url:
try:
ii = Interaction(url,
params={
'release': self.release,
'paramdisplay': 'all'
},
base=bconfig._collab_api_url)
except Exception as e:
warnings.warn(
'Could not remotely retrieve full set of parameters. {0}'.
format(e), MarvinUserWarning)
self._keys = []
else:
# this deals with all parameters from all releases at once
# PARAM_CACHE.update(ii.getData())
# self._check_aliases()
# for key in list(PARAM_CACHE.keys()):
# keys = PARAM_CACHE[key] if key in PARAM_CACHE else []
# self._remove_query_params(keys=keys)
# this deals with parameters per release
self._keys = ii.getData()
PARAM_CACHE[self.release] = self._keys
self._check_aliases()
self._remove_query_params()
def _cleanUpQueries(self):
''' Attempt to clean up idle queries on the server
This is a hack to try to kill all idl processes on the server.
Using pg_terminate_backend and pg_stat_activity it terminates all
transactions that are in an idle, or idle in transaction, state
that have running for > 1 minute, and whose application_name is
not psql, and the process is not the one initiating the terminate.
The rank part ranks the processes and originally killed all > 1, to
leave one alive as a warning to the others. I've changed this to 0
to kill everything.
I think this will sometimes also leave a newly orphaned idle
ROLLBACK transaction. Not sure why.
'''
if self.mode == 'local':
sql = ("with inactive as (select p.pid, rank() over (partition by \
p.client_addr order by p.backend_start ASC) as rank from \
pg_stat_activity as p where p.application_name !~ 'psql' \
and p.state ilike '%idle%' and p.pid <> pg_backend_pid() and \
current_timestamp-p.state_change > interval '1 minutes') \
select pg_terminate_backend(pid) from inactive where rank > 0;")
self.session.expire_all()
self.session.expunge_all()
res = self.session.execute(sql)
tmp = res.fetchall()
#self.session.close()
#marvindb.db.engine.dispose()
elif self.mode == 'remote':
# Fail if no route map initialized
if not config.urlmap:
raise MarvinError('No URL Map found. Cannot make remote call')
# Get the query route
url = config.urlmap['api']['cleanupqueries']['url']
params = {'task': 'clean', 'release': self._release}
try:
ii = Interaction(route=url, params=params)
except Exception as e:
raise MarvinError('API Query call failed: {0}'.format(e))
else:
res = ii.getData()
def get_best_params(self):
''' Retrieves a list of best parameters to query on '''
if self.mode == 'local':
keys = self.get_available_params()
bestkeys = self._read_best_params()
return bestkeys
elif self.mode == 'remote':
# Get the query route
url = config.urlmap['api']['getparams']['url']
params = {'paramdisplay': 'best'}
try:
ii = Interaction(route=url, params=params)
except MarvinError as e:
raise MarvinError('API Query call to get params failed: {0}'.format(e))
else:
bestkeys = ii.getData()
return bestkeys
def _setup_mode(self):
''' Setup the mode the retrieve the query keys '''
if self._mode == 'local':
self._cleanup_keys()
elif self._mode == 'remote':
from marvin.api.api import Interaction
# try to get the url
try:
url = config.urlmap['api']['getparams']['url']
except Exception as e:
warnings.warn(
'Cannot access Marvin API to get the full list of query parameters. '
'for the Query Datamodel. Only showing the best ones.',
MarvinUserWarning)
url = None
self._cleanup_keys()
# make the call
if url:
try:
ii = Interaction(url,
params={
'release': self.release,
'paramdisplay': 'all'
})
except Exception as e:
warnings.warn(
'Could not remotely retrieve full set of parameters. {0}'
.format(e), MarvinUserWarning)
self._keys = []
else:
self._keys = ii.getData()
self._remove_query_params()
elif self._mode == 'auto':
if config.db:
self._mode = 'local'
else:
self._mode = 'remote'
self._setup_mode()
def get_nsa_data(mangaid, source='nsa', mode='auto', drpver=None, drpall=None):
"""Returns a dictionary of NSA data from the DB or from the drpall file.
Parameters:
mangaid (str):
The mangaid of the target for which the NSA information will be returned.
source ({'nsa', 'drpall'}):
The data source. If ``source='nsa'``, the full NSA catalogue from the DB will
be used. If ``source='drpall'``, the subset of NSA columns included in the drpall
file will be returned.
mode ({'auto', 'local', 'remote'}):
See :ref:`mode-decision-tree`.
drpver (str or None):
The version of the DRP to use, if ``source='drpall'``. If ``None``, uses the
version set by ``marvin.config.release``.
drpall (str or None):
A path to the drpall file to use if ``source='drpall'``. If not defined, the
default drpall file matching ``drpver`` will be used.
Returns:
nsa_data (dict):
A dictionary containing the columns and values from the NSA catalogue for
``mangaid``.
"""
from marvin import config, marvindb
from marvin.core.core import DotableCaseInsensitive
valid_modes = ['auto', 'local', 'remote']
assert mode in valid_modes, 'mode must be one of {0}'.format(valid_modes)
valid_sources = ['nsa', 'drpall']
assert source in valid_sources, 'source must be one of {0}'.format(
valid_sources)
log.debug(
'get_nsa_data: getting NSA data for mangaid=%r with source=%r, mode=%r',
mangaid, source, mode)
if mode == 'auto':
log.debug('get_nsa_data: running auto mode mode.')
try:
nsa_data = get_nsa_data(mangaid,
mode='local',
source=source,
drpver=drpver,
drpall=drpall)
return nsa_data
except MarvinError as ee:
log.debug('get_nsa_data: local mode failed with error %s', str(ee))
try:
nsa_data = get_nsa_data(mangaid,
mode='remote',
source=source,
drpver=drpver,
drpall=drpall)
return nsa_data
except MarvinError as ee:
raise MarvinError(
'get_nsa_data: failed to get NSA data for mangaid=%r in '
'auto mode with with error: %s', mangaid, str(ee))
elif mode == 'local':
if source == 'nsa':
if config.db is not None:
session = marvindb.session
sampledb = marvindb.sampledb
nsa_row = session.query(sampledb.NSA).join(
sampledb.MangaTargetToNSA, sampledb.MangaTarget).filter(
sampledb.MangaTarget.mangaid == mangaid).all()
if len(nsa_row) == 1:
return DotableCaseInsensitive(
_db_row_to_dict(nsa_row[0],
remove_columns=['pk', 'catalogue_pk']))
elif len(nsa_row) > 1:
warnings.warn(
'get_nsa_data: multiple NSA rows found for mangaid={0}. '
'Using the first one.'.format(mangaid),
MarvinUserWarning)
return DotableCaseInsensitive(
_db_row_to_dict(nsa_row[0],
remove_columns=['pk', 'catalogue_pk']))
elif len(nsa_row) == 0:
raise MarvinError(
'get_nsa_data: cannot find NSA row for mangaid={0}'.
format(mangaid))
else:
raise MarvinError(
'get_nsa_data: cannot find a valid DB connection.')
elif source == 'drpall':
plateifu = mangaid2plateifu(mangaid,
drpver=drpver,
drpall=drpall,
mode='drpall')
log.debug('get_nsa_data: found plateifu=%r for mangaid=%r',
plateifu, mangaid)
drpall_row = get_drpall_row(plateifu, drpall=drpall, drpver=drpver)
nsa_data = collections.OrderedDict()
for col in drpall_row.colnames:
if col.startswith('nsa_'):
value = drpall_row[col]
if isinstance(value, np.ndarray):
value = value.tolist()
else:
value = np.asscalar(value)
nsa_data[col[4:]] = value
return DotableCaseInsensitive(nsa_data)
elif mode == 'remote':
from marvin.api.api import Interaction
try:
if source == 'nsa':
request_name = 'nsa_full'
else:
request_name = 'nsa_drpall'
url = marvin.config.urlmap['api'][request_name]['url']
response = Interaction(url.format(mangaid=mangaid))
except MarvinError as ee:
raise MarvinError('API call to {0} failed: {1}'.format(
request_name, str(ee)))
else:
if response.results['status'] == 1:
return DotableCaseInsensitive(
collections.OrderedDict(response.getData()))
else:
raise MarvinError('get_nsa_data: %s', response['error'])
def mangaid2plateifu(mangaid, mode='auto', drpall=None, drpver=None):
"""Returns the plate-ifu for a certain mangaid.
Uses either the DB or the drpall file to determine the plate-ifu for
a mangaid. If more than one plate-ifu are available for a certain ifu,
and ``mode='drpall'``, the one with the higher SN2 (calculated as the sum
of redSN2 and blueSN2) will be used. If ``mode='db'``, the most recent one
will be used.
Parameters:
mangaid (str):
The mangaid for which the plate-ifu will be returned.
mode ({'auto', 'drpall', 'db', 'remote'}):
If `'drpall'` or ``'db'``, the drpall file or the local database,
respectively, will be used. If ``'remote'``, a request to the API
will be issued. If ``'auto'``, the local modes will be tried before
the remote mode.
drpall (str or None):
The path to the drpall file to use. If None, the file in
``config.drpall`` will be used.
drpver (str or None):
The DRP version to use. If None, the one in ``config.drpver`` will
be used. If ``drpall`` is defined, this value is ignored.
Returns:
plateifu (str):
The plate-ifu string for the input ``mangaid``.
"""
from marvin import config, marvindb
from marvin.api.api import Interaction
# The modes and order over which the auto mode will loop.
autoModes = ['db', 'drpall', 'remote']
assert mode in autoModes + ['auto'], 'mode={0} is not valid'.format(mode)
config_drpver, __ = config.lookUpVersions()
drpver = drpver if drpver else config_drpver
drpall = drpall if drpall else config._getDrpAllPath(drpver=drpver)
if mode == 'drpall':
if not drpall:
raise ValueError('no drpall file can be found.')
# Loads the drpall table if it was not cached from a previos session.
if drpver not in drpTable:
drpTable[drpver] = table.Table.read(drpall)
mangaids = np.array([mm.strip() for mm in drpTable[drpver]['mangaid']])
plateifus = drpTable[drpver][np.where(mangaids == mangaid)]
if len(plateifus) > 1:
warnings.warn(
'more than one plate-ifu found for mangaid={0}. '
'Using the one with the highest SN2.'.format(mangaid),
MarvinUserWarning)
plateifus = plateifus[[
np.argmax(plateifus['bluesn2'] + plateifus['redsn2'])
]]
if len(plateifus) == 0:
raise ValueError(
'no plate-ifus found for mangaid={0}'.format(mangaid))
return plateifus['plateifu'][0]
elif mode == 'db':
if not marvindb.isdbconnected:
raise MarvinError('no DB connection found')
if not drpver:
raise MarvinError('drpver not set.')
cubes = marvindb.session.query(marvindb.datadb.Cube).join(
marvindb.datadb.PipelineInfo,
marvindb.datadb.PipelineVersion).filter(
marvindb.datadb.Cube.mangaid == mangaid,
marvindb.datadb.PipelineVersion.version == drpver).all()
if len(cubes) == 0:
raise ValueError(
'no plate-ifus found for mangaid={0}'.format(mangaid))
elif len(cubes) > 1:
warnings.warn(
'more than one plate-ifu found for mangaid={0}. '
'Using a the one with the higest SN2'.format(mangaid),
MarvinUserWarning)
total_sn2 = [
float(cube.header['BLUESN2']) + float(cube.header['REDSN2'])
for cube in cubes
]
cube = cubes[np.argmax(total_sn2)]
else:
cube = cubes[0]
return '{0}-{1}'.format(cube.plate, cube.ifu.name)
elif mode == 'remote':
try:
# response = Interaction('api/general/mangaid2plateifu/{0}/'.format(mangaid))
url = marvin.config.urlmap['api']['mangaid2plateifu']['url']
response = Interaction(url.format(mangaid=mangaid))
except MarvinError as e:
raise MarvinError(
'API call to mangaid2plateifu failed: {0}'.format(e))
else:
plateifu = response.getData(astype=str)
if not plateifu:
if 'error' in response.results and response.results['error']:
raise MarvinError(response.results['error'])
else:
raise MarvinError(
'API call to mangaid2plateifu failed with error unknown.')
return plateifu
elif mode == 'auto':
for mm in autoModes:
try:
plateifu = mangaid2plateifu(mangaid,
mode=mm,
drpver=drpver,
drpall=drpall)
return plateifu
except:
continue
raise MarvinError(
'mangaid2plateifu was not able to find a plate-ifu for '
'mangaid={0} either local or remotely.'.format(mangaid))
def run(self, qmode='all'):
''' Runs a Marvin Query
Runs the query and return an instance of Marvin Results class
to deal with results. Input qmode allows to perform
different sqlalchemy queries
Parameters:
qmode ({'all', 'one', 'first', 'count'}):
String indicating
Returns:
results (object):
An instance of the Marvin Results class containing the
results from the Query.
'''
if self.mode == 'local':
# Check for adding a sort
self._sortQuery()
# Check to add the cache
if self._caching:
from marvin.core.caching_query import FromCache
self.query = self.query.options(FromCache("default")).\
options(*marvindb.cache_bits)
# get total count, and if more than 150 results, paginate and only return the first 10
start = datetime.datetime.now()
count = self.query.count()
self.totalcount = count
if count > 1000:
query = self.query.slice(0, self.limit)
warnings.warn('Results contain more than 150 entries. Only returning first {0}'.format(self.limit), MarvinUserWarning)
else:
query = self.query
if qmode == 'all':
res = query.all()
elif qmode == 'one':
res = query.one()
elif qmode == 'first':
res = query.first()
elif qmode == 'count':
res = query.count()
# get the runtime
end = datetime.datetime.now()
self.runtime = (end - start)
# close the session and engine
#self.session.close()
#marvindb.db.engine.dispose()
return Results(results=res, query=self.query, count=count, mode=self.mode, returntype=self.returntype,
queryobj=self, totalcount=self.totalcount, chunk=self.limit, runtime=self.runtime)
elif self.mode == 'remote':
# Fail if no route map initialized
if not config.urlmap:
raise MarvinError('No URL Map found. Cannot make remote call')
# Get the query route
url = config.urlmap['api']['querycubes']['url']
params = {'searchfilter': self.searchfilter,
'params': self._returnparams,
'returntype': self.returntype,
'limit': self.limit,
'sort': self.sort, 'order': self.order,
'release': self._release}
try:
ii = Interaction(route=url, params=params)
except Exception as e:
# if a remote query fails for any reason, then try to clean them up
# self._cleanUpQueries()
raise MarvinError('API Query call failed: {0}'.format(e))
else:
res = ii.getData()
self.queryparams_order = ii.results['queryparams_order']
self.params = ii.results['params']
self.query = ii.results['query']
count = ii.results['count']
chunk = int(ii.results['chunk'])
totalcount = ii.results['totalcount']
runtime = ii.results['runtime']
# close the session and engine
#self.session.close()
#marvindb.db.engine.dispose()
print('Results contain of a total of {0}, only returning the first {1} results'.format(totalcount, count))
return Results(results=res, query=self.query, mode=self.mode, queryobj=self, count=count,
returntype=self.returntype, totalcount=totalcount, chunk=chunk, runtime=runtime)
def getPrevious(self, chunk=None):
''' Retrieve the previous chunk of results.
Returns a previous chunk of results from the query.
from start to end in units of chunk. Used with getNext
to paginate through a long list of results
Parameters:
chunk (int):
The number of objects to return
Returns:
results (list):
A list of query results
Example:
>>> r = q.run()
>>> r.getPrevious(5)
>>> Retrieving previous 5, from 30 to 35
>>> [(u'4-3988', u'1901', -9999.0),
>>> (u'4-3862', u'1902', -9999.0),
>>> (u'4-3293', u'1901', -9999.0),
>>> (u'4-3602', u'1902', -9999.0),
>>> (u'4-4602', u'1901', -9999.0)]
'''
newend = self.start
self.chunk = chunk if chunk else self.chunk
newstart = newend - self.chunk
if newstart < 0:
warnings.warn('You have reached the beginning.', MarvinUserWarning)
newstart = 0
newend = newstart + self.chunk
log.info('Retrieving previous {0}, from {1} to {2}'.format(self.chunk, newstart, newend))
if self.mode == 'local':
self.results = self.query.slice(newstart, newend).all()
elif self.mode == 'remote':
# Fail if no route map initialized
if not config.urlmap:
raise MarvinError('No URL Map found. Cannot make remote call')
# Get the query route
url = config.urlmap['api']['getsubset']['url']
params = {'searchfilter': self.searchfilter, 'params': self.returnparams,
'start': newstart, 'end': newend, 'limit': self.limit,
'sort': self.sortcol, 'order': self.order}
try:
ii = Interaction(route=url, params=params)
except MarvinError as e:
raise MarvinError('API Query GetNext call failed: {0}'.format(e))
else:
self.results = ii.getData()
self._makeNamedTuple()
self.start = newstart
self.end = newend
if self.returntype:
self.convertToTool()
return self.results
def sort(self, name, order='asc'):
''' Sort the set of results by column name
Sorts the results by a given parameter / column name. Sets
the results to the new sorted results.
Parameters:
name (str):
order ({'asc', 'desc'}):
Returns:
sortedres (list):
The listed of sorted results.
Example:
>>> r = q.run()
>>> r.getColumns()
>>> [u'mangaid', u'name', u'nsa.z']
>>> r.results
>>> [(u'4-3988', u'1901', -9999.0),
>>> (u'4-3862', u'1902', -9999.0),
>>> (u'4-3293', u'1901', -9999.0),
>>> (u'4-3602', u'1902', -9999.0),
>>> (u'4-4602', u'1901', -9999.0)]
>>> # Sort the results by mangaid
>>> r.sort('mangaid')
>>> [(u'4-3293', u'1901', -9999.0),
>>> (u'4-3602', u'1902', -9999.0),
>>> (u'4-3862', u'1902', -9999.0),
>>> (u'4-3988', u'1901', -9999.0),
>>> (u'4-4602', u'1901', -9999.0)]
>>> # Sort the results by IFU name in descending order
>>> r.sort('ifu.name', order='desc')
>>> [(u'4-3602', u'1902', -9999.0),
>>> (u'4-3862', u'1902', -9999.0),
>>> (u'4-3293', u'1901', -9999.0),
>>> (u'4-3988', u'1901', -9999.0),
>>> (u'4-4602', u'1901', -9999.0)]
'''
refname = self._getRefName(name)
self.sortcol = refname
self.order = order
if self.mode == 'local':
reverse = True if order == 'desc' else False
sortedres = sorted(self.results, key=lambda row: row.__getattribute__(refname), reverse=reverse)
self.results = sortedres
elif self.mode == 'remote':
# Fail if no route map initialized
if not config.urlmap:
raise MarvinError('No URL Map found. Cannot make remote call')
# Get the query route
url = config.urlmap['api']['querycubes']['url']
params = {'searchfilter': self.searchfilter, 'params': self.returnparams,
'sort': refname, 'order': order, 'limit': self.limit}
try:
ii = Interaction(route=url, params=params)
except MarvinError as e:
raise MarvinError('API Query Sort call failed: {0}'.format(e))
else:
self.results = ii.getData()
self._makeNamedTuple()
sortedres = self.results
return sortedres