def top_occupations(year, bra_id):
cache_key = CAROUSEL_NS + "top_occupations" + str(year) + bra_id
prev = object_cache(cache_key)
if prev:
return pickle.loads(prev)
table = rais.Ybo
filters = [table.bra_id == bra_id, table.year == year]
raw = query_helper.query_table(table, [table.cbo_id],
filters,
order=table.wage_avg,
limit=10,
sort="desc")
cbos = [x[0] for x in raw["data"]]
table = raisd.Ybod
filters = [
table.bra_id == bra_id, table.year == year,
table.cbo_id.in_(cbos),
table.d_id.in_(["A", "B"])
]
columns = [
table.cbo_id, table.d_id, table.num_jobs, table.wage_avg,
table.wage_growth
]
results = query_helper.query_table(table,
columns,
filters,
order=table.wage_avg)
object_cache(cache_key, pickle.dumps(results))
return results
def secex_api(**kwargs):
limit = int(kwargs.pop("limit", 0)) or int(request.args.get("limit", 0))
order = request.args.get("order", None) or kwargs.pop("order", None)
sort = request.args.get("sort", None) or kwargs.pop("sort", "desc")
offset = request.args.get("offset", None) or kwargs.pop("offset", None)
if order and "." in order:
order, sort = order.split(".")
ignore_zeros = request.args.get("zeros", False) or kwargs.pop("zeros", False)
serialize = request.args.get("serialize", None) or kwargs.pop("serialize", True)
exclude = request.args.get("exclude", None) or kwargs.pop("exclude", None)
download = request.args.get("download", None) or kwargs.pop("download", None)
if not "month" in kwargs:
kwargs["month"] = query_helper.ALL
if exclude and "," in exclude:
exclude = exclude.split(",")
tables = [Ymw, Ymp, Ymb, Ympw, Ymbw, Ymbp, Ymbpw]
allowed_when_not, possible_tables = table_helper.prepare(["bra_id", "hs_id", "wld_id"], tables)
table = table_helper.select_best_table(kwargs, allowed_when_not, possible_tables)
filters, groups, show_column = query_helper.build_filters_and_groups(table, kwargs, exclude=exclude)
# if ignore_zeros:
# filters.append( getattr(table, "val_usd") > 0 )
results = query_helper.query_table(
table, filters=filters, groups=groups, limit=limit, order=order, sort=sort, offset=offset, serialize=serialize
)
if table is Ymbp:
stripped_filters, stripped_groups, show_column2 = query_helper.convert_filters(
Ymp, kwargs, remove=["bra_id", "month"]
)
stripped_columns = [Ymp.year, Ymp.hs_id, Ymp.pci]
stripped_filters.append(Ymp.month == 0)
tmp = query_helper.query_table(
Ymp, columns=stripped_columns, filters=stripped_filters, groups=stripped_groups, serialize=serialize
)
results["pci"] = tmp
stripped_filters, stripped_groups, show_column2 = query_helper.convert_filters(
Ymb, kwargs, remove=["hs_id", "month"]
)
stripped_columns = [Ymb.year, Ymb.bra_id, Ymb.eci]
stripped_filters.append(Ymb.month == 0)
tmp = query_helper.query_table(
Ymp, columns=stripped_columns, filters=stripped_filters, groups=stripped_groups, serialize=serialize
)
tmp = {d[0]: d[2] for d in tmp["data"]}
results["eci"] = tmp
if serialize or download:
response = jsonify(results)
if download:
return gen_csv(results, "secex")
return gzip_response(response)
return results
def rais_api(**kwargs):
limit = int(kwargs.pop('limit', 0)) or int(request.args.get('limit', 0) )
order = request.args.get('order', None) or kwargs.pop('order', None)
sort = request.args.get('sort', None) or kwargs.pop('sort', 'desc')
offset = request.args.get('offset', None) or kwargs.pop('offset', None)
if order and "." in order:
order, sort = order.split(".")
# ignore_zeros = request.args.get('zeros', True) or kwargs.pop('zeros', True)
serialize = request.args.get('serialize', None) or kwargs.pop('serialize', True)
exclude = request.args.get('exclude', None) or kwargs.pop('exclude', None)
download = request.args.get('download', None) or kwargs.pop('download', None)
required_bras = request.args.get('required_bras', False) or kwargs.pop('required_bras', False)
if required_bras:
bra_id = kwargs.get("bra_id")
cnae_id = kwargs.get("cnae_id").split(".")[0]
reqs = Ybi_reqs.query.filter(Ybi_reqs.bra_id == bra_id).filter(Ybi_reqs.cnae_id == cnae_id)
year = kwargs.get("year")
if year != "all":
reqs = reqs.filter(Ybi_reqs.year == year)
reqs = reqs.all()
results = {}
for req in reqs:
bras = [Bra.query.get(b).serialize() for b in req.required_bras.split(",")]
results[req.year] = bras
return jsonify(data=results)
if exclude and "," in exclude:
exclude = exclude.split(",")
allowed_when_not, possible_tables = table_helper.prepare(['bra_id', 'cnae_id', 'cbo_id'], [ Yb_rais, Yi, Yo, Ybi, Ybo, Yio, Ybio ] )
table = table_helper.select_best_table(kwargs, allowed_when_not, possible_tables)
filters, groups, show_column = query_helper.build_filters_and_groups(table, kwargs, exclude=exclude)
results = query_helper.query_table(table, filters=filters, groups=groups, limit=limit, order=order, sort=sort, offset=offset, serialize=serialize)
if table is Ybi:
stripped_filters, stripped_groups, show_column2 = query_helper.convert_filters(Yi, kwargs, remove=['bra_id'])
stripped_columns = [Yi.year, Yi.cnae_id, Yi.cbo_diversity, Yi.cbo_diversity_eff]
diversity_results = query_helper.query_table(Yi, columns=stripped_columns, filters=stripped_filters, groups=stripped_groups, limit=limit, order=order, sort=sort, serialize=serialize)
results["diversity"] = diversity_results
elif table is Ybo:
stripped_filters, stripped_groups, show_column2 = query_helper.convert_filters(Yo, kwargs, remove=['bra_id'])
stripped_columns = [Yo.year, Yo.cbo_id, Yo.cnae_diversity, Yo.cnae_diversity_eff]
diversity_results = query_helper.query_table(Yo, columns=stripped_columns, filters=stripped_filters, groups=stripped_groups, limit=limit, order=order, sort=sort, serialize=serialize)
results["diversity"] = diversity_results
if serialize or download:
response = jsonify(results)
if download:
return gen_csv(results, "rais")
return gzip_response(response)
return results
def rais_api(**kwargs):
limit = int(kwargs.pop('limit', 0)) or int(request.args.get('limit', 0) )
order = request.args.get('order', None) or kwargs.pop('order', None)
sort = request.args.get('sort', None) or kwargs.pop('sort', 'desc')
offset = request.args.get('offset', None) or kwargs.pop('offset', None)
if order and "." in order:
order, sort = order.split(".")
# ignore_zeros = request.args.get('zeros', True) or kwargs.pop('zeros', True)
serialize = request.args.get('serialize', None) or kwargs.pop('serialize', True)
exclude = request.args.get('exclude', None) or kwargs.pop('exclude', None)
download = request.args.get('download', None) or kwargs.pop('download', None)
required_bras = request.args.get('required_bras', False) or kwargs.pop('required_bras', False)
if required_bras:
bra_id = kwargs.get("bra_id")
cnae_id = kwargs.get("cnae_id").split(".")[0]
reqs = Ybi_reqs.query.filter(Ybi_reqs.bra_id == bra_id).filter(Ybi_reqs.cnae_id == cnae_id)
year = kwargs.get("year")
if year != "all":
reqs = reqs.filter(Ybi_reqs.year == year)
reqs = reqs.all()
results = {}
for req in reqs:
bras = [Bra.query.get(b).serialize() for b in req.required_bras.split(",")]
results[req.year] = bras
return jsonify(data=results)
if exclude and "," in exclude:
exclude = exclude.split(",")
allowed_when_not, possible_tables = table_helper.prepare(['bra_id', 'cnae_id', 'cbo_id'], [ Yb_rais, Yi, Yo, Ybi, Ybo, Yio, Ybio ] )
table = table_helper.select_best_table(kwargs, allowed_when_not, possible_tables)
filters, groups, show_column = query_helper.build_filters_and_groups(table, kwargs, exclude=exclude)
results = query_helper.query_table(table, filters=filters, groups=groups, limit=limit, order=order, sort=sort, offset=offset, serialize=serialize)
if table is Ybi:
stripped_filters, stripped_groups, show_column2 = query_helper.convert_filters(Yi, kwargs, remove=['bra_id'])
stripped_columns = [Yi.year, Yi.cnae_id, Yi.cbo_diversity, Yi.cbo_diversity_eff]
diversity_results = query_helper.query_table(Yi, columns=stripped_columns, filters=stripped_filters, groups=stripped_groups, limit=limit, order=order, sort=sort, serialize=serialize)
results["diversity"] = diversity_results
elif table is Ybo:
stripped_filters, stripped_groups, show_column2 = query_helper.convert_filters(Yo, kwargs, remove=['bra_id'])
stripped_columns = [Yo.year, Yo.cbo_id, Yo.cnae_diversity, Yo.cnae_diversity_eff]
diversity_results = query_helper.query_table(Yo, columns=stripped_columns, filters=stripped_filters, groups=stripped_groups, limit=limit, order=order, sort=sort, serialize=serialize)
results["diversity"] = diversity_results
if serialize or download:
response = jsonify(results)
if download:
return gen_csv(results, "rais")
return gzip_response(response)
return results
def hedu_api(**kwargs):
tables = [hedu.Yb_hedu, hedu.Yc_hedu, hedu.Yu, hedu.Ybc_hedu, hedu.Ybu, hedu.Yuc, hedu.Ybuc]
idonly = request.args.get('id', False) is not False
limit = int(request.args.get('limit', 0) or kwargs.pop('limit', 0))
order = request.args.get('order', None) or kwargs.pop('order', None)
sort = request.args.get('sort', None) or kwargs.pop('sort', 'desc')
offset = request.args.get('offset', None) or kwargs.pop('offset', None)
if order and "." in order:
order, sort = order.split(".")
serialize = request.args.get('serialize', None) or kwargs.pop('serialize', True)
exclude = request.args.get('exclude', None) or kwargs.pop('exclude', None)
download = request.args.get('download', None) or kwargs.pop('download', None)
if "university_id" in kwargs:
# -- there is no nesting for university ids
kwargs["university_id"] = kwargs["university_id"].replace("show.5", "show")
allowed_when_not, possible_tables = table_helper.prepare(['bra_id', 'university_id', 'course_hedu_id'], tables)
table = table_helper.select_best_table(kwargs, allowed_when_not, possible_tables)
filters, groups, show_column = query_helper.build_filters_and_groups(table, kwargs, exclude=exclude)
results = query_helper.query_table(table, filters=filters, groups=groups, limit=limit, order=order, sort=sort, offset=offset, serialize=serialize)
if serialize or download:
response = jsonify(results)
if download:
return gen_csv(results, "hedu")
return gzip_response(response)
return results
def ei_api(**kwargs):
tables = [Ymr, Yms, Ymsr]
limit = int(request.args.get('limit', 0) or kwargs.pop('limit', 0))
order = request.args.get('order', None) or kwargs.pop('order', None)
if order and "." in order:
order, sort = order.split(".")
sort = request.args.get('sort', None) or kwargs.pop('sort', 'desc')
offset = request.args.get('offset', None) or kwargs.pop('offset', None)
serialize = request.args.get('serialize', None) or kwargs.pop('serialize', True)
exclude = request.args.get('exclude', None) or kwargs.pop('exclude', None)
download = request.args.get('download', None) or kwargs.pop('download', None)
if not "month" in kwargs:
kwargs["month"] = query_helper.ALL
allowed_when_not, possible_tables = table_helper.prepare(['bra_id_r', 'bra_id_s', 'month', 'year'], tables)
table = table_helper.select_best_table(kwargs, allowed_when_not, possible_tables)
if not table:
raise Exception("No table!")
filters, groups, show_column = query_helper.build_filters_and_groups(table, kwargs, exclude=exclude)
results = query_helper.query_table(table, filters=filters, groups=groups, limit=limit, order=order, sort=sort, offset=offset, serialize=serialize)
if serialize or download:
response = jsonify(results)
if download:
response.headers["Content-Disposition"] = "attachment;filename=ei_data.json"
return response
return results
def top_occupations(year, bra_id):
cache_key = CAROUSEL_NS + "top_occupations" + str(year) + bra_id
prev = object_cache(cache_key)
if prev:
return pickle.loads(prev)
table = rais.Ybo
filters = [table.bra_id == bra_id, table.year == year]
raw = query_helper.query_table(table, [table.cbo_id], filters, order=table.wage_avg, limit=10, sort="desc")
cbos = [x[0] for x in raw["data"]]
table = raisd.Ybod
filters = [table.bra_id == bra_id, table.year == year, table.cbo_id.in_(cbos), table.d_id.in_(["A", "B"])]
columns = [table.cbo_id, table.d_id, table.num_jobs, table.wage_avg, table.wage_growth]
results = query_helper.query_table(table, columns, filters, order=table.wage_avg)
object_cache(cache_key, pickle.dumps(results))
return results
def compute_stats(metric, shows, limit=None, offset=None, sort="desc", depth=None, filters=[]):
cache_key = CAROUSEL_NS + "".join(([metric] + shows) + ([str(limit), str(offset),sort,str(depth)]))
prev = cached_query(cache_key)
if prev:
return pickle.loads(prev)
kwargs = {metric:"dummy"}
kwargs[shows[0]] = 'show'
for show in shows[1:]:
kwargs[show] = "dummy"
table = table_helper.select_best_table(kwargs, allowed_when_not, possible_tables)
if not table:
raise Exception("No Valid Table Available!")
filters = []
show_columns = [getattr(table, show) for show in shows]
metric_col = getattr(table, metric)
i = 0
for show_column in show_columns:
show=shows[i]
if table in no_length_column:
depth_val = depth or max_depth[show]
filters.append(func.char_length(show_column) == depth_val )
elif show in max_depth:
depth_val = depth or max_depth[show]
filters.append(getattr(table, show + table_helper.LEN) == depth_val )
i+=1
if table in filters_map:
filters += filters_map[table]
growth_regex = re.match('(num_emp)_growth(_5)?', metric)
VAL_THRESOLD = 10000
if growth_regex:
orig_col_name = growth_regex.group(1)
orig_col = getattr(table, orig_col_name)
filters.append(orig_col >= VAL_THRESOLD)
elif metric == "wage_avg" and len(shows) == 1 and shows[0] == "bra_id":
# when looking at wage_avg for cities, only look at places
# with >= 50k people
cities = cities_by_pop(50000)
filters.append(table.bra_id.in_(cities))
columns = show_columns + [metric_col]
results = query_helper.query_table(table, columns, filters, order=metric, limit=limit, sort=sort, offset=offset)
cached_query(cache_key, pickle.dumps(results))
return results
def hedu_api(**kwargs):
tables = [
hedu.Yb_hedu, hedu.Yc_hedu, hedu.Yu, hedu.Ybc_hedu, hedu.Ybu, hedu.Yuc,
hedu.Ybuc
]
idonly = request.args.get('id', False) is not False
limit = int(request.args.get('limit', 0) or kwargs.pop('limit', 0))
order = request.args.get('order', None) or kwargs.pop('order', None)
sort = request.args.get('sort', None) or kwargs.pop('sort', 'desc')
offset = request.args.get('offset', None) or kwargs.pop('offset', None)
if order and "." in order:
order, sort = order.split(".")
serialize = request.args.get('serialize', None) or kwargs.pop(
'serialize', True)
exclude = request.args.get('exclude', None) or kwargs.pop('exclude', None)
download = request.args.get('download', None) or kwargs.pop(
'download', None)
if "university_id" in kwargs:
# -- there is no nesting for university ids
kwargs["university_id"] = kwargs["university_id"].replace(
"show.5", "show")
allowed_when_not, possible_tables = table_helper.prepare(
['bra_id', 'university_id', 'course_hedu_id'], tables)
table = table_helper.select_best_table(kwargs, allowed_when_not,
possible_tables)
filters, groups, show_column = query_helper.build_filters_and_groups(
table, kwargs, exclude=exclude)
results = query_helper.query_table(table,
filters=filters,
groups=groups,
limit=limit,
order=order,
sort=sort,
offset=offset,
serialize=serialize)
if serialize or download:
response = jsonify(results)
if download:
return gen_csv(results, "hedu")
return gzip_response(response)
return results
def ei_api(**kwargs):
tables = [Ymr, Yms, Ymsr]
limit = int(request.args.get('limit', 0) or kwargs.pop('limit', 0))
order = request.args.get('order', None) or kwargs.pop('order', None)
if order and "." in order:
order, sort = order.split(".")
sort = request.args.get('sort', None) or kwargs.pop('sort', 'desc')
offset = request.args.get('offset', None) or kwargs.pop('offset', None)
serialize = request.args.get('serialize', None) or kwargs.pop(
'serialize', True)
exclude = request.args.get('exclude', None) or kwargs.pop('exclude', None)
download = request.args.get('download', None) or kwargs.pop(
'download', None)
if not "month" in kwargs:
kwargs["month"] = query_helper.ALL
allowed_when_not, possible_tables = table_helper.prepare(
['bra_id_r', 'bra_id_s', 'month', 'year'], tables)
table = table_helper.select_best_table(kwargs, allowed_when_not,
possible_tables)
if not table:
raise Exception("No table!")
filters, groups, show_column = query_helper.build_filters_and_groups(
table, kwargs, exclude=exclude)
results = query_helper.query_table(table,
filters=filters,
groups=groups,
limit=limit,
order=order,
sort=sort,
offset=offset,
serialize=serialize)
if serialize or download:
response = jsonify(results)
if download:
response.headers[
"Content-Disposition"] = "attachment;filename=ei_data.json"
return response
return results
def sc_api(**kwargs):
tables = [Yc_sc, Yb_sc, Ys, Ybc_sc, Ybs, Ysc, Ybsc]
idonly = request.args.get('id', False) is not False
limit = int(request.args.get('limit', 0) or kwargs.pop('limit', 0))
order = request.args.get('order', None) or kwargs.pop('order', None)
sort = request.args.get('sort', None) or kwargs.pop('sort', 'desc')
offset = request.args.get('offset', None) or kwargs.pop('offset', None)
if order and "." in order:
order, sort = order.split(".")
serialize = request.args.get('serialize', None) or kwargs.pop('serialize', True)
exclude = request.args.get('exclude', None) or kwargs.pop('exclude', None)
download = request.args.get('download', None) or kwargs.pop('download', None)
only_vocational = False
if "school_id" in kwargs:
# -- there is no nesting for school ids
kwargs["school_id"] = kwargs["school_id"].replace("show.8", "show")
only_vocational = "show" in kwargs["school_id"] and not "xx" in kwargs["course_sc_id"] and exclude == "xx%"
if only_vocational:
exclude = None
# -- 2. select table
allowed_when_not, possible_tables = table_helper.prepare(['bra_id', 'school_id', 'course_sc_id'], tables)
table = table_helper.select_best_table(kwargs, allowed_when_not, possible_tables)
if not table:
raise Exception("No table!")
filters, groups, show_column = query_helper.build_filters_and_groups(table, kwargs, exclude=exclude)
if only_vocational:
vschools = School.query.with_entities(School.id).filter(School.is_vocational == 1).all()
vschools = [v.id for v in vschools]
filters.append(table.school_id.in_(vschools))
results = query_helper.query_table(table, filters=filters, groups=groups, limit=limit, order=order, sort=sort, offset=offset, serialize=serialize)
if serialize or download:
response = jsonify(results)
if download:
return gen_csv(results, "sc")
return gzip_response(response)
return results
def compute_stats(metric,
shows,
limit=None,
offset=None,
sort="desc",
depth=None,
filters=[]):
cache_key = CAROUSEL_NS + "".join(
([metric] + shows) +
([str(limit), str(offset), sort,
str(depth)]))
prev = cached_query(cache_key)
if prev:
return pickle.loads(prev)
kwargs = {metric: "dummy"}
kwargs[shows[0]] = 'show'
for show in shows[1:]:
kwargs[show] = "dummy"
table = table_helper.select_best_table(kwargs, allowed_when_not,
possible_tables)
if not table:
raise Exception("No Valid Table Available!")
filters = []
show_columns = [getattr(table, show) for show in shows]
metric_col = getattr(table, metric)
i = 0
for show_column in show_columns:
show = shows[i]
if table in no_length_column:
depth_val = depth or max_depth[show]
filters.append(func.char_length(show_column) == depth_val)
elif show in max_depth:
depth_val = depth or max_depth[show]
filters.append(
getattr(table, show + table_helper.LEN) == depth_val)
i += 1
if table in filters_map:
filters += filters_map[table]
growth_regex = re.match('(num_emp)_growth(_5)?', metric)
VAL_THRESOLD = 10000
if growth_regex:
orig_col_name = growth_regex.group(1)
orig_col = getattr(table, orig_col_name)
filters.append(orig_col >= VAL_THRESOLD)
elif metric == "wage_avg" and len(shows) == 1 and shows[0] == "bra_id":
# when looking at wage_avg for cities, only look at places
# with >= 50k people
cities = cities_by_pop(50000)
filters.append(table.bra_id.in_(cities))
columns = show_columns + [metric_col]
results = query_helper.query_table(table,
columns,
filters,
order=metric,
limit=limit,
sort=sort,
offset=offset)
cached_query(cache_key, pickle.dumps(results))
return results
def secex_api(**kwargs):
limit = int(kwargs.pop('limit', 0)) or int(request.args.get('limit', 0))
order = request.args.get('order', None) or kwargs.pop('order', None)
sort = request.args.get('sort', None) or kwargs.pop('sort', 'desc')
offset = request.args.get('offset', None) or kwargs.pop('offset', None)
if order and "." in order:
order, sort = order.split(".")
ignore_zeros = request.args.get('zeros', False) or kwargs.pop(
'zeros', False)
serialize = request.args.get('serialize', None) or kwargs.pop(
'serialize', True)
exclude = request.args.get('exclude', None) or kwargs.pop('exclude', None)
download = request.args.get('download', None) or kwargs.pop(
'download', None)
if not "month" in kwargs:
kwargs["month"] = query_helper.ALL
if exclude and "," in exclude:
exclude = exclude.split(",")
tables = [Ymw, Ymp, Ymb, Ympw, Ymbw, Ymbp, Ymbpw]
allowed_when_not, possible_tables = table_helper.prepare(
['bra_id', 'hs_id', 'wld_id'], tables)
table = table_helper.select_best_table(kwargs, allowed_when_not,
possible_tables)
filters, groups, show_column = query_helper.build_filters_and_groups(
table, kwargs, exclude=exclude)
# if ignore_zeros:
# filters.append( getattr(table, "val_usd") > 0 )
results = query_helper.query_table(table,
filters=filters,
groups=groups,
limit=limit,
order=order,
sort=sort,
offset=offset,
serialize=serialize)
if table is Ymbp:
stripped_filters, stripped_groups, show_column2 = query_helper.convert_filters(
Ymp, kwargs, remove=['bra_id', 'month'])
stripped_columns = [Ymp.year, Ymp.hs_id, Ymp.pci]
stripped_filters.append(Ymp.month == 0)
tmp = query_helper.query_table(Ymp,
columns=stripped_columns,
filters=stripped_filters,
groups=stripped_groups,
serialize=serialize)
results["pci"] = tmp
stripped_filters, stripped_groups, show_column2 = query_helper.convert_filters(
Ymb, kwargs, remove=['hs_id', 'month'])
stripped_columns = [Ymb.year, Ymb.bra_id, Ymb.eci]
stripped_filters.append(Ymb.month == 0)
tmp = query_helper.query_table(Ymp,
columns=stripped_columns,
filters=stripped_filters,
groups=stripped_groups,
serialize=serialize)
tmp = {d[0]: d[2] for d in tmp["data"]}
results["eci"] = tmp
if serialize or download:
response = jsonify(results)
if download:
return gen_csv(results, "secex")
return gzip_response(response)
return results