Python script to change collation for multiple tables in a database

change_collation

import MySQLdb

# MySQL Connection Parameters
MYSQL_USER=''
MYSQL_PASSWORD=''
MYSQL_DB=''
MYSQL_HOST=''

# Collation Information
OLD_COLLATION=''
NEW_COLLATION=''
NEW_CHARACTER_SET=''

# Let's start with the MySQL connection first
try:
    # The DB connection and cursor global variables
    db = MySQLdb.connect(host=MYSQL_HOST,
                         user=MYSQL_USER,
                         passwd=MYSQL_PASSWORD,
                         db=MYSQL_DB)
    db.autocommit(True)
    cursor = db.cursor()
except MySQLdb.OperationalError:
    print("Could not connect to database. Please check the database configuration.")
    exit(1)


def main():
    """Script for fetching list of tables with old collation and updating it to a new one."""
    old_collation_tables = get_databases()
    change_table_collation(old_collation_tables)


def get_databases():
    """Get the list of tables that match OLD_COLLATION."""
    SELECT_TABLE_QUERY="SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA=%s AND TABLE_COLLATION=%s"
    cursor.execute(SELECT_TABLE_QUERY, (MYSQL_DB, OLD_COLLATION))
    TABLES=cursor.fetchall()
    return TABLES


def change_table_collation(tables):
    """Change collation from OLD_COLLATION to NEW_COLLATION for the given tables."""
    # iterate through the tuple of tables once by one and update collation for each table
    # FIXME: We should be using iteration with executemany but need to figure out how to do it properly.
    for table in tables:
        # FIXME we aren't changing character set as of now because it'll affect foreign keys.
        # FIXME: tables[0] is a hack because we get tuple of tuples in the previous query.
        COLLATION_UPDATE_QUERY="ALTER TABLE {0} COLLATE {1}".format(table[0], NEW_COLLATION)
        try:
            cursor.execute(COLLATION_UPDATE_QUERY)
        except:
            print cursor._last_executed


if __name__ == "__main__":
    main()