def stats_route(): with session_scope() as session: infos = {getattr(x, 'code'): {c.name: getattr(x, c.name) for c in x.__table__.columns if c.name not in ['id', 'code']} for x in session.query(Stat)} return json.dumps(infos)
def search_route(): query = request.args.get('q') per_page = request.args.get('per_page', 10, type=int) page = request.args.get('page', 0, type=int) if query is None: return json.dumps({'results':[]}) result = {} with session_scope() as session: results = session.query( City.name, City.country, func.ST_Y(cast(City.location, Geometry())), func.ST_X(cast(City.location, Geometry())), City.id ) \ .filter(unaccent(City.name).ilike(unaccent('%' + query + '%'))) \ .limit(per_page + 1) \ .offset(page * per_page) \ .all() more = len(results) == per_page + 1 results = results[:per_page] result = json.dumps({ 'results': [{'id': c[4], 'text': '{}, {}'.format(c[0], c[1]), 'coords': (c[2], c[3])} for i,c in enumerate(results)], 'more': more}) return result
parser.add_argument('input_file', help='parsed and augmented dump') parser.add_argument('--clear-cities', help='clears the cities before' ' entering new ones', action='store_true') parser.add_argument('--force-clear-cities', help='no prompt before clearing' ' cities', action='store_true') parser.add_argument('--fast-priority-index', help='faster but the cities ' ' look less nice (less spread out) on the map', action='store_true') parser.add_argument('--country', help='Will only keep cities in this' ' country and ignore the rest') parser.add_argument('--max-cities', '-m', type=int) args = parser.parse_args() configure_logging() with session_scope() as session: nb_cities = session.query(City).count() if args.force_clear_cities and not args.clear_cities: print('You must specify the --clear-cities argument if you want to' ' use the --force-clear-cities argument') if nb_cities > 0: if args.clear_cities: # Here we are erasing already existing cities before inserting new # ones. if args.force_clear_cities or are_you_sure( 'Are you sure you want to erase all the existing cities?'): with session_scope() as session: logger.info('deleting cities') session.query(MonthlyStat).delete()
description=description, name_short=name_short) session.add(s) if __name__ == '__main__': parser = argparse.ArgumentParser(description='Creates the database.' ' Only fills with the stat descriptions') parser.add_argument('--force-drop', action='store_true') args = parser.parse_args() configure_logging() db_name = DATABASE['name'] with session_scope(url=DATABASE_STR_WITUOUT_TABLE) as session: session.execute('commit') try: session.execute('create database ' + db_name) except ProgrammingError: # the database already exists if args.force_drop or are_you_sure( 'The database "{}" already exists, do you wish to DROP it?' .format(db_name)): session.execute('drop database ' + db_name) session.execute('create database ' + db_name) else: print('Did nothing.') sys.exit() with session_scope() as session:
name_short = name_short) session.add(s) if __name__ == '__main__': parser = argparse.ArgumentParser( description='Creates the database.' ' Only fills with the stat descriptions') parser.add_argument('--force-drop', action='store_true') args = parser.parse_args() configure_logging() db_name = DATABASE['name'] with session_scope(url=DATABASE_STR_WITUOUT_TABLE) as session: session.execute('commit') try: session.execute('create database ' + db_name) except ProgrammingError: # the database already exists if args.force_drop or are_you_sure( 'The database "{}" already exists, do you wish to DROP it?' .format(db_name)): session.execute('drop database ' + db_name) session.execute('create database ' + db_name) else: print('Did nothing.') sys.exit() with session_scope() as session:
def data_route(): west = request.args.get('w') east = request.args.get('e') north = request.args.get('n') south = request.args.get('s') month = request.args.get('m') nb = int(request.args.get('nb', config['max_nb_cities_at_once'])) if nb > config['max_nb_cities_at_once']: nb = config['max_nb_cities_at_once'] if west is None or east is None or south is None or north is None: return 'TODO 404' rectangle = 'POLYGON(({0} {1}, {0} {2}, {3} {2}, {3} {1}, {0} {1}))' \ .format(west, south, north, east) with session_scope() as session: # choose N cities sq = session.query(City) \ .filter(func.ST_Covers( cast(rectangle, Geometry()), func.ST_SetSRID(cast(City.location, Geometry()), 0))) \ .order_by(City.priority_index) \ .limit(nb).subquery('city') # get their data query = session.query( sq.c.id, sq.c.name, func.ST_Y(cast(sq.c.location, Geometry())), func.ST_X(cast(sq.c.location, Geometry())), sq.c.population, MonthlyStat.month, MonthlyStat.value, Stat.code, sq.c.country, sq.c.source) \ .join(MonthlyStat) \ .join(Stat) \ .filter(Stat.code.in_(['avgHigh', 'avgLow', 'precipitation', 'precipitationDays', 'monthlySunHours', 'rain', 'rainDays'])) if month is not None: query = query.filter(MonthlyStat.month == month) def default(): return {'month_stats': defaultdict(dict)} cities = defaultdict(default) # print(query) # format what is returned from the query for row in query: id = row[0] cities[id]['name'] = row[1] cities[id]['coords'] = (row[2], row[3]) cities[id]['pop'] = row[4] cities[id]['month_stats'][row[7]][row[5]] = row[6] cities[id]['country'] = row[8] cities[id]['source'] = row[9] # changing rain to precipitation # TODO something similar with snow? for _,c in cities.items(): for old, new in [('rain', 'precipitation'), ('rainDays', 'precipitationDays')]: if old in c['month_stats'] and new not in c['month_stats']: c['month_stats'][new] = c['month_stats'].pop(old) # from pprint import pprint # pprint(cities) return json.dumps(cities)
help='no prompt before clearing' ' cities', action='store_true') parser.add_argument('--fast-priority-index', help='faster but the cities ' ' look less nice (less spread out) on the map', action='store_true') parser.add_argument('--country', help='Will only keep cities in this' ' country and ignore the rest') parser.add_argument('--max-cities', '-m', type=int) args = parser.parse_args() configure_logging() with session_scope() as session: nb_cities = session.query(City).count() if args.force_clear_cities and not args.clear_cities: print('You must specify the --clear-cities argument if you want to' ' use the --force-clear-cities argument') if nb_cities > 0: if args.clear_cities: # Here we are erasing already existing cities before inserting new # ones. if args.force_clear_cities or are_you_sure( 'Are you sure you want to erase all the existing cities?'): with session_scope() as session: logger.info('deleting cities') session.query(MonthlyStat).delete()