class PumsIoCrosswalk(db.Model):
__tablename__ = 'naics_to_io'
__table_args__ = {"schema": "pums_attrs"}
pums_naics = db.Column(db.String, primary_key=True)
iocode = db.Column(db.String)
iocode_parent = db.Column(db.String)
class BaseAttr(db.Model):
__abstract__ = True
__table_args__ = {"schema": "attrs"}
id = db.Column(db.String(10), primary_key=True)
name = db.Column(db.String())
HEADERS = ["id", "name"]
@classmethod
def parents(cls, attr_id):
raise Exception("Not implemented!")
@classmethod
def children(cls, attr_id):
raise Exception("Not implemented!")
def serialize(self):
return {
key: val
for key, val in self.__dict__.items() if not key.startswith("_")
}
def data_serialize(self):
return [self.id, self.name]
def __repr__(self):
return '<{}, id: {}, name: {}>'.format(self.__class__, self.id,
self.name)
class PumsNaics(BaseAttr, ImageAttr):
__tablename__ = 'pums_naics'
__table_args__ = {"schema": "pums_attrs"}
id = db.Column(db.String(10), primary_key=True)
level = db.Column(db.Integer, primary_key=True)
parent = db.Column(db.String, db.ForeignKey(id))
grandparent = db.Column(db.String, db.ForeignKey(id))
@classmethod
def children(cls, naics_id, **kwargs):
objs = PumsNaics.query.filter(PumsNaics.parent == naics_id)
data = [[obj.id, obj.name] for obj in objs if obj.id != naics_id]
return data, PumsNaics.HEADERS
@classmethod
def parents(cls, naics_id, **kwargs):
naics_obj = PumsNaics.query.filter_by(id=naics_id).first()
tmp = []
if naics_obj and naics_obj.grandparent:
tmp.append(
PumsNaics.query.filter_by(id=naics_obj.grandparent).first())
if naics_obj and naics_obj.parent:
tmp.append(PumsNaics.query.filter_by(id=naics_obj.parent).first())
tmp = [[x.id, x.name] for x in tmp if x]
return tmp, PumsNaics.HEADERS
class TuitionYc(Tuition, CipId):
__tablename__ = "tuition_yc"
median_moe = 1
year = db.Column(db.Integer(), primary_key=True)
oos_tuition_rank = db.Column(db.Integer())
state_tuition_rank = db.Column(db.Integer())
class Cip(BaseAttr, ImageAttr):
__tablename__ = 'course'
level = db.Column(db.Integer)
name_long = db.Column(db.String)
is_stem = db.Column(db.Boolean)
@classmethod
def parents(cls, cip_id, **kwargs):
show_all = kwargs.get("show_all", True)
cips = []
if len(cip_id) >= 4:
cips.append(cip_id[:2])
if len(cip_id) == 6:
cips.append(cip_id[:4])
if not show_all:
cips = [cips[-1]]
cips = Cip.query.filter(Cip.id.in_(cips)).all()
return [attr.data_serialize() for attr in cips], Cip.HEADERS
@classmethod
def children(cls, cip_id, **kwargs):
show_all = kwargs.get("show_all", False)
sumlevel = kwargs.get("sumlevel", False)
filters = [Cip.id.startswith(cip_id), Cip.id != cip_id]
if not show_all:
# if we are not showing all children, then only display
# cip attrs of length (parent length) + 2
t_map = {0: 2, 1: 4, 2: 6}
target = len(cip_id) + 2
if sumlevel:
target = t_map[int(sumlevel[0])]
filters.append(func.length(Cip.id) == target)
cips = Cip.query.filter(*filters).distinct(Cip.id).all()
return [attr.data_serialize() for attr in cips], Cip.HEADERS
class OccCrosswalk(db.Model):
__tablename__ = 'occ_crosswalk'
__table_args__ = {"schema": "attrs"}
acs_occ = db.Column(db.String, primary_key=True)
pums_soc = db.Column(db.String,
db.ForeignKey(PumsSoc.id),
primary_key=True)
level = db.Column(db.Integer)
class ZipLookup(db.Model):
__tablename__ = 'zip_lookup'
__table_args__ = {"schema": "attrs"}
child_geoid = db.Column(db.String, db.ForeignKey(Geo.id), primary_key=True)
parent_geoid = db.Column(db.String, db.ForeignKey(Search.id),
primary_key=True)
percent_covered = db.Column(db.Float)
parent_area = db.Column(db.Float)
class IndCrosswalk(db.Model):
__tablename__ = 'ind_crosswalk'
__table_args__ = {"schema": "attrs"}
acs_ind = db.Column(db.String, primary_key=True)
pums_naics = db.Column(db.String,
db.ForeignKey(PumsNaics.id),
primary_key=True)
level = db.Column(db.Integer)
class GeoCrosswalker(db.Model):
__tablename__ = 'geo_crosswalker'
__table_args__ = {"schema": "attrs"}
geo_a = db.Column(db.String, db.ForeignKey(Geo.id), primary_key=True)
geo_b = db.Column(db.String, db.ForeignKey(Geo.id), primary_key=True)
a = relationship('Geo', foreign_keys='GeoCrosswalker.geo_a')
b = relationship('Geo',
foreign_keys='GeoCrosswalker.geo_b',
lazy='subquery')
class BaseFreight(db.Model, BaseModel):
__abstract__ = True
__table_args__ = {"schema": "freight"}
supported_levels = {}
source_title = 'Freight Analysis Framework'
source_link = 'https://www.rita.dot.gov/bts/sites/rita.dot.gov.bts/files/subject_areas/freight_transportation/faf'
source_org = 'Bureau of Transportation Statistics'
tons = db.Column(db.Float)
millions_of_2012_dollars = db.Column(db.Float)
class SkillByCip(BaseOnet, SkillId, CipId):
__tablename__ = "skills_by_cip"
median_moe = 1
value = db.Column(db.Float)
value_rca = db.Column(db.Float)
@classmethod
def get_supported_levels(cls):
return {"cip": ["2", "4", "6", "all"], "skill": ["all"]}
class GeoContainment(db.Model):
__tablename__ = 'geo_containment'
__table_args__ = {"schema": "attrs"}
child_geoid = db.Column(db.String, db.ForeignKey(Geo.id), primary_key=True)
parent_geoid = db.Column(db.String, db.ForeignKey(Geo.id),
primary_key=True)
percent_covered = db.Column(db.Float)
parent = relationship('Geo', foreign_keys='GeoContainment.parent_geoid')
child = relationship('Geo', foreign_keys='GeoContainment.child_geoid',
lazy='subquery')
class GradsYc(Grads, CipId):
__tablename__ = "grads_yc"
median_moe = 0
year = db.Column(db.Integer(), primary_key=True)
grads_rank = db.Column(db.Integer)
@classmethod
def get_supported_levels(cls):
return {"cip": CipId.LEVELS}
class GradsYud(Grads, UniversityId, DegreeId):
__tablename__ = "grads_yud"
median_moe = 2
year = db.Column(db.Integer(), primary_key=True)
grads_total = db.Column(db.Integer())
@classmethod
def get_supported_levels(cls):
return {"university": UniversityId.LEVELS, "degree": [ALL]}
class Ygl_Speakers(object):
median_moe = 2
num_speakers = db.Column(db.Float)
num_speakers_moe = db.Column(db.Float)
num_speakers_rca = db.Column(db.Float)
@declared_attr
def language(cls):
return db.Column(db.String(), db.ForeignKey(AcsLanguage.id), primary_key=True)
class TuitionYcs(Tuition, CipId, SectorId):
__tablename__ = "tuition_ycs"
median_moe = 1
year = db.Column(db.Integer(), primary_key=True)
num_universities = db.Column(db.Integer())
@classmethod
def get_supported_levels(cls):
return {"cip": CipId.LEVELS, "sector": [ALL]}
class TuitionYu(Tuition, UniversityId):
__tablename__ = "tuition_yu"
median_moe = 1
year = db.Column(db.Integer(), primary_key=True)
books_and_supplies = db.Column(db.Float())
@classmethod
def get_supported_levels(cls):
return {"university": UniversityId.LEVELS}
class ICMaxLivingExpensesYua(BaseIpeds, UniversityId):
__tablename__ = "ic_max_living_expenses_yu"
median_moe = 1
year = db.Column(db.Integer(), primary_key=True)
max_other_expenses = db.Column(db.Float())
max_room_and_board = db.Column(db.Float())
@classmethod
def get_supported_levels(cls):
return {"year": [ALL], "university": UniversityId.LEVELS}
class TuitionYcu(Tuition, CipId, UniversityId):
__tablename__ = "tuition_ycu"
median_moe = 2
year = db.Column(db.Integer(), primary_key=True)
grads_total = db.Column(db.Integer())
@classmethod
def get_supported_levels(cls):
return {"cip": CipId.LEVELS, "university": UniversityId.LEVELS}
class GradsYgs(Grads, GeoId, SectorId):
__tablename__ = "grads_ygs"
median_moe = 2
year = db.Column(db.Integer(), primary_key=True)
grads_total_growth = db.Column(db.Float)
@classmethod
def get_supported_levels(cls):
return {"sector": [ALL], GEO: GeoId.LEVELS}
class GradsYgu(Grads, GeoId, UniversityId):
__tablename__ = "grads_ygu"
median_moe = 2
year = db.Column(db.Integer(), primary_key=True)
grads_total_growth = db.Column(db.Float)
@classmethod
def get_supported_levels(cls):
return {"university": UniversityId.LEVELS, GEO: GeoId.LEVELS}
class GradsYg(Grads, GeoId):
__tablename__ = "grads_yg"
median_moe = 1
year = db.Column(db.Integer, primary_key=True)
grads_total_growth = db.Column(db.Float)
@classmethod
def get_supported_levels(cls):
return {GEO: GeoId.LEVELS}
class UniversityCrosswalk(db.Model, BaseModel):
__tablename__ = 'unitid_to_opeid6'
__table_args__ = {"schema": "attrs"}
opeid6 = db.Column(db.String, primary_key=True)
university = db.Column(db.String,
db.ForeignKey(University.id),
primary_key=True)
@classmethod
def get_supported_levels(cls):
return {"university": [ALL], "opeid": [ALL]}
class SocHierarchy(db.Model):
__tablename__ = 'pums_soc_hierarchy'
__table_args__ = {"schema": "hierarchies"}
great_grandparent = db.Column(db.String, db.ForeignKey(PumsSoc.id))
grandparent = db.Column(db.String, db.ForeignKey(PumsSoc.id))
parent = db.Column(db.String, db.ForeignKey(PumsSoc.id))
soc = db.Column(db.String, db.ForeignKey(PumsSoc.id), primary_key=True)
parent_obj = relationship('PumsSoc', foreign_keys='SocHierarchy.parent', lazy='subquery')
grandparent_obj = relationship('PumsSoc', foreign_keys='SocHierarchy.grandparent', lazy='subquery')
great_grandparent_obj = relationship('PumsSoc', foreign_keys='SocHierarchy.great_grandparent', lazy='subquery')
soc_obj = relationship('PumsSoc', foreign_keys='SocHierarchy.soc', lazy='subquery')
class GradsYgcd(Grads, GeoId, CipId, DegreeId):
__tablename__ = "grads_ygcd"
median_moe = 3
year = db.Column(db.Integer(), primary_key=True)
grads_total_growth = db.Column(db.Float)
grads_total_rca = db.Column(db.Float)
@classmethod
def get_supported_levels(cls):
return {"cip": CipId.LEVELS, GEO: GeoId.LEVELS, "degree": [ALL]}
class SkillBySoc(BaseOnet, SkillId):
__tablename__ = "skills_by_soc"
median_moe = 1
soc = db.Column(db.String(), primary_key=True)
value = db.Column(db.Float)
value_rca = db.Column(db.Float)
@classmethod
def get_supported_levels(cls):
return {"soc": ["all"], "skill": ["all"]}
class BaseEd(db.Model, BaseModel):
__abstract__ = True
__table_args__ = {"schema": "ed"}
supported_levels = {"year": [ALL]}
source_title = 'Official Cohort Default Rates for Schools'
source_link = 'https://www2.ed.gov/offices/OSFAP/defaultmanagement/cdr.html'
source_org = 'Department of Education'
default_rate = db.Column(db.Float)
num_defaults = db.Column(db.Integer)
num_borrowers = db.Column(db.Integer)
class ImageAttr(db.Model):
__abstract__ = True
image_link = db.Column(db.String)
image_author = db.Column(db.String)
image_path = db.Column(db.String)
url_name = db.Column(db.String)
HEADERS = ["id", "name", "image_link", "image_author", "image_path", "url_name"]
def data_serialize(self):
return [self.id, self.name, self.image_link, self.image_author, self.image_path, self.url_name]
class BeaUse(db.Model, BaseModel):
__table_args__ = {"schema": "bea"}
__tablename__ = 'use'
source_title = 'Use Tables'
source_link = 'http://bea.gov'
source_org = 'Bureau of Economic Analysis'
median_moe = 2
to_filter = [
"TOTCOMOUT", "HS", "ORE", "GFGD", "G", "TOTII", "GFGN", "GSLE", "GFE",
"GSLG", "Other", "Used", "TOTFU", "TOTVA", "TOTINDOUT"
]
no_value_added = to_filter + [
"V001", "V002", "V003", "F010", "F020", "F030", "F040", "F050"
"F100"
]
year = db.Column(db.Integer, primary_key=True)
industry_iocode = db.Column(db.String,
db.ForeignKey(IoCode.id),
primary_key=True)
commodity_iocode = db.Column(db.String,
db.ForeignKey(IoCode.id),
primary_key=True)
value_millions = db.Column(db.Integer)
industry_level = db.Column(db.Integer)
@classmethod
def get_supported_levels(cls):
return {
"industry_iocode": [ALL, "0", "1", "naics", NO_VALUE_ADDED],
"commodity_iocode": [ALL, "naics", NO_VALUE_ADDED],
}
@classmethod
def industry_iocode_filter(cls, level):
if level == ALL:
return True
elif level == "naics":
return ~cls.industry_iocode.in_(cls.to_filter)
elif level == NO_VALUE_ADDED:
return ~cls.industry_iocode.in_(cls.no_value_added)
target_len = int(level)
return cls.industry_level == target_len
@classmethod
def commodity_iocode_filter(cls, level):
if level == ALL:
return True
elif level == NO_VALUE_ADDED:
return ~cls.commodity_iocode.in_(cls.no_value_added)
return ~cls.commodity_iocode.in_(cls.to_filter)
class NISSalariesYuo(NISSalariesBase, UniversityId, IPedsOccId):
__tablename__ = "noninstructional_salaries_yuo"
median_moe = 2
year = db.Column(db.Integer(), primary_key=True)
num_noninstructional_staff_rca = db.Column(db.Float)
@classmethod
def get_supported_levels(cls):
return {
"year": [ALL],
"ipeds_occ": [ALL],
"university": UniversityId.LEVELS
}
