### Load or Initialize Dedupe Model

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_example

Loads a pre-existing dedupe model from a settings file if available. If the settings file does not exist, it proceeds to define the data fields and create a new dedupe model. This allows for resuming training or starting from scratch.

```python
settings_file = "mysql_example_settings"

    if os.path.exists(settings_file):
        print("reading from ", settings_file)
        with open(settings_file, "rb") as sf:
            deduper = dedupe.StaticDedupe(sf, num_cores=4)
    else:
        fields = [
            dedupe.variables.String("name"),
            dedupe.variables.String("address", has_missing=True),
            dedupe.variables.ShortString("city", has_missing=True),
            dedupe.variables.ShortString("state", has_missing=True),
            dedupe.variables.ShortString("zip", has_missing=True),
        ]

        deduper = dedupe.Dedupe(fields, num_cores=4)
```

--------------------------------

### Initialize Blocking Process

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_example

Prints a message indicating the start of the blocking process. This is a simple logging statement to track progress.

```python
print("blocking...")
```

--------------------------------

### Prepare Dedupe Training Data

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_example

Fetches data from the MySQL database using the `DONOR_SELECT` query and prepares it for dedupe training. It either loads existing labeled examples from a training file or initiates training with the fetched data. Users can delete the training file to train from scratch.

```python
training_file = "mysql_example_training.json"

        with read_con.cursor() as cur:
            cur.execute(DONOR_SELECT)
            temp_d = {i: row for i, row in enumerate(cur)}

        if os.path.exists(training_file):
            print("reading labeled examples from ", training_file)
            with open(training_file) as tf:
                deduper.prepare_training(temp_d, training_file=tf)
        else:
            deduper.prepare_training(temp_d)

        del temp_d
```

--------------------------------

### Setup Command-Line Argument Parsing for Verbosity

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_example

Configures command-line argument parsing to control the logging verbosity level. Users can specify '-v' multiple times for increasing levels of detail (INFO, DEBUG). This helps in debugging and monitoring the deduplication process.

```python
optp = optparse.OptionParser()
    optp.add_option(
        "-v",
        "--verbose",
        dest="verbose",
        action="count",
        help="Increase verbosity (specify multiple times for more)",
    )
    (opts, args) = optp.parse_args()
    log_level = logging.WARNING
    if opts.verbose:
        if opts.verbose == 1:
            log_level = logging.INFO
        elif opts.verbose >= 2:
            log_level = logging.DEBUG
    logging.getLogger().setLevel(log_level)
```

--------------------------------

### Initiate Console-Based Active Learning

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_example

Starts the active learning loop using dedupe's console_label function. This interactive process prompts the user to label record pairs as duplicates or not, helping to train the dedupe model effectively. Users can use 'y', 'n', or 'u' keys and press 'f' to finish.

```python
print("starting active labeling...")



        dedupe.convenience.console_label(deduper)
```

--------------------------------

### Initialize Dedupe with Fields

Source: https://dedupeio.github.io/dedupe-examples/docs/csv_example

Defines the data fields to be considered by dedupe, specifying their types (e.g., String, Exact) and whether they can contain missing values. This setup is crucial for training.

```python
fields = [
    dedupe.variables.String("Site name"),
    dedupe.variables.String("Address"),
    dedupe.variables.Exact("Zip", has_missing=True),
    dedupe.variables.String("Phone", has_missing=True),
]

deduper = dedupe.Dedupe(fields)
```

--------------------------------

### Establish MySQL Database Connections

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_example

Connects to the MySQL database using provided credentials from a configuration file (`mysql.cnf`). It establishes two connections: one for reading with a server-side cursor (SSDictCursor) for handling large result sets efficiently, and another for writing.

