import simplejson as json
import web
import smartersql as sql
from settings import db
sql.Table.db = db
class State(sql.Table):
@property
def _uri_(self):
return 'http://watchdog.net/us/%s#it' % self.code.lower()
# states.json
code = sql.String(2, primary=True)
name = sql.String(256)
status = sql.String(256)
wikipedia = sql.URL()
fipscode = sql.String(2)
#senators = sql.Backreference('Politician', 'district')
districts = sql.Backreference('District', 'state', order='name asc')
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 Zip(sql.Table):
zip = sql.String(5, primary=True)
city = sql.String()
state = sql.String(2) #@@ reference state...(@@no AE=Armed Forces Europe)
gini = sql.Float()
# @@other IRS stuff
class Zip4(sql.Table):
zip = sql.String(5, primary=True) #@@references zip, (seems some ZIP+4s aren't in ctyst?)
plus4 = sql.String(4, primary=True)
district = sql.Reference(District)
#--alter table zip4 drop constraint zip4_pkey;
#--COPY zip4 FROM '/home/watchdog/web/data/load/zip4.tsv';
#alter table zip4 add primary key (zip, plus4);
#--alter table zip4 add constraint "zip4_district_fkey" FOREIGN KEY (district) REFERENCES district(name) #@@
#--GRANT ALL ON zip4 TO watchdog;
class GovtrackID(sql.URL):
towhatever = lambda f: (lambda self, x, *a: f(self,
'http://www.govtrack.us/congress/person.xpd?id=' + x, *a))
toxml = towhatever(sql.URL.toxml)
ton3 = towhatever(sql.URL.ton3)
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 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 Politician_FEC_IDs(sql.Table):
politician = sql.Reference(Politician, primary=True)
fec_id = sql.String(primary=True)
# cycle = sql.Integer()
class Interest_Group(sql.Table): # @@capitalization looks dorky..
id = sql.Serial(primary=True)
groupname = sql.String(10)
category_id = sql.String(10) # references category,
longname = sql.String(unique=True)
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 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
class Roll(sql.Table):
id = sql.String(primary=True)
type = sql.String()
question = sql.String()
required = sql.String()
result = sql.String()
bill = sql.Reference(Bill)
@property
def _uri_(self):
return 'http://watchdog.net/r/us/%s#it' % self.id
#@@@@@ DON'T REPEAT YOURSELF
@property
def votes_by_party(self):
"""Get the votes of the political parties for a bill."""
result = db.select(['politician p, vote v'],
what="v.vote, count(v.vote), p.party",
where="v.politician_id = p.id and v.roll_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(['vote'],
where=web.sqlors('politician_id=', members) +
'AND roll_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
class Vote(sql.Table):
roll = sql.Reference(Roll, primary=True)
politician = sql.Reference(Politician, primary=True)
vote = sql.Int2()
class Position(sql.Table):
bill = sql.Reference(Bill, primary=True)
politician = sql.Reference(Politician, primary=True)
vote = sql.Int2()
class Interest_group_bill_support(sql.Table):
bill = sql.Reference(Bill, primary=True)
group = sql.Reference(Interest_Group, primary=True)
support = sql.Int2()
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 Category (sql.Table):
id = sql.String(10, primary=True)
name = sql.String()
industry = sql.String()
sector = sql.String()
class Committee (sql.Table):
id = sql.String(primary=True) # FEC ID
name = sql.String()
treasurer = sql.String()
street1 = sql.String()
street2 = sql.String()
city = sql.String()
state = sql.String()
zip = sql.String()
connected_org_name = sql.String()
candidate_id = sql.String()
type = sql.String()
class Contribution (sql.Table):
id = sql.Serial(primary=True)
fec_record_id = sql.String()
microfilm_loc = sql.String()
report_id = sql.String()
recipient = sql.Reference(Committee)
# contributor
name = sql.String()
street = sql.String()
city = sql.String()
state = sql.String()
zip = sql.String()
occupation = sql.String()
employer = sql.String()
employer_stem = sql.String()
committee = sql.String() # sigh: sometimes committee, sometimes candidate
sent = sql.Date()
amount = sql.Float()
#@@INDEX by employer_stem
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 Earmark_sponsor(sql.Table):
earmark = sql.Reference(Earmark, primary=True)
politician = sql.Reference(Politician, primary=True)
## Lobbyiest stuff:
class lob_organization(sql.Table):
id = sql.Number(primary=True) #@@TODO: design stable ids
name = sql.String()
filings = sql.Backreference('lob_filing', 'org')
class lob_person(sql.Table):
id = sql.Number(primary=True) #@@TODO: design stable ids
prefix = sql.String()
firstname = sql.String()
middlename = sql.String()
lastname = sql.String()
suffix = sql.String()
contact_name = sql.String()
filings = sql.Backreference('lob_filing', 'lobbyist')
class lob_pac(sql.Table):
id = sql.Number(primary=True) #@@TODO: design stable ids
name = sql.String()
filings = sql.Backreference('lob_pac_filings', 'pac')
class lob_filing(sql.Table):
id = sql.Number(primary=True) #Uses xml file number as a stable id.
year = sql.Year()
type = sql.String()
signed_date = sql.Date()
amendment = sql.Boolean()
certified = sql.Boolean()
comments = sql.String()
senate_id = sql.Number()
house_id = sql.Number()
filer_type = sql.String(1)
lobbyist = sql.Reference(lob_person)
org = sql.Reference(lob_organization)
pacs = sql.Backreference('lob_pac_filings', 'filing')
contributions = sql.Backreference('lob_contribution', 'filing', order='amount asc')
@property
def house_url(self):
return "http://disclosures.house.gov/lc/xmlform.aspx?id=%d" % self.id
class lob_contribution(sql.Table):
filing = sql.Reference(lob_filing)
date = sql.Date()
type = sql.String()
contributor = sql.String()
payee = sql.String()
recipient = sql.String()
amount = sql.Dollars()
politician = sql.Reference(Politician)
class lob_pac_filings(sql.Table):
pac = sql.Reference(lob_pac)
filing = sql.Reference(lob_filing)
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 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 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 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 Pol_contacts(sql.Table):
politician = sql.Reference(Politician, primary=True)
contact = sql.String()
contacttype = sql.String(1) # E=email, W=wyr, I=ima, Z=zipauth
captcha = sql.Boolean()
class Pol_phones(sql.Table):
politician = sql.Reference(Politician, primary=True)
city = sql.String(primary=True)
phone1 = sql.String(15)
phone2 = sql.String(15)
tollfree = sql.String(15)
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 Handshakes(sql.Table):
politician = sql.Reference(Politician, primary=True)
corporation = sql.String(primary=True)
pol2corp = sql.Dollars()
corp2pol = sql.Dollars()
year = sql.Integer()
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 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()
def init():
db.query("CREATE VIEW census AS select * from census_meta NATURAL JOIN census_data")
db.query("CREATE INDEX contribution_recipient_id_idx ON contribution (recipient_id)")
db.query("CREATE INDEX contribution_zip_idx ON contribution (zip)")
db.query("CREATE INDEX contribution_empl_stem_idx ON contribution (LOWER(employer_stem))")
db.query("CREATE INDEX contribution_occupation_idx ON contribution (LOWER(occupation));")
db.query("CREATE INDEX contribution_lower_name_zip_idx ON contribution (LOWER(name), zip);")
db.query("ANALYZE contribution;")
db.query("CREATE VIEW curr_politician AS SELECT politician.* FROM politician, congress WHERE politician.id = politician_id AND congress_num='111' AND current_member = 't' ;")
db.query("GRANT ALL on curr_politician TO watchdog;")
try:
db.query("GRANT ALL on census TO watchdog")
except:
pass # group doesn't exist
) is private.
