<commit_before>#!/usr/bin/python
# -*- coding: utf-8 -*-
"""
This is a setup script for mysql_example.  It downloads a zip file of
Illinois campaign contributions and loads them into a MySQL database
named 'contributions'.
 
__Note:__ You will need to run this script first before execuing
[mysql_example.py](mysql_example.html).
 
Tables created:
* raw_table - raw import of entire CSV file
* donors - all distinct donors based on name and address
* recipients - all distinct campaign contribution recipients
* contributions - contribution amounts tied to donor and recipients tables
"""

from __future__ import print_function

import os
import zipfile
import warnings

try:
    from urllib2 import urlopen  # Python2
except ImportError:
    from urllib.request import urlopen   # Python3

import MySQLdb

warnings.filterwarnings('ignore', category=MySQLdb.Warning)

contributions_zip_file = 'Illinois-campaign-contributions.txt.zip'
contributions_txt_file = 'Illinois-campaign-contributions.txt'

if not os.path.exists(contributions_zip_file) :
    print('downloading', contributions_zip_file, '(~60mb) ...')
    u = urlopen('https://s3.amazonaws.com/dedupe-data/Illinois-campaign-contributions.txt.zip')
    localFile = open(contributions_zip_file, 'wb')
    localFile.write(u.read())
    localFile.close()

if not os.path.exists(contributions_txt_file) :
    zip_file = zipfile.ZipFile(contributions_zip_file, 'r')
    print('extracting %s' % contributions_zip_file)
    zip_file_contents = zip_file.namelist()
    for f in zip_file_contents:
        if ('.txt' in f):
            zip_file.extract(f)
    zip_file.close()

conn = MySQLdb.connect(read_default_file = os.path.abspath('.') + '/mysql.cnf', 
                       local_infile = 1,
                       db='contributions')
c = conn.cursor()

print('importing raw data from csv...')
c.execute("DROP TABLE IF EXISTS raw_table")
c.execute("DROP TABLE IF EXISTS donors")
c.execute("DROP TABLE IF EXISTS recipients")
c.execute("DROP TABLE IF EXISTS contributions")
c.execute("DROP TABLE IF EXISTS processed_donors")

c.execute("CREATE TABLE raw_table "
          "(reciept_id INT, last_name VARCHAR(70), first_name VARCHAR(35), "
          " address_1 VARCHAR(35), address_2 VARCHAR(36), city VARCHAR(20), "
          " state VARCHAR(15), zip VARCHAR(11), report_type VARCHAR(24), "
          " date_recieved VARCHAR(10), loan_amount VARCHAR(12), "
          " amount VARCHAR(23), receipt_type VARCHAR(23), "
          " employer VARCHAR(70), occupation VARCHAR(40), "
          " vendor_last_name VARCHAR(70), vendor_first_name VARCHAR(20), "
          " vendor_address_1 VARCHAR(35), vendor_address_2 VARCHAR(31), "
          " vendor_city VARCHAR(20), vendor_state VARCHAR(10), "
          " vendor_zip VARCHAR(10), description VARCHAR(90), "
          " election_type VARCHAR(10), election_year VARCHAR(10), "
          " report_period_begin VARCHAR(10), report_period_end VARCHAR(33), "
          " committee_name VARCHAR(70), committee_id VARCHAR(37)) "
          "CHARACTER SET utf8 COLLATE utf8_unicode_ci")


conn.commit()

c.execute("LOAD DATA LOCAL INFILE %s INTO TABLE raw_table "
          "FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' " 
          "IGNORE 1 LINES "
          "(reciept_id, last_name, first_name, "
          " address_1, address_2, city, state, "
          " zip, report_type, date_recieved, "
          " loan_amount, amount, receipt_type, "
          " employer, occupation, vendor_last_name, "
          " vendor_first_name, vendor_address_1, "
          " vendor_address_2, vendor_city, vendor_state, "
          " vendor_zip, description, election_type, "
          " election_year, "
          " report_period_begin, report_period_end, "
          " committee_name, committee_id, @dummy)",
          (contributions_txt_file,))
conn.commit()

print('creating donors table...')
c.execute("CREATE TABLE donors "
          "(donor_id INTEGER PRIMARY KEY AUTO_INCREMENT, "
          " last_name VARCHAR(70), first_name VARCHAR(35), "
          " address_1 VARCHAR(35), address_2 VARCHAR(36), "
          " city VARCHAR(20), state VARCHAR(15), "
          " zip VARCHAR(11), employer VARCHAR(70), "
          " occupation VARCHAR(40)) "
          "CHARACTER SET utf8 COLLATE utf8_unicode_ci")