```python
MYSQL_CNF = os.path.abspath(".") + "/mysql.cnf"

    read_con = MySQLdb.connect(
        db="contributions",
        charset="utf8",
        read_default_file=MYSQL_CNF,
        cursorclass=MySQLdb.cursors.SSDictCursor,
    )

    write_con = MySQLdb.connect(
        db="contributions", charset="utf8", read_default_file=MYSQL_CNF
    )
```

--------------------------------

### Import Libraries for Dedupe CSV Example

Source: https://dedupeio.github.io/dedupe-examples/docs/csv_example

Imports necessary libraries for data manipulation, logging, argument parsing, file operations, and the dedupe library itself. It also imports 'unidecode' for character normalization and 're' for regular expressions.

```python
import csv
import logging
import optparse
import os
import re

import dedupe
from unidecode import unidecode
```

--------------------------------

### Save Labeled Training Pairs to Disk

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_example

Saves the labeled training pairs generated by the deduper object to a specified training file. This allows for persistence of training data.

```python
with open(training_file, "w") as tf:
    deduper.write_training(tf)
```

--------------------------------

### Configure Logging Level

Source: https://dedupeio.github.io/dedupe-examples/docs/csv_example

Sets up command-line argument parsing to control the verbosity of dedupe's logging. Users can increase verbosity with the '-v' flag.

```python
optp = optparse.OptionParser()
optp.add_option(
    "-v",
    "--verbose",
    dest="verbose",
    action="count",
    help="Increase verbosity (specify multiple times for more)",
)
(opts, args) = optp.parse_args()
log_level = logging.WARNING
if opts.verbose:
    if opts.verbose == 1:
        log_level = logging.INFO
    elif opts.verbose >= 2:
        log_level = logging.DEBUG
logging.basicConfig(level=log_level)
```

--------------------------------

### Load Pre-trained Dedupe Settings

Source: https://dedupeio.github.io/dedupe-examples/docs/csv_example

Checks if a settings file exists and loads the pre-trained dedupe model from it. This skips the training and active learning steps if a settings file is present, allowing for faster execution on subsequent runs.

```python
if os.path.exists(settings_file):
    print("reading from", settings_file)
    with open(settings_file, "rb") as f:
        deduper = dedupe.StaticDedupe(f)
```

--------------------------------

### Import Dedupe Libraries and MySQL Connector

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_example

Imports necessary libraries for deduplication, JSON handling, logging, argument parsing, time, and connecting to a MySQL database. It specifically uses MySQLdb for database interaction and its cursor classes for efficient data retrieval.

```python
import json
import locale
import logging
import optparse
import os
import time

import dedupe
import dedupe.backport
import MySQLdb
import MySQLdb.cursors
```

--------------------------------

### Load or Prepare Training Data for Dedupe

Source: https://dedupeio.github.io/dedupe-examples/docs/csv_example

Loads existing training data from a file if available, or prepares for new training by reading the dataset. If the training file exists, it's loaded; otherwise, the deduper is prepared with the raw data.

```python
if os.path.exists(training_file):
    print("reading labeled examples from ", training_file)
    with open(training_file, "rb") as f:
        deduper.prepare_training(data_d, f)
else:
    deduper.prepare_training(data_d)
```

--------------------------------

### Create Blocking Map Database Table

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_example

Creates a database table named 'blocking_map' to store blocking keys and donor IDs. It drops the table if it already exists and sets up the schema with appropriate character set and collation.

```python
print("creating blocking_map database")
with write_con.cursor() as cur:
    cur.execute("DROP TABLE IF EXISTS blocking_map")
    cur.execute(
        "CREATE TABLE blocking_map "
        "(block_key VARCHAR(200), donor_id INTEGER) "
        "CHARACTER SET utf8 COLLATE utf8_unicode_ci"
    )

write_con.commit()
```

--------------------------------

### Clean Up Deduper Training Objects

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_example

Removes memory-intensive objects used during the training phase of the dedupe model. This is useful for freeing up resources after training is complete.

```python
deduper.cleanup_training()
```

--------------------------------

