class Census_Population(sql.Table):
state_id = sql.String(2) # STATE
county_id = sql.String(3) # COUNTY
blockgrp_id = sql.String(1) # BLOCKGRP
block_id = sql.String(4) # BLOCK
district_id = sql.String(2) # CD110
tract_id = sql.String(6) # TRACT
zip_id = sql.String(5) # ZCTA
sumlev = sql.String(16) # SUMLEV
area_land = sql.Integer() # AREALAND
area_land.sql_type = 'bigint'
population = sql.Integer()
class Exempt_Org(sql.Table):
ein = sql.Integer(primary=True)
primary_name = sql.String()
careof_name = sql.String()
street = sql.String()
city = sql.String()
state = sql.String(2)
zip = sql.String()
group_exemption_num = sql.String()
subsection_code = sql.String()
affiliation = sql.String()
classification_code = sql.String()
ruling_date = sql.String()
deductibility_code = sql.String()
foundation_code = sql.String()
activity_code = sql.String()
organization_code = sql.String()
exempt_org_status_code = sql.String()
advance_ruling_expiration = sql.String()
tax_period = sql.String()
asset_code = sql.String()
income_code = sql.String()
filing_requirement_code = sql.String()
accounting_period = sql.String()
asset_amt = sql.BigInteger()
income_amt = sql.BigInteger()
form_990_revenue_amt = sql.BigInteger()
ntee_code = sql.String()
sort_name = sql.String()
class Congress(sql.Table):
politician = sql.Reference(Politician, primary=True)
congress_num = sql.Integer(primary=True)
#district = sql.Reference(District) #@@ renames to district_id
district_id = sql.String(10, primary=True) # Can't make this a reference to district. District table only has CURRENT districts in it.
party = sql.String()
current_member = sql.Boolean()
class Past_Elections(sql.Table):
politician_id = sql.String(256, primary=True) # don't refer `politician` table as lost candidates might not be there
district = sql.Reference(District, primary=True)
year = sql.Year(primary=True)
type = sql.String(10, primary=True) #Gen for general, SpGen for special-general elections
votes_received = sql.Integer()
pct_votes_received = sql.Percentage()
expenditure = sql.Dollars()
class Interest_group_rating(sql.Table):
"""interest group scores for politicians"""
politician = sql.Reference(Politician)
# almanac.json
year = sql.Year()
group = sql.Reference(Interest_Group)
rating = sql.Integer() # typically 0-100
class District(sql.Table):
@property
def _uri_(self):
return 'http://watchdog.net/us/%s#it' % self.name.lower()
# districts.json
name = sql.String(10, primary=True)
district = sql.Integer()
state = sql.Reference(State) #@@renames to state_id
voting = sql.Boolean()
wikipedia = sql.URL()
#politician = sql.Backreference('Politician', 'district')
@property
def districtth(self):
if self.district == 0:
return 'at-large'
else:
return web.nthstr(self.district)
@property
def representatives(self):
ids = [x.id for x in db.select(
'curr_politician', where="district_id = $self.name", vars=locals())]
return Politician.select(where=web.sqlors('id=', ids))
# almanac.json
almanac = sql.URL()
area_sqmi = sql.Number() #@@Square Miles
cook_index = sql.String() #@@integer
poverty_pct = sql.Percentage()
median_income = sql.Dollars()
est_population = sql.Number() # most recent population estimate
est_population_year = sql.Year() # year of the estimate
# shapes.json
outline = sql.String(export=False) #@@geojson
# centers.json
center_lat = sql.Float() #@@ latlong type
center_lng = sql.Float()
zoom_level = sql.Integer()
earmark_per_capita = sql.Float()
class Expenditure (sql.Table):
id = sql.Serial(primary=True)
candidate_name = sql.String()
committee = sql.String()
expenditure_date = sql.Date()
recipient = sql.String()
filer_id = sql.String(10)
report_id = sql.Integer()
amount = sql.String(20)
class Earmark(sql.Table):
id = sql.Integer(primary=True)
house_request = sql.Dollars()
senate_request = sql.Dollars()
final_amt = sql.Dollars()
budget_request = sql.Dollars()
prereduction_amt = sql.Dollars()
description = sql.String()
city = sql.String() # eventually a ref, we hope
county = sql.String()
state = sql.String() #@@ref?
bill = sql.String() #@@ref
bill_section = sql.String()
bill_subsection = sql.String()
project_heading = sql.String()
district = sql.Integer()
presidential = sql.String()
undisclosed = sql.String()
intended_recipient = sql.String()
recipient_stem = sql.String()
notes = sql.String()
sponsors = sql.Backreference('Earmark_sponsor', 'earmark')
class SOI(sql.Table):
#district_id = sql.String(10, primary=True)
district = sql.Reference(District, primary=True)
# irs/soi
bracket_low = sql.Integer(primary=True)
agi = sql.Float()
n_dependents = sql.Float()
n_eitc = sql.Float()
n_filers = sql.Float()
n_prepared = sql.Float()
tot_charity = sql.Float()
tot_eitc = sql.Float()
tot_tax = sql.Float()
avg_dependents = sql.Float()
avg_eitc = sql.Float()
avg_income = sql.Float()
avg_taxburden = sql.Float()
pct_charity = sql.Percentage()
pct_eitc = sql.Percentage()
pct_prepared = sql.Percentage()
class Politician(sql.Table):
@property
def _uri_(self): return 'http://watchdog.net/p/%s#it' % self.id
# politicians.json
id = sql.String(256, primary=True)
district = sql.Reference(District) # Moved to Congress table
wikipedia = sql.URL()
# govtrack.json --@@get from votesmart?
bioguideid = sql.String()
opensecretsid = sql.String()
govtrackid = GovtrackID()
gender = sql.String(1)
birthday = sql.String() #@@date
firstname = sql.String()
middlename = sql.String()
lastname = sql.String()
election_status = sql.String()
def xmllines(self):
sameas = lambda x: ' <owl:sameAs xmlns:owl="http://www.w3.org/2002/07/owl#" \
rdf:resource="%s" />' % x
return [sameas(x) for x in self.akas()]
def n3lines(self, indent):
sameas = lambda x: indent + '<http://www.w3.org/2002/07/owl#sameAs> <%s>;' % x
return [sameas(x) for x in self.akas()]
def akas(self):
if self.bioguideid:
yield 'http://www.rdfabout.com/rdf/usgov/congress/people/' + self.bioguideid
if self.wikipedia:
yield 'http://dbpedia.org/resource/' + self.wikipedia.split('/')[-1]
@property
def name(self):
if hasattr(self, 'nickname') and self.nickname:
return self.nickname + ' ' + self.lastname
else:
return self.fullname
@property
def fullname(self):
return (self.firstname or '') + ' ' + (self.middlename or '') + ' ' + (self.lastname or '')
@property
def title(self):
dist = self.district_id
if self.is_current:
return 'Sen.' if State.where(code=dist) else 'Rep.'
else:
return ''
@property
def handshakes(self):
return db.query('select * from handshakes '
'where politician_id=$self.id '
'order by year desc, pol2corp+corp2pol desc',
vars=locals()).list()
@property
def opponents(self):
return db.select('past_elections',
where='district_id=$self.district_id and politician_id != $self.id',
order='year desc', vars=locals()).list()
@property
def past_votes(self):
return db.select('past_elections',
where='district_id=$self.district_id and politician_id = $self.id',
order='year desc', vars=locals()).list()
@property
def is_current(self):
latest_congress = 111
return latest_congress in self.congresses
#@@ cache query results?
@property
def congresses(self):
return [r.congress_num for r in
db.select('congress',
where='politician_id = $self.id',
order='congress_num',
vars=locals())]
officeurl = sql.URL()
party = sql.String() # Moved to Congress table
religion = sql.String()
n_bills_introduced = sql.Number()
n_bills_enacted = sql.Number()
n_bills_debated = sql.Number()
n_bills_cosponsored = sql.Number()
n_speeches = sql.Number()
words_per_speech = sql.Number()
# voteview.json
icpsrid = sql.Integer()
nominate = sql.Float() #@@
predictability = sql.Percentage()
# earmarks.json
amt_earmark_requested = sql.Dollars(default=0)
n_earmark_requested = sql.Number(default=0)
n_earmark_received = sql.Number(default=0)
amt_earmark_received = sql.Dollars(default=0)
# photos.json
photo_path = sql.URL()
photo_credit_url = sql.URL()
photo_credit_text = sql.String()
# fec
money_raised = sql.Dollars()
pct_spent = sql.Percentage()
pct_self = sql.Percentage()
pct_indiv = sql.Percentage()
pct_pac = sql.Percentage()
pct_labor = sql.Percentage()
pct_instate = sql.Percentage()
pct_smalldonor = sql.Percentage()
# votesmart
nickname = sql.String()
votesmartid = sql.String()
birthplace = sql.String()
education = sql.String()
# punch
chips2008 = sql.Percentage()
progressive2008 = sql.Percentage()
progressiveall = sql.Percentage()
# opensecrets
pct_pac_business = sql.Percentage()
# almanac.json
n_vote_received = sql.Number()
pct_vote_received = sql.Percentage()
last_elected_year = sql.Number()
bills_sponsored = sql.Backreference('Bill', 'sponsor')
earmarks_sponsored = sql.Backreference('Earmark_sponsor', 'politician')
class Handshakes(sql.Table):
politician = sql.Reference(Politician, primary=True)
corporation = sql.String(primary=True)
pol2corp = sql.Dollars()
corp2pol = sql.Dollars()
year = sql.Integer()
class Census_data(sql.Table):
#district_id = sql.String(10, primary=True)
district = sql.Reference(District, primary=True)
internal_key = sql.String(10, primary=True)
census_type = sql.Integer(primary=True)
value = sql.Float()
class Census_meta(sql.Table):
internal_key = sql.String(10, primary=True)
census_type = sql.Integer(primary=True)
hr_key = sql.String(512)
label = sql.String()
class Group_politician_similarity(sql.Table):
group = sql.Reference(Interest_Group, primary=True)
politician = sql.Reference(Politician, primary=True)
agreed = sql.Integer()
total = sql.Integer()
class Bill(sql.Table):
@property
def _uri_(self):
return 'http://watchdog.net/b/%s#it' % self.id
id = sql.String(primary=True)
session = sql.Integer()
type = sql.String(5)
number = sql.Integer()
introduced = sql.Date()
title = sql.String()
sponsor = sql.Reference(Politician) #@@rename to sponsor_id
summary = sql.String()
maplightid = sql.String(10)
interest_group_support = sql.Backreference('Interest_group_bill_support', 'bill', order='support desc')
@property
def name(self):
typemap = {
'h': 'H.R.',
's': 'S.',
'hj': 'H.J.Res.',
'sj': 'S.J.Res.',
'hc': 'H.Con.Res.',
'sc': 'S.Con.Res.',
'hr': 'H.Res.',
'sr': 'S.Res.'
}
return typemap[self.type] + ' ' + str(self.number)
@property
def votes_by_party(self):
"""Get the votes of the political parties for a bill."""
result = db.select(['politician p, position v'],
what="v.vote, count(v.vote), p.party",
where="v.politician_id = p.id and v.bill_id = $self.id "
"AND v.vote is not null",
group="p.party, v.vote",
vars = locals()
).list()
d = {}
for r in result:
d.setdefault(r.party, {})
d[r.party][r.vote] = r.count
return d
@property
def votes_by_caucus(self):
caucuses = json.load(file('import/load/manual/caucuses.json'))
members = sum([x['members'] for x in caucuses], [])
result = db.select(['position'],
where=web.sqlors('politician_id=', members) +
'AND bill_id=' + web.sqlquote(self.id),
vars=locals()
).list()
if not result: return None
votemap = dict((r.politician_id, r.vote) for r in result)
d = {}
for c in caucuses:
cdict = d[c['name']] = {}
for m in c['members']:
v = votemap.get(m)
cdict.setdefault(v, 0)
cdict[v] += 1
return d