Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Using Python to Get Out the Vote

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.

E4c5e3c69566ff80db62a4ab521b6e5a?s=128

Mike Pirnat

August 01, 2015
Tweet

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!