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 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']))
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()
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()
def _fetch_bill_record(bill_id_str):
return DB().fetch_one(
"""
SELECT
id
FROM bills
WHERE bill_id = %s
""", (bill_id_str, ))
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()
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
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
def _db(cls):
return DB()
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 = (