Using Python to Get Out the Vote

Slides from my PyOhio 2015 talk about using Python tools to drive the voter outreach campaign for a recent school levy ballot issue, featuring a breezy introduction to CSVs, SQLAlchemy, and Alembic.

Transcript

1. VOTE USING PYTHON TO GET OUT THE Mike Pirnat ž @mpirnat ž mike.pirnat.com ž
   ž
   ž
   ž
   ž
2. Background
3. Objectives • Manage committee outreach to voters • Mailing list • Door-to-door list • Call lists by school • Minimize wasted contacts • Avoid redundant contacts • Repeatable; re-create database at any time
4. Sources of Data CSVs from Ohio Board of Elections: • List of registered voters • Absentee ballots
5. Sources of Data Excel spreadsheets (XLSX) from levy committee: • School directory data • School district staff list • Early childhood PTA list • Do not contact list
6. Technologies • Excel/Numbers • Python • Standard library: csv, datetime, re • Standard library: SQLite • SQLAlchemy • Alembic
7. Data Model
8. Voter CSV - name - BOE voter id - precinct - ward - address - party - status - birth year - ballot history Student CSV - name - address - parent names - phone - school Absentee Ballot CSV - name - BOE voter id - address - date requested - date returned - challenged PTA CSV - name - address - phone - email - position - kid info Staff CSV - name - school - position - address - phone - email Do Not Contact CSV - name - address
9. Voter CSV - name - BOE voter id - party - status - birth year - ballot history Student CSV - name - school Absentee Ballot CSV - name - BOE voter id - address - date requested - date returned - challenged PTA CSV - name - email - position - kid info Staff CSV - name - school - position - email Do Not Contact CSV - name - address - precinct - ward - address - address - address - address - phone - phone - phone - parent names
10. Voter CSV - name - BOE voter id - party - status - birth year - ballot history Student CSV - name - school Absentee Ballot CSV - name - BOE voter id - address - date requested - date returned - challenged PTA CSV - name - email - position - kid info Staff CSV - name - school - position - email Do Not Contact CSV - name - address - precinct - ward - address - address - address - address - phone - phone - phone - parent names Household - address - precinct - ward - parent names - phone - staff - pta - do not contact
11. Voter CSV - name - BOE voter id - precinct - ward - address - party - status - birth year - ballot history Student CSV - address - parent names - phone Absentee Ballot CSV - name - BOE voter id - address - date requested - date returned - challenged PTA CSV - name - address - phone - email - position - kid info Staff CSV - name - school - position - address - phone - email Do Not Contact CSV - name - address - name - school
12. Voter CSV - name - BOE voter id - precinct - ward - address - party - status - birth year - ballot history Student CSV - address - parent names - phone Absentee Ballot CSV - name - BOE voter id - address - date requested - date returned - challenged PTA CSV - name - address - phone - email - position - kid info Staff CSV - name - school - position - address - phone - email Do Not Contact CSV - name - address - name - school Student - name - school
13. Voter CSV - precinct - ward - address - birth year - ballot history Student CSV - name - address - parent names - phone - school Absentee Ballot CSV - name - address PTA CSV - name - address - phone - email - position - kid info Staff CSV - name - school - position - address - phone - email Do Not Contact CSV - name - address - name - party - status - BOE voter id - date requested - BOE voter id - date returned - challenged
14. Voter CSV - precinct - ward - address - birth year - ballot history Student CSV - name - address - parent names - phone - school Absentee Ballot CSV - name - address PTA CSV - name - address - phone - email - position - kid info Staff CSV - name - school - position - address - phone - email Do Not Contact CSV - name - address - name - party - status - BOE voter id - date requested - BOE voter id - date returned - challenged Voter - name - party - status - BOE voter id - absentee requested - absentee returned - absentee challenged
15. Voter CSV - name - BOE voter id - precinct - ward - address - party - status - birth year Student CSV - name - address - parent names - phone - school Absentee Ballot CSV - name - BOE voter id - address - date requested - date returned - challenged PTA CSV - name - address - phone - email - position - kid info Staff CSV - name - school - position - address - phone - email Do Not Contact CSV - name - address - ballot history
16. Voter CSV - name - BOE voter id - precinct - ward - address - party - status - birth year Student CSV - name - address - parent names - phone - school Absentee Ballot CSV - name - BOE voter id - address - date requested - date returned - challenged PTA CSV - name - address - phone - email - position - kid info Staff CSV - name - school - position - address - phone - email Do Not Contact CSV - name - address - ballot history Ballot - ballot type - election type - date
17. Household - address - precinct - ward - parent names - phone - staff - pta - do not contact Student - name - school Voter - name - party - status - BOE voter id - absentee requested - absentee returned - absentee challenged Ballot - type - date
18. Structure (Briefly)
19. levydb/ alembic/ data/ database.db levydb/ __init__.py db.py ingest_data.py models.py make_a_report.py make_another_report.py ...
20. Model Classes
21. # levydb/models.py... from sqlalchemy import Column, Integer, String, \ Boolean, Date, ForeignKey from sqlalchemy.orm import relationship, backref from sqlalchemy.ext.declarative import declarative_base Base = declarative_base()
22. # levydb/models.py... from sqlalchemy import Column, Integer, String, \ Boolean, Date, ForeignKey from sqlalchemy.orm import relationship, backref from sqlalchemy.ext.declarative import declarative_base Base = declarative_base()
23. # levydb/models.py... from sqlalchemy import Column, Integer, String, \ Boolean, Date, ForeignKey from sqlalchemy.orm import relationship, backref from sqlalchemy.ext.declarative import declarative_base Base = declarative_base()
24. # levydb/models.py... from sqlalchemy import Column, Integer, String, \ Boolean, Date, ForeignKey from sqlalchemy.orm import relationship, backref from sqlalchemy.ext.declarative import declarative_base Base = declarative_base()
25. class Household(Base): __tablename__ = 'households' id = Column(Integer, primary_key=True) precinct = Column(String) ward = Column(String) address = Column(String) mother_last_name = Column(String) mother_first_name = Column(String) father_last_name = Column(String) father_first_name = Column(String) phone = Column(String) do_not_contact = Column(Boolean) early_childhood_pta = Column(Boolean) staff = Column(Boolean) students = relationship('Student', order_by='Student.id', backref='household', cascade='all, delete, delete-orphan') voters = relationship('Voter', order_by='Voter.id', backref='household', cascade='all, delete, delete-orphan')
26. class Household(Base): __tablename__ = 'households' id = Column(Integer, primary_key=True) precinct = Column(String) ward = Column(String) address = Column(String) mother_last_name = Column(String) mother_first_name = Column(String) father_last_name = Column(String) father_first_name = Column(String) phone = Column(String) do_not_contact = Column(Boolean) early_childhood_pta = Column(Boolean) staff = Column(Boolean) students = relationship('Student', order_by='Student.id', backref='household', cascade='all, delete, delete-orphan') voters = relationship('Voter', order_by='Voter.id', backref='household', cascade='all, delete, delete-orphan')
27. class Household(Base): __tablename__ = 'households' id = Column(Integer, primary_key=True) precinct = Column(String) ward = Column(String) address = Column(String) mother_last_name = Column(String) mother_first_name = Column(String) father_last_name = Column(String) father_first_name = Column(String) phone = Column(String) do_not_contact = Column(Boolean) early_childhood_pta = Column(Boolean) staff = Column(Boolean) students = relationship('Student', order_by='Student.id', backref='household', cascade='all, delete, delete-orphan') voters = relationship('Voter', order_by='Voter.id', backref='household', cascade='all, delete, delete-orphan')
28. class Household(Base): __tablename__ = 'households' id = Column(Integer, primary_key=True) precinct = Column(String) ward = Column(String) address = Column(String) mother_last_name = Column(String) mother_first_name = Column(String) father_last_name = Column(String) father_first_name = Column(String) phone = Column(String) do_not_contact = Column(Boolean) early_childhood_pta = Column(Boolean) staff = Column(Boolean) students = relationship('Student', order_by='Student.id', backref='household', cascade='all, delete, delete-orphan') voters = relationship('Voter', order_by='Voter.id', backref='household', cascade='all, delete, delete-orphan')
29. class Voter(Base): __tablename__ = 'voters' id = Column(Integer, primary_key=True) boe_voter_id = Column(Integer) last_name = Column(String) first_name = Column(String) middle_name = Column(String) suffix = Column(String) birth_year = Column(Integer) party = Column(String) status = Column(String) absentee_ballot_requested = Column(Boolean) absentee_ballot_returned = Column(Date) absentee_ballot_challenged = Column(String) household_id = Column(Integer, ForeignKey('households.id')) ballots = relationship('Ballot', order_by='Ballot.id', backref='voter', cascade='all, delete, delete-orphan')
30. class Voter(Base): __tablename__ = 'voters' id = Column(Integer, primary_key=True) boe_voter_id = Column(Integer) last_name = Column(String) first_name = Column(String) middle_name = Column(String) suffix = Column(String) birth_year = Column(Integer) party = Column(String) status = Column(String) absentee_ballot_requested = Column(Boolean) absentee_ballot_returned = Column(Date) absentee_ballot_challenged = Column(String) household_id = Column(Integer, ForeignKey('households.id')) ballots = relationship('Ballot', order_by='Ballot.id', backref='voter', cascade='all, delete, delete-orphan')
31. class Voter(Base): __tablename__ = 'voters' id = Column(Integer, primary_key=True) boe_voter_id = Column(Integer) last_name = Column(String) first_name = Column(String) middle_name = Column(String) suffix = Column(String) birth_year = Column(Integer) party = Column(String) status = Column(String) absentee_ballot_requested = Column(Boolean) absentee_ballot_returned = Column(Date) absentee_ballot_challenged = Column(String) household_id = Column(Integer, ForeignKey('households.id')) ballots = relationship('Ballot', order_by='Ballot.id', backref='voter', cascade='all, delete, delete-orphan')
32. class Voter(Base): __tablename__ = 'voters' id = Column(Integer, primary_key=True) boe_voter_id = Column(Integer) last_name = Column(String) first_name = Column(String) middle_name = Column(String) suffix = Column(String) birth_year = Column(Integer) party = Column(String) status = Column(String) absentee_ballot_requested = Column(Boolean) absentee_ballot_returned = Column(Date) absentee_ballot_challenged = Column(String) household_id = Column(Integer, ForeignKey('households.id')) ballots = relationship('Ballot', order_by='Ballot.id', backref='voter', cascade='all, delete, delete-orphan')
33. class Voter(Base): __tablename__ = 'voters' id = Column(Integer, primary_key=True) boe_voter_id = Column(Integer) last_name = Column(String) first_name = Column(String) middle_name = Column(String) suffix = Column(String) birth_year = Column(Integer) party = Column(String) status = Column(String) absentee_ballot_requested = Column(Boolean) absentee_ballot_returned = Column(Date) absentee_ballot_challenged = Column(String) household_id = Column(Integer, ForeignKey('households.id')) ballots = relationship('Ballot', order_by='Ballot.id', backref='voter', cascade='all, delete, delete-orphan')
34. class Ballot(Base): __tablename__ = 'ballots' id = Column(Integer, primary_key=True) ballot_type = Column(String) election_type = Column(String) election_date = Column(Date) voter_id = Column(Integer, ForeignKey('voters.id'))
35. class Student(Base): __tablename__ = 'students' id = Column(Integer, primary_key=True) last_name = Column(String) first_name = Column(String) school_name = Column(String) household_id = Column(Integer, ForeignKey('households.id'))
36. Creating the Database
37. $ alembic init alembic
38. $ alembic init alembic => alembic/ README env.py script.py.mako versions/ alembic.ini
39. # in alembic.ini... sqlalchemy.url = sqlite:////Users/mike/code/levydb/database.db # in alembic/env.py... from levydb.models import Base target_metadata = Base.metadata
40. $ alembic revision -m "create model"
41. $ alembic revision -m "create model" => alembic/versions/<hash>_create_model.py
42. $ alembic revision -m "create model" => alembic/versions/<hash>_create_model.py $ alembic upgrade <hash> OR $ alembic upgrade head
43. Ingestion
44. # In levydb/ingest_data.py... def ingest_voter_data(): ... def ingest_student_data(): ... def ingest_staff_data(): ... def ingest_pta_data(): ... def ingest_do_not_contact_data(): ...
45. Connecting to the Database
46. # In levydb/db.py... from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from levydb import config Session = sessionmaker() engine = create_engine(config.DB_FILE, echo=config.DB_ECHO) Session.configure(bind=engine) session = Session() # In levydb/ingest_data.py... from levydb.db import engine, session
47. # In levydb/db.py... from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from levydb import config Session = sessionmaker() engine = create_engine(config.DB_FILE, echo=config.DB_ECHO) Session.configure(bind=engine) session = Session() # In levydb/ingest_data.py... from levydb.db import engine, session
48. # In levydb/db.py... from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from levydb import config Session = sessionmaker() engine = create_engine(config.DB_FILE, echo=config.DB_ECHO) Session.configure(bind=engine) session = Session() # In levydb/ingest_data.py... from levydb.db import engine, session
49. # In levydb/db.py... from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from levydb import config Session = sessionmaker() engine = create_engine(config.DB_FILE, echo=config.DB_ECHO) Session.configure(bind=engine) session = Session() # In levydb/ingest_data.py... from levydb.db import engine, session
50. Reading CSVs
51. import csv with open("some_data.csv") as f: reader = csv.reader(f) header_row = reader.next() for row in reader: value0 = row[0] value1 = row[1] ...
52. import csv with open("some_data.csv") as f: reader = csv.reader(f) header_row = reader.next() for row in reader: value0 = row[0] value1 = row[1] ...
53. import csv with open("some_data.csv") as f: reader = csv.reader(f) header_row = reader.next() for row in reader: value0 = row[0] value1 = row[1] ...
54. import csv with open("some_data.csv") as f: reader = csv.reader(f) header_row = reader.next() for row in reader: value0 = row[0] value1 = row[1] ...
55. Inserting Data with SQLAlchemy
56. for row in reader: ... model_object = ModelObject( attr0=value0, attr1=value1, ...) session.add(model_object) session.commit()
57. for row in reader: ... model_object = ModelObject( attr0=value0, attr1=value1, ...) session.add(model_object) session.commit()
58. for row in reader: ... model_object = ModelObject( attr0=value0, attr1=value1, ...) session.add(model_object) session.commit()
59. for row in reader: ... model_object = ModelObject( attr0=value0, attr1=value1, ...) session.add(model_object) session.commit()
60. Ballot Data
61. Election type and date are encoded as header row titles: ...,G_11_04_2014,S_08_05_2014,P_05-06-2014,... The ballot type voted in those elections is specified in each row and can indicate a general or party-specific ballot (eg, in primaries): ...,Y,,D,... Ballot Data
62. Election type and date are encoded as header row titles: ...,G_11_04_2014,S_08_05_2014,P_05-06-2014,... The ballot type voted in those elections is specified in each row and can indicate a general or party-specific ballot (eg, in primaries): ...,Y,,D,... Ballot Data
63. Election type and date are encoded as header row titles: ...,G_11_04_2014,S_08_05_2014,P_05-06-2014,... The ballot type voted in those elections is specified in each row and can indicate a general or party-specific ballot (eg, in primaries): ...,Y,,D,... Ballot Data
64. Election type and date are encoded as header row titles: ...,G_11_04_2014,S_08_05_2014,P_05-06-2014,... The ballot type voted in those elections is specified in each row and can indicate a general or party-specific ballot (eg, in primaries): ...,Y,,D,... Ballot Data
65. Election type and date are encoded as header row titles: ...,G_11_04_2014,S_08_05_2014,P_05-06-2014,... The ballot type voted in those elections is specified in each row and can indicate a general or party-specific ballot (eg, in primaries): ...,Y,,D,... Ballot Data
66. Election type and date are encoded as header row titles: ...,G_11_04_2014,S_08_05_2014,P_05-06-2014,... The ballot type voted in those elections is specified in each row and can indicate a general or party-specific ballot (eg, in primaries): ...,Y,,D,... Ballot Data
67. Election type and date are encoded as header row titles: ...,G_11_04_2014,S_08_05_2014,P_05-06-2014,... The ballot type voted in those elections is specified in each row and can indicate a general or party-specific ballot (eg, in primaries): ...,Y,,D,... Ballot Data
68. # Get ballot data for election_title, ballot_type in zip(header_row[18:65], row[18:65]): if not ballot_type: continue election_type, election_date = parse_election_title(election_title) ballot = Ballot( ballot_type=ballot_type, election_type=election_type, election_date=election_date, voter_id=voter.id) session.add(ballot) session.commit() def parse_election_title(title): election_type, month, day, year = title.replace('-', '_').split('_') election_date = datetime.date(int(year), int(month), int(day)) return election_type, election_date
69. # Get ballot data for election_title, ballot_type in zip(header_row[18:65], row[18:65]): if not ballot_type: continue election_type, election_date = parse_election_title(election_title) ballot = Ballot( ballot_type=ballot_type, election_type=election_type, election_date=election_date, voter_id=voter.id) session.add(ballot) session.commit() def parse_election_title(title): election_type, month, day, year = title.replace('-', '_').split('_') election_date = datetime.date(int(year), int(month), int(day)) return election_type, election_date
70. # Get ballot data for election_title, ballot_type in zip(header_row[18:65], row[18:65]): if not ballot_type: continue election_type, election_date = parse_election_title(election_title) ballot = Ballot( ballot_type=ballot_type, election_type=election_type, election_date=election_date, voter_id=voter.id) session.add(ballot) session.commit() def parse_election_title(title): election_type, month, day, year = title.replace('-', '_').split('_') election_date = datetime.date(int(year), int(month), int(day)) return election_type, election_date
71. # Get ballot data for election_title, ballot_type in zip(header_row[18:65], row[18:65]): if not ballot_type: continue election_type, election_date = parse_election_title(election_title) ballot = Ballot( ballot_type=ballot_type, election_type=election_type, election_date=election_date, voter_id=voter.id) session.add(ballot) session.commit() def parse_election_title(title): election_type, month, day, year = title.replace('-', '_').split('_') election_date = datetime.date(int(year), int(month), int(day)) return election_type, election_date
72. # Get ballot data for election_title, ballot_type in zip(header_row[18:65], row[18:65]): if not ballot_type: continue election_type, election_date = parse_election_title(election_title) ballot = Ballot( ballot_type=ballot_type, election_type=election_type, election_date=election_date, voter_id=voter.id) session.add(ballot) session.commit() def parse_election_title(title): election_type, month, day, year = title.replace('-', '_').split('_') election_date = datetime.date(int(year), int(month), int(day)) return election_type, election_date
73. # Get ballot data for election_title, ballot_type in zip(header_row[18:65], row[18:65]): if not ballot_type: continue election_type, election_date = parse_election_title(election_title) ballot = Ballot( ballot_type=ballot_type, election_type=election_type, election_date=election_date, voter_id=voter.id) session.add(ballot) session.commit() def parse_election_title(title): election_type, month, day, year = title.replace('-', '_').split('_') election_date = datetime.date(int(year), int(month), int(day)) return election_type, election_date
74. # Get ballot data for election_title, ballot_type in zip(header_row[18:65], row[18:65]): if not ballot_type: continue election_type, election_date = parse_election_title(election_title) ballot = Ballot( ballot_type=ballot_type, election_type=election_type, election_date=election_date, voter_id=voter.id) session.add(ballot) session.commit() def parse_election_title(title): election_type, month, day, year = title.replace('-', '_').split('_') election_date = datetime.date(int(year), int(month), int(day)) return election_type, election_date
75. Normalizing &
    Linking Data
