def stars(page, limit):
"""
Retrieve requested number of stars of a given page satisfying a condition
:param page: page number
:param limit: number of stars in this page
:return:
"""
try:
# TODO: Possible SQL injection due to WHERE clause
query = "SELECT * FROM star WHERE {} ORDER BY `hip` LIMIT %s OFFSET %s".format(
request.args.get("query") or "1 = 1")
db_res = MySQL.execute(DATABASE, query, [limit, page * limit])
index_of_hip = db_res['columns'].index('hip')
resp = {
row[index_of_hip]: dict(
zip(db_res['columns'],
[str(t) if type(t) is bytearray else t for t in row]))
for row in db_res['rows']
}
return jsonify({
'stars': resp,
"status": {
"message": "Fetched %s stars" % (len(resp), )
}
})
except Exception as err:
logger.exception(err)
return jsonify({"status": {"message": "Something went wrong"}}), 500
def composition_scatter():
"""
Median composition of requested elements for the requested star considering common catalogs
POST BODY:
{
normalization: [required] the type of solar normalization
stars: [required] comma separated list of hips
elements: [required] comma separated list of elements for which compositions are required
catalogs: [optional] comma separated list of catalogs (author_year column) to exclude if provided, else all will be used
}
:return: {hip1: {FeH: {mdn: 0.5, avg: 0.56}, OH: {mdn: -0.07, avg: 0}, ...}, {hip2: {FeH: {mdn: 0.09, avg: 0.1}, ...}}
"""
try:
solarnorm = request.json['normalization']
stars = map(lambda s: s.strip(), request.json['stars'])
elements = map(lambda e: e.strip(), request.json['elements'])
catalogs = map(lambda c: c.strip(), request.json.get('catalogs', []))
query = """SELECT
t1.hip,
t1.cid,
t1.element,
t1.value
FROM composition t1, catalogue c, composition t2
WHERE t1.solarnorm = '%s' AND t2.solarnorm = '%s'
AND t1.cid = t2.cid
AND t1.element <> t2.element
AND t1.hip = t2.hip
AND t1.hip IN (%s)
AND t1.element IN (%s)
AND t2.element IN (%s)
AND t1.cid = c.id %s;"""
in_str_stars = ','.join(['%s'] * len(stars))
in_str_elems = ','.join(['%s'] * len(elements))
in_str_cats = ','.join(['%s'] * len(catalogs))
catalog_query = 'AND c.author_year NOT IN (%s)' % in_str_cats if len(
catalogs) > 0 else ''
db_res = MySQL.execute(
DATABASE, query % (solarnorm, solarnorm, in_str_stars,
in_str_elems, in_str_elems, catalog_query),
stars + elements + elements + catalogs)
resp = {}
for row in db_res['rows']:
upsert_dict_arr(resp, row[0], row[2], row[3])
for star in resp:
for e in resp[star]:
resp[star][e] = {
'mdn': median(resp[star][e]),
'avg': mean(resp[star][e])
}
return jsonify({
'stars': resp,
"status": {
"message": "Fetched %s stars" % len(resp)
}
})
except Exception as err:
logger.exception(err)
return jsonify({"status": {"message": "Something went wrong"}}), 500
def elements_of_star(hip):
"""
Fetches the elements of a star
:param hip: hip of the star
:return:
"""
try:
query = "SELECT DISTINCT element FROM composition WHERE hip = %s"
res = map(lambda e: e[0], MySQL.execute(DATABASE, query, [hip])['rows'])
return jsonify({'elements': res})
except Exception as err:
logger.exception(err)
return jsonify({"status": {"message": "Something went wrong"}}), 500
def tuples_as_dict(query, entities, key_col):
"""
Fetches the tuples and converts to a dictionary based on the key column from the result
:param query: query as string
:param entities: input entities. Eg: hips
:param key_col: index of the key column in the returned tuples
:return: a dictionary with array of values for each key col
"""
in_str = ','.join(['%s'] * len(entities))
db_res = MySQL.execute(DATABASE, query % in_str, entities)
resp = {}
for r in db_res['rows']:
upsert(resp, r[key_col], dict(zip(db_res['columns'], r)))
return resp
def elements_of_star(hip):
"""
Fetches the elements of a star
:param hip: hip of the star
:return:
"""
try:
query = "SELECT DISTINCT element FROM composition WHERE hip = %s"
res = map(lambda e: e[0],
MySQL.execute(DATABASE, query, [hip])['rows'])
return jsonify({'elements': res})
except Exception as err:
logger.exception(err)
return jsonify({"status": {"message": "Something went wrong"}}), 500
def tuples_as_dict(query, entities, key_col):
"""
Fetches the tuples and converts to a dictionary based on the key column from the result
:param query: query as string
:param entities: input entities. Eg: hips
:param key_col: index of the key column in the returned tuples
:return: a dictionary with array of values for each key col
"""
in_str = ','.join(['%s'] * len(entities))
db_res = MySQL.execute(DATABASE, query % in_str, entities)
resp = {}
for r in db_res['rows']:
upsert(resp, r[key_col], dict(zip(db_res['columns'], r)))
return resp
def composition_scatter():
"""
Median composition of requested elements for the requested star considering common catalogs
POST BODY:
{
normalization: [required] the type of solar normalization
stars: [required] comma separated list of hips
elements: [required] comma separated list of elements for which compositions are required
catalogs: [optional] comma separated list of catalogs (author_year column) to exclude if provided, else all will be used
}
:return: {hip1: {FeH: {mdn: 0.5, avg: 0.56}, OH: {mdn: -0.07, avg: 0}, ...}, {hip2: {FeH: {mdn: 0.09, avg: 0.1}, ...}}
"""
try:
solarnorm = request.json['normalization']
stars = map(lambda s: s.strip(), request.json['stars'])
elements = map(lambda e: e.strip(), request.json['elements'])
catalogs = map(lambda c: c.strip(), request.json.get('catalogs', []))
query = """SELECT
t1.hip,
t1.cid,
t1.element,
t1.value
FROM composition t1, catalogue c, composition t2
WHERE t1.solarnorm = '%s' AND t2.solarnorm = '%s'
AND t1.cid = t2.cid
AND t1.element <> t2.element
AND t1.hip = t2.hip
AND t1.hip IN (%s)
AND t1.element IN (%s)
AND t2.element IN (%s)
AND t1.cid = c.id %s;"""
in_str_stars = ','.join(['%s'] * len(stars))
in_str_elems = ','.join(['%s'] * len(elements))
in_str_cats = ','.join(['%s'] * len(catalogs))
catalog_query = 'AND c.author_year NOT IN (%s)' % in_str_cats if len(catalogs) > 0 else ''
db_res = MySQL.execute(DATABASE, query % (solarnorm, solarnorm, in_str_stars, in_str_elems, in_str_elems,
catalog_query), stars + elements + elements + catalogs)
resp = {}
for row in db_res['rows']:
upsert_dict_arr(resp, row[0], row[2], row[3])
for star in resp:
for e in resp[star]:
resp[star][e] = {'mdn': median(resp[star][e]), 'avg': mean(resp[star][e])}
return jsonify({'stars': resp, "status": {"message": "Fetched %s stars" % len(resp)}})
except Exception as err:
logger.exception(err)
return jsonify({"status": {"message": "Something went wrong"}}), 500
def stars(page, limit):
"""
Retrieve requested number of stars of a given page satisfying a condition
:param page: page number
:param limit: number of stars in this page
:return:
"""
try:
# TODO: Possible SQL injection due to WHERE clause
query = "SELECT * FROM star WHERE {} ORDER BY `hip` LIMIT %s OFFSET %s".format(
request.args.get("query") or "1 = 1")
db_res = MySQL.execute(DATABASE, query, [limit, page * limit])
index_of_hip = db_res['columns'].index('hip')
resp = {row[index_of_hip]: dict(zip(db_res['columns'], [str(t) if type(t) is bytearray else t for t in row]))
for row in db_res['rows']}
return jsonify({'stars': resp, "status": {"message": "Fetched %s stars" % (len(resp),)}})
except Exception as err:
logger.exception(err)
return jsonify({"status": {"message": "Something went wrong"}}), 500
def compositions_of_star(hip):
"""
Retrieves composition of a star
If an element has multiple values from different catalogs, average value is returned
:param hip: hip of the star
:return: {FeH: 0.5, OH: -0.6}
"""
try:
elements = request.args.getlist('elements')
in_clause = ','.join(['%s'] * len(elements))
query = """SELECT element, AVG(value)
FROM composition WHERE hip = %s AND element IN ({})
GROUP BY element;""".format(in_clause)
res = {}
for k, v in MySQL.execute(DATABASE, query, [hip] + elements)['rows']:
res[k] = v
return jsonify(res)
except Exception as err:
logger.exception(err)
return jsonify({"status": {"message": "Something went wrong"}}), 500
def compositions_of_star(hip):
"""
Retrieves composition of a star
If an element has multiple values from different catalogs, average value is returned
:param hip: hip of the star
:return: {FeH: 0.5, OH: -0.6}
"""
try:
elements = request.args.getlist('elements')
in_clause = ','.join(['%s'] * len(elements))
query = """SELECT element, AVG(value)
FROM composition WHERE hip = %s AND element IN ({})
GROUP BY element;""".format(in_clause)
res = {}
for k, v in MySQL.execute(DATABASE, query, [hip] + elements)['rows']:
res[k] = v
return jsonify(res)
except Exception as err:
logger.exception(err)
return jsonify({"status": {"message": "Something went wrong"}}), 500
def _catalogs_for_ids(ids):
query = "SELECT id, author_year FROM catalogue WHERE id IN (%s);"
in_str = ','.join(['%s'] * len(ids))
db_res = MySQL.execute(DATABASE, query % in_str, ids)['rows']
return db_res
def _catalogs_for_ids(ids):
query = "SELECT id, author_year FROM catalogue WHERE id IN (%s);"
in_str = ','.join(['%s'] * len(ids))
db_res = MySQL.execute(DATABASE, query % in_str, ids)['rows']
return db_res