c.execute("INSERT INTO donors "<commit_msg>Remove records with missing date_received values.<commit_after>#!/usr/bin/python
# -*- coding: utf-8 -*-
"""
This is a setup script for mysql_example.  It downloads a zip file of
Illinois campaign contributions and loads them into a MySQL database
named 'contributions'.
 
__Note:__ You will need to run this script first before execuing
[mysql_example.py](mysql_example.html).
 
Tables created:
* raw_table - raw import of entire CSV file
* donors - all distinct donors based on name and address
* recipients - all distinct campaign contribution recipients
* contributions - contribution amounts tied to donor and recipients tables
"""

from __future__ import print_function

import os
import zipfile
import warnings

try:
    from urllib2 import urlopen  # Python2
except ImportError:
    from urllib.request import urlopen   # Python3

import MySQLdb

warnings.filterwarnings('ignore', category=MySQLdb.Warning)

contributions_zip_file = 'Illinois-campaign-contributions.txt.zip'
contributions_txt_file = 'Illinois-campaign-contributions.txt'

if not os.path.exists(contributions_zip_file) :
    print('downloading', contributions_zip_file, '(~60mb) ...')
    u = urlopen('https://s3.amazonaws.com/dedupe-data/Illinois-campaign-contributions.txt.zip')
    localFile = open(contributions_zip_file, 'wb')
    localFile.write(u.read())
    localFile.close()

if not os.path.exists(contributions_txt_file) :
    zip_file = zipfile.ZipFile(contributions_zip_file, 'r')
    print('extracting %s' % contributions_zip_file)
    zip_file_contents = zip_file.namelist()
    for f in zip_file_contents:
        if ('.txt' in f):
            zip_file.extract(f)
    zip_file.close()

conn = MySQLdb.connect(read_default_file = os.path.abspath('.') + '/mysql.cnf', 
                       local_infile = 1,
                       db='contributions')
c = conn.cursor()

print('importing raw data from csv...')
c.execute("DROP TABLE IF EXISTS raw_table")
c.execute("DROP TABLE IF EXISTS donors")
c.execute("DROP TABLE IF EXISTS recipients")
c.execute("DROP TABLE IF EXISTS contributions")
c.execute("DROP TABLE IF EXISTS processed_donors")

c.execute("CREATE TABLE raw_table "
          "(reciept_id INT, last_name VARCHAR(70), first_name VARCHAR(35), "
          " address_1 VARCHAR(35), address_2 VARCHAR(36), city VARCHAR(20), "
          " state VARCHAR(15), zip VARCHAR(11), report_type VARCHAR(24), "
          " date_recieved VARCHAR(10), loan_amount VARCHAR(12), "
          " amount VARCHAR(23), receipt_type VARCHAR(23), "
          " employer VARCHAR(70), occupation VARCHAR(40), "
          " vendor_last_name VARCHAR(70), vendor_first_name VARCHAR(20), "
          " vendor_address_1 VARCHAR(35), vendor_address_2 VARCHAR(31), "
          " vendor_city VARCHAR(20), vendor_state VARCHAR(10), "
          " vendor_zip VARCHAR(10), description VARCHAR(90), "
          " election_type VARCHAR(10), election_year VARCHAR(10), "
          " report_period_begin VARCHAR(10), report_period_end VARCHAR(33), "
          " committee_name VARCHAR(70), committee_id VARCHAR(37)) "
          "CHARACTER SET utf8 COLLATE utf8_unicode_ci")


conn.commit()

c.execute("LOAD DATA LOCAL INFILE %s INTO TABLE raw_table "
          "FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' " 
          "IGNORE 1 LINES "
          "(reciept_id, last_name, first_name, "
          " address_1, address_2, city, state, "
          " zip, report_type, date_recieved, "
          " loan_amount, amount, receipt_type, "
          " employer, occupation, vendor_last_name, "
          " vendor_first_name, vendor_address_1, "
          " vendor_address_2, vendor_city, vendor_state, "
          " vendor_zip, description, election_type, "
          " election_year, "
          " report_period_begin, report_period_end, "
          " committee_name, committee_id, @dummy)",
          (contributions_txt_file,))

# Remove the very few records that mess up the demo 
# (demo purposes only! Don't do something like this in production)
c.execute("DELETE FROM raw_table WHERE LENGTH(date_recieved) < 10")
conn.commit()



print('creating donors table...')
c.execute("CREATE TABLE donors "
          "(donor_id INTEGER PRIMARY KEY AUTO_INCREMENT, "
          " last_name VARCHAR(70), first_name VARCHAR(35), "
          " address_1 VARCHAR(35), address_2 VARCHAR(36), "
          " city VARCHAR(20), state VARCHAR(15), "
          " zip VARCHAR(11), employer VARCHAR(70), "
          " occupation VARCHAR(40)) "
          "CHARACTER SET utf8 COLLATE utf8_unicode_ci")
c.execute("INSERT INTO donors "
{'fraction_non_alphanumeric': 0.1081414935429534, 'fraction_numerical': 0.022010106681639528, 'mean_word_length': 3.3980246913580245, 'pattern_counts': {'":': 0, '<': 4, '<?xml version=': 0, '>': 3, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3688428', 'n_tokens_mistral': 2919, 'n_tokens_neox': 2638, 'n_words': 695}