### Create Inverted Index for Deduper Fingerprinter

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_example

Iterates through fields identified by the deduper's fingerprinter that require indexing. For each field, it selects distinct non-null values from the 'processed_donors' table and indexes them using the fingerprinter.

```python
print("creating inverted index")

for field in deduper.fingerprinter.index_fields:
    with read_con.cursor() as cur:
        cur.execute(
            "SELECT DISTINCT {field} FROM processed_donors "
            "WHERE {field} IS NOT NULL".format(field=field)
        )
        field_data = (row[field] for row in cur)
        deduper.fingerprinter.index(field_data, field)
```

--------------------------------

### Cluster Records Using Dedupe

Source: https://dedupeio.github.io/dedupe-examples/docs/csv_example

Applies the trained dedupe model to partition the dataset into clusters of duplicate records. A threshold of 0.5 is used to determine the confidence level for assigning records to the same cluster.

```python
print("clustering...")
clustered_dupes = deduper.partition(data_d, 0.5)

print("# duplicate sets", len(clustered_dupes))
```

--------------------------------

### Perform Dedupe Clustering

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_example

Initiates the clustering process using the dedupe library. It scores record pairs obtained from a cursor and clusters them based on a specified threshold. The results are then prepared for writing to an entity map table.

```python
print("clustering...")
clustered_dupes = deduper.cluster(
    deduper.score(record_pairs(read_cur)), threshold=0.5
)

with write_con.cursor() as write_cur:
```

--------------------------------

### Write Blocking Map Table

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_example

Generates `(block_key, donor_id)` tuples using the deduper's fingerprinter and inserts them into the 'blocking_map' table. This process involves reading data from 'processed_donors' and committing the changes.

```python
print("writing blocking map")

with read_con.cursor() as read_cur:
    read_cur.execute(DONOR_SELECT)
    full_data = ((row["donor_id"], row) for row in read_cur)
    b_data = deduper.fingerprinter(full_data)

    with write_con.cursor() as write_cur:

        write_cur.executemany("INSERT INTO blocking_map VALUES (%s, %s)", b_data)

write_con.commit()
```

--------------------------------

### Create Index on Entity Map Table

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_example

Creates an index named 'head_index' on the 'canon_id' column of the 'entity_map' table. This index can improve the performance of queries that involve grouping or filtering by canonical IDs.

```python
with write_con.cursor() as cur:
    cur.execute("CREATE INDEX head_index ON entity_map (canon_id)")

write_con.commit()
read_con.commit()
```

--------------------------------

### Train Dedupe Model and Save Settings

Source: https://dedupeio.github.io/dedupe-examples/docs/csv_example

Trains the dedupe model using the labeled data and learns blocking predicates. The learned weights and predicates are then saved to a settings file for future use, allowing skipping of the training phase.

```python
deduper.train()

with open(training_file, "w") as tf:
    deduper.write_training(tf)

with open(settings_file, "wb") as sf:
    deduper.write_settings(sf)
```

--------------------------------

### Create Temporary Table and Query Deduplicated Donor Data (SQL/Python)

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_example

This snippet first creates a temporary table 'e_map' to consolidate donor information, mapping original donor IDs to canonical IDs. It then executes a query to find the top 10 donors based on total contributions after deduplication, formatting the output using locale settings. This requires a database connection and the 'locale' module in Python.

```sql
CREATE TEMPORARY TABLE e_map \
            SELECT IFNULL(canon_id, donor_id) AS canon_id, donor_id \
            FROM entity_map \
            RIGHT JOIN donors USING(donor_id)
```

```sql
SELECT CONCAT_WS(' ', donors.first_name, donors.last_name) AS name, \
            donation_totals.totals AS totals \
            FROM donors INNER JOIN \
            (SELECT canon_id, SUM(amount) AS totals \
             FROM contributions INNER JOIN e_map \
             USING (donor_id) \
             GROUP BY (canon_id) \
             ORDER BY totals \
             DESC LIMIT 10) \
            AS donation_totals \
            WHERE donors.donor_id = donation_totals.canon_id
```

