Find unused options in Magento database

magento-find-unused-options.py

import MySQLdb, sys, time

# Lists out unused attribute options in a Magento DB
# This script generates an SQL query you can use but doesn't make any changes 
# to DB on its own

db = MySQLdb.connect(
    host="localhost",
    user="root",
    passwd="",
    db="database"
)

sql_output_file = open('delete-unused-options-by-id.sql', 'w')
list_output_file = open('options_to_be_deleted.txt', 'w')

def column(matrix, i):
    return [row[i] for row in matrix]

def splitColumn(matrix, i):
    values = []
    for row in matrix:
        if isinstance(row[i], basestring):
            for value in row[i].split(','):
                if value.isdigit():
                    values.append(int(value))
    return values

sql_output_file.write("""# Generated %s
DELETE FROM `eav_attribute_option`
WHERE `option_id` IN (""" % time.strftime("%Y-%m-%d %H:%M:%S"))

cur = db.cursor()

cur.execute("""
    SELECT `option`.`attribute_id`, `option`.`option_id`, 
        `attribute`.`attribute_code`, `value`.`value`
    FROM `eav_attribute_option` AS `option`
    INNER JOIN `eav_attribute` AS `attribute`
        ON `attribute`.`attribute_id` = `option`.`attribute_id`
    INNER JOIN `eav_attribute_option_value` AS `value`
        ON `value`.`option_id` = `option`.`option_id`
    """)

unused_options = []

entity_ints = {}
entity_varchars = {}

rows = cur.fetchall()
row_count = len(rows)

for i, option in enumerate(rows):

    option_attribute_id = option[0]
    option_id = option[1]
    option_attribute_code = option[2]
    option_value = option[3]

    if option_attribute_id not in entity_ints:
        # Get product entity ints
        cur.execute("""
            SELECT `value`
            FROM `catalog_product_entity_int`
            WHERE `attribute_id` = %s
        """ % option_attribute_id)
        entity_ints[option_attribute_id] = column(cur.fetchall(), 0)

    if option_attribute_id not in entity_varchars:
        # Get product entity varchars
        cur.execute("""
            SELECT `value`
            FROM `catalog_product_entity_varchar`
            WHERE `attribute_id` = %s
        """ % option_attribute_id)
        entity_varchars[option_attribute_id] = splitColumn(cur.fetchall(), 0)

    used_options = list(set(
        entity_ints[option_attribute_id] + entity_varchars[option_attribute_id]
        ))

    if option_id not in used_options:
        unused_options.append(option_id)
        list_output_file.write("Option #%s (%s)\n" % (option_id, option_attribute_code))

    sys.stdout.write("\r %i / %i rows, %i unused options found" % (
        i,
        row_count,
        len(unused_options)
    ))
    sys.stdout.flush()

sql_output_file.write(
   ','.join(map(str, unused_options)) + ');'
)

sql_output_file.write(
    "\n# Listed %i option IDs for deletion" % len(unused_options)
    )

print "\nWrote %i ids to SQL file for deletion" % len(unused_options)