76. Board of Elections: School Directory: house_no pre_dir street apartment zip 221 N BAKER ST B1 12345 Data != Data Mailing Street Mailing Zip 221 North Baker Street APT #B-1 12345
77. def normalize_address(address): # Convert to uppercase address = address.upper() # Remove non-alphanumerics/non-space address = re.sub(r'[^A-Z0-9 ]', '', address) # Condense whitespace address = re.sub(r'\s+', ' ', address) # Replace things for pattern, replacement in address_replacements: address = re.sub(pattern, replacement, address) # Condense whitespace again in case we messed it up along the way address = re.sub(r'\s+', ' ', address) # Strip again in case we added extra whitespace somehow address = address.strip() return address
78. def normalize_address(address): # Convert to uppercase address = address.upper() # Remove non-alphanumerics/non-space address = re.sub(r'[^A-Z0-9 ]', '', address) # Condense whitespace address = re.sub(r'\s+', ' ', address) # Replace things for pattern, replacement in address_replacements: address = re.sub(pattern, replacement, address) # Condense whitespace again in case we messed it up along the way address = re.sub(r'\s+', ' ', address) # Strip again in case we added extra whitespace somehow address = address.strip() return address
79. def normalize_address(address): # Convert to uppercase address = address.upper() # Remove non-alphanumerics/non-space address = re.sub(r'[^A-Z0-9 ]', '', address) # Condense whitespace address = re.sub(r'\s+', ' ', address) # Replace things for pattern, replacement in address_replacements: address = re.sub(pattern, replacement, address) # Condense whitespace again in case we messed it up along the way address = re.sub(r'\s+', ' ', address) # Strip again in case we added extra whitespace somehow address = address.strip() return address
80. def normalize_address(address): # Convert to uppercase address = address.upper() # Remove non-alphanumerics/non-space address = re.sub(r'[^A-Z0-9 ]', '', address) # Condense whitespace address = re.sub(r'\s+', ' ', address) # Replace things for pattern, replacement in address_replacements: address = re.sub(pattern, replacement, address) # Condense whitespace again in case we messed it up along the way address = re.sub(r'\s+', ' ', address) # Strip again in case we added extra whitespace somehow address = address.strip() return address
81. def normalize_address(address): # Convert to uppercase address = address.upper() # Remove non-alphanumerics/non-space address = re.sub(r'[^A-Z0-9 ]', '', address) # Condense whitespace address = re.sub(r'\s+', ' ', address) # Replace things for pattern, replacement in address_replacements: address = re.sub(pattern, replacement, address) # Condense whitespace again in case we messed it up along the way address = re.sub(r'\s+', ' ', address) # Strip again in case we added extra whitespace somehow address = address.strip() return address
82. def normalize_address(address): # Convert to uppercase address = address.upper() # Remove non-alphanumerics/non-space address = re.sub(r'[^A-Z0-9 ]', '', address) # Condense whitespace address = re.sub(r'\s+', ' ', address) # Replace things for pattern, replacement in address_replacements: address = re.sub(pattern, replacement, address) # Condense whitespace again in case we messed it up along the way address = re.sub(r'\s+', ' ', address) # Strip again in case we added extra whitespace somehow address = address.strip() return address
83. def normalize_address(address): # Convert to uppercase address = address.upper() # Remove non-alphanumerics/non-space address = re.sub(r'[^A-Z0-9 ]', '', address) # Condense whitespace address = re.sub(r'\s+', ' ', address) # Replace things for pattern, replacement in address_replacements: address = re.sub(pattern, replacement, address) # Condense whitespace again in case we messed it up along the way address = re.sub(r'\s+', ' ', address) # Strip again in case we added extra whitespace somehow address = address.strip() return address
84. address_replacements = [ ('APT', ''), ('SUITE', ''), (' EAST ', ' E '), (' WEST ', ' W '), (' NORTH ', ' N '), (' SOUTH ', ' S '), (r'(\D) ROAD', r'\1 RD'), (r'(\D) AVENUE', r'\1 AVE'), (r'(\D) DRIVE', r'\1 DR'), (r'(\D) CIRCLE', r'\1 CIR'), (r'(\D) TRAIL', r'\1 TRL'), (r'(\D) BOULEVARD', r'\1 BLVD'), (r'(\D) LANE', r'\1 LN'), (r'(\D) COURT', r'\1 CT'), (r'(\D) PARKWAY', r'\1 PKWY'), (r'(\D) PKY', r'\1 PKWY'), (r'(\D) MEADOWS', r'\1 MDWS'), (r'(\D) PLACE', r'\1 PL'), (r'(\D) ROWE', r'\1 ROW'), (r'(\D) CROSSING$', r'\1 XING'), ..., (r'(\s\D) (\d+)$', r'\1\2'), ]
85. # When ingesting voter data for row in reader: ... # Normalize address raw_address = ' '.join( [house_number, direction_prefix, street, apartment]) address = normalize_address(raw_address) # Try to match to an existing household household = session.query(Household).filter_by(address=address).first() if household: voter.household_id = household.id else: # Create new household ...
86. # When ingesting voter data for row in reader: ... # Normalize address raw_address = ' '.join( [house_number, direction_prefix, street, apartment]) address = normalize_address(raw_address) # Try to match to an existing household household = session.query(Household).filter_by(address=address).first() if household: voter.household_id = household.id else: # Create new household ...
87. # When ingesting voter data for row in reader: ... # Normalize address raw_address = ' '.join( [house_number, direction_prefix, street, apartment]) address = normalize_address(raw_address) # Try to match to an existing household household = session.query(Household).filter_by(address=address).first() if household: voter.household_id = household.id else: # Create new household ...
88. # When ingesting voter data for row in reader: ... # Normalize address raw_address = ' '.join( [house_number, direction_prefix, street, apartment]) address = normalize_address(raw_address) # Try to match to an existing household household = session.query(Household).filter_by(address=address).first() if household: voter.household_id = household.id else: # Create new household ...
89. # When ingesting student data... for row in reader: ... # Attach students to households address = normalize_address(raw_address) household = session.query(Household).filter_by(address=address).first() if household: student.household_id = household.id if mother_last_name and not household.mother_last_name: household.mother_last_name = mother_last_name if mother_first_name and not household.mother_first_name: household.mother_first_name = mother_first_name ... if phone and not household.phone: household.phone = phone else: # Create new household ...
90. # When ingesting student data... for row in reader: ... # Attach students to households address = normalize_address(raw_address) household = session.query(Household).filter_by(address=address).first() if household: student.household_id = household.id if mother_last_name and not household.mother_last_name: household.mother_last_name = mother_last_name if mother_first_name and not household.mother_first_name: household.mother_first_name = mother_first_name ... if phone and not household.phone: household.phone = phone else: # Create new household ...
91. # When ingesting student data... for row in reader: ... # Attach students to households address = normalize_address(raw_address) household = session.query(Household).filter_by(address=address).first() if household: student.household_id = household.id if mother_last_name and not household.mother_last_name: household.mother_last_name = mother_last_name if mother_first_name and not household.mother_first_name: household.mother_first_name = mother_first_name ... if phone and not household.phone: household.phone = phone else: # Create new household ...
92. # When ingesting staff, early childhood PTA, do not contact data... for row in reader: ... address = normalize_address(raw_address) household = session.query(Household).filter_by(address=address).first() if not household: continue ...
93. # When ingesting staff, early childhood PTA, do not contact data... for row in reader: ... address = normalize_address(raw_address) household = session.query(Household).filter_by(address=address).first() if not household: continue ...
94. # When ingesting staff, early childhood PTA, do not contact data... for row in reader: ... address = normalize_address(raw_address) household = session.query(Household).filter_by(address=address).first() if not household: continue ...
95. Updating Data with SQLAlchemy
96. A single model object: voter.absentee_ballot_requested = True session.commit() Many rows, without a model object: session.query(Voter).update({ 'absentee_ballot_requested': None, 'absentee_ballot_returned': None, 'absentee_ballot_challenged': None, }) session.commit() Updating the Database
97. Extraction
98. #!/bin/env python def get_data(...): ... def write_csv(...): ... def main(): data = get_data() write_csv(data) if __name__ == "__main__": main()
99. Querying with SQLAlchemy
100. model_object = session.query(ModelObject).first() model_objects = session.query(ModelObject).all() model_objects = session.query(ModelObject)[42:2112] model_object = session.query(ModelObject).\ filter(ModelObject.attribute == value).first() model_objects = session.query(ModelObject).\ filter(ModelObject.attribute == value).\ group_by(ModelObject.other_attribute).\ order_by(ModelObject.third_attribute).\ all()
101. model_object = session.query(ModelObject).first() model_objects = session.query(ModelObject).all() model_objects = session.query(ModelObject)[42:2112] model_object = session.query(ModelObject).\ filter(ModelObject.attribute == value).first() model_objects = session.query(ModelObject).\ filter(ModelObject.attribute == value).\ group_by(ModelObject.other_attribute).\ order_by(ModelObject.third_attribute).\ all()
102. model_object = session.query(ModelObject).first() model_objects = session.query(ModelObject).all() model_objects = session.query(ModelObject)[42:2112] model_object = session.query(ModelObject).\ filter(ModelObject.attribute == value).first() model_objects = session.query(ModelObject).\ filter(ModelObject.attribute == value).\ group_by(ModelObject.other_attribute).\ order_by(ModelObject.third_attribute).\ all() Offset Limit
103. model_object = session.query(ModelObject).first() model_objects = session.query(ModelObject).all() model_objects = session.query(ModelObject)[42:2112] model_object = session.query(ModelObject).\ filter(ModelObject.attribute == value).first() model_objects = session.query(ModelObject).\ filter(ModelObject.attribute == value).\ group_by(ModelObject.other_attribute).\ order_by(ModelObject.third_attribute).\ all()
104. model_object = session.query(ModelObject).first() model_objects = session.query(ModelObject).all() model_objects = session.query(ModelObject)[42:2112] model_object = session.query(ModelObject).\ filter(ModelObject.attribute == value).first() model_objects = session.query(ModelObject).\ filter(ModelObject.attribute == value).\ group_by(ModelObject.other_attribute).\ order_by(ModelObject.third_attribute).\ all()
105. A simple example: household = session.query(Household).\ filter_by(address=address).first() A more complex example: voters = session.query(Voter).join(Household).\ filter(Household.do_not_contact == None).\ filter(Voter.status.in_(allowed_statuses)).\ group_by(Voter.household_id).\ order_by(Voter.last_name, Voter.first_name).\ options(contains_eager(Voter.household)).\ all() More Specifically...
106. Writing CSVs
107. def write_csv(model_objects): with open("report.csv", "w") as f: writer = csv.writer(f) header_row = ["attr0", "attr1", ...] writer.writerow(header_row) for model_object in model_objects: row = [model_object.attr0, model_object.attr1, ...] writer.writerow(row)
108. def write_csv(model_objects): with open("report.csv", "w") as f: writer = csv.writer(f) header_row = ["attr0", "attr1", ...] writer.writerow(header_row) for model_object in model_objects: row = [model_object.attr0, model_object.attr1, ...] writer.writerow(row)
109. def write_csv(model_objects): with open("report.csv", "w") as f: writer = csv.writer(f) header_row = ["attr0", "attr1", ...] writer.writerow(header_row) for model_object in model_objects: row = [model_object.attr0, model_object.attr1, ...] writer.writerow(row)
110. def write_csv(model_objects): with open("report.csv", "w") as f: writer = csv.writer(f) header_row = ["attr0", "attr1", ...] writer.writerow(header_row) for model_object in model_objects: row = [model_object.attr0, model_object.attr1, ...] writer.writerow(row)
111. Reports
112. Targeting Strategies
113. Active Voters • Registered voter • Voter status is active or provisional
114. Recent Voters • Have voted in a recent election... • That’s a non-Presidential election
115. School-Affiliated Voters • Kids enrolled in a school • Parents in early childhood PTA • School district staff
116. Counts Become Costs Total Active Recent School Affiliated Voters 23470 20648 11486 3748 Households 12764 11423 7238 1847
117. Counts Become Costs Total Active Recent School Affiliated Voters 23470 20648 11486 3748 Households 12764 11423 7238 1847 4500?
118. Mailing List
119. Mailing List • Households... • With a registered, active voter... • Who voted in May 2014... • Or who voted in November 2014 and has a student in school... • That aren’t flagged do-not-call
120. allowed_statuses = [VoterStatus.ACTIVE, VoterStatus.PROVISIONAL] def get_voters(): date1 = datetime.date(2014, 5, 6) date2 = datetime.date(2014, 11, 4) voters = session.query(Voter).join(Ballot).\ join(Household).outerjoin(Student).\ filter(Voter.status.in_(allowed_statuses)).\ filter(Household.do_not_contact == None).\ filter( or_( Ballot.election_date == date1, and_( Ballot.election_date == date2, Student.last_name != None ) )).\ group_by(Voter.household_id).\ order_by(Voter.last_name, Voter.first_name).\ options(contains_eager(Voter.household)).\ all() return voters
121. allowed_statuses = [VoterStatus.ACTIVE, VoterStatus.PROVISIONAL] def get_voters(): date1 = datetime.date(2014, 5, 6) date2 = datetime.date(2014, 11, 4) voters = session.query(Voter).join(Ballot).\ join(Household).outerjoin(Student).\ filter(Voter.status.in_(allowed_statuses)).\ filter(Household.do_not_contact == None).\ filter( or_( Ballot.election_date == date1, and_( Ballot.election_date == date2, Student.last_name != None ) )).\ group_by(Voter.household_id).\ order_by(Voter.last_name, Voter.first_name).\ options(contains_eager(Voter.household)).\ all() return voters
122. allowed_statuses = [VoterStatus.ACTIVE, VoterStatus.PROVISIONAL] def get_voters(): date1 = datetime.date(2014, 5, 6) date2 = datetime.date(2014, 11, 4) voters = session.query(Voter).join(Ballot).\ join(Household).outerjoin(Student).\ filter(Voter.status.in_(allowed_statuses)).\ filter(Household.do_not_contact == None).\ filter( or_( Ballot.election_date == date1, and_( Ballot.election_date == date2, Student.last_name != None ) )).\ group_by(Voter.household_id).\ order_by(Voter.last_name, Voter.first_name).\ options(contains_eager(Voter.household)).\ all() return voters
123. allowed_statuses = [VoterStatus.ACTIVE, VoterStatus.PROVISIONAL] def get_voters(): date1 = datetime.date(2014, 5, 6) date2 = datetime.date(2014, 11, 4) voters = session.query(Voter).join(Ballot).\ join(Household).outerjoin(Student).\ filter(Voter.status.in_(allowed_statuses)).\ filter(Household.do_not_contact == None).\ filter( or_( Ballot.election_date == date1, and_( Ballot.election_date == date2, Student.last_name != None ) )).\ group_by(Voter.household_id).\ order_by(Voter.last_name, Voter.first_name).\ options(contains_eager(Voter.household)).\ all() return voters
124. allowed_statuses = [VoterStatus.ACTIVE, VoterStatus.PROVISIONAL] def get_voters(): date1 = datetime.date(2014, 5, 6) date2 = datetime.date(2014, 11, 4) voters = session.query(Voter).join(Ballot).\ join(Household).outerjoin(Student).\ filter(Voter.status.in_(allowed_statuses)).\ filter(Household.do_not_contact == None).\ filter( or_( Ballot.election_date == date1, and_( Ballot.election_date == date2, Student.last_name != None ) )).\ group_by(Voter.household_id).\ order_by(Voter.last_name, Voter.first_name).\ options(contains_eager(Voter.household)).\ all() return voters
125. allowed_statuses = [VoterStatus.ACTIVE, VoterStatus.PROVISIONAL] def get_voters(): date1 = datetime.date(2014, 5, 6) date2 = datetime.date(2014, 11, 4) voters = session.query(Voter).join(Ballot).\ join(Household).outerjoin(Student).\ filter(Voter.status.in_(allowed_statuses)).\ filter(Household.do_not_contact == None).\ filter( or_( Ballot.election_date == date1, and_( Ballot.election_date == date2, Student.last_name != None ) )).\ group_by(Voter.household_id).\ order_by(Voter.last_name, Voter.first_name).\ options(contains_eager(Voter.household)).\ all() return voters
126. allowed_statuses = [VoterStatus.ACTIVE, VoterStatus.PROVISIONAL] def get_voters(): date1 = datetime.date(2014, 5, 6) date2 = datetime.date(2014, 11, 4) voters = session.query(Voter).join(Ballot).\ join(Household).outerjoin(Student).\ filter(Voter.status.in_(allowed_statuses)).\ filter(Household.do_not_contact == None).\ filter( or_( Ballot.election_date == date1, and_( Ballot.election_date == date2, Student.last_name != None ) )).\ group_by(Voter.household_id).\ order_by(Voter.last_name, Voter.first_name).\ options(contains_eager(Voter.household)).\ all() return voters
127. allowed_statuses = [VoterStatus.ACTIVE, VoterStatus.PROVISIONAL] def get_voters(): date1 = datetime.date(2014, 5, 6) date2 = datetime.date(2014, 11, 4) voters = session.query(Voter).join(Ballot).\ join(Household).outerjoin(Student).\ filter(Voter.status.in_(allowed_statuses)).\ filter(Household.do_not_contact == None).\ filter( or_( Ballot.election_date == date1, and_( Ballot.election_date == date2, Student.last_name != None ) )).\ group_by(Voter.household_id).\ order_by(Voter.last_name, Voter.first_name).\ options(contains_eager(Voter.household)).\ all() return voters
128. allowed_statuses = [VoterStatus.ACTIVE, VoterStatus.PROVISIONAL] def get_voters(): date1 = datetime.date(2014, 5, 6) date2 = datetime.date(2014, 11, 4) voters = session.query(Voter).join(Ballot).\ join(Household).outerjoin(Student).\ filter(Voter.status.in_(allowed_statuses)).\ filter(Household.do_not_contact == None).\ filter( or_( Ballot.election_date == date1, and_( Ballot.election_date == date2, Student.last_name != None ) )).\ group_by(Voter.household_id).\ order_by(Voter.last_name, Voter.first_name).\ options(contains_eager(Voter.household)).\ all() return voters
129. allowed_statuses = [VoterStatus.ACTIVE, VoterStatus.PROVISIONAL] def get_voters(): date1 = datetime.date(2014, 5, 6) date2 = datetime.date(2014, 11, 4) voters = session.query(Voter).join(Ballot).\ join(Household).outerjoin(Student).\ filter(Voter.status.in_(allowed_statuses)).\ filter(Household.do_not_contact == None).\ filter( or_( Ballot.election_date == date1, and_( Ballot.election_date == date2, Student.last_name != None ) )).\ group_by(Voter.household_id).\ order_by(Voter.last_name, Voter.first_name).\ options(contains_eager(Voter.household)).\ all() return voters
130. • 4279 households to mail to • Right in our sweet spot AND within budget How Many?
131. Call Lists
132. Call Lists • One list for each school • Households... • With a registered, active voter... • With a student in the school... • With a phone number... • That didn’t request an absentee ballot... • That aren’t flagged do-not-contact... • De-duplicated across lists
133. def make_list(school_name, file_name, households_seen): households = get_households(school_name) write_csv(filename, households, households_seen) def get_households(school_name): households = session.query(Household).join(Voter).join(Student).\ filter(Voter.status.in_(allowed_statuses)).\ filter(Voter.absentee_ballot_requested == None).\ filter(Student.school_name == school_name).\ filter(Household.phone != None).\ filter(Household.do_not_contact == None).\ order_by(Voter.last_name, Voter.first_name).\ all() return households def write_csv(filename, households, households_seen): ... for household in households: if household.id in households_seen: continue row = [...] writer.writerow(row) households_seen[household.id] = household
134. def make_list(school_name, file_name, households_seen): households = get_households(school_name) write_csv(filename, households, households_seen) def get_households(school_name): households = session.query(Household).join(Voter).join(Student).\ filter(Voter.status.in_(allowed_statuses)).\ filter(Voter.absentee_ballot_requested == None).\ filter(Student.school_name == school_name).\ filter(Household.phone != None).\ filter(Household.do_not_contact == None).\ order_by(Voter.last_name, Voter.first_name).\ all() return households def write_csv(filename, households, households_seen): ... for household in households: if household.id in households_seen: continue row = [...] writer.writerow(row) households_seen[household.id] = household
135. def make_list(school_name, file_name, households_seen): households = get_households(school_name) write_csv(filename, households, households_seen) def get_households(school_name): households = session.query(Household).join(Voter).join(Student).\ filter(Voter.status.in_(allowed_statuses)).\ filter(Voter.absentee_ballot_requested == None).\ filter(Student.school_name == school_name).\ filter(Household.phone != None).\ filter(Household.do_not_contact == None).\ order_by(Voter.last_name, Voter.first_name).\ all() return households def write_csv(filename, households, households_seen): ... for household in households: if household.id in households_seen: continue row = [...] writer.writerow(row) households_seen[household.id] = household
136. def make_list(school_name, file_name, households_seen): households = get_households(school_name) write_csv(filename, households, households_seen) def get_households(school_name): households = session.query(Household).join(Voter).join(Student).\ filter(Voter.status.in_(allowed_statuses)).\ filter(Voter.absentee_ballot_requested == None).\ filter(Student.school_name == school_name).\ filter(Household.phone != None).\ filter(Household.do_not_contact == None).\ order_by(Voter.last_name, Voter.first_name).\ all() return households def write_csv(filename, households, households_seen): ... for household in households: if household.id in households_seen: continue row = [...] writer.writerow(row) households_seen[household.id] = household
137. def main(): households_seen = {} make_list(SchoolNames.POKEY_OAKS, '...', households_seen) make_list(SchoolNames.SPRINGFIELD_ELEMENTARY, '...', households_seen) make_list(SchoolNames.HOGWARTS, '...', households_seen) make_list(SchoolNames.MORNING_GLORY, '...', households_seen) make_list(SchoolNames.XAVIERS, '...', households_seen) make_list(SchoolNames.UNSEEN_UNIVERSITY, '...', households_seen)
138. def main(): households_seen = {} make_list(SchoolNames.POKEY_OAKS, '...', households_seen) make_list(SchoolNames.SPRINGFIELD_ELEMENTARY, '...', households_seen) make_list(SchoolNames.HOGWARTS, '...', households_seen) make_list(SchoolNames.MORNING_GLORY, '...', households_seen) make_list(SchoolNames.XAVIERS, '...', households_seen) make_list(SchoolNames.UNSEEN_UNIVERSITY, '...', households_seen)
139. def main(): households_seen = {} make_list(SchoolNames.POKEY_OAKS, '...', households_seen) make_list(SchoolNames.SPRINGFIELD_ELEMENTARY, '...', households_seen) make_list(SchoolNames.HOGWARTS, '...', households_seen) make_list(SchoolNames.MORNING_GLORY, '...', households_seen) make_list(SchoolNames.XAVIERS, '...', households_seen) make_list(SchoolNames.UNSEEN_UNIVERSITY, '...', households_seen)
140. Unreturned Absentee Ballots
141. Unreturned Absentee Ballots • Households with... • One or more registered, active voters... • Who requested an absentee ballot... • But haven’t returned it... • And aren’t flagged do-not-contact
142. def get_voters(): voters = session.query(Voter).join(Household).\ filter(Voter.absentee_ballot_requested != None).\ filter(Voter.absentee_ballot_returned == None).\ filter(Household.do_not_contact == None).\ group_by(Voter.household_id).\ order_by(Voter.last_name, Voter.first_name).\ options(contains_eager(Voter.household)).\ all() return voters
143. def get_voters(): voters = session.query(Voter).join(Household).\ filter(Voter.absentee_ballot_requested != None).\ filter(Voter.absentee_ballot_returned == None).\ filter(Household.do_not_contact == None).\ group_by(Voter.household_id).\ order_by(Voter.last_name, Voter.first_name).\ options(contains_eager(Voter.household)).\ all() return voters
144. Shame List
145. Shame List • Registered, active voters who… • Are school district staff, or... • Are early childhood PTA members, or... • Have a student in school • Who haven’t voted recently
146. voters = session.query(Voter).join(Household).outerjoin(Student).\ filter(Voter.status.in_(allowed_statuses)).\ filter( or_( Student.first_name != None, or_( Household.staff == True, Household.early_childhood_pta == True ) ) ).\ order_by(Voter.last_name, Voter.first_name).\ options(contains_eager(Voter.household)).\ all() shame_voters = [ voter for voter in voters if not any( [ballot for ballot in voter.ballots if end_date >= ballot.election_date >= start_date] ) ]
147. voters = session.query(Voter).join(Household).outerjoin(Student).\ filter(Voter.status.in_(allowed_statuses)).\ filter( or_( Student.first_name != None, or_( Household.staff == True, Household.early_childhood_pta == True ) ) ).\ order_by(Voter.last_name, Voter.first_name).\ options(contains_eager(Voter.household)).\ all() shame_voters = [ voter for voter in voters if not any( [ballot for ballot in voter.ballots if end_date >= ballot.election_date >= start_date] ) ]
148. voters = session.query(Voter).join(Household).outerjoin(Student).\ filter(Voter.status.in_(allowed_statuses)).\ filter( or_( Student.first_name != None, or_( Household.staff == True, Household.early_childhood_pta == True ) ) ).\ order_by(Voter.last_name, Voter.first_name).\ options(contains_eager(Voter.household)).\ all() shame_voters = [ voter for voter in voters if not any( [ballot for ballot in voter.ballots if end_date >= ballot.election_date >= start_date] ) ]
149. voters = session.query(Voter).join(Household).outerjoin(Student).\ filter(Voter.status.in_(allowed_statuses)).\ filter( or_( Student.first_name != None, or_( Household.staff == True, Household.early_childhood_pta == True ) ) ).\ order_by(Voter.last_name, Voter.first_name).\ options(contains_eager(Voter.household)).\ all() shame_voters = [ voter for voter in voters if not any( [ballot for ballot in voter.ballots if end_date >= ballot.election_date >= start_date] ) ]
150. voters = session.query(Voter).join(Household).outerjoin(Student).\ filter(Voter.status.in_(allowed_statuses)).\ filter( or_( Student.first_name != None, or_( Household.staff == True, Household.early_childhood_pta == True ) ) ).\ order_by(Voter.last_name, Voter.first_name).\ options(contains_eager(Voter.household)).\ all() shame_voters = [ voter for voter in voters if not any( [ballot for ballot in voter.ballots if end_date >= ballot.election_date >= start_date] ) ]
151. voters = session.query(Voter).join(Household).outerjoin(Student).\ filter(Voter.status.in_(allowed_statuses)).\ filter( or_( Student.first_name != None, or_( Household.staff == True, Household.early_childhood_pta == True ) ) ).\ order_by(Voter.last_name, Voter.first_name).\ options(contains_eager(Voter.household)).\ all() shame_voters = [ voter for voter in voters if not any( [ballot for ballot in voter.ballots if end_date >= ballot.election_date >= start_date] ) ]
152. voters = session.query(Voter).join(Household).outerjoin(Student).\ filter(Voter.status.in_(allowed_statuses)).\ filter( or_( Student.first_name != None, or_( Household.staff == True, Household.early_childhood_pta == True ) ) ).\ order_by(Voter.last_name, Voter.first_name).\ options(contains_eager(Voter.household)).\ all() shame_voters = [ voter for voter in voters if not any( [ballot for ballot in voter.ballots if end_date >= ballot.election_date >= start_date] ) ]
153. voters = session.query(Voter).join(Household).outerjoin(Student).\ filter(Voter.status.in_(allowed_statuses)).\ filter( or_( Student.first_name != None, or_( Household.staff == True, Household.early_childhood_pta == True ) ) ).\ order_by(Voter.last_name, Voter.first_name).\ options(contains_eager(Voter.household)).\ all() shame_voters = [ voter for voter in voters if not any( [ballot for ballot in voter.ballots if end_date >= ballot.election_date >= start_date] ) ]
154. Other Reports
155. Other Reports • Door-to-door list • School-affiliated absentee list • Praise list • Various counts
156. Concluding Thoughts
157. Next Steps • Post-election analysis • Absentee ballot returns • School-affiliated voter turnout • Party affiliation voter turnout • Refactor & repackage for reuse
158. Thoughts / Advice • Model only what you need to model • Code for repeatability • Shared understanding of query specification is 90% of the work • Expect arbitrary changes on short notice • Use your powers for Good
159. Useful Links • Cuyahoga County Board of Elections Data:
     http://boe.cuyahogacounty.us/en-US/downloads.aspx • USPS Street Suffix Abbreviations:
     http://pe.usps.gov/text/pub28/28apc_002.htm • SQLAlchemy ORM Tutorial:
     http://docs.sqlalchemy.org/en/latest/orm/tutorial.html • Alembic Tutorial:
     http://alembic.readthedocs.org/en/latest/tutorial.html
160. Contact Me Mike Pirnat http://mike.pirnat.com @mpirnat Thanks!