```python
print("Top Donors (deduped)")
        for row in cur:
            row["totals"] = locale.currency(row["totals"], grouping=True)
            print("%(totals)20s: %(name)s" % row)
```

--------------------------------

### Query Raw Donor Data Without Deduplication (SQL/Python)

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_example

This snippet retrieves the top 10 donors based on total contributions without applying any deduplication logic. It joins the 'donors' and 'contributions' tables, groups by donor, sums their contributions, and orders the results. The output is then formatted using locale settings. This requires a database connection and the 'locale' module in Python.

```sql
SELECT CONCAT_WS(' ', donors.first_name, donors.last_name) as name, \
            SUM(contributions.amount) AS totals \
            FROM donors INNER JOIN contributions \
            USING (donor_id) \
            GROUP BY (donor_id) \
            ORDER BY totals DESC \
            LIMIT 10
```

```python
print("Top Donors (raw)")
        for row in cur:
            row["totals"] = locale.currency(row["totals"], grouping=True)
            print("%(totals)20s: %(name)s" % row)
```

--------------------------------

### Perform Active Learning Labeling

Source: https://dedupeio.github.io/dedupe-examples/docs/csv_example

Initiates an active learning process where dedupe presents the user with the most uncertain record pairs for labeling. The user inputs 'y' for duplicates, 'n' for non-duplicates, and 'u' for uncertain, finishing with 'f'.

```python
print("starting active labeling...")
dupe.console_label(deduper)
```

--------------------------------

### Reset Deduper Fingerprinter Indices

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_example

Frees up memory by removing indices that are no longer needed after the blocking map has been generated. This helps in managing memory usage during the deduplication process.

```python
deduper.fingerprinter.reset_indices()
```

--------------------------------

### Train Deduper Model with Recall

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_example

Trains the dedupe model with a specified recall value, which is the proportion of true duplicate pairs that the learned rules must cover. It also saves the learned settings to a file and cleans up training objects.

```python
deduper.train(recall=0.90)

with open(settings_file, "wb") as sf:
    deduper.write_settings(sf)
```

--------------------------------

### Insert Clustered Duplicates into Entity Map

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_example

Inserts the identified duplicate clusters into the 'entity_map' table. It uses `executemany` to efficiently insert tuples containing 'donor_id', 'canon_id', and 'cluster_score', which are generated by the `cluster_ids` function.

```python
write_cur.executemany(
    "INSERT INTO entity_map VALUES (%s, %s, %s)",
    cluster_ids(clustered_dupes),
)
```

--------------------------------

### Create Unique Index on Blocking Map

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_example

Creates a unique index named 'bm_idx' on the 'block_key' and 'donor_id' columns of the 'blocking_map' table. This index is crucial for efficient lookups and comparisons during the blocking and comparison phases.

```python
print("creating index")
with write_con.cursor() as cur:
    cur.execute("CREATE UNIQUE INDEX bm_idx ON blocking_map (block_key, donor_id)")

write_con.commit()
read_con.commit()
```

--------------------------------

### Preprocess Text Data for Dedupe

Source: https://dedupeio.github.io/dedupe-examples/docs/csv_example

Cleans and normalizes text data by converting to lowercase, removing extra spaces and newlines, stripping quotes, and handling unidecode characters. Missing values are represented as None.

```python
def preProcess(column):
    column = unidecode(column)
    column = re.sub("  +", " ", column)
    column = re.sub("\n", " ", column)
    column = column.strip().strip('"').strip("'").lower().strip()
    if not column:
        column = None
    return column
```

--------------------------------

### Read and Process CSV Data for Dedupe

Source: https://dedupeio.github.io/dedupe-examples/docs/csv_example

