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 update(self, connection):
self.columns.pop(
'created_at', None
) # So that original timestamp is not overwritten with current one
update_planet_sql = MySQL.update(self.TABLE, self.columns.keys(),
['hip', 'name'], ['=', '='], ['AND'])
return super(Planet, self).update(update_planet_sql, connection)
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 update(self, connection):
self.columns.pop(
'created_at', None
) # So that original timestamp is not overwritten with current one
self.columns.pop('id', None) # ID should not be updated
update_catalogue = MySQL.update(self.TABLE, self.columns.keys(),
['author_year'], ['='])
return super(Catalogue, self).update(update_catalogue, connection)
def update(self, connection):
self.columns.pop(
'created_at', None
) # So that original timestamp is not overwritten with current one
update_composition = MySQL.update(
self.TABLE, self.columns.keys(),
['solarnorm', 'hip', 'cid', 'element'], ['=', '=', '=', '='],
['AND', 'AND', 'AND'])
return super(Composition, self).update(update_composition, connection)
def find(self, connection, only_one=True):
"""
Finds in the database for a star by HIP
:param connection: open connection
:param only_one: if True, only the first record returned by DB is returned, else all are returned
:return: record(s) if found, else None
"""
find_star = MySQL.select(self.TABLE, ['hip'], ['='])
return super(Star, self).find(find_star, {'hip': self.columns['hip']}, connection, only_one)
def find(self, connection, only_one=True):
"""
Finds in the database for a catalogue by author and year
:param connection: open connection
:param only_one: if True, only the first record returned by DB is returned, else all are returned
:return: record(s) if found, else None
"""
find_catalogue = MySQL.select(self.TABLE, ['author_year'], ['='])
return super(Catalogue, self).find(find_catalogue,
{'author_year': self.columns['author_year'].strip()}, connection, only_one)
def find(self, connection, only_one=True):
"""
Finds in the database for a catalogue by author and year
:param connection: open connection
:param only_one: if True, only the first record returned by DB is returned, else all are returned
:return: record(s) if found, else None
"""
find_catalogue = MySQL.select(self.TABLE, ['author_year'], ['='])
return super(Catalogue, self).find(
find_catalogue,
{'author_year': self.columns['author_year'].strip()}, connection,
only_one)
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 find(self, connection, only_one=True):
"""
Finds in the database for this composition
:param connection: open connection
:param only_one: if True, only the first record returned by DB is returned, else all are returned
:return: record(s) if found, else None
"""
find_composition = MySQL.select(self.TABLE, ['solarnorm', 'hip', 'cid', 'element'],
['=', '=', '=', '='], ['AND', 'AND', 'AND'])
return super(Composition, self).find(find_composition,
{'solarnorm': self.columns['solarnorm'], 'hip': self.columns['hip'],
'cid': self.columns['cid'], 'element': self.columns['element'].strip()},
connection, only_one)
def find(self, connection, only_one=True):
"""
Finds in the database for this planet
:param connection: open connection
:param only_one: if True, only the first record returned by DB is returned, else all are returned
:return: record(s) if found, else None
"""
find_planet_sql = MySQL.select(self.TABLE, ['hip', 'name'], ['=', '='],
['AND'])
return super(Planet, self).find(find_planet_sql, {
'hip': self.columns['hip'],
'name': self.columns['name']
}, connection, only_one)
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 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 save(self, connection):
"""
saves the changes to in-memory object to disk
:param connection: open connection to DB. Assumes auto-commit is OFF
:return: ID of the newly inserted entity
"""
self.columns['created_at'] = datetime.datetime.today()
self.columns['updated_at'] = self.columns['created_at']
add_entity = MySQL.insert(self.TABLE, self.columns.keys())
c = connection.cursor()
c.execute(add_entity, self.columns)
connection.commit() # TODO: Decide if this is OK as rollbacks can be difficult
c.close()
return c.lastrowid
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 find(self, connection, only_one=True):
"""
Finds in the database for this composition
:param connection: open connection
:param only_one: if True, only the first record returned by DB is returned, else all are returned
:return: record(s) if found, else None
"""
find_composition = MySQL.select(self.TABLE,
['solarnorm', 'hip', 'cid', 'element'],
['=', '=', '=', '='],
['AND', 'AND', 'AND'])
return super(Composition, self).find(
find_composition, {
'solarnorm': self.columns['solarnorm'],
'hip': self.columns['hip'],
'cid': self.columns['cid'],
'element': self.columns['element'].strip()
}, connection, only_one)
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 upload():
"""
Imports a file and saves to DB
:return:
"""
datafile = request.files['file']
c = MySQL.get_connection(DATABASE)
if datafile:
try:
logfile = os.path.splitext(datafile.filename)[0] + str(
int(time.time())) + '.log' # given name + current timestamp
f = logging.FileHandler(os.path.join(LOG_DIR, logfile), 'w')
Config.setup_logging(f)
filepath = os.path.join(UPLOADS_DIR, datafile.filename)
datafile.save(filepath) # to file system
Importer.run(filepath, c, {"normalization": request.form['normalization']})
logger.removeHandler(f)
f.close()
return jsonify({"name": datafile.filename, 'log': logfile})
finally:
c.close()
def upload():
"""
Imports a file and saves to DB
:return:
"""
datafile = request.files['file']
c = MySQL.get_connection(DATABASE)
if datafile:
try:
logfile = os.path.splitext(datafile.filename)[0] + str(
int(time.time())) + '.log' # given name + current timestamp
f = logging.FileHandler(os.path.join(LOG_DIR, logfile), 'w')
Config.setup_logging(f)
filepath = os.path.join(UPLOADS_DIR, datafile.filename)
datafile.save(filepath) # to file system
Importer.run(filepath, c,
{"normalization": request.form['normalization']})
logger.removeHandler(f)
f.close()
return jsonify({"name": datafile.filename, 'log': logfile})
finally:
c.close()
def main():
Config.setup_logging()
c = MySQL.get_connection('astronomy_test')
# run('./Assets/test_inp.txt', c, {"normalization": "Lodders et al. (2009)"})
run('./Assets/exo_test_inp.txt', c,
{"normalization": "Lodders et al. (2009)"})
def main():
Config.setup_logging()
c = MySQL.get_connection('astronomy_test')
# run('./Assets/test_inp.txt', c, {"normalization": "Lodders et al. (2009)"})
run('./Assets/exo_test_inp.txt', c, {"normalization": "Lodders et al. (2009)"})
def update(self, connection):
self.columns.pop('created_at', None) # So that original timestamp is not overwritten with current one
update_star = MySQL.update(self.TABLE, self.columns.keys(), ['hip'], ['='])
return super(Star, self).update(update_star, connection)
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 update(self, connection):
self.columns.pop('created_at', None) # So that original timestamp is not overwritten with current one
self.columns.pop('id', None) # ID should not be updated
update_catalogue = MySQL.update(self.TABLE, self.columns.keys(), ['author_year'], ['='])
return super(Catalogue, self).update(update_catalogue, connection)
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 update(self, connection):
self.columns.pop('created_at', None) # So that original timestamp is not overwritten with current one
update_composition = MySQL.update(self.TABLE, self.columns.keys(), ['solarnorm', 'hip', 'cid', 'element'],
['=', '=', '=', '='], ['AND', 'AND', 'AND'])
return super(Composition, self).update(update_composition, connection)