Пример #1
0
    def __init__(self,
                 user_token,
                 unix_timestamp,
                 bill_id,
                 vote,
                 konverter_klass=ConstituentVoteConverter):
        """
        Inits the class and sets the instance variables

        :param user_token: NOT the user id. The unique token assigned to a user
        :param unix_timestamp: just what it says
        :param bill_id: a bill id with the session suffix i.e. hr123-115
        :param vote: one of for, against or abstain
        :param konverter_klass: a class that can be called for conversion of user_id, bill_id and dttm stuff
        """

        converters = konverter_klass
        valid_votes = ['for', 'against', 'abstain']
        self.constituent_id = converters.user_token_converter(user_token)
        self.dttm = converters.timestamp_converter(unix_timestamp)
        self.bill_id_int = converters.bill_id_converter(bill_id)

        if vote not in valid_votes:
            raise ValueError("A vote must be one of %s" % valid_votes)

        self.vote = vote

        self._db_conn = DB()
Пример #2
0
 def log_transaction(self):
     db = DB()
     db.execute(
         """INSERT INTO transactional_emails
     (email_type, to_user_id, to_email, sent_dttm) VALUES
     (%s, %s, %s, NOW())""",
         ('tally_mail', self.to['id'], self.to['email']))
Пример #3
0
    def update_all_pl_bills(cls):
        db = DB()
        query = ("""SELECT DISTINCT(bill_id) FROM bills""")
        bills = [bills[0] for bills in db.fetch_records(query)]

        for bill in bills:
            print("updating %s" % bill)
            TrackedBill(bill).upsert()
Пример #4
0
    def upsert_uncaptured_votes(cls):
        """Find all legislative votes that have not been saved and save them"""

        query = """
            SELECT DISTINCT most_recent_house_vote_id as vote_id
            FROM bills
            WHERE most_recent_house_vote_id IS NOT NULL

            UNION

            SELECT DISTINCT most_recent_senate_vote_id as vote_id
            FROM bills
            WHERE most_recent_senate_vote_id IS NOT NULL

            EXCEPT

            SELECT DISTINCT vote_id
            -- This is legislative votes, named poorly
            FROM votes
        """

        bills = DB().fetch_records(query)
        for result_tuple in bills:
            vote_id = result_tuple[0]
            print(vote_id)
            lv = LegislativeVotes(vote_id)
            lv.upsert_bill_votes()

            ilv = IndividualLegislatorVote(vote_id)
            ilv.upsert_all_votes()
Пример #5
0
 def _fetch_bill_record(bill_id_str):
     return DB().fetch_one(
         """
         SELECT
             id
         FROM bills
         WHERE bill_id = %s
         """, (bill_id_str, ))
Пример #6
0
    def send_tally_emails(cls, args, klass=None):
        """
        Fetch all constituents and send tally emails after a vote

        Expects to be called from the accompanying send_tally_email_for_user.py
        script. Check there for the defined arguments.

        Optional klass kwarg can be passed in.
        """

        conn = DB()
        klass = klass if klass else CmdSendTallyEmails

        query = "SELECT * FROM constituents"
        constituents = conn.fetch_records(query)

        for cons in constituents:
            print("processing %s" % cons[1])
            klass(cons, args).execute()
Пример #7
0
    def user_token_converter(user_token):
        """Converts a user token into a user_id

        :param user_token: a character hash representing a user to other systems
        """

        # NOTE: this will throw an exception further down. Let the caller handle
        # the very non-exceptional case where a user is not found
        try:
            user_id, user_token = DB().fetch_one(
                """
                SELECT
                    id
                    , user_token
                    FROM constituents
                    WHERE user_token = %s
                """, (user_token, ))
        except TypeError as e:
            user_id = None

        return user_id
Пример #8
0
class ConstituentVoteCreator:
    """
    Given that I have a user token, timestamp, bill_id and vote,
    I should store that as a user_vote in the database
    """
    def __init__(self,
                 user_token,
                 unix_timestamp,
                 bill_id,
                 vote,
                 konverter_klass=ConstituentVoteConverter):
        """
        Inits the class and sets the instance variables

        :param user_token: NOT the user id. The unique token assigned to a user
        :param unix_timestamp: just what it says
        :param bill_id: a bill id with the session suffix i.e. hr123-115
        :param vote: one of for, against or abstain
        :param konverter_klass: a class that can be called for conversion of user_id, bill_id and dttm stuff
        """

        converters = konverter_klass
        valid_votes = ['for', 'against', 'abstain']
        self.constituent_id = converters.user_token_converter(user_token)
        self.dttm = converters.timestamp_converter(unix_timestamp)
        self.bill_id_int = converters.bill_id_converter(bill_id)

        if vote not in valid_votes:
            raise ValueError("A vote must be one of %s" % valid_votes)

        self.vote = vote

        self._db_conn = DB()

    def commit_user_vote(self):
        """
        Upserts user vote to the database.

        :return: id (INT) if tuple has ever been committed to the db. None if not
        """
        insert_tuple = (self.constituent_id, self.bill_id_int, self.vote,
                        self.dttm)
        self._db_conn.execute(
            """
            INSERT INTO user_votes
            (constituent_id, bill_id, vote, vote_collected_dttm)
            VALUES (%s, %s, %s, %s)
            ON CONFLICT (constituent_id, bill_id)
            DO UPDATE
                SET (vote, vote_collected_dttm) = 
                    (EXCLUDED.vote, EXCLUDED.vote_collected_dttm)
                WHERE user_votes.constituent_id = EXCLUDED.constituent_id
                AND user_votes.bill_id = EXCLUDED.bill_id
            """, insert_tuple)

        vote_record_id = self._db_conn.fetch_one(
            """
            SELECT
            id
            FROM user_votes
            WHERE constituent_id = %s
              AND bill_id = %s
              AND vote = %s
              AND vote_collected_dttm = %s
        """, insert_tuple)
        return vote_record_id if vote_record_id else None
Пример #9
0
 def _db(cls):
     return DB()
Пример #10
0
Update the repo on the local file system and then reference the appropriate files. The files have a standardized
format.

This solution is not very scalable but the need to update legislators is not frequent.

Run this with: ENV=development python -m utilities.import_legislators utilities/import_legislators.py
"""

import yaml

from utilities.db import DB

current_leg_file = "/media/brycemcd/bkp/congress/congress-legislators/legislators-current.yaml"
historical_leg_file = "/media/brycemcd/bkp/congress/congress-legislators/legislators-historical.yaml"

db = DB()
query = """
    INSERT INTO legislators_new
    (bioguide_id, lis_id, first_name, last_name, type, party, state, district) VALUES
    (%s, %s, %s, %s, %s, %s, %s, %s)
"""


def insert_into_db_from_yaml_record(record):
    """Given a yaml representation of one legislator, write it to the database"""

    most_recent_term = record['terms'][len(record['terms']) - 1]
    district = None if most_recent_term['type'] == 'sen' else most_recent_term[
        'district']

    insert_tuple = (