Reads data from a CSV file, preprocesses each field using `preProcess`, and stores it in a dictionary where the key is the record ID. Assumes an 'Id' column exists for unique identification.

```python
def readData(filename):
    data_d = {}
    with open(filename) as f:
        reader = csv.DictReader(f)
        for row in reader:
            clean_row = [(k, preProcess(v)) for (k, v) in row.items()]
            row_id = int(row["Id"])
            data_d[row_id] = dict(clean_row)

    return data_d
```

--------------------------------

### Close Database Connections and Measure Execution Time (Python)

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_example

This Python snippet demonstrates how to properly close database connections to prevent resource leaks. It also includes timing logic to measure the total execution time of the script, useful for performance analysis. It requires active database connections ('read_con', 'write_con') and a 'start_time' variable.

```python
read_con.close()
    write_con.close()

    print("ran in", time.time() - start_time, "seconds")
```

--------------------------------

### Set Locale for Number Formatting

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_example

Sets the locale for all category to the user's default setting, which is often used for formatting numbers with appropriate separators (e.g., commas for thousands). This is typically done before displaying numerical data to users.

```python
locale.setlocale(locale.LC_ALL, "")  # for pretty printing numbers
```

--------------------------------

### Define SQL Query for Donor Data

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_example

Defines a standard SQL SELECT statement to retrieve processed donor information, including ID, city, name, zip, state, and address, from the `processed_donors` table. This query is used to fetch data for deduplication.

```sql
DONOR_SELECT = (
        "SELECT donor_id, city, name, zip, state, address " "from processed_donors"
    )
```

--------------------------------

### Create Entity Map Database Table

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_example

Creates a database table named 'entity_map' to store the results of the deduplication process. This table maps each 'donor_id' to a 'canon_id' (canonical ID) and includes a 'cluster_score'. The table is dropped if it already exists, and a primary key is set on 'donor_id'.

```python
write_cur.execute("DROP TABLE IF EXISTS entity_map")

print("creating entity_map database")
write_cur.execute(
    "CREATE TABLE entity_map "
    "(donor_id INTEGER, canon_id INTEGER, "
    " cluster_score FLOAT, PRIMARY KEY(donor_id))"
)
```

--------------------------------

### Define Cluster ID Extractor

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_example

A generator function to extract cluster IDs and scores from clustered duplicate records. It iterates through clusters and their associated scores, yielding each donor ID with its corresponding cluster ID and the confidence score.

```python
def cluster_ids(clustered_dupes):

    for cluster, scores in clustered_dupes:
        cluster_id = cluster[0]
        for donor_id, score in zip(cluster, scores):
            yield donor_id, cluster_id, score
```

--------------------------------

### Select Unique Pairs to Compare (SQL)

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_example

A SQL query snippet designed to select unique pairs of records for comparison. It joins the 'blocking_map' table with itself to find records sharing the same 'block_key' and ensures that each pair is considered only once by filtering `l.donor_id < r.donor_id`. It then joins with 'processed_donors' to retrieve full record details, including city, name, zip, state, and address, formatted as JSON objects.

```sql
select a.donor_id, json_object(‘city’, a.city, ‘name’, a.name, ‘zip’, a.zip, ‘state’, a.state, ‘address’, a.address), b.donor_id, json_object(‘city’, b.city, ‘name’, b.name, ‘zip’, b.zip, ‘state’, b.state, ‘address’, b.address) from (select DISTINCT l.donor_id as east, r.donor_id as west from blocking_map as l INNER JOIN blocking_map as r using (block_key) where l.donor_id < r.donor_id) ids INNER JOIN processed_donors a on ids.east=a.donor_id INNER JOIN processed_donors b on ids.west=b.donor_id
```

--------------------------------

### Define MySQL Record Pair Generator

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_example

A generator function that yields record pairs from a MySQL result set. It parses JSON-encoded records and handles potential errors during JSON loading. This function is crucial for processing large datasets by yielding pairs one by one.

