Example #1
0
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()
Example #2
0
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()
Example #3
0
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()
Example #4
0
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()
Example #5
0
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
Example #6
0
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()
Example #7
0
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)
Example #8
0
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')
Example #9
0
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()
Example #10
0
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')
Example #11
0
class Handshakes(sql.Table):
    politician = sql.Reference(Politician, primary=True)
    corporation = sql.String(primary=True)
    pol2corp = sql.Dollars()
    corp2pol = sql.Dollars()
    year = sql.Integer()
Example #12
0
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()
Example #13
0
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()
Example #14
0
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()
Example #15
0
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