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)