```python
def record_pairs(result_set):
    for i, row in enumerate(result_set):
        a_record_id, a_record, b_record_id, b_record = row
        record_a = (a_record_id, json.loads(a_record))
        record_b = (b_record_id, json.loads(b_record))

        yield record_a, record_b

        if i % 10000 == 0:
            print(i)
```

--------------------------------

### Write Clustered Data to CSV with Cluster ID - Python

Source: https://dedupeio.github.io/dedupe-examples/docs/csv_example

This Python code processes clustered deduplication results and writes the original data back to a CSV file. It adds a 'Cluster ID' column and a 'confidence_score' column for each record. The code reads from an input CSV, enriches each row with cluster information, and writes to an output CSV. Dependencies include the `csv` module.

```python
cluster_membership = {}
for cluster_id, (records, scores) in enumerate(clustered_dupes):
    for record_id, score in zip(records, scores):
        cluster_membership[record_id] = {
            "Cluster ID": cluster_id,
            "confidence_score": score,
        }

with open(output_file, "w") as f_output, open(input_file) as f_input:

    reader = csv.DictReader(f_input)
    fieldnames = ["Cluster ID", "confidence_score"] + reader.fieldnames

    writer = csv.DictWriter(f_output, fieldnames=fieldnames)
    writer.writeheader()

    for row in reader:
        row_id = int(row["Id"])
        row.update(cluster_membership[row_id])
        writer.writerow(row)

```

--------------------------------

### Download and Extract Campaign Contribution Data

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_init_db

Downloads a zip file containing Illinois campaign contribution data from a specified URL and extracts the text file. This script requires the 'urllib' and 'zipfile' modules. It saves the downloaded zip file and extracts the text file if they do not already exist.

```python
import os
import zipfile
from urllib.request import urlopen

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()
```

--------------------------------

### SQL Table Creation and Data Insertion: Contributions Table

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_init_db

This SQL script creates a 'contributions' table and populates it by joining 'raw_table' with the newly created 'donors' table. It converts date strings to DATE types and selects relevant fields, mapping donor information to ensure accurate contribution records.

```SQL
print("creating contributions table")
c.execute(
    "CREATE TABLE contributions "
    "(contribution_id INT, donor_id INT, recipient_id INT, "
    " report_type VARCHAR(24), date_recieved DATE, "
    " loan_amount VARCHAR(12), amount VARCHAR(23), "
    " receipt_type VARCHAR(23), "
    " 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 DATE, report_period_end DATE) "
    "CHARACTER SET utf8 COLLATE utf8_unicode_ci"
)
c.execute(
    "INSERT INTO contributions "
    "SELECT reciept_id, donors.donor_id, committee_id, "
    " report_type, STR_TO_DATE(date_recieved, '%m/%d/%Y'), "
    " loan_amount, amount, "
    " receipt_type, vendor_last_name , "
    " vendor_first_name, vendor_address_1, vendor_address_2, "
    " vendor_city, vendor_state, vendor_zip, description, "
    " election_type, election_year, "
    " STR_TO_DATE(report_period_begin, '%m/%d/%Y'), "
    " STR_TO_DATE(report_period_end, '%m/%d/%Y') "
    "FROM raw_table JOIN donors ON "
    "donors.first_name = TRIM(raw_table.first_name) AND "
    "donors.last_name = TRIM(raw_table.last_name) AND "
    "donors.address_1 = TRIM(raw_table.address_1) AND "
    "donors.address_2 = TRIM(raw_table.address_2) AND "
    "donors.city = TRIM(raw_table.city) AND "
    "donors.state = TRIM(raw_table.state) AND "
    "donors.employer = TRIM(raw_table.employer) AND "
    "donors.occupation = TRIM(raw_table.occupation) AND "
    "donors.zip = TRIM(raw_table.zip)"
)
conn.commit()
```

--------------------------------

### Initialize MySQL Database and Load Raw Data

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_init_db

Connects to a MySQL database, drops existing tables, creates a 'raw_table' with a defined schema, and loads data from a tab-separated text file into 'raw_table'. This requires the 'MySQLdb' library and a 'mysql.cnf' file for connection details. The data is loaded with specific field and line terminators, ignoring the header row.

```python
import MySQLdb
import os
import warnings

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

contributions_txt_file = "Illinois-campaign-contributions.txt"

conn = MySQLdb.connect(
    read_default_file=os.path.abspath(".") + "/mysql.cnf",
    local_infile=1,
    sql_mode="ALLOW_INVALID_DATES",
    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,),
)

```

--------------------------------

### SQL Table Creation and Data Insertion: Donors Table

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_init_db

This SQL code creates a 'donors' table with various personal and employment details, then populates it by selecting distinct records from 'raw_table', trimming whitespace from relevant columns. It ensures unique donor entries.

```SQL
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 "
    "(first_name, last_name, address_1," 
    " address_2, city, state, zip, employer, occupation) "
    "SELECT DISTINCT "
    "TRIM(first_name), TRIM(last_name), TRIM(address_1),  "
    "TRIM(address_2), TRIM(city), TRIM(state), TRIM(zip), "
    "TRIM(employer), TRIM(occupation) "
    "FROM raw_table"
)
conn.commit()
```

--------------------------------

### SQL Index Creation: Donors Table

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_init_db

This SQL snippet creates an index named 'donors_donor_info' on the 'donors' table, specifically on columns used for identifying donor information. This optimizes queries that filter or sort by these columns.

```SQL
print("creating indexes on donors table")
c.execute(
    "CREATE INDEX donors_donor_info ON donors "
    "(last_name, first_name, address_1, address_2, city, "
    " state, zip)"
)
conn.commit()
```

--------------------------------

### SQL Index Creation: Contributions Table

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_init_db

This SQL snippet adds a primary key to the 'contribution_id' column and creates indexes on 'donor_id' and 'recipient_id' in the 'contributions' table. These indexes significantly improve the performance of queries involving these foreign key relationships.

```SQL
print("creating indexes on contributions")
c.execute("ALTER TABLE contributions ADD PRIMARY KEY(contribution_id)")
c.execute("CREATE INDEX donor_idx ON contributions (donor_id)")
c.execute("CREATE INDEX recipient_idx ON contributions (recipient_id)")
conn.commit()
```

--------------------------------

### Clean Raw Data: Set Empty Date Strings to NULL

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_init_db

Updates the 'raw_table' to set empty or non-zero strings in 'report_period_begin' and 'report_period_end' to NULL if their length is less than 10 characters. This standardizes the date fields for better data handling.

```sql
c.execute(
    "UPDATE raw_table SET report_period_begin = NULL WHERE LENGTH(report_period_begin) < 10"
)
c.execute(
    "UPDATE raw_table SET report_period_end = NULL WHERE LENGTH(report_period_end) < 10"
)
```

--------------------------------

### Close Connection and Print Done Message

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_init_db

This snippet demonstrates how to close a database connection and print a confirmation message, indicating the completion of an operation. It assumes a connection object 'conn' is already established.

```python
conn.close()
print("done")
```

--------------------------------

### SQL Table Creation and Data Insertion: Recipients Table

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_init_db

This SQL code creates a 'recipients' table to store recipient names. It then populates this table using 'INSERT IGNORE' to add distinct 'committee_id' and 'committee_name' from 'raw_table', preventing duplicate entries.

```SQL
print("creating recipients table...")
c.execute(
    "CREATE TABLE recipients "
    "(recipient_id INTEGER PRIMARY KEY AUTO_INCREMENT, name VARCHAR(70)) "
    "CHARACTER SET utf8 COLLATE utf8_unicode_ci"
)
c.execute(
    "INSERT IGNORE INTO recipients "
    "SELECT DISTINCT committee_id, committee_name FROM raw_table"
)
conn.commit()
```

--------------------------------

### SQL Index Creation: Processed Donors Table

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_init_db

This SQL snippet creates an index on the 'donor_id' column of the 'processed_donors' table. This index is crucial for efficient lookups and joins involving donor IDs from this processed dataset.

```SQL
c.execute("CREATE INDEX donor_idx ON processed_donors (donor_id)")
```

--------------------------------

### SQL Table Creation: Processed Donors Table

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_init_db

This SQL script creates a 'processed_donors' table by selecting and transforming data from the 'donors' table. It converts text fields to lowercase, concatenates names and addresses, and creates a boolean flag 'person' to indicate if a first and last name exist.

```SQL
c.execute(
    "CREATE TABLE processed_donors AS "
    "(SELECT donor_id, "
    " LOWER(city) AS city, "
    " CASE WHEN (first_name IS NULL AND last_name IS NULL) "
    "      THEN NULL "
    "      ELSE LOWER(CONCAT_WS(' ', first_name, last_name)) "
    " END AS name, "
    " LOWER(zip) AS zip, "
    " LOWER(state) AS state, "
    " CASE WHEN (address_1 IS NULL AND address_2 IS NULL) "
    "      THEN NULL "
    "      ELSE LOWER(CONCAT_WS(' ', address_1, address_2)) "
    " END AS address, "
    " LOWER(occupation) AS occupation, "
    " LOWER(employer) AS employer, "
    " ISNULL(first_name) AS person "
    " FROM donors)"
)
```

--------------------------------

### Clean Raw Data: Remove Records with Short Date Strings

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_init_db

Removes rows from the 'raw_table' where the 'date_recieved' field has a length less than 10 characters. This is a data cleaning step intended for demonstration purposes and may not be suitable for production environments.

```sql
c.execute("DELETE FROM raw_table WHERE LENGTH(date_recieved) < 10")
```

--------------------------------

### SQL Data Transformation: Nullify Empty Strings in Donors

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_init_db

This SQL statement updates the 'donors' table, converting any empty string values ('') in specified columns to NULL. This standardization improves data integrity and consistency, particularly for fields like names and addresses.

```SQL
print("nullifying empty strings in donors")
c.execute(
    "UPDATE donors "
    "SET "
    "first_name = CASE first_name WHEN '' THEN NULL ELSE first_name END, "
    "last_name = CASE last_name WHEN '' THEN NULL ELSE last_name END, "
    "address_1 = CASE address_1 WHEN '' THEN NULL ELSE address_1 END, "
    "address_2 = CASE address_2 WHEN '' THEN NULL ELSE address_2 END, "
    "city = CASE city WHEN '' THEN NULL ELSE city END, "
    "state = CASE state WHEN '' THEN NULL ELSE state END, "
    "employer = CASE employer WHEN '' THEN NULL ELSE employer END, "
    "occupation = CASE occupation WHEN '' THEN NULL ELSE occupation END, "
    "zip = CASE zip WHEN '' THEN NULL ELSE zip END"
)
conn.commit()
```

--------------------------------

### Clean Raw Data: Remove Records Missing Committee ID

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_init_db

Deletes rows from the 'raw_table' where the 'committee_id' field is an empty string. This ensures that all remaining records have a valid committee identifier, which is stated as a requirement.

```sql
c.execute("DELETE FROM raw_table WHERE committee_id=''")
```

--------------------------------

### SQL Data Cleaning: Delete Invalid Committee IDs

Source: https://dedupeio.github.io/dedupe-examples/docs/mysql_init_db

This SQL snippet removes records from 'raw_table' where the 'committee_id' column contains data longer than 9 characters, likely indicating an invalid entry. This is a preliminary step before creating other tables.

```SQL
c.execute("DELETE FROM raw_table WHERE LENGTH( committee_id ) > 9")
conn.commit()